Fonction Pandas pd.read_sql()
Fonctions courantes de Pandasread_sql()C'est une fonction de la bibliothèque pandas utilisée pour lire des données à partir de bases de données, prenant en charge les requêtes SQL et la lecture directe de tables de base de données.
Les bases de données sont des composants essentiels des applications d'entreprise, stockant de grandes quantités de données métier.read_sql()Elle peut se connecter à divers types de bases de données (telles que MySQL, PostgreSQL, SQLite, etc.), exécuter des requêtes SQL et convertir les résultats en DataFrames, ce qui facilite l'analyse et le traitement ultérieurs des données.
Syntaxe de base et paramètres
Format de syntaxe
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None,
parse_dates=None, chunksize=None, dtype=None, ...)
Description du paramètre
| Paramètre | Type | Description | Valeur par défaut |
|---|---|---|---|
| sql | str ou SQLAlchemy Selectable | Instruction de requête SQL ou nom de table | Requis |
| con | moteur SQLAlchemy, ou connexion sqlite3 | Objet de connexion à la base de données | Requis |
| index_col | str, liste de str | Nom de colonne utilisé comme index de ligne | Aucune |
| coerce_float | bool | S'il faut tenter de convertir les chaînes numériques en nombres à virgule flottante | True |
| params | list, tuple, dict | Paramètres pour les requêtes SQL paramétrées | Aucune |
| parse_dates | list, dict | Colonnes à analyser comme dates | Aucune |
| chunksize | int | Taille de l'itérateur pour la lecture par blocs | Aucune |
Valeur de retour
- Type de retour：
pd.DataFrameou Iterator[DataFrame] - Quand
chunksizeLorsque None, un seul DataFrame est renvoyé. - Lorsqu'il est défini,
chunksizeun itérateur est renvoyé, et chaque itération renvoie un DataFrame.
Exemples
À travers les exemples suivants, maîtrisez de manière exhaustiveread_sql()les différentes utilisations de celui-ci.
Exemple 1 : Utilisation d'une base de données SQLite
SQLite est une base de données embarquée légère qui ne nécessite pas de serveur de base de données séparé, ce qui la rend très adaptée à l'apprentissage et aux tests.
Exemple
import pandas as pd
import sqlite3
# Créer une base de données SQLite en mémoire et insérer des données de test
conn = sqlite3.connect(':memory:')
# Créer une table et insérer des données
cursor = conn.cursor()
# Créer la table des employés
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Insérer des données de test
employees_data = [
(1, 'Tom', 28, 'Beijing', 8000),
(2, 'Jerry', 35, 'Shanghai', 12000),
(3, 'Mike', 42, 'Guangzhou', 15000),
(4, 'Lucy', 26, 'Shenzhen', 7000),
(5, 'John', 31, 'Beijing', 9500)
]
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?)', employees_data)
conn.commit()
# Exemple 1a : Exécuter une requête SQL et lire les données
# sql : instruction de requête SQL (obligatoire)
# con : objet de connexion à la base de données (obligatoire)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("Exécuter la requête SQL :")
print(df_query)
print()
# Exemple 1b : Lire directement toute la table
# Vous pouvez utiliser le nom de la table comme paramètre SQL
df_table = pd.read_sql('employees', conn)
print("Lire toute la table :")
print(df_table)
print()
# Exemple 1c : Utiliser index_col pour définir l'index
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Définir la colonne d'index :")
print(df_indexed)
import sqlite3
# Créer une base de données SQLite en mémoire et insérer des données de test
conn = sqlite3.connect(':memory:')
# Créer une table et insérer des données
cursor = conn.cursor()
# Créer la table des employés
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Insérer des données de test
employees_data = [
(1, 'Tom', 28, 'Beijing', 8000),
(2, 'Jerry', 35, 'Shanghai', 12000),
(3, 'Mike', 42, 'Guangzhou', 15000),
(4, 'Lucy', 26, 'Shenzhen', 7000),
(5, 'John', 31, 'Beijing', 9500)
]
cursor.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?)', employees_data)
conn.commit()
# Exemple 1a : Exécuter une requête SQL et lire les données
# sql : instruction de requête SQL (obligatoire)
# con : objet de connexion à la base de données (obligatoire)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("Exécuter la requête SQL :")
print(df_query)
print()
# Exemple 1b : Lire directement toute la table
# Vous pouvez utiliser le nom de la table comme paramètre SQL
df_table = pd.read_sql('employees', conn)
print("Lire toute la table :")
print(df_table)
print()
# Exemple 1c : Utiliser index_col pour définir l'index
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Définir la colonne d'index :")
print(df_indexed)
Résultats d'exécution attendus :
执行 SQL 查询:
name age city salary
0 Tom 28 Beijing 8000
1 Jerry 35 Shanghai 12000
2 Mike 42 Guangzhou 15000
chunksize
空字符串 NaN NaN NaN NaN
3 Lucy 26 Shenzhen 7000
4 John 31 Beijing 9500
读取整个表:
id name age city salary
0 1 Tom 28 Beijing 8000
1 pandas 的 read_sql() 和 read_sql_query() 函数说明 read_sql() 是统一的接口，可以接受 SQL 语句或表名 read_sql_query() 只接受 SQL 语句，返回查询结果 read_sql_table() 只接受表名，返回整个表
2 3 Mike 42 Guangzhou 15000
3 4 Lucy 26 Shenzhen 7000
4 5 John 31 Beijing 9500
设置索引列:
name age city salary
id
1 Tom 28 Beijing 8000
2 Jerry 35 Shanghai 12000
3 Mike 42 Guangzhou 15000
4 Lucy 26 Shenzhen 7000
5 John 31 Beijing 9500
Explication du code :
read_sql()Accepte une instruction de requête SQL comme premier paramètre.conLe paramètre con nécessite de passer un objet de connexion à une base de données, qui peut être une connexion sqlite3, SQLAlchemy, etc.- Vous pouvez directement passer le nom de la table pour lire les données de toute la table.
index_colLe paramètre index_col peut spécifier une colonne comme index de ligne.
Exemple 2 : Utilisation de SQLAlchemy pour se connecter à une base de données
SQLAlchemy est le framework ORM le plus populaire en Python, prenant en charge les connexions à plusieurs bases de données.
Exemple
import pandas as pd
from sqlalchemy import create_engine, text
# Utiliser SQLAlchemy pour créer une base de données SQLite en mémoire
engine = create_engine('sqlite:///:memory:')
# Créer des données de test
with engine.connect() as conn:
# Créer la table products
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Insérer des données
products_data = [
('A', 'Électronique', 999.99, 50),
('B', 'Électronique', 499.99, 100),
('C', 'Vêtements', 29.99, 200),
('D', 'Alimentation', 5.99, 500),
('E', 'Livres', 19.99, 150)
]
conn.execute(text('''
INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)
'''), products_data)
conn.commit()
# Exemple 2a : Utiliser le moteur SQLAlchemy pour lire des données
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
requête,
engine,
params={'category': 'Electronics'} # Requête paramétrée pour prévenir l'injection SQL
)
print("Utilisation d'une requête paramétrée :")
print(df_params)
print()
# Exemple 2b : lire directement en utilisant le nom de la table
df_all = pd.read_sql('products', engine)
print("Lire toute la table :")
print(df_all)
print()
# Exemple 2c : essayer d'autres connexions à la base de données (en prenant MySQL comme exemple)
# Remarque : le pilote de base de données correspondant doit être installé
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# Exemple de connexion MySQL
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Remarque : en réalité, la connexion à MySQL/PostgreSQL nécessite l'installation du pilote correspondant")
from sqlalchemy import create_engine, text
# Utiliser SQLAlchemy pour créer une base de données SQLite en mémoire
engine = create_engine('sqlite:///:memory:')
# Créer des données de test
with engine.connect() as conn:
# Créer la table products
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Insérer des données
products_data = [
('A', 'Électronique', 999.99, 50),
('B', 'Électronique', 499.99, 100),
('C', 'Vêtements', 29.99, 200),
('D', 'Alimentation', 5.99, 500),
('E', 'Livres', 19.99, 150)
]
conn.execute(text('''
INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)
'''), products_data)
conn.commit()
# Exemple 2a : Utiliser le moteur SQLAlchemy pour lire des données
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
requête,
engine,
params={'category': 'Electronics'} # Requête paramétrée pour prévenir l'injection SQL
)
print("Utilisation d'une requête paramétrée :")
print(df_params)
print()
# Exemple 2b : lire directement en utilisant le nom de la table
df_all = pd.read_sql('products', engine)
print("Lire toute la table :")
print(df_all)
print()
# Exemple 2c : essayer d'autres connexions à la base de données (en prenant MySQL comme exemple)
# Remarque : le pilote de base de données correspondant doit être installé
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# Exemple de connexion MySQL
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Remarque : en réalité, la connexion à MySQL/PostgreSQL nécessite l'installation du pilote correspondant")
Résultats d'exécution attendus :
使用参数化查询: id name category price stock 0 1 A Electronics 999.99 50 的 read_sql() 配对 write_frame() 已弃用，使用 to_sql() 方法替代 to_sql() 可以将 DataFrame 写入数据库表 2 2 B Electronics 499.99 100 读取整个表: id 名称 类别 价格 库存 0 1 A Electronics 999.99 50 1 2 B ... 读取 499. 参数 100 2 3 C Clothing 29.99 200 3 4 配对 Food 5.99 500 4 5 E Books 19.99 150
Explication du code :
- SQLAlchemy fournit une interface unifiée de connexion aux bases de données, prenant en charge plusieurs bases de données telles que MySQL, PostgreSQL, Oracle, etc.
paramsLe paramètre params est utilisé pour les requêtes paramétrées, ce qui peut empêcher les attaques par injection SQL.- En utilisant SQLAlchemy
create_engine()crée le moteur de base de données.
Exemple 3 : Lecture et traitement de données volumineuses par blocs
Lorsque le résultat de la requête contient une grande quantité de données, vous pouvez le lire par blocs pour économiser de la mémoire.
Exemple
import pandas as pd
import sqlite3
# Créer une grande quantité de données de test
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Créer une table
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# Insérer 10000 enregistrements de données de test
import random
large_data = [(i, random.randint(1, 1000), random.choice(['A', 'B', 'C']))
for i in range(1, 10001)]
cursor.executemany('INSERT INTO large_table VALUES (?, ?, ?)', large_data)
conn.commit()
# Exemple 3a : Lire les données par blocs
# Le paramètre chunksize indique combien de lignes renvoyer à chaque fois
print("Lecture des données par blocs :")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Utiliser un générateur pour traiter chaque bloc
for i, chunk in enumerate(chunks):
print(f"Bloc {i+1} : {len(chunk)} lignes")
print(f" Le nombre de category='A' dans ce bloc : {len(chunk[chunk['category'] == 'A'])}")
print()
# Exemple 3b : Calcul d'agrégation (fusionner tous les morceaux)
# Si des statistiques globales sont nécessaires, vous pouvez fusionner tous les morceaux
all_chunks = []
chunks = pd.read_sql('SELECT category, SUM(value) as total FROM large_table GROUP BY category',
conn, chunksize=1000)
for chunk in chunks:
all_chunks.append(chunk)
# Fusionner les résultats
result = pd.concat(all_chunks, ignore_index=True)
print("Résultat de l'agrégation :")
print(result)
print()
# Fermer la connexion
conn.close()
# Nettoyer les fichiers de test
import os
os.remove('test_large.db')
print("La base de données de test a été nettoyée")
import sqlite3
# Créer une grande quantité de données de test
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Créer une table
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# Insérer 10000 enregistrements de données de test
import random
large_data = [(i, random.randint(1, 1000), random.choice(['A', 'B', 'C']))
for i in range(1, 10001)]
cursor.executemany('INSERT INTO large_table VALUES (?, ?, ?)', large_data)
conn.commit()
# Exemple 3a : Lire les données par blocs
# Le paramètre chunksize indique combien de lignes renvoyer à chaque fois
print("Lecture des données par blocs :")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Utiliser un générateur pour traiter chaque bloc
for i, chunk in enumerate(chunks):
print(f"Bloc {i+1} : {len(chunk)} lignes")
print(f" Le nombre de category='A' dans ce bloc : {len(chunk[chunk['category'] == 'A'])}")
print()
# Exemple 3b : Calcul d'agrégation (fusionner tous les morceaux)
# Si des statistiques globales sont nécessaires, vous pouvez fusionner tous les morceaux
all_chunks = []
chunks = pd.read_sql('SELECT category, SUM(value) as total FROM large_table GROUP BY category',
conn, chunksize=1000)
for chunk in chunks:
all_chunks.append(chunk)
# Fusionner les résultats
result = pd.concat(all_chunks, ignore_index=True)
print("Résultat de l'agrégation :")
print(result)
print()
# Fermer la connexion
conn.close()
# Nettoyer les fichiers de test
import os
os.remove('test_large.db')
print("La base de données de test a été nettoyée")
Résultats d'exécution attendus :
分块读取数据:
Chunk 1: 2000 行
块 读取 category='A' 的数据
...
块 5: 2000 行
该块 category='A' 的数量: 约 667 行
聚合结果:
category total
0 A 1687500
1 B 1678250
2 Chunks 读取 约 665 行
Explication du code :
chunksizeLe paramètre chunksize renvoie un itérateur, et chaque itération renvoie un DataFrame.- La lecture par blocs convient aux grands ensembles de données et peut éviter les problèmes de mémoire insuffisante.
- Pour les requêtes d'agrégation, vous pouvez utiliser
pd.concat()pour fusionner les résultats de tous les blocs.
Notes
- Pour utiliser
read_sql()vous devez installer le pilote de base de données correspondant. - SQLite ne nécessite aucune installation supplémentaire et peut être utilisé directement.
- MySQL nécessite
pymysqloumysql-connector-python：pip install pymysql。 - PostgreSQL nécessite
psycopg2：pip install psycopg2。 - L'utilisation de requêtes paramétrées (
paramsparamètre) peut prévenir les attaques par injection SQL. - Lors de la lecture de grandes tables, utilisez
chunksizele paramètre pour lire par blocs.
Résumé
read_sql()C'est une fonction essentielle de pandas pour se connecter aux bases de données et lire des données. Elle peut exécuter n'importe quelle requête SQL et convertir les résultats de la requête au format DataFrame.
Dans le travail réel d'analyse de données, les bases de données sont le principal moyen de stockage des données d'entreprise. Maîtriser parfaitementread_sql()vous permet d'obtenir directement des données de la base de données pour l'analyse. Veillez à utiliser des requêtes paramétrées pour garantir la sécurité, et à lire par blocs pour gérer de grands ensembles de données.