DEV Community

MagnoRSantos
MagnoRSantos

Posted on

Evitando conversão implícita no Sql Server com Python usando o módulo pyodbc

O python facilita muito trabalhar com processos de ingestão de dados em tabelas no Sql Server usando o módulo pyodbc, conforme abaixo:

# -*- coding: utf-8 -*-

import os
import dotenv
import pyodbc as po
from datetime import datetime


## Carrega os valores do .env que contem os dados sensíveis de conexao
dotenv.load_dotenv()


## funcao de formacao da connString Sql
def strConnectionSql():

    #variaveis de conexao SQL
    server   = os.getenv("SERVER_TARGET_SQL")
    port     = os.getenv("PORT_TARGET_SQL")
    database = os.getenv("DATABASE_TARGET_SQL")
    username = os.getenv("USERNAME_TARGET_SQL")
    password = os.getenv("PASSWORD_TARGET_SQL")

    strConnection = 'DRIVER={{ODBC Driver 17 for SQL Server}};\
        SERVER={v_server};\
        PORT={v_port};\
        DATABASE={v_database};\
        UID={v_username};\
        PWD={v_password}'.format(v_server = server, v_port = port, v_database = database, v_username = username, v_password = password)

    return strConnection


def gravaDadosSqlTable():

    try:
        ## Connection string
        connString = str(strConnectionSql())
        cnxn = po.connect(connString)
        cnxn.autocommit = False
        cursor = cnxn.cursor()

        RowCount = 0

        ## sql insert 
        sqlcmd = """  
        INSERT INTO [dbo].[Cliente] 
            (
                [Id], [Codigo], [Cidade], [Estado]
            ) VALUES (?, ?, ?, ?);  
        """

        listSqlSource = [  (1, 'A12345', 'São Paulo', 'SP'), 
                    (2, 'A12346', 'São Paulo', 'SP'),
                    (3, 'A12347', 'Campinas', 'SP'),
                    (4, 'B12348', 'Curitiba', 'PR'),
                    (5, 'C12349', 'Rio de Janeiro', 'RJ')

        ]


        for params in listSqlSource:
            cursor.execute(sqlcmd, params)
            RowCount = RowCount + cursor.rowcount


    except Exception as e:
        msgException = "Error: {0}".format(e)
        msgLog = 'Fim inserção de dados no destino [Erro]: {0}'.format(msgException)
        print(msgLog)
        cnxn.rollback()

    else:
        cnxn.commit()

    finally:
        ## Close the database connection
        cursor.close()
        del cursor
        cnxn.close()
        msgLog = 'Quantidade de Registros Inseridos no destino: {0}'.format(RowCount)
        print(msgLog)


## funcao inicial criada para iniciar as chamadas das demais funcoes
def main():
    ## log do início da aplicacao
    datahora = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    msgLog = '\n***** Início da aplicação: {0}'.format(datahora)
    print(msgLog)

    gravaDadosSqlTable()

    ## log do final da aplicacao
    datahora = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    msgLog = '***** Final da aplicação: {0}'.format(datahora)
    print(msgLog)

#### inicio da aplicacao ####
if __name__ == "__main__":
    ## chamada da função inicial
    main()

Enter fullscreen mode Exit fullscreen mode

Porém analisando questões de performance no processo de insert acima é identificado que ocorrem conversões implícitas no Sql Server, pois por padrão o pyodbc envia os dados no formato nvarchar, e caso suas tabelas foram criadas com tipos de dados int, char, varchar entre outros essa conversão afeta a performance no Sql Server.

Abaixo como é apresentado a instrução de insert no Sql Server:

<?query --
(@P1 nvarchar(1),@P2 nvarchar(6),@P3 nvarchar(9),@P4 nvarchar(2))  
        INSERT INTO [dbo].[Teste] 
            (
                [Id], [Codigo], 
                [Cidade], [Estado]
            ) VALUES (@P1, @P2, @P3, @P4);  

--?>
Enter fullscreen mode Exit fullscreen mode

Pesquisando sobre como podemos enviar os dados ao Sql Server no formato de tipo de dado esperado pelas colunas, encontrei uma forma que acho que nem todos sabem, que é o uso de cursor.setinputsizes que permite definir os campos do insert conforme os tipos de dados de cada coluna da sua tabela de destino.
Aqui o link que encontrei essa informação: Clique aqui

Exemplo:

cursor.setinputsizes([( po.SQL_INTEGER), (po.SQL_VARCHAR, 10, 0), \
                                (po.SQL_VARCHAR, 60, 0), (po.SQL_CHAR, 2, 0)])
Enter fullscreen mode Exit fullscreen mode

