Méthode to_sql() de Pandas : Conseils pour écrire du SQL efficacement

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)

La méthode to_sql() de pandas est un outil puissant pour écrire des données d'un DataFrame dans une base de données SQL. Elle est couramment utilisée dans des scénarios tels que l'envoi de données à partir de prédictions de machine learning en batch, le suivi de métriques et le stockage de jeux de données préparés. Cependant, pour utiliser to_sql() de manière efficace, il est nécessaire de bien comprendre ses paramètres et les pièges potentiels.

Cet article propose un guide complet sur l'utilisation de la méthode to_sql() de pandas, en mettant l'accent sur les bonnes pratiques et les conseils pour écrire du SQL de manière sûre et efficace. Nous aborderons tout, de la connexion à votre base de données à la gestion de gros volumes de données et l'amélioration des performances.

Envie de créer rapidement des visualisations de données à partir d'un DataFrame Pandas en évitant d'écrire du code ?

PyGWalker est une bibliothèque Python pour l'exploration des données avec visualisation. PyGWalker (opens in a new tab) peut simplifier votre flux de travail d'analyse et de visualisation de données dans Jupyter Notebook, en transformant votre DataFrame pandas (et votre DataFrame polars) en une interface utilisateur de style Tableau pour l'exploration visuelle.

(opens in a new tab)

Partie 1 : Installation des dépendances et connexion à votre base de données

Avant de pouvoir utiliser la méthode to_sql(), vous devez installer les packages requis, à savoir pandas et sqlalchemy. Il est recommandé de créer un environnement Python séparé pour chaque projet. Vous pouvez le faire avec Conda :

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

Une fois que vous avez installé les paquets nécessaires, la prochaine étape consiste à créer une connexion à votre base de données. Cet objet de connexion peut être fourni soit par sqlite3, soit par sqlalchemy. Dans ce guide, nous utiliserons sqlalchemy car c'est l'approche recommandée par pandas.

from sqlalchemy import create_engine
 
# Créez une connexion vers Snowflake en utilisant votre compte et votre nom d'utilisateur
account_identifier = '<identifiant_compte>'
user = '<nom_utilisateur>'
password = '<mot_de_passe>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/"
engine = create_engine(conn_string)

Partie 2 : Comprendre les paramètres de to_sql()

La méthode to_sql() possède plusieurs paramètres qui ajustent la fonctionnalité et les données envoyées à la base de données. Ils comprennent :

• name : nom de la table SQL (requis)
• con : Connexion fournie par sqlite ou sqlalchemy (requis)
• schema : schéma de la base de données SQL
• if_exists : Ce qu'il faut faire si la table existe déjà, soit "fail", "replace" ou "append"
• index : Si l'index du DataFrame doit être écrit dans la table ou non
• index_label : Le nom à donner à la colonne de l'index si elle est écrite dans la table
• chunksize : Nombre de lignes à écrire à la fois
• dtype : Dictionnaire de toutes les colonnes et de leurs types de données
• method : Spécifie la clause d'insertion SQL

Comprendre ces paramètres est crucial pour utiliser efficacement la méthode to_sql().

Partie 3 : Écrire dans une base de données avec to_sql()

Maintenant que nous avons installé les dépendances, créé la connexion et compris les paramètres de la méthode, nous pouvons commencer à écrire des données dans notre base de données. Dans cet exemple, nous allons écrire un DataFrame très simple dans une table dans Snowflake :

import pandas as pd
from sqlalchemy import create
 
