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.
Top comments (0)