Abaixo o script python ajustado para envio dos dados no formato correto de cada coluna da tabela evitando conversão implícita:

# -*- coding: utf-8 -*-

import os
import dotenv
import pyodbc as po
from datetime import datetime


## Carrega os valores do .env que contem os dados sensíveis de conexao
dotenv.load_dotenv()


## funcao de formacao da connString Sql
def strConnectionSql():

    #variaveis de conexao SQL
    server   = os.getenv("SERVER_TARGET_SQL")
    port     = os.getenv("PORT_TARGET_SQL")
    database = os.getenv("DATABASE_TARGET_SQL")
    username = os.getenv("USERNAME_TARGET_SQL")
    password = os.getenv("PASSWORD_TARGET_SQL")

    strConnection = 'DRIVER={{ODBC Driver 17 for SQL Server}};\
        SERVER={v_server};\
        PORT={v_port};\
        DATABASE={v_database};\
        UID={v_username};\
        PWD={v_password}'.format(v_server = server, v_port = port, v_database = database, v_username = username, v_password = password)

    return strConnection


def gravaDadosSqlTable():

    try:
        ## Connection string
        connString = str(strConnectionSql())
        cnxn = po.connect(connString)
        cnxn.autocommit = False
        cursor = cnxn.cursor()

        RowCount = 0

        ## sql insert 
        sqlcmd = """  
        INSERT INTO [dbo].[Cliente] 
            (
                [Id], [Codigo], [Cidade], [Estado]
            ) VALUES (?, ?, ?, ?);  
        """

        listSqlSource = [  (1, 'A12345', 'São Paulo', 'SP'), 
                    (2, 'A12346', 'São Paulo', 'SP'),
                    (3, 'A12347', 'Campinas', 'SP'),
                    (4, 'B12348', 'Curitiba', 'PR'),
                    (5, 'C12349', 'Rio de Janeiro', 'RJ')

        ]

        ## define os tipos de dados de inputs no sql server
        # isso evita que o Sql Server realize conversão implícita que afeta a performance
        # foi necessário devido o pyodbc por padrão mandar dados no format nvarchar causando a conversão implícita
        cursor.setinputsizes([( po.SQL_INTEGER), (po.SQL_VARCHAR, 10, 0), \
                                (po.SQL_VARCHAR, 60, 0), (po.SQL_CHAR, 2, 0)])

        for params in listSqlSource:
            cursor.execute(sqlcmd, params)
            RowCount = RowCount + cursor.rowcount


    except Exception as e:
        msgException = "Error: {0}".format(e)
        msgLog = 'Fim inserção de dados no destino [Erro]: {0}'.format(msgException)
        print(msgLog)
        cnxn.rollback()

    else:
        cnxn.commit()

    finally:
        ## Close the database connection
        cursor.close()
        del cursor
        cnxn.close()
        msgLog = 'Quantidade de Registros Inseridos no destino: {0}'.format(RowCount)
        print(msgLog)


## funcao inicial criada para iniciar as chamadas das demais funcoes
def main():
    ## log do início da aplicacao
    datahora = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    msgLog = '\n***** Início da aplicação: {0}'.format(datahora)
    print(msgLog)

    gravaDadosSqlTable()

    ## log do final da aplicacao
    datahora = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    msgLog = '***** Final da aplicação: {0}'.format(datahora)
    print(msgLog)

#### inicio da aplicacao ####
if __name__ == "__main__":
    ## chamada da função inicial
    main()

Enter fullscreen mode Exit fullscreen mode

Abaixo como está a execução do insert no Sql Server:

<?query --
(@P1 int,@P2 varchar(6),@P3 varchar(9),@P4 char(2))  
        INSERT INTO [dbo].[Teste] 
            (
                [Id], [Codigo], 
                [Cidade], [Estado]
            ) VALUES (@P1, @P2, @P3, @P4);  

--?>
Enter fullscreen mode Exit fullscreen mode

Inicialmente pode não parecer que isso faz alguma diferença, porém uma comparação simples dos planos de execução sem conversão implícita e com conversão implícita apresenta planos de execução diferentes e com menos custo de execução do Sql Server quando não ocorre a conversão, isso é válido tanto para instruções de insert, quanto select, update e delete.

Documentação oficial pyodbc: https://github.com/mkleehammer/pyodbc/wiki/Cursor#setinputsizeslist_of_value_tuples

Artigo detalhado sobre conversão implícita: https://portosql.wordpress.com/2018/10/25/os-perigos-da-conversao-implicita-1/

Bom, por enquanto é isso. Espero que ajude a mais alguém.
Fiquem com Deus.

Top comments (0)