```python
# Créez votre DataFrame
table_name = 'jobs'
df = pd.DataFrame(data=[['Stephen','Data scientist'],['Jane','Data analyst']], columns=['Name','Job'])
 
# Que faire si la table existe déjà ? 'replace', 'append', ou 'fail' ?
if_exists = 'replace'
 
# Écrire les données dans Snowflake
with engine.connect() as con:
 df.to_sql(
 name=table_name.lower(), 
 con=con, 
 if_exists=if_exists
 )

Ce code crée un DataFrame avec deux colonnes, 'Name' et 'Job', et deux lignes de données. Il écrit ensuite ce DataFrame dans une table dans Snowflake. Le paramètre if_exists est défini sur 'replace', ce qui signifie que si la table existe déjà, elle sera remplacée par le nouveau DataFrame.

Partie 4 : Améliorer la vitesse d'insertion dans la base de données

Bien que la méthode to_sql() soit facile à utiliser et pratique, elle peut être lente lorsqu'elle est utilisée avec de grandes tables. Cela est dû au fait que, par défaut, to_sql() écrit les données dans la base de données une ligne à la fois. Cela peut être inefficace pour de grands ensembles de données, car chaque opération d'insertion implique une quantité significative de surcharge.

Une façon d'améliorer la vitesse d'insertion est d'utiliser l'option fast_executemany disponible dans certains connecteurs de base de données. Cette option permet à to_sql() d'écrire les données dans la base de données par lots, plutôt qu'une ligne à la fois. Cela peut considérablement réduire la surcharge des opérations d'insertion et améliorer considérablement les performances.

Pour utiliser fast_executemany, vous devez créer votre moteur avec l'option fast_executemany=True, comme ceci :

engine = create_engine(conn_string, fast_executemany=True)

Avec cette option activée, to_sql() écrira les données dans la base de données par lots, ce qui peut être beaucoup plus rapide que l'écriture d'une ligne à la fois, surtout pour de grands ensembles de données.

Partie 5 : Gérer les valeurs NULL ou NaN

Lorsque vous écrivez un DataFrame dans une base de données SQL à l'aide de la méthode to_sql(), il est important de prendre en compte la façon dont les valeurs NULL ou NaN sont traitées. Par défaut, pandas convertira les valeurs NaN du DataFrame en NULL lors de l'écriture dans la base de données. C'est généralement ce que l'on souhaite, car cela permet à la base de données de gérer les valeurs manquantes de manière cohérente avec son type et ses contraintes de données. Cependant, dans certains cas, vous voudrez peut-être gérer les valeurs NaN différemment. Par exemple, vous voudrez peut-être remplacer les valeurs NaN par une valeur spécifique avant d'écrire dans la base de données. Vous pouvez le faire en utilisant la méthode fillna() dans pandas:

df = df.fillna(valeur)

Ce code remplacera toutes les valeurs NaN dans le DataFrame par la valeur spécifiée.

Partie 6: Ajouter des enregistrements à une table existante

La méthode to_sql() offre un moyen pratique d'ajouter des enregistrements à une table existante dans une base de données SQL. Pour ce faire, vous devez simplement définir le paramètre if_exists sur 'append':

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

Ce code ajoutera les enregistrements du DataFrame à la table existante. Si la table n'existe pas, elle sera créée.

Partie 7: Mise à jour des enregistrements existants

Bien que la méthode to_sql() ne prenne pas en charge directement la mise à jour des enregistrements existants dans une base de données SQL, vous pouvez y parvenir en combinant to_sql() avec d'autres opérations SQL.

Par exemple, vous pouvez utiliser to_sql() pour écrire le DataFrame dans une table temporaire dans la base de données, puis utiliser une instruction SQL UPDATE pour mettre à jour les enregistrements de la table cible en fonction des enregistrements de la table temporaire.

Voici un exemple de la façon dont vous pouvez le faire:

# Écrire le DataFrame dans une table temporaire
df.to_sql(name='table_temporaire', con=con, if_exists='replace')
 
# Créer une instruction SQL UPDATE
update_sql = """
UPDATE table_cible
SET table_cible.colonne1 = table_temporaire.colonne1,
    table_cible.colonne2 = table_temporaire.colonne2,
    ...
FROM table_temporaire
WHERE table_cible.id = table_temporaire.id
"""
 
# Exécuter l'instruction UPDATE
with engine.connect() as con:
    con.execute(update_sql)

Ce code écrit d'abord le DataFrame dans une table temporaire dans la base de données. Il construit ensuite une instruction SQL UPDATE qui met à jour les enregistrements de la table cible en fonction des enregistrements de la table temporaire. Enfin, il exécute l'instruction UPDATE en utilisant l'objet de connexion.

Questions fréquemment posées

1. Qu'est-ce que la méthode to_sql() dans pandas?

La méthode to_sql() dans pandas est une fonction qui vous permet d'écrire des données d'un DataFrame dans une base de données SQL. Elle permet de stocker facilement des données pandas dans une base de données pour un stockage à long terme, un partage avec d'autres systèmes ou un traitement ultérieur avec SQL.

2. Comment gérer les valeurs NULL ou NaN lorsque l'on utilise to_sql()?

Par défaut, pandas convertira les valeurs NaN du DataFrame en NULL lors de l'écriture dans la base de données. Si vous souhaitez gérer les valeurs NaN différemment, vous pouvez utiliser la méthode fillna() dans pandas pour remplacer les valeurs NaN par une valeur spécifique avant d'écrire dans la base de données.

3. Pouvez-vous utiliser to_sql() pour ajouter des enregistrements à une table existante?

Oui, vous pouvez utiliser to_sql() pour ajouter des enregistrements à une table existante dans une base de données SQL. Pour ce faire, il vous suffit de définir le paramètre if_exists sur 'append' lors de l'appel à to_sql().