Pandas pd.read_sql() Function
Pandas Common Functionsread_sql()It is a function in the pandas library used to read data from databases, supporting SQL queries and direct reading of database tables.
Databases are core components of enterprise-level applications, storing large amounts of business data.read_sql()It can connect to various types of databases (such as MySQL, PostgreSQL, SQLite, etc.), execute SQL queries, and convert the results into DataFrames, making subsequent data analysis and processing convenient.
Basic Syntax and Parameters
Syntax Format
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None,
parse_dates=None, chunksize=None, dtype=None, ...)
Parameter Description
| Parameter | Type | Description | Default Value |
|---|---|---|---|
| sql | str or SQLAlchemy Selectable | SQL query statement or table name | Required |
| con | sqlalchemy engine, or sqlite3 connection | Database connection object | Required |
| index_col | str, list of str | Column name used as the row index | None |
| coerce_float | bool | Whether to attempt to convert numeric strings to floating-point numbers | True |
| params | list, tuple, dict | Parameters for SQL parameterized queries | None |
| parse_dates | list, dict | Columns that need to be parsed as dates | None |
| chunksize | int | Iterator size for chunked reading | None |
Return Value
- Return Type：
pd.DataFrameor Iterator[DataFrame] - When
chunksizeWhen None, a single DataFrame is returned. - When set,
chunksizean iterator is returned, and each iteration returns a DataFrame.
Examples
Through the following examples, comprehensively masterread_sql()the various usages of it.
Example 1: Using SQLite Database
SQLite is a lightweight embedded database that does not require a separate database server, making it very suitable for learning and testing.
Example
import pandas as pd
import sqlite3
# Create an in-memory SQLite database and insert test data
conn = sqlite3.connect(':memory:')
# Create a table and insert data
cursor = conn.cursor()
# Create employee table
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Insert test data
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()
# Example 1a: Execute SQL query and read data
# sql: SQL query statement (required)
# con: database connection object (required)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("Execute SQL query:")
print(df_query)
print()
# Example 1b: Directly read the entire table
# You can use the table name as the sql parameter
df_table = pd.read_sql('employees', conn)
print("Read the entire table:")
print(df_table)
print()
# Example 1c: Use index_col to set the index
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Set index column:")
print(df_indexed)
import sqlite3
# Create an in-memory SQLite database and insert test data
conn = sqlite3.connect(':memory:')
# Create a table and insert data
cursor = conn.cursor()
# Create employee table
cursor.execute('''
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
city TEXT,
salary INTEGER
)
''')
# Insert test data
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()
# Example 1a: Execute SQL query and read data
# sql: SQL query statement (required)
# con: database connection object (required)
query = "SELECT name, age, city, salary FROM employees WHERE salary > 8000"
df_query = pd.read_sql(query, conn)
print("Execute SQL query:")
print(df_query)
print()
# Example 1b: Directly read the entire table
# You can use the table name as the sql parameter
df_table = pd.read_sql('employees', conn)
print("Read the entire table:")
print(df_table)
print()
# Example 1c: Use index_col to set the index
df_indexed = pd.read_sql('employees', conn, index_col='id')
print("Set index column:")
print(df_indexed)
Expected running results:
执行 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 explanation:
read_sql()Accepts an SQL query statement as the first parameter.conThe con parameter requires passing in a database connection object, which can be a sqlite3, SQLAlchemy, etc. connection.- You can directly pass in the table name to read the data of the entire table.
index_colThe index_col parameter can specify a column as the row index.
Example 2: Using SQLAlchemy to Connect to Database
SQLAlchemy is the most popular ORM framework in Python, supporting connections to multiple databases.
Example
import pandas as pd
from sqlalchemy import create_engine, text
# Use SQLAlchemy to create an in-memory SQLite database
engine = create_engine('sqlite:///:memory:')
# Create test data
with engine.connect() as conn:
# Create product table
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Insert data
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()
# Example 2a: Use SQLAlchemy engine to read data
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
query,
engine,
params={'category': 'Electronics'} # Parameterized query to prevent SQL injection
)
print("Using parameterized query:")
print(df_params)
print()
# Example 2b: Directly read using the table name
df_all = pd.read_sql('products', engine)
print("Read the entire table:")
print(df_all)
print()
# Example 2c: Try other database connections (taking MySQL as an example)
# Note: The corresponding database driver needs to be installed
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# MySQL connection example
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Note: Actually connecting to MySQL/PostgreSQL requires installing the corresponding driver")
from sqlalchemy import create_engine, text
# Use SQLAlchemy to create an in-memory SQLite database
engine = create_engine('sqlite:///:memory:')
# Create test data
with engine.connect() as conn:
# Create product table
conn.execute(text('''
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL,
stock INTEGER
)
'''))
# Insert data
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()
# Example 2a: Use SQLAlchemy engine to read data
query = "SELECT * FROM products WHERE category = :category"
df_params = pd.read_sql(
query,
engine,
params={'category': 'Electronics'} # Parameterized query to prevent SQL injection
)
print("Using parameterized query:")
print(df_params)
print()
# Example 2b: Directly read using the table name
df_all = pd.read_sql('products', engine)
print("Read the entire table:")
print(df_all)
print()
# Example 2c: Try other database connections (taking MySQL as an example)
# Note: The corresponding database driver needs to be installed
# pip install pymysql # MySQL
# pip install psycopg2 # PostgreSQL
# MySQL connection example
# engine_mysql = create_engine('mysql+pymysql://username:password@host/database')
# df_mysql = pd.read_sql('SELECT * FROM table_name', engine_mysql)
print("Note: Actually connecting to MySQL/PostgreSQL requires installing the corresponding driver")
Expected running results:
使用参数化查询: 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 explanation:
- SQLAlchemy provides a unified database connection interface, supporting multiple databases such as MySQL, PostgreSQL, Oracle, etc.
paramsThe params parameter is used for parameterized queries, which can prevent SQL injection attacks.- Using SQLAlchemy's
create_engine()creates the database engine.
Example 3: Reading and Processing Large Data in Chunks
When the query result contains a large amount of data, you can read it in chunks to save memory.
Example
import pandas as pd
import sqlite3
# Create a large amount of test data
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Create table
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# Insert 10000 test data records
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()
# Example 3a: Read data in chunks
# The chunksize parameter specifies how many rows to return each time
print("Reading data in chunks:")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Use a generator to process each chunk
for i, chunk in enumerate(chunks):
print(f"Chunk {i+1}: {len(chunk)} rows")
print(f" The count of category='A' in this chunk: {len(chunk[chunk['category'] == 'A'])}")
print()
# Example 3b: Aggregation calculation (merge all chunks)
# If global statistics are needed, you can merge all chunks
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)
# Merge results
result = pd.concat(all_chunks, ignore_index=True)
print("Aggregation result:")
print(result)
print()
# Close connection
conn.close()
# Clean up test files
import os
os.remove('test_large.db')
print("Test database has been cleaned up")
import sqlite3
# Create a large amount of test data
conn = sqlite3.connect('test_large.db')
cursor = conn.cursor()
# Create table
cursor.execute('''
CREATE TABLE large_table (
id INTEGER PRIMARY KEY,
value INTEGER,
category TEXT
)
''')
# Insert 10000 test data records
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()
# Example 3a: Read data in chunks
# The chunksize parameter specifies how many rows to return each time
print("Reading data in chunks:")
chunks = pd.read_sql('SELECT * FROM large_table', conn, chunksize=2000)
# Use a generator to process each chunk
for i, chunk in enumerate(chunks):
print(f"Chunk {i+1}: {len(chunk)} rows")
print(f" The count of category='A' in this chunk: {len(chunk[chunk['category'] == 'A'])}")
print()
# Example 3b: Aggregation calculation (merge all chunks)
# If global statistics are needed, you can merge all chunks
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)
# Merge results
result = pd.concat(all_chunks, ignore_index=True)
print("Aggregation result:")
print(result)
print()
# Close connection
conn.close()
# Clean up test files
import os
os.remove('test_large.db')
print("Test database has been cleaned up")
Expected running results:
分块读取数据:
Chunk 1: 2000 行
块 读取 category='A' 的数据
...
块 5: 2000 行
该块 category='A' 的数量: 约 667 行
聚合结果:
category total
0 A 1687500
1 B 1678250
2 Chunks 读取 约 665 行
Code explanation:
chunksizeThe chunksize parameter returns an iterator, and each iteration returns a DataFrame.- Chunked reading is suitable for large datasets and can avoid out-of-memory issues.
- For aggregation queries, you can use
pd.concat()to merge the results of all chunks.
Notes
- To use
read_sql()you need to install the corresponding database driver. - SQLite does not require additional installation and can be used directly.
- MySQL requires
pymysqlormysql-connector-python：pip install pymysql。 - PostgreSQL requires
psycopg2：pip install psycopg2。 - Using parameterized queries (
paramsparameter) can prevent SQL injection attacks. - When reading large tables, use
chunksizethe parameter to read in chunks.
Summary
read_sql()It is a core function in pandas for connecting to databases and reading data. It can execute any SQL query and convert the query results into DataFrame format.
In actual data analysis work, databases are the core storage method for enterprise data. Proficiently masteringread_sql()allows you to directly obtain data from the database for analysis. Note the use of parameterized queries to ensure security, and the use of chunked reading to handle large datasets.