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 urllibFeito 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!
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)
dftodasastabelasParâ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!
De imediato não sei responder, mais vou dar uma pesquisa.
Muito bom mesmo Gabriel . Usando o Anaconda 3 , se faz necessario algum pip install XPTO para fazer o ODBC com SQL Server , Mysql , Oracle e etc.. ?