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

MagnoRSantos - Aug 19 - - Dev Community

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.

Image
The Ultimate Guide to Data Analytics
Image
Why Should You Use Shopify One Page Checkout in 2024?

hireshopifydevelopers, shopifydevelopmentcompany
Image
Buy Verified Paxful Account

webdev, javascript, beginners, programming
Image
reverb + websocket
Image
Augmented Reality navigation

mixedreality, powerplatform
Image
Augmented Reality Features of PlugXR

mixedreality, lowcode, node, news
Image
Continue in C#: Tutorial + Examples

csharp, dotnet, programming, tutorial
Image
IT Infrastructure Consulting for Enhanced Cybersecurity
Image
मैं पेटीएम से कैसे शिकायत कर सकता हूं?
Image
How To Smoke Your First Cigar

cigar, wedding, c
Image
You Can Use AI In Technical Writing! Here's how:

writing, devrel, documentation
Image
मैं पेटीएम से कैसे शिकायत कर सकता हूं?
Image
Build A Transcription App with Strapi, ChatGPT, & Whisper: Part 1

ai, nextjs, whisper
Image
Benefits of Using an AC Cover
Image
Buy Verified Paxful Account

webdev, javascript, beginners, programming
Image
Zed에서 VSCode로 돌아옴
Image
The Wise Bucks - Stock Market Course in Chandigarh
Image
The best static site generators in 2024🚀🔥

webdev, beginners, javascript, programming
Image
Top 20 API Development Tools for Back-end Developers

api, development, tooling, backenddevelopment
. . . . . .
Terabox Video Player