Pandas Read SQL Database

Pandas provides a set of functions for directly interacting with SQL databases. Query results can be read directly into a DataFrame, and a DataFrame can also be written back to the database. This allows data analysts to avoid manually handling database connections and result parsing, greatly simplifying the interaction between databases and Python.

Core Function Overview

Function	Purpose	Return Type
`pd.read_sql()`	Executes SQL queries or reads an entire table (a general-purpose function compatible with both scenarios)	DataFrame
`pd.read_sql_query()`	Executes SQL query statements, suitable for complex queries	DataFrame
`pd.read_sql_table()`	Directly reads an entire table, only supports SQLAlchemy connections	DataFrame
`DataFrame.to_sql()`	Writes a DataFrame to a database table	None / int

In practical work, it is recommended to usepd.read_sql()because it automatically determines whether to execute a query or read an entire table based on the passed parameters, offering the best compatibility.pd.read_sql_table()Only supports SQLAlchemy engine connections; it does not support nativesqlite3and other DB-API connections.

Establishing a Database Connection

Pandas itself does not connect directly to databases. It requires a third-party library to establish a connection, which is then passed in. There are two mainstream approaches:SQLAlchemy engine(recommended) andnative DB-API connection(lightweight and simple).

Method 1: SQLAlchemy (Recommended)

SQLAlchemy is Python's most mainstream database toolkit. It supports all major databases and has the best compatibility with Pandas:

pip install sqlalchemy

Example

from sqlalchemy import create_engine

# SQLAlchemy connection string format: database type + driver://username:password@host:port/database name
# SQLite (file-based database, no username or password needed)
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")

# Verify whether the connection is successful
with engine.connect() as conn:
print("Connection successful")

Method 2: Native sqlite3 Connection (SQLite Only)

Python's built-insqlite3module requires no additional installation and is suitable for lightweight local SQLite databases:

Example

import sqlite3

# Connect to a SQLite file database (automatically created if the file does not exist)
conn = sqlite3.connect("mydata.db")

# Connect to an in-memory database (data disappears after the program exits; suitable for testing)
conn_memory = sqlite3.connect(":memory:")

# The connection must be manually closed after use
# conn.close()

pd.read_sql() Reading Data

Basic Syntax

pd.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Main parameter descriptions:

Parameter	Type	Description
`sql`	str	SQL query statement, or table name (related to the`con`type)
`con`	Connection object	SQLAlchemy engine or DB-API connection object
`index_col`	str or list	Sets the specified column as the row index of the DataFrame
`params`	list or dict	Parameter values for SQL parameterized queries, preventing SQL injection
`parse_dates`	list or dict	Parses the specified column as datetime type
`chunksize`	int	Reads in chunks, returning an iterator of DataFrames with the specified number of rows per chunk

1. Reading an Entire Table

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Read the entire employees table
df = pd.read_sql("employees", con=engine)
print(df.head())
print(f"Total {len(df)} rows, {len(df.columns)} columns")

2. Executing SQL Queries

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Query with conditions
df = pd.read_sql("SELECT * FROM employees WHERE department = 'IT'", con=engine)

# Multi-table join query
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. Parameterized Queries (Preventing SQL Injection)

When query conditions come from user input,always use parameterized queriesand do not use string concatenation for SQL:

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# ❌ Dangerous approach: string concatenation of SQL, risk of SQL injection
dept = "IT"
# df = pd.read_sql(f"SELECT * FROM employees WHERE department = '{dept}'", engine)

# ✅ Safe approach: use ? placeholders (sqlite3) or :name named parameters (SQLAlchemy)
# SQLite / DB-API style (using ?)
df = pd.read_sql(
"SELECT * FROM employees WHERE department = ? AND salary > ?",
con=engine,
params=["IT", 8000] # params is a list, replacing ? positionally
)

# SQLAlchemy named parameter style (using :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. Setting the Index Column

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Set the id column in the database as the row index of the DataFrame
df = pd.read_sql("SELECT * FROM employees", con=engine, index_col="id")
print(df.head())

# Use multiple columns as a composite index
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
index_col=["year", "month"] # Composite index
)

5. Parsing Date Columns

Date fields stored in the database are read as strings by default. Using theparse_datesparameter, they can be directly parsed intodatetime：

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Parse the created_at and updated_at columns as datetime type
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]

# You can also specify the parsing format (for non-standard date formats)
df = pd.read_sql(
"SELECT * FROM orders",
con=engine,
parse_dates={"created_at": "%Y%m%d"} # Parse a format like "20240115" as a date
)

Reading Large Data in Chunks (chunksize)

When a database table has a very large amount of data, reading it all into memory at once can cause OOM (out of memory). Using thechunksizeparameter, data can be read in batches, processing only a portion at a time:

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:password@localhost/bigdata")

# chunksize=10000 means reading 10000 rows at a time, returning an iterator
chunks = pd.read_sql("SELECT * FROM large_table", con=engine, chunksize=10000)

# Process chunk by chunk (only 10000 rows in memory at a time)
result_list = []
for i, chunk in enumerate(chunks):
# Apply processing logic to each chunk (e.g., filtering, aggregation, etc.)
processed = chunk[chunk["status"] == "active"]
result_list.append(processed)
print(f"Processed chunk {i+1}, valid rows in current chunk: {len(processed)}")

# Combine all processed chunks into a single DataFrame
final_df = pd.concat(result_list, ignore_index=True)
print(f"Final valid data rows: {len(final_df)}")

pd.read_sql_query() and pd.read_sql_table()

pd.read_sql_query(): Only Executes Queries

