Écrire des DataFrames dans des tables de base de données
Enregistrez un DataFrame nettoyé dans une table nouvelle ou existante avec DataFrame.to_sql(), en contrôlant if_exists et chunksize.
Écrire des DataFrames dans des tables de base de données est une leçon Pandas & NumPy Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Pandas & NumPy Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Pandas & NumPy Academy comprend 4 leçons au total.
Pourquoi écrire des DataFrames dans des bases de données ?
Après avoir nettoyé et transformé les données dans Pandas, vous devez souvent conserver les résultats dans une base de données : pour les rendre accessibles à d’autres applications, tableaux de bord ou membres de l’équipe ; pour stocker des résultats d’analyse incrémentiels ; ou pour créer un magasin de données à partir d’un lac de données brutes. DataFrame.to_sql() est la méthode Pandas standard pour écrire des données dans toute base de données prise en charge par SQLAlchemy en un seul appel.
Utilisation de base de to_sql()
df.to_sql('table_name', con=engine, if_exists='replace', index=False) écrit le DataFrame dans une table de base de données. Le paramètre if_exists contrôle ce qui se passe si la table existe déjà : 'fail' déclenche une erreur, 'replace' supprime et recrée la table, et 'append' ajoute de nouvelles lignes sans modifier les lignes existantes. Définissez toujours index=False, sauf si vous souhaitez explicitement enregistrer l’index du DataFrame comme colonne dans la base de données.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({
'date': pd.date_range('2024-01-01', periods=5),
'revenue': [1200.0, 980.5, 1450.0, 760.3, 1100.0],
'region': ['North', 'South', 'East', 'West', 'North']
})
df.to_sql('daily_revenue', con=engine,
if_exists='replace', index=False)
print('Table written successfully')Explication du paramètre if_exists
Les trois valeurs de if_exists correspondent à des cas d’utilisation différents. 'replace' est destiné au développement : supprimez l’ancienne table et créez-en une nouvelle — les changements de schéma sont automatiques, mais toutes les anciennes données sont perdues. 'append' est destiné aux chargements incrémentiels : ajoutez de nouvelles lignes à la table existante sans modifier sa structure — ce qui est utile pour les traitements par lots quotidiens. 'fail' est une protection : utilisez-le pour empêcher qu’une chaîne de traitement défectueuse ne remplace accidentellement des tables importantes.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
new_batch = pd.DataFrame({
'date': ['2024-06-01', '2024-06-02'],
'revenue': [1500.0, 1300.0],
'region': ['North', 'East']
})
# Append new rows without losing existing data
new_batch.to_sql('daily_revenue', con=engine,
if_exists='append', index=False)
print('Appended new rows')Contrôler les types de données des colonnes
Par défaut, to_sql() associe automatiquement les types d Pandas aux types SQLAlchemy. Les valeurs par défaut sont parfois incorrectes : par exemple, une colonne datetime64 peut être enregistrée sous forme de TEXT dans SQLite. Utilisez le paramètre dtype pour spécifier les types SQL exacts à l’aide d’objets de type SQLAlchemy. Cela garantit un stockage correct, un indexage approprié et une gestion précise des types lors de la relecture des données. Vérifiez toujours le schéma après l’écriture à l’aide d’un rapide PRAGMA table_info() ou inspector.get_columns().
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import types
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({
'id': [1, 2, 3],
'name': ['Alice', 'Bob', 'Carol'],
'score': [0.95, 0.87, 0.91],
'created_at': pd.to_datetime(['2024-01-01', '2024-01-02', '2024-01-03'])
})
df.to_sql('users', con=engine, if_exists='replace', index=False,
dtype={'id': types.Integer(),
'score': types.Float(),
'created_at': types.DateTime()})Écrire par blocs avec chunksize
Pour les grands DataFrames, to_sql() sans chunksize tente d’insérer toutes les lignes dans une seule instruction, ce qui peut provoquer un délai d’attente de la base de données ou une erreur de mémoire. Indiquez chunksize=N pour insérer N lignes par transaction. La base de données peut ainsi valider les données progressivement, tout en réduisant l’utilisation maximale de la mémoire. Une valeur de chunksize comprise entre 10 000 et 50 000 lignes offre généralement un bon compromis entre vitesse d’insertion et mémoire, mais la valeur optimale dépend de votre base de données et de la latence du réseau.
import pandas as pd
import sqlalchemy as sa
import numpy as np
engine = sa.create_engine('sqlite:///results.db')
# Large DataFrame
df = pd.DataFrame({
'id': range(500000),
'value': np.random.randn(500000)
})
# Insert in chunks of 10,000 rows at a time
df.to_sql('large_table', con=engine,
if_exists='replace',
index=False,
chunksize=10000)
print('Written 500,000 rows')Upsert : insérer ou mettre à jour
La méthode to_sql() de Pandas ne prend pas nativement en charge l’upsert (insérer si la ligne est nouvelle, mettre à jour si elle existe). Pour l’implémenter, utilisez le module Core de SQLAlchemy avec une instruction INSERT OR REPLACE (SQLite) ou ON CONFLICT DO UPDATE (PostgreSQL). La solution courante dans Pandas consiste à écrire les données dans une table temporaire de préparation avec if_exists='replace', puis à exécuter du SQL brut pour fusionner cette table avec la table de production, et enfin à supprimer la table de préparation.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
new_data = pd.DataFrame({
'id': [1, 2, 5],
'value': [99.9, 88.8, 77.7]
})
# Write to staging table
new_data.to_sql('staging', con=engine,
if_exists='replace', index=False)
# Merge into production (SQLite syntax)
with engine.connect() as conn:
conn.execute(sa.text(
'INSERT OR REPLACE INTO production SELECT * FROM staging'
))
conn.commit()
print('Upsert complete')Vérifier l’écriture
Après l’écriture, vérifiez toujours le résultat en relisant un décompte récapitulatif et le nombre de lignes. Comparez-les au DataFrame source. Cela permet de détecter les échecs silencieux dus à des incompatibilités de types (par exemple, des NaN dans une colonne entière entraînant des insertions partielles) ou à des contraintes de base de données (par exemple, des violations de clé unique qui ignorent silencieusement des lignes dans certaines configurations). Un rapide SELECT COUNT(*) FROM table après chaque appel à to_sql entraîne un surcoût minimal et évite les pertes de données silencieuses.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({'id': range(1000), 'value': range(1000)})
df.to_sql('my_table', con=engine, if_exists='replace', index=False)
# Verify
with engine.connect() as conn:
count = conn.execute(sa.text('SELECT COUNT(*) FROM my_table')).scalar()
print(f'Source rows: {len(df)}, DB rows: {count}')
assert count == len(df), 'Row count mismatch!'Ajouter une clé primaire après l’écriture
to_sql() écrit les données, mais n’ajoute pas de clés primaires ni de contraintes de base de données : il crée une table simple. Pour une table de production, ajoutez la contrainte de clé primaire séparément à l’aide de SQL brut exécuté via SQLAlchemy. SQLite exige de recréer la table pour ajouter des contraintes après sa création, tandis que PostgreSQL prend en charge ALTER TABLE ADD PRIMARY KEY. Vous pouvez également définir le schéma complet à l’avance et utiliser if_exists='append' pour insérer les données dans une table existante correctement définie.
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import Table, Column, Integer, Float, MetaData
engine = sa.create_engine('sqlite:///results.db')
meta = MetaData()
# Define table with primary key
my_table = Table('defined_table', meta,
Column('id', Integer, primary_key=True),
Column('value', Float)
)
meta.create_all(engine) # Create table with constraints
# Then insert data using append
df = pd.DataFrame({'id': range(5), 'value': [1.1, 2.2, 3.3, 4.4, 5.5]})
df.to_sql('defined_table', con=engine,
if_exists='append', index=False)Écritures transactionnelles
Pour garantir la cohérence des données, placez to_sql() dans une transaction explicite. Si une étape quelconque d’une écriture portant sur plusieurs tables échoue, vous pouvez annuler toutes les modifications. Sans transaction, des écritures partielles peuvent laisser la base de données dans un état incohérent. Le gestionnaire de contexte de connexion de SQLAlchemy avec conn.begin() permet de contrôler manuellement la transaction. Vous pouvez également utiliser engine.begin() pour un bloc avec validation automatique qui annule les modifications en cas d’exception.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df_orders = pd.DataFrame({'id': [1, 2], 'amount': [100.0, 200.0]})
df_summary = pd.DataFrame({'total': [300.0], 'count': [2]})
try:
with engine.begin() as conn: # Auto-rollback on exception
df_orders.to_sql('orders_v2', con=conn,
if_exists='replace', index=False)
df_summary.to_sql('summary_v2', con=conn,
if_exists='replace', index=False)
print('Both tables written atomically')
except Exception as e:
print(f'Write failed, rolled back: {e}')Performances : méthodes d’insertion en masse
Par défaut, to_sql() insère une ligne par instruction SQL, ce qui est très lent pour les grands DataFrames. Passez method='multi' pour utiliser une seule instruction INSERT contenant plusieurs tuples de valeurs — généralement 10 à 100 fois plus rapide. Avec PostgreSQL, passez une fonction method personnalisée qui utilise le protocole COPY (via copy_expert de psycopg2) pour obtenir le chargement en masse le plus rapide possible. La méthode optimale dépend de la version de votre base de données et de la configuration de votre réseau.
import pandas as pd
import sqlalchemy as sa
import numpy as np
import time
engine = sa.create_engine('sqlite:///perf.db')
df = pd.DataFrame({'a': range(100000), 'b': np.random.randn(100000)})
# Default (one row per INSERT) — slow
start = time.time()
df.to_sql('test_default', con=engine, if_exists='replace', index=False)
print(f'Default: {time.time()-start:.2f}s')
# multi-row INSERT — faster
start = time.time()
df.to_sql('test_multi', con=engine, if_exists='replace',
index=False, method='multi', chunksize=1000)
print(f'Multi: {time.time()-start:.2f}s')Journalisation et audit des écritures
Dans les chaînes de traitement de production, suivez ce qui a été écrit et à quel moment en tenant une table de journal d’audit. Après chaque appel réussi à to_sql(), insérez une ligne dans le journal d’audit avec le nom de la table, le nombre de lignes, l’horodatage et l’identifiant d’exécution de la chaîne de traitement. Vous pourrez ainsi détecter facilement les exécutions manquantes, les doubles écritures ou les changements de schéma au fil du temps. Le journal d’audit lui-même est un DataFrame Pandas écrit avec to_sql : la même technique appliquée de manière récursive à la surveillance opérationnelle.
import pandas as pd
import sqlalchemy as sa
from datetime import datetime
engine = sa.create_engine('sqlite:///results.db')
def write_with_audit(df, table_name, engine, run_id):
df.to_sql(table_name, con=engine, if_exists='append', index=False)
audit = pd.DataFrame([{
'run_id': run_id,
'table_name': table_name,
'rows_written': len(df),
'written_at': datetime.utcnow().isoformat()
}])
audit.to_sql('audit_log', con=engine, if_exists='append', index=False)
print(f'Wrote {len(df)} rows to {table_name}')
df = pd.DataFrame({'id': [1, 2], 'val': [10, 20]})
write_with_audit(df, 'my_table', engine, run_id='run_001')Vérification rapide
Vérifiez votre compréhension des concepts d’analyse de données présentés dans cette leçon.
Récapitulatif de la leçon
Dans cette leçon, vous avez appris que df.to_sql() écrit un DataFrame dans toute table de base de données connectée à SQLAlchemy, le paramètre if_exists contrôlant le comportement de création, d’ajout ou de remplacement, que chunksize et method='multi' améliorent les performances pour les grands DataFrames, et que les écritures transactionnelles avec engine.begin() garantissent des mises à jour atomiques de plusieurs tables, annulées en cas d’échec. Nous allons maintenant comparer Pandas et SQL afin de comprendre dans quels cas chaque outil constitue le meilleur choix.
Questions Fréquemment Posées
La leçon « Écrire des DataFrames dans des tables de base de données » est-elle gratuite ?
Oui — le texte complet de « Écrire des DataFrames dans des tables de base de données » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Pandas & NumPy Academy, passe à CoddyKit PRO. Le cours Pandas & NumPy Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Écrire des DataFrames dans des tables de base de données » ?
Enregistrez un DataFrame nettoyé dans une table nouvelle ou existante avec DataFrame.to_sql(), en contrôlant if_exists et chunksize. Tu pratiques Pandas & NumPy Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer Pandas & NumPy Academy ?
Aucune expérience préalable n'est requise. Pandas & NumPy Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.
Combien de temps prend la leçon « Écrire des DataFrames dans des tables de base de données » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon Pandas & NumPy Academy ?
Oui. Chaque leçon Pandas & NumPy Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Se connecter à une base de données avec SQLAlchemy
- Exécuter des requêtes SQL depuis Pandas
- Écrire des DataFrames dans des tables de base de données
- Pandas ou SQL : choisir le bon outil