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'utiliserpd.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

from sqlalchemy import create_engine

# 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

import sqlite3

# 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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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

import pandas as pd
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 pandas as pd
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 sqlite3
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
Autres extensions