Pandas pd.read_sql()-Funktion
Pandas Häufige Funktionenread_sql()Es ist eine Funktion in der pandas-Bibliothek, die zum Lesen von Daten aus Datenbanken verwendet wird und SQL-Abfragen sowie das direkte Lesen von Datenbanktabellen unterstützt.
Datenbanken sind Kernbestandteile von Unternehmensanwendungen und speichern große Mengen an Geschäftsdaten.read_sql()Es kann sich mit verschiedenen Arten von Datenbanken verbinden (wie MySQL, PostgreSQL, SQLite usw.), SQL-Abfragen ausführen und die Ergebnisse in DataFrames umwandeln, wodurch die anschließende Datenanalyse und -verarbeitung erleichtert wird.
Grundlegende Syntax und Parameter
Syntaxformat
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None,
parse_dates=None, chunksize=None, dtype=None, ...)
Parameterbeschreibung
| Parameter | Type | Beschreibung | Standardwert |
|---|---|---|---|
| sql | str oder SQLAlchemy Selectable | SQL-Abfrageanweisung oder Tabellenname | Erforderlich |
| con | SQLAlchemy-Engine oder sqlite3-Verbindung | Datenbankverbindungsobjekt | Erforderlich |
| index_col | str, Liste von str | Spaltenname, der als Zeilenindex verwendet wird | Keine |
| coerce_float | bool | Ob versucht werden soll, numerische Zeichenfolgen in Gleitkommazahlen umzuwandeln | True |
| params | list, tuple, dict | Parameter für parametrisierte SQL-Abfragen | Keine |
| parse_dates | list, dict | Spalten, die als Datumsangaben geparst werden müssen | Keine |
| chunksize | int | Iteratorgröße für blockweises Lesen | Keine |
Rückgabewert
- Rückgabetyp：
pd.DataFrameoder Iterator[DataFrame] - Wann
chunksizeWenn None, wird ein einzelnes DataFrame zurückgegeben. - Wenn gesetzt,
chunksizewird ein Iterator zurückgegeben, und jede Iteration gibt ein DataFrame zurück.
Beispiele
Anhand der folgenden Beispiele meistern Sie umfassendread_sql()die verschiedenen Verwendungsweisen davon.
Beispiel 1: Verwendung einer SQLite-Datenbank
SQLite ist eine leichtgewichtige eingebettete Datenbank, die keinen separaten Datenbankserver benötigt, was sie sehr gut zum Lernen und Testen geeignet macht.
Beispiel
import pandas as pd
import sqlite3
# Erstelle eine In-Memory-SQLite-Datenbank und füge Testdaten ein
conn = sqlite3.connect(':memory:')
# Erstelle eine Tabelle und füge Daten ein
cursor = conn.cursor()
# Mitarbeitertabelle erstellen
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Testdaten einfügen
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()
# Beispiel 1a: SQL-Abfrage ausführen und Daten lesen
# sql: SQL-Abfrageanweisung (erforderlich)
# con: Datenbankverbindungsobjekt (erforderlich)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("SQL-Abfrage ausführen:")
print(df_query)
print()
# Beispiel 1b: Die gesamte Tabelle direkt lesen
# Sie können den Tabellennamen als SQL-Parameter verwenden
df_table = pd.read_sql('employees', conn)
print("Die gesamte Tabelle lesen:")
print(df_table)
print()
# Beispiel 1c: index_col zum Festlegen des Index verwenden
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Indexspalte festlegen:")
print(df_indexed)
import sqlite3
# Erstelle eine In-Memory-SQLite-Datenbank und füge Testdaten ein
conn = sqlite3.connect(':memory:')
# Erstelle eine Tabelle und füge Daten ein
cursor = conn.cursor()
# Mitarbeitertabelle erstellen
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Testdaten einfügen
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()
# Beispiel 1a: SQL-Abfrage ausführen und Daten lesen
# sql: SQL-Abfrageanweisung (erforderlich)
# con: Datenbankverbindungsobjekt (erforderlich)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("SQL-Abfrage ausführen:")
print(df_query)
print()
# Beispiel 1b: Die gesamte Tabelle direkt lesen
# Sie können den Tabellennamen als SQL-Parameter verwenden
df_table = pd.read_sql('employees', conn)
print("Die gesamte Tabelle lesen:")
print(df_table)
print()
# Beispiel 1c: index_col zum Festlegen des Index verwenden
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Indexspalte festlegen:")
print(df_indexed)
Erwartete Ausführungsergebnisse:
执行 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
Code-Erklärung:
read_sql()Akzeptiert eine SQL-Abfrageanweisung als ersten Parameter.conDer Parameter con erfordert die Übergabe eines Datenbankverbindungsobjekts, das eine sqlite3-, SQLAlchemy- usw. Verbindung sein kann.- Sie können den Tabellennamen direkt übergeben, um die Daten der gesamten Tabelle zu lesen.
index_colDer Parameter index_col kann eine Spalte als Zeilenindex angeben.
Beispiel 2: Verwenden von SQLAlchemy zum Verbinden mit einer Datenbank
SQLAlchemy ist das beliebteste ORM-Framework in Python und unterstützt Verbindungen zu mehreren Datenbanken.
Beispiel
import pandas as pd
from sqlalchemy import create_engine, text
# SQLAlchemy verwenden, um eine In-Memory-SQLite-Datenbank zu erstellen
engine = create_engine('sqlite:///:memory:')
# Testdaten erstellen
with engine.connect() as conn:
# Tabelle products erstellen
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Daten einfügen
products_data = [
('A', 'Electronics', 999.99, 50),
('B', 'Electronics', 499.99, 100),
('C', 'Clothing', 29.99, 200),
('D', 'Food', 5.99, 500),
('E', 'Books', 19.99, 150)
]
conn.execute(text('''
INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)
'''), products_data)
conn.commit()
# Beispiel 2a: SQLAlchemy-Engine zum Lesen von Daten verwenden
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
Abfrage,
engine,
params={'category': 'Electronics'} # Parametrisierte Abfrage zur Verhinderung von SQL-Injection
)
print("Verwenden einer parametrisierten Abfrage:")
print(df_params)
print()
# Beispiel 2b: Direktes Lesen mit dem Tabellennamen
df_all = pd.read_sql('products', engine)
print("Die gesamte Tabelle lesen:")
print(df_all)
print()
# Beispiel 2c: Andere Datenbankverbindungen ausprobieren (am Beispiel von MySQL)
# Hinweis: Der entsprechende Datenbanktreiber muss installiert werden
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# MySQL-Verbindungsbeispiel
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Hinweis: Das tatsächliche Herstellen einer Verbindung zu MySQL/PostgreSQL erfordert die Installation des entsprechenden Treibers")
from sqlalchemy import create_engine, text
# SQLAlchemy verwenden, um eine In-Memory-SQLite-Datenbank zu erstellen
engine = create_engine('sqlite:///:memory:')
# Testdaten erstellen
with engine.connect() as conn:
# Tabelle products erstellen
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Daten einfügen
products_data = [
('A', 'Electronics', 999.99, 50),
('B', 'Electronics', 499.99, 100),
('C', 'Clothing', 29.99, 200),
('D', 'Food', 5.99, 500),
('E', 'Books', 19.99, 150)
]
conn.execute(text('''
INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)
'''), products_data)
conn.commit()
# Beispiel 2a: SQLAlchemy-Engine zum Lesen von Daten verwenden
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
Abfrage,
engine,
params={'category': 'Electronics'} # Parametrisierte Abfrage zur Verhinderung von SQL-Injection
)
print("Verwenden einer parametrisierten Abfrage:")
print(df_params)
print()
# Beispiel 2b: Direktes Lesen mit dem Tabellennamen
df_all = pd.read_sql('products', engine)
print("Die gesamte Tabelle lesen:")
print(df_all)
print()
# Beispiel 2c: Andere Datenbankverbindungen ausprobieren (am Beispiel von MySQL)
# Hinweis: Der entsprechende Datenbanktreiber muss installiert werden
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# MySQL-Verbindungsbeispiel
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Hinweis: Das tatsächliche Herstellen einer Verbindung zu MySQL/PostgreSQL erfordert die Installation des entsprechenden Treibers")
Erwartete Ausführungsergebnisse:
使用参数化查询: 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
Code-Erklärung:
- SQLAlchemy bietet eine einheitliche Datenbankverbindungsschnittstelle, die mehrere Datenbanken wie MySQL, PostgreSQL, Oracle usw. unterstützt.
paramsDer Parameter params wird für parametrisierte Abfragen verwendet, wodurch SQL-Injection-Angriffe verhindert werden können.- Mit SQLAlchemy
create_engine()wird die Datenbank-Engine erstellt.
Beispiel 3: Große Datenmengen in Blöcken lesen und verarbeiten
Wenn das Abfrageergebnis eine große Datenmenge enthält, können Sie es in Blöcken lesen, um Speicher zu sparen.
Beispiel
import pandas as pd
import sqlite3
# Eine große Menge Testdaten erstellen
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Tabelle erstellen
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# 10000 Testdatensätze einfügen
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()
# Beispiel 3a: Daten in Blöcken lesen
# Der Parameter chunksize gibt an, wie viele Zeilen jeweils zurückgegeben werden
print("Daten werden in Blöcken gelesen:")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Generator verwenden, um jeden Block zu verarbeiten
for i, chunk in enumerate(chunks):
print(f"Block {i+1}: {len(chunk)} Zeilen")
print(f" Die Anzahl von category='A' in diesem Block: {len(chunk[chunk['category'] == 'A'])}")
print()
# Beispiel 3b: Aggregationsberechnung (alle Chunks zusammenführen)
# Wenn globale Statistiken benötigt werden, kann man alle Chunks zusammenführen
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)
# Ergebnisse zusammenführen
result = pd.concat(all_chunks, ignore_index=True)
print("Aggregationsergebnis:")
print(result)
print()
# Verbindung schließen
conn.close()
# Testdateien aufräumen
import os
os.remove('test_large.db')
print("Testdatenbank wurde bereinigt")
import sqlite3
# Eine große Menge Testdaten erstellen
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Tabelle erstellen
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# 10000 Testdatensätze einfügen
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()
# Beispiel 3a: Daten in Blöcken lesen
# Der Parameter chunksize gibt an, wie viele Zeilen jeweils zurückgegeben werden
print("Daten werden in Blöcken gelesen:")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Generator verwenden, um jeden Block zu verarbeiten
for i, chunk in enumerate(chunks):
print(f"Block {i+1}: {len(chunk)} Zeilen")
print(f" Die Anzahl von category='A' in diesem Block: {len(chunk[chunk['category'] == 'A'])}")
print()
# Beispiel 3b: Aggregationsberechnung (alle Chunks zusammenführen)
# Wenn globale Statistiken benötigt werden, kann man alle Chunks zusammenführen
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)
# Ergebnisse zusammenführen
result = pd.concat(all_chunks, ignore_index=True)
print("Aggregationsergebnis:")
print(result)
print()
# Verbindung schließen
conn.close()
# Testdateien aufräumen
import os
os.remove('test_large.db')
print("Testdatenbank wurde bereinigt")
Erwartete Ausführungsergebnisse:
分块读取数据:
Chunk 1: 2000 行
块 读取 category='A' 的数据
...
块 5: 2000 行
该块 category='A' 的数量: 约 667 行
聚合结果:
category total
0 A 1687500
1 B 1678250
2 Chunks 读取 约 665 行
Code-Erklärung:
chunksizeDer Parameter chunksize gibt einen Iterator zurück, und jede Iteration gibt ein DataFrame zurück.- Chunkweises Lesen eignet sich für große Datensätze und kann Out-of-Memory-Probleme vermeiden.
- Für Aggregationsabfragen können Sie
pd.concat()verwenden, um die Ergebnisse aller Chunks zusammenzuführen.
Hinweise
- Um
read_sql()Sie müssen den entsprechenden Datenbanktreiber installieren. - SQLite erfordert keine zusätzliche Installation und kann direkt verwendet werden.
- MySQL erfordert
pymysqlodermysql-connector-python：pip install pymysql。 - PostgreSQL erfordert
psycopg2：pip install psycopg2。 - Die Verwendung parametrisierter Abfragen (
paramsParameter) kann SQL-Injection-Angriffe verhindern. - Beim Lesen großer Tabellen verwenden Sie
chunksizeden Parameter, um blockweise zu lesen.
Zusammenfassung
read_sql()Es ist eine Kernfunktion in pandas zum Verbinden mit Datenbanken und zum Lesen von Daten. Sie kann jede SQL-Abfrage ausführen und die Abfrageergebnisse in das DataFrame-Format umwandeln.
In der tatsächlichen Datenanalysearbeit sind Datenbanken die zentrale Speichermethode für Unternehmensdaten. Die sichere Beherrschungread_sql()ermöglicht es Ihnen, Daten direkt aus der Datenbank zur Analyse abzurufen. Beachten Sie die Verwendung parametrisierter Abfragen zur Gewährleistung der Sicherheit und die Verwendung von blockweisem Lesen, um große Datensätze zu verarbeiten.