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()

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);  

--?>

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)])

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()

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);  

--?>

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.

Blog

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

MagnoRSantos

Join Our Newsletter. No Spam, Only the good stuff.

Related