Python e SQL Server, uma combinação perfeita!

E ai meu amigo, como que anda essa força? Tem acompanhando minhas postagens? Caso positivo, o que tem achado? Você sabia que seu comentário é muito importante para o direcionamento dos nossos estudos?

Hoje trago para você uma combinação bem interessante. Nesse post vou falar sobre como é possível realizar as operações de DDL e DML no Microsoft SQL Server tudo através do Python, claro que contando com a ajuda de duas importantes bibliotecas, a PyODBC e a SQLAlchemy.

Confesso para você que quando comecei a estudar e trabalhar com Python, nunca explorei a comunicação com o Microsoft SQL Server diretamente com o Python, dava uma volta absurda. Manipulava os dados pelo Python, depois transformava o DataFrame Pandas para um DataFrame Spark e a partir desse momento realizava a inserção no Microsoft SQL Server.

Em outros estudos pessoais, direcionei para esse combinação, Microsoft SQL Server e comecei a manipular os dados via Python, porém sempre fazendo registro a registro, nunca manda um bloco grande de dados, o que tornava o processo bem lento quando existiam muitos registros.

Porém, avançando nos estudos, tomei conhecimento de outros métodos do Pandas que mudaram o rumo do meu trabalho. Trago primeiro a abordagem de envio de registro a registro e em seguida modo envio de blocos de registros diretamente para o banco.

Vamos prosseguindo que irei comentar cada um deles.

Primeiro vamos aprender como realizamos a comunicação e em seguida, as operações de DDL e DML. Mãos a massa!

Para iniciar, vamos importar as bibliotecas necessárias.

import pandas as pd 
import pyodbc
from sqlalchemy import __version__ as sa_version, create_engine, text
import urllib

Feito isso, o próximo passo é definir como será feita a conexão com o Microsoft SQL Server.

# Parâmetros de conexão 
server = 'NOME_DO_SERVIDOR'
database = 'NOME_DO_DATABASE'
username = 'NOME_DO_USUARIO'
password = 'SENHA'

# Criação da String de Conexão
quoted = urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

Beleza! Conexão concluída, próxima etapa será importar um arquivo qualquer para seguir com a nosso explicação.

Como no estudo anterior sobre mercado financeiro geramos alguns arquivos e para não perder tempo vou usa-los como base. Caso você não tenha assistido, basta clicar no link abaixo e conferir, tenho certeza que vai adorar!

https://www.dbaassists.com.br/post/python-analisando-mercado-financeiro

Vamos começar importando o arquivo de bitcoin. No final do post irei disponibilizar junto com o arquivo do notebook.

diretorioArquivoEmpresasMoedas  = "C:\\Temp\\Python\\AcompanhamentoAtivo\\BTC-USD.csv"
nomeArquivo = pd.read_csv(diretorioArquivoEmpresasMoedas,header=0,sep=';')

Nessa primeira abordagem de INSERT a inserção de registro é bem mais demorada, pois é feita linha a linha e quando for trabalhar com grandes massas de dados não aconselho o seu uso.

conexaoSQL = engine.raw_connection()

for index,row in nomeArquivo.iterrows():
    
    conexaoSQL.execute("""INSERT INTO stage.dados_bitcoin(Date,High,[Open],[Close],Low,Volume,[Adj Close],DataExtracao,AcaoMoeda) 
                         values (?,?,?,?,?,?,?,?,?)""", \
                       row['Date'], \
                       row['High'], \
                       row['Low'], \
                       row['Open'], \
                       row['Close'], \
                       row['Volume'], \
                       row['Adj Close'], \
                       row['DataExtracao'], \
                       row['AcaoMoeda']                       
                      ) 

    conexaoSQL.commit()

conexaoSQL.close()

Agora sim vamos abordar um outro mundo, uma nova vida, novos horizontes hahah! Na biblioteca Pandas do Python existe um método chamado to_sql() que permite realizar a inserção dos dados dentro de um banco de dados. Ele pode ser executado da forma mais simples, onde informamos apenas o nome da tabela e a string de conexão.

Nesse primeiro passo, a tabela é criada quando executado.

Aqui vai um ponto de atenção! Caso a tabela já exista no database, você deve usar o comando com passagem de parâmetros, onde é informado se você deseja sobrescrever (ele realiza um TRUNCATE e depois um INSERT) ou se deseja acrescentar os dados.

# Insert criando tabela
nomeArquivo.to_sql("dados_bitcoin", engine)

E também podemos fazer a mesma chamada informando alguns parâmetros. Analise o script abaixo:

# Insert com passagem de parâmetros
nomeArquivo.to_sql(
    name = 'dados_bitcoin',
    schema  = 'stage',
    con = engine,
    index = False,
    if_exists ='replace',
    chunksize = 100
)

Conforme definido acima, existe a possibilidade de passar alguns parâmetros quando executamos o método to_sql() do Pandas, tais como, nome da tabela (name), nome do schema da tabela (schema), se a coluna do DataFrame referente ao index (index) será levada para a tabela, se a tabela será sobrescrita ou apenas iremos adicionar os registros (if_exists) e outras possibilidades.

Parâmetros do método to_sql()

• name = Nome da tabela que será criada no SGBD.

• con = Objeto conexão criado pelo SQLAlchemy.

• schema = Nome do Schema onde será criada a tabela.

• if_exists = Comportamento caso a tabela exista no SGBD.

• index = Escreve o índice do DataFrame como uma coluna da tabela.

• index_label = Nome da coluna de índice.

No final do post estarei disponibilizando a documentação oficial para que você possa estudar com maiores detalhes.

Agora pense que você precisa executar o comando DELETE para eliminar o registro do dia 04/01/2021.

