Pandas Lire une base de données SQL
Pandas fournit un ensemble de fonctions permettant d'interagir directement avec des bases de données SQL. Les résultats des requêtes peuvent être lus directement dans un DataFrame, et un DataFrame peut également être réécrit dans la base de données. Cela permet aux analystes de données d'éviter de gérer manuellement les connexions aux bases de données et l'analyse des résultats, ce qui simplifie grandement l'interaction entre les bases de données et Python.
Aperçu des fonctions principales
| Fonction | Objectif | Type de retour |
|---|---|---|
pd.read_sql() |
Exécute des requêtes SQL ou lit une table entière (une fonction polyvalente compatible avec les deux scénarios) | DataFrame |
pd.read_sql_query() |
Exécute des instructions de requête SQL, adapté aux requêtes complexes | DataFrame |
pd.read_sql_table() |
Lit directement une table entière, ne prend en charge que les connexions SQLAlchemy | DataFrame |
DataFrame.to_sql() |
Écrit un DataFrame dans une table de base de données | None / int |
En pratique, il est recommandé d'utiliser
pd.read_sql()car il détermine automatiquement s'il faut exécuter une requête ou lire une table entière en fonction des paramètres transmis, offrant ainsi la meilleure compatibilité.pd.read_sql_table()Ne prend en charge que les connexions au moteur SQLAlchemy ; il ne prend pas en charge les connexions nativessqlite3et d'autres connexions DB-API.
Établir une connexion à une base de données
Pandas ne se connecte pas directement aux bases de données. Il nécessite une bibliothèque tierce pour établir une connexion, qui est ensuite transmise. Il existe deux approches principales :moteur SQLAlchemy(recommandé) etconnexion DB-API native(léger et simple).
Méthode 1 : SQLAlchemy (Recommandé)
SQLAlchemy est la boîte à outils de base de données la plus courante de Python. Il prend en charge toutes les principales bases de données et offre la meilleure compatibilité avec Pandas :
pip install sqlalchemy
Exemple
# Format de la chaîne de connexion SQLAlchemy : type de base de données + driver://username:password@host:port/database name
# SQLite (base de données basée sur fichier, aucun nom d'utilisateur ni mot de passe requis)
engine = create_engine("sqlite:///mydata.db")
# MySQL
engine = create_engine("mysql+pymysql://root:password@localhost:3306/mydb")
# PostgreSQL
engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/mydb")
# SQL Server
engine = create_engine("mssql+pyodbc://user:password@server/mydb?driver=ODBC+Driver+17+for+SQL+Server")
# Vérifier si la connexion est réussie
with engine.connect() as conn:
print("Connexion réussie")
Méthode 2 : Connexion native sqlite3 (SQLite uniquement)
intégré à Pythonsqlite3module ne nécessite aucune installation supplémentaire et convient aux bases de données SQLite locales légères :
Exemple
# Se connecter à une base de données SQLite sous forme de fichier (créée automatiquement si le fichier n'existe pas)
conn = sqlite3.connect("mydata.db")
# Se connecter à une base de données en mémoire (les données disparaissent après la fin du programme ; adapté aux tests)
conn_memory = sqlite3.connect(":memory:")
# La connexion doit être fermée manuellement après utilisation
# conn.close()
Lecture des données avec pd.read_sql()
Syntaxe de base
pd.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
Description des principaux paramètres :
| Paramètre | Type | Description |
|---|---|---|
sql |
str | Instruction de requête SQL, ou nom de table (lié à l'contype) |
con |
Objet de connexion | Moteur SQLAlchemy ou objet de connexion DB-API |
index_col |
str ou list | Définit la colonne spécifiée comme index de ligne du DataFrame |
params |
list ou dict | Valeurs des paramètres pour les requêtes SQL paramétrées, empêchant l’injection SQL |
parse_dates |
list ou dict | Analyse la colonne spécifiée comme type datetime |
chunksize |
int | Lit par blocs, en renvoyant un itérateur de DataFrames avec le nombre de lignes spécifié par bloc |
1. Lecture d’une table entière
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Lire toute la table employees
df = pd.read_sql("employees", con=engine)
print(df.head())
print(f"Total {len(df)} lignes, {len(df.columns)} colonnes")
2. Exécution de requêtes SQL
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Requête avec conditions
df = pd.read_sql("SELECT * FROM employees WHERE department = 'IT'", con=engine)
# Requête de jointure multi-tables
sql = """
SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 10000
ORDER BY e.salary DESC
"""
df = pd.read_sql(sql, con=engine)
print(df.head(10))
3. Requêtes paramétrées (Prévention des injections SQL)
Lorsque les conditions de requête proviennent de la saisie utilisateur,utilisez toujours des requêtes paramétréeset n'utilisez pas la concaténation de chaînes pour SQL :
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# ❌ Approche dangereuse : concaténation de chaînes SQL, risque d'injection SQL
dept = "IT"
# df = pd.read_sql(f"SELECT * FROM employees WHERE department = '{dept}'", engine)
# ✅ Approche sécurisée : utiliser des espaces réservés ? (sqlite3) ou des paramètres nommés :name (SQLAlchemy)
# Style SQLite / DB-API (avec ?)
df = pd.read_sql(
"SELECT * FROM employees WHERE department = ? AND salary > ?",
con=engine,
params=["IT", 8000] # params est une liste, remplaçant ? de manière positionnelle
)
# Style de paramètres nommés SQLAlchemy (avec :param_name)
from sqlalchemy import text
with engine.connect() as conn:
df = pd.read_sql(
text("SELECT * FROM employees WHERE department = :dept AND salary > :min_salary"),
con=conn,
params={"dept": "IT", "min_salary": 8000}
)
print(df)
4. Définition de la colonne d’index
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Définir la colonne id de la base de données comme index des lignes du DataFrame
df = pd.read_sql("SELECT * FROM employees", con=engine, index_col="id")
print(df.head())
# Utiliser plusieurs colonnes comme index composite
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
index_col=["year", "month"] # Index composite
)
5. Analyse des colonnes de dates
Les champs de date stockés dans la base de données sont lus comme des chaînes de caractères par défaut. En utilisant leparse_datesparamètre, ils peuvent être directement analysés endatetime:
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Analyser les colonnes created_at et updated_at comme type datetime
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
parse_dates=["created_at", "updated_at"]
)
print(df.dtypes)
# created_at datetime64[ns]
# updated_at datetime64[ns]
# Vous pouvez également spécifier le format d'analyse (pour les formats de date non standard)
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
parse_dates={"created_at": "%Y%m%d"} # Analyser un format comme "20240115" en date
)
Lecture de grandes données par blocs (chunksize)
Lorsqu'une table de base de données contient une très grande quantité de données, la lire entièrement en mémoire d'un seul coup peut provoquer une OOM (out of memory). En utilisant lechunksizeparamètre, les données peuvent être lues par lots, en ne traitant qu'une portion à la fois :
Exemple
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:password@localhost/bigdata")
# chunksize=10000 signifie lire 10000 lignes à la fois, en renvoyant un itérateur
chunks = pd.read_sql("SELECT * FROM large_table", con=engine, chunksize=10000)
# Traiter bloc par bloc (seulement 10000 lignes en mémoire à la fois)
result_list = []
for i, chunk in enumerate(chunks):
# Appliquer la logique de traitement à chaque bloc (par ex., filtrage, agrégation, etc.)
processed = chunk[chunk["status"] == "active"]
result_list.append(processed)
print(f"Bloc traité {i+1}, lignes valides dans le bloc en cours : {len(processed)}")
# Combiner tous les blocs traités en un seul DataFrame
final_df = pd.concat(result_list, ignore_index=True)
print(f"Lignes de données valides finales : {len(final_df)}")
pd.read_sql_query() et pd.read_sql_table()
pd.read_sql_query() : Exécute uniquement des requêtes
etpd.read_sql()La fonctionnalité est essentiellement la même, mais elle accepte uniquement des instructions de requête SQL, et non des noms de tables. Les paramètres sont exactement les mêmes, adaptés aux scénarios où vous devez clairement distinguer les opérations de « requête » et de « lecture de table » :
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
df = pd.read_sql_query(
"SELECT name, salary FROM employees WHERE salary > 5000",
con=engine
)
print(df)
pd.read_sql_table(): lit une table entière (SQLAlchemy uniquement)
pd.read_sql_table()Spécifiquement utilisé pour lire une table entière, avec prise en charge du filtrage des colonnes et des lignes via des paramètres, maisne prend en charge que les connexions au moteur SQLAlchemyet ne prend pas en charge les connexions natives telles que sqlite3 :
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Lire les colonnes spécifiées de la table employees
df = pd.read_sql_table(
"employees",
con=engine,
columns=["id", "name", "salary", "department"] # Ne lire que ces colonnes
)
# Prend également en charge la spécification de schema (le schéma dans la base de données)
df = pd.read_sql_table(
"employees",
con=engine,
schema="hr" # Lire la table employees sous le schéma hr
)
print(df.head())
Écrire un DataFrame dans une base de données (to_sql)
UtilisationDataFrame.to_sql()Vous pouvez écrire un DataFrame dans une base de données, avec prise en charge de la création d'une nouvelle table, de l'ajout de données ou du remplacement de la table d'origine :
Syntaxe de base
DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)
if_existsLe paramètre détermine le comportement lorsque la table cible existe déjà :
| Valeur de if_exists | Comportement | Cas d'utilisation |
|---|---|---|
'fail'(Par défaut) |
Lève une erreur si la table existe déjà | Empêche l'écrasement accidentel des données existantes |
'replace' |
Supprime d'abord la table d'origine, puis recrée la table et y écrit | Rafraîchissement complet des données |
'append' |
Ajoute des données à une table existante sans modifier la structure de la table | Écrit de nouvelles données de manière incrémentielle |
Exemple
from sqlalchemy import create_engine
engine = create_engine("sqlite:///mydata.db")
# Préparer des données d'exemple
df = pd.DataFrame({
"name": ["Zhang San", "Li Si", "Wang Wu"],
"department": ["IT", "HR", "IT"],
"salary": [12000, 8000, 15000]
})
# Écrire le DataFrame dans la table employees ; la remplacer si elle existe déjà
df.to_sql(
"employees",
con=engine,
if_exists="replace", # Écraser les données d'origine
index=False # Ne pas écrire l'index de ligne du DataFrame dans la base de données (généralement inutile)
)
# Ajouter de nouvelles données à la table existante (remarque : les noms de colonnes et les types de données doivent correspondre)
new_employees = pd.DataFrame({
"name": ["Zhao Liu"],
"department": ["Finance"],
"salary": [11000]
})
new_employees.to_sql("employees", con=engine, if_exists="append", index=False)
# Vérifier le résultat de l'écriture
result = pd.read_sql("SELECT * FROM employees", con=engine)
print(result)
Spécification des types de données des colonnes
Lors de l'écriture, vous pouvez utiliser ledtypeparamètre pour spécifier explicitement le type de chaque colonne dans la base de données :
Exemple
from sqlalchemy import create_engine, Integer, String, Float, DateTime
engine = create_engine("sqlite:///mydata.db")
df = pd.DataFrame({
"id": [1, 2, 3],
"name": ["Alice", "Bob", "Charlie"],
"score": [92.5, 88.0, 95.3],
})
df.to_sql(
"students",
con=engine,
if_exists="replace",
index=False,
dtype={
"id": Integer(), # Type entier
"name": String(50), # Chaîne de longueur variable, maximum 50 caractères
"score": Float() # Nombre à virgule flottante
}
)
Exemple d'utilisation complet
Voici un exemple complet de lecture de données de ventes à partir d'une base de données SQLite et de leur analyse :
Exemple
import sqlite3
from sqlalchemy import create_engine
# Étape 1 : Préparer la base de données de test
engine = create_engine("sqlite:///sales.db")
conn = sqlite3.connect("sales.db")
# Créer des données d'exemple et les écrire dans la base de données
sales_data = pd.DataFrame({
"order_id": [1001, 1002, 1003, 1004, 1005, 1006],
"product": ["Laptop", "Phone", "Tablet", "Laptop", "Phone", "Tablet"],
"amount": [6999, 3999, 2999, 7299, 4299, 3199],
"quantity": [2, 5, 3, 1, 4, 2],
"order_date": ["2024-01-10", "2024-01-15", "2024-01-20",
"2024-02-05", "2024-02-10", "2024-02-18"]
})
sales_data.to_sql("sales", con=engine, if_exists="replace", index=False)
# Étape 2 : Lire les données et analyser les dates
df = pd.read_sql(
"SELECT * FROM sales",
con=engine,
parse_dates=["order_date"]
)
print("Données brutes :")
print(df)
print()
# Étape 3 : Calculer les ventes par produit
sql_agg = """
SELECT product,
COUNT(*) AS order_count,
SUM(amount * quantity) AS total_sales,
AVG(amount) AS avg_unit_price
FROM sales
GROUP BY product
ORDER BY total_sales DESC
"""
summary = pd.read_sql(sql_agg, con=engine)
print("Résumé des ventes par produit :")
print(summary)
print()
# Étape 4 : Réécrire les résultats récapitulatifs dans la base de données
summary.to_sql("sales_summary", con=engine, if_exists="replace", index=False)
print("Les données de synthèse ont été écrites dans la table sales_summary")
conn.close()
Le résultat d'exécution du code ci-dessus est :
原始数据: order_id product amount quantity order_date 0 1001 笔记本 6999 2 2024-01-10 1 1002 手机 3999 5 2024-01-15 2 1003 平板 2999 3 2024-01-20 3 1004 笔记本 7299 1 2024-02-05 4 1005 手机 4299 4 2024-02-10 5 1006 平板 3199 2 2024-02-18 各产品销售汇总: product 订单数 总销售额 平均单价 0 手机 2 36991 4149.0 1 笔记本 2 21297 7149.0 2 平板 2 15395 3099.0 汇总数据已写入 sales_summary 表
Problèmes courants et notes
1. La connexion doit être fermée après utilisation
Lorsque vous utilisez une connexion native DB-API (comme sqlite3), vous devez fermer manuellement la connexion après l'opération. Il est recommandé d'utiliserwithl'instruction pour la gérer automatiquement :
Exemple
import pandas as pd
# Utilisez l'instruction with ; la connexion est automatiquement fermée à la sortie, et elle ne fuira pas même si une exception se produit
with sqlite3.connect("mydata.db") as conn:
df = pd.read_sql("SELECT * FROM employees", con=conn)
print(df) # La connexion a été automatiquement fermée, mais les données df sont toujours disponibles
2. Ne lisez pas toutes les données d'une grande table directement
Pour les grandes tables avec des millions de lignes ou plus, directementSELECT *lire toutes les données épuisera la mémoire. Vous devriez privilégier le filtrage des données au niveau SQL (WHERE, LIMIT), ou utiliserchunksizelecture par morceaux.
3. read_sql_table ne prend en charge que SQLAlchemy
Si vous utilisez une connexion native DB-API telle que sqlite3 pour l'appelerpd.read_sql_table(), une erreur sera levéeNotImplementedError. Veuillez utiliserpd.read_sql()ou basculez vers un moteur SQLAlchemy.
4. Le paramètre index de to_sql vaut True par défaut
Par défautto_sqll'index de ligne du DataFrame (0, 1, 2...) est également écrit dans la base de données, créant une colonne nomméeindexcolonne, ce qui est généralement redondant. Il est recommandé de passer explicitementindex=False。
5. Les pilotes de base de données doivent être installés séparément
Lorsque SQLAlchemy se connecte à différentes bases de données, vous devez également installer le paquet de pilote correspondant :
| Base de données | Paquet de pilote | Commande d'installation |
|---|---|---|
| MySQL | PyMySQL | pip install pymysql |
| PostgreSQL | psycopg2 | pip install psycopg2-binary |
| SQL Server | pyodbc | pip install pyodbc |
| Oracle | cx_Oracle | pip install cx_Oracle |
| SQLite | Intégré | Non requis |