andpd.read_sql()The functionality is basically the same, but it only accepts SQL query statements, not table names. The parameters are exactly the same, suitable for scenarios where you need to clearly distinguish between "query" and "read table" operations:

Example

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(): Reads an Entire Table (SQLAlchemy Only)

pd.read_sql_table()Specifically used to read an entire table, with support for filtering columns and rows via parameters, butonly supports SQLAlchemy engine connectionsand does not support native connections such as sqlite3:

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Read specified columns from the employees table
df = pd.read_sql_table(
"employees",
con=engine,
columns=["id", "name", "salary", "department"] # Only read these columns
)

# Also supports specifying schema (the mode in the database)
df = pd.read_sql_table(
"employees",
con=engine,
schema="hr" # Read the employees table under the hr schema
)

print(df.head())

Writing a DataFrame to a Database (to_sql)

UsageDataFrame.to_sql()You can write a DataFrame to a database, with support for creating a new table, appending data, or overwriting the original table:

Basic Syntax

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

if_existsThe parameter determines the behavior when the target table already exists:

if_exists Value	Behavior	Use Case
`'fail'`(Default)	Raises an error if the table already exists	Prevents accidentally overwriting existing data
`'replace'`	Drops the original table first, then recreates the table and writes to it	Full data refresh
`'append'`	Appends data to an existing table without changing the table structure	Incrementally writes new data

Example

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydata.db")

# Prepare sample data
df = pd.DataFrame({
"name": ["Zhang San", "Li Si", "Wang Wu"],
"department": ["IT", "HR", "IT"],
"salary": [12000, 8000, 15000]
})

# Write the DataFrame to the employees table; replace it if it already exists
df.to_sql(
"employees",
con=engine,
if_exists="replace", # Overwrite the original data
index=False # Do not write the DataFrame's row index to the database (usually not needed)
)

# Append new data to the existing table (note: column names and data types must match)
new_employees = pd.DataFrame({
"name": ["Zhao Liu"],
"department": ["Finance"],
"salary": [11000]
})
new_employees.to_sql("employees", con=engine, if_exists="append", index=False)

# Verify the write result
result = pd.read_sql("SELECT * FROM employees", con=engine)
print(result)

Specifying Column Data Types

When writing, you can use thedtypeparameter to explicitly specify the type of each column in the database:

Example

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(), # Integer type
"name": String(50), # Variable-length string, maximum 50 characters
"score": Float() # Floating-point number
}
)

Complete Usage Example

Below is a complete example of reading sales data from a SQLite database and analyzing it:

Example

import pandas as pd
import sqlite3
from sqlalchemy import create_engine

# Step 1: Prepare the test database
engine = create_engine("sqlite:///sales.db")
conn = sqlite3.connect("sales.db")

# Create sample data and write it to the database
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)

# Step 2: Read the data and parse the dates
df = pd.read_sql(
"SELECT * FROM sales",
con=engine,
parse_dates=["order_date"]
)
print("Raw data:")
print(df)
print()

# Step 3: Calculate sales by product
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("Sales summary by product:")
print(summary)
print()

# Step 4: Write the summary results back to the database
summary.to_sql("sales_summary", con=engine, if_exists="replace", index=False)
print("Summary data has been written to the sales_summary table")

conn.close()

The execution result of the above code is:

原始数据：
   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 表

Common Issues and Notes

1. The connection needs to be closed after use

When using a DB-API native connection (such as sqlite3), you need to manually close the connection after the operation. It is recommended to use thewithstatement to manage it automatically:

Example

import sqlite3
import pandas as pd

# Use the with statement; the connection is automatically closed when exiting, and it will not leak even if an exception occurs
with sqlite3.connect("mydata.db") as conn:
df = pd.read_sql("SELECT * FROM employees", con=conn)

print(df) # The connection has been automatically closed, but the df data is still available

2. Do not read all data from a large table directly

For large tables with millions of rows or more, directlySELECT *reading all the data will exhaust memory. You should prioritize filtering data at the SQL level (WHERE, LIMIT), or usechunksizechunked reading.

3. read_sql_table only supports SQLAlchemy

If you use a DB-API native connection such as sqlite3 to call itpd.read_sql_table(), an error will be raisedNotImplementedError. Please usepd.read_sql()or switch to a SQLAlchemy engine.

4. The index parameter of to_sql defaults to True

By defaultto_sqlthe DataFrame's row index (0, 1, 2...) is also written to the database, creating a column namedindexcolumn, which is usually redundant. It is recommended to explicitly passindex=False。

5. Database drivers need to be installed separately

When SQLAlchemy connects to different databases, you also need to install the corresponding driver package:

Database	Driver package	Installation command
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	Built-in	No installation required

Other extensions

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas Read SQL Database

Core Function Overview

Establishing a Database Connection

Method 1: SQLAlchemy (Recommended)

Example

Method 2: Native sqlite3 Connection (SQLite Only)

Example

pd.read_sql() Reading Data

Basic Syntax

1. Reading an Entire Table

Example

2. Executing SQL Queries

Example

3. Parameterized Queries (Preventing SQL Injection)

Example

4. Setting the Index Column

Example

5. Parsing Date Columns

Example

Reading Large Data in Chunks (chunksize)

Example

pd.read_sql_query() and pd.read_sql_table()

pd.read_sql_query(): Only Executes Queries

Example

pd.read_sql_table(): Reads an Entire Table (SQLAlchemy Only)

Example

Writing a DataFrame to a Database (to_sql)

Basic Syntax

Example

Specifying Column Data Types

Example

Complete Usage Example

Example

Common Issues and Notes

Example