Bem simples, podemos montar esse processo de duas formas, acompanha comigo!

conexaoSQL = engine.raw_connection()
vData = '2021-01-04'
conexaoSQL.execute(f"""DELETE FROM stage.dados_bitcoin where Date = '{vData}';""")
conexaoSQL.commit()
conexaoSQL.close()

Ou...

conexaoSQL = engine.raw_connection()
vData = '2021-01-05'
query = """DELETE FROM stage.dados_bitcoin where Date = ?"""
conexaoSQL.execute(query, vData)
conexaoSQL.commit()
conexaoSQL.close()

E podemos também excluir os dados dos dias 05/01/2021 até 07/01/2021.

conexaoSQL = engine.raw_connection()
vDataInicio = '2021-01-05'
vDataFim = '2021-01-07'
conexaoSQL.execute(f"""DELETE FROM stage.dados_bitcoin where Date BETWEEN '{vDataInicio}' AND '{vDataFim}'""")
conexaoSQL.commit()
conexaoSQL.close()

Ou...

conexaoSQL = engine.raw_connection()
vDataInicio = '2021-01-05'
vDataFim = '2021-01-07'
query = """DELETE FROM stage.dados_bitcoin where Date BETWEEN ? AND ?"""
conexaoSQL.execute(query, (vDataInicio,vDataFim))
conexaoSQL.commit()
conexaoSQL.close()

Agora vamos acompanhar como que seria se precisássemos atualizar algum registro.

Podemos fazer de duas formas, assim...

conexaoSQL = engine.raw_connection()
vData = '2021-01-10'
conexaoSQL.execute(f"""UPDATE A 
                        SET Volume = '0' 
                        FROM stage.dados_bitcoin A
                        WHERE Date = '{vData}';""")
conexaoSQL.commit()
conexaoSQL.close()

Ou....

conexaoSQL = engine.raw_connection()
vData = '2021-01-07'
conexaoSQL.execute("""UPDATE A 
                        SET Volume = '0' 
                        FROM stage.dados_bitcoin A 
                        WHERE Date = ?;""",vData)
conexaoSQL.commit()
conexaoSQL.close()

Além das operações de INSERT, DELETE e UPDATE, podemos também consultar os dados diretamente do nosso banco de dados e armazena-los dentro de um DataFrame, veja como que é simples!

query = """SELECT * FROM sys.tables"""

dftodasastabelas = pd.read_sql_query(query,engine)

dftodasastabelas

Parâmetros do método read_sql_query()

• sql = String SQL Query que deverá ser executada para retornar o conjunto de dados.

• con = Objeto conexão criado pelo SQLAlchemy.

• index_col = Coluna a ser definida como index.

• params= Lista de parâmetros para serem passados ao método.

Quando utilizamos o método read_sql_query(), alguns parâmetros podem ser informados.

Em nossa tabela stage.dados_bitcoin a coluna DataExtracao no banco de dados é uma coluna de datatype DateTime. Quando realizamos a sua leitura, o pandas transforma essa coluna para o tipo object, que no Python é como se fosse do tipo string, pois no pandas não existe o tipo DateTime.

Observe a figura abaixo.

Agora analise a diferença quando informamos através do parâmetro parse_dates qual coluna deve ser transformada para que seja do tipo DateTime.

Um outro método que pode ser usado é o read_sql_table(). Esse método é bem semelhante ao read_sql_query() porém com nesse método não é possível informar uma query, você informa qual a tabela (table_name), schema (schema) e caso queira filtrar alguma coluna especifica, basta informar no parâmetro columns

Parâmetros do método read_sql_table()

• table_name = Nome da tabela onde será feita a leitura dos dados.

• con = Objeto conexão criado pelo SQLAlchemy.

• schema = Schema onde a tabela está armazenada.

• index_col = Coluna a ser definida como index.

• columns = listagem das colunas que deverão ser retornadas

schema='stage'
table_name='dados_bitcoin'
dftodasastabelas = pd.read_sql_table(table_name,engine,schema)
dftodasastabelas.head(3)

É possível retornar também algumas colunas da tabela.

schema='stage'
table_name='dados_bitcoin'
dftodasastabelas = pd.read_sql_table(table_name,engine,schema,columns=["High","Low"])
dftodasastabelas.head(3)

Passando pelas operações básicas de DML, é possível também executar comandos DDL.

Eles seguem a mesma lógica dos outros.

Vamos ver como que seria a escrita do comando TRUNCATE TABLE.

conexaoSQL = engine.raw_connection()
query = 'TRUNCATE TABLE stage.dados_bitcoin'
conexaoSQL.execute(query)
conexaoSQL.commit()
conexaoSQL.close()

Agora precisamos realizar a inclusão de um índice em nossa tabela.

conexaoSQL = engine.raw_connection()
query = """
IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'IX_NCL_dados_bitcoin')
CREATE NONCLUSTERED INDEX [IX_NCL_dados_bitcoin] ON stage.dados_bitcoin(Volume)
"""
conexaoSQL.execute(query)
conexaoSQL.commit()
conexaoSQL.close()

Bem meu amigo! Nosso post termina por aqui. Acredito que tenha conseguido alcançar meu objetivo de trazer para você de forma clara e objetiva uma maneira de manipular nossos dados em um banco de dados com o Python e Pandas.

Espero que tenha curtido, eu gostei bastante! E não esqueça de deixar seu comentário. Grande abraço e que Deus lhe abençoe!

Python_Com_Microsoft_SQL_Server.ipynb

Fazer download de IPYNB • 9KB

BTC-USD.csv

Fazer download de CSV • 53KB

Pandas to_sql() https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html