Método to_sql() do Pandas: Dicas para uma escrita SQL eficiente

Name

Matt Popovic

Published on 17/08/2023

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

O método to_sql() do Pandas é uma ferramenta poderosa para escrever dados de um DataFrame em um banco de dados SQL. É comumente usado em cenários como o envio de dados de volta a partir de previsões em lote de aprendizado de máquina, rastreamento de métricas e armazenamento de conjuntos de dados preparados. No entanto, usar o to_sql() efetivamente requer uma boa compreensão de seus parâmetros e armadilhas potenciais.

Este artigo fornecerá um guia abrangente sobre como usar o método to_sql() do Pandas, com foco nas melhores práticas e dicas para uma escrita SQL segura e eficiente. Vamos abordar desde a conexão com seu banco de dados até o gerenciamento de grandes conjuntos de dados e a melhoria do desempenho.

Quer criar rapidamente visualizações de dados a partir de um DataFrame do Python Pandas sem código?

PyGWalker é uma biblioteca Python para Análise Exploratória de Dados com Visualização. O PyGWalker (opens in a new tab) pode simplificar seu fluxo de trabalho de análise e visualização de dados no Jupyter Notebook, transformando seu dataframe pandas (e dataframe polars) em uma interface de usuário no estilo Tableau para exploração visual.

(opens in a new tab)

Parte 1: Instalando Dependências e Conectando-se ao Banco de Dados

Antes de usar o método to_sql(), você precisa instalar os pacotes necessários, que são o pandas e o sqlalchemy. É recomendado criar um ambiente Python separado para cada projeto. Você pode fazer isso usando o Conda:

conda create -n nome_do_ambiente python=3.9 pandas sqlalchemy
conda activate nome_do_ambiente

Depois de instalar os pacotes necessários, o próximo passo é criar uma conexão com seu banco de dados. Essa conexão pode ser fornecida pelo sqlite3 ou pelo sqlalchemy. Neste guia, estaremos usando o sqlalchemy, pois é a abordagem recomendada pelo Pandas.

from sqlalchemy import create_engine
 
# Crie uma conexão com o Snowflake usando sua conta e usuário
account_identifier = '<identificador_da_conta>'
user = '<nome_de_login_do_usuário>'
password = '<senha>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/"
engine = create_engine(conn_string)

Parte 2: Compreendendo os Parâmetros do to_sql()

O método to_sql() possui vários parâmetros que ajustam a funcionalidade e os dados enviados para o banco de dados. Esses parâmetros incluem:

• name: nome da tabela SQL (obrigatório)
• con: conexão fornecida pelo sqlite ou sqlalchemy (obrigatório)
• schema: esquema do banco de dados SQL
• if_exists: o que fazer se a tabela já existir, 'fail', 'replace' ou 'append'
• index: se deve gravar a coluna de índice do DataFrame na tabela ou não
• index_label: o nome a ser atribuído à coluna de índice, se gravada na tabela
• chunksize: número de linhas a serem gravadas de uma vez
• dtype: dicionário de todas as colunas e seus tipos de dados
• method: especifica a cláusula de inserção SQL

Compreender esses parâmetros é crucial para usar o método to_sql() de forma eficaz.

Parte 3: Gravando em um Banco de Dados Usando o to_sql()

Agora que instalamos os requisitos, criamos a conexão e compreendemos os parâmetros envolvidos no método, podemos começar a gravar dados em nosso banco de dados. Neste exemplo, vamos gravar um DataFrame muito simples em uma tabela no Snowflake:

import pandas as pd
from sqlalchemy import create
 
```python
# Crie seu DataFrame
nome_tabela = 'empregos'
df = pd.DataFrame(data=[['Stephen','Cientista de dados'],['Jane','Analista de dados']],columns=['Nome','Cargo'])
 
# O que fazer se a tabela existir? substituir, anexar ou falhar?
if_exists = 'replace'
 
# Gravar os dados no Snowflake
with engine.connect() as con:
 df.to_sql(
 name=nome_tabela.lower(), 
 con=con, 
 if_exists=if_exists
 )

Este código cria um DataFrame com duas colunas, 'Nome' e 'Cargo', e duas linhas de dados. Em seguida, ele grava este DataFrame em uma tabela no Snowflake. O parâmetro if_exists está definido como 'replace', o que significa que se a tabela já existir, ela será substituída pelo novo DataFrame.

Parte 4: Melhorando a Velocidade de Inserção no Banco de Dados

Embora o método to_sql() seja fácil de usar e conveniente, ele pode ser lento ao trabalhar com tabelas grandes. Isso ocorre porque, por padrão, o to_sql() grava dados no banco de dados uma linha por vez. Isso pode ser ineficiente para conjuntos de dados grandes, pois cada operação de inserção envolve uma quantidade significativa de overhead.

Uma maneira de melhorar a velocidade de inserção é usar a opção fast_executemany disponível em alguns conectores de banco de dados. Essa opção permite que o to_sql() grave dados no banco de dados em lotes, em vez de uma linha por vez. Isso pode reduzir significativamente o overhead das operações de inserção e melhorar o desempenho de forma drástica.

Para usar o fast_executemany, você precisa criar sua engine com a opção fast_executemany=True, assim:

engine = create_engine(conn_string, fast_executemany=True)

Com essa opção habilitada, o to_sql() gravará dados no banco de dados em lotes, o que pode ser muito mais rápido do que gravar uma linha por vez, especialmente para conjuntos de dados grandes.

Parte 5: Lidando com Valores Nulos ou NaN

Ao escrever um DataFrame em um banco de dados SQL usando o método to_sql(), é importante considerar como os valores nulos ou NaN são tratados. Por padrão, o Pandas converterá valores NaN no DataFrame para NULL ao gravar no banco de dados. Isso geralmente é o que você deseja, pois permite que o banco de dados lide com valores ausentes de maneira consistente com seu tipo de dado e restrições. No entanto, em alguns casos, você pode querer lidar com valores NaN de maneira diferente. Por exemplo, você pode querer substituir os valores NaN por um valor específico antes de gravar no banco de dados. Você pode fazer isso usando o método fillna() no pandas:

df = df.fillna(valor)

Este código substituirá todos os valores NaN no DataFrame pelo valor especificado.

Parte 6: Anexando Registros a uma Tabela Existente

O método to_sql() fornece uma maneira conveniente de anexar registros a uma tabela existente em um banco de dados SQL. Para fazer isso, basta definir o parâmetro if_exists como 'append':

df.to_sql(name=nome_tabela, con=con, if_exists='append')

Este código irá anexar os registros no DataFrame à tabela existente. Se a tabela não existe, ela será criada.

Parte 7: Atualizando Registros Existente

Embora o método to_sql() não suporte diretamente a atualização de registros existentes em um banco de dados SQL, você pode fazer isso combinando to_sql() com outras operações SQL.

Por exemplo, você pode usar to_sql() para escrever o DataFrame em uma tabela temporária no banco de dados e, em seguida, usar uma declaração SQL UPDATE para atualizar os registros na tabela de destino com base nos registros na tabela temporária.

Aqui está um exemplo de como fazer isso:

# Escreva o DataFrame em uma tabela temporária
df.to_sql(name='tabela_temporaria', con=con, if_exists='replace')
 
# Crie uma declaração SQL UPDATE
update_sql = """
UPDATE tabela_destino
SET tabela_destino.coluna1 = tabela_temporaria.coluna1,
    tabela_destino.coluna2 = tabela_temporaria.coluna2,
    ...
FROM tabela_temporaria
WHERE tabela_destino.id = tabela_temporaria.id
"""
 
# Execute a declaração UPDATE
with engine.connect() as con:
    con.execute(update_sql)

Este código primeiro escreve o DataFrame em uma tabela temporária no banco de dados. Em seguida, constrói uma declaração SQL UPDATE que atualiza os registros na tabela de destino com base nos registros na tabela temporária. Por fim, executa a declaração UPDATE usando o objeto de conexão.

Perguntas Frequentes

1. O que é o método to_sql() no pandas?

O método to_sql() no pandas é uma função que permite escrever dados de um DataFrame em um banco de dados SQL. Ele oferece uma maneira conveniente de armazenar dados do pandas em um banco de dados para armazenamento a longo prazo, compartilhamento com outros sistemas ou processamento adicional com SQL.

2. Como lidar com valores NULL ou NaN ao usar to_sql()?

Por padrão, o pandas converterá valores NaN no DataFrame para NULL ao gravar no banco de dados. Se você deseja lidar com valores NaN de maneira diferente, pode usar o método fillna() no pandas para substituir os valores NaN por um valor específico antes de gravar no banco de dados.

3. É possível usar to_sql() para anexar registros a uma tabela existente?

Sim, é possível usar to_sql() para anexar registros a uma tabela existente em um banco de dados SQL. Para fazer isso, basta definir o parâmetro if_exists como 'append' ao chamar to_sql().