Use pandas’ read_sql_query(sql, con) to run a SQL query and receive its rows as a DataFrame. For a local SQLite database, Python’s built-in sqlite3 module is the simplest route. For PostgreSQL and other server databases, use the database’s driver with a SQLAlchemy connection, or an appropriate ADBC connection.
Choose the connection method for your database
| Connection path | Best suited to | What to know |
|---|---|---|
sqlite3 |
A local SQLite file or an in-memory SQLite database | Included with Python, and pandas supports its connections directly. SQLite’s SQL and type behavior can differ from server databases. pandas read_sql documentation |
| SQLAlchemy plus a database driver | PostgreSQL or another database supported by the appropriate SQLAlchemy dialect and DBAPI driver | Install the driver for your database, use the correct URL format, and manage the connection or engine lifecycle. SQLAlchemy engine documentation |
| ADBC connection | A database with an appropriate ADBC driver, particularly when native-type or high-performance I/O is useful | Driver and database support vary; verify compatibility in the current pandas and ADBC documentation. pandas read_sql documentation |
The pandas API accepts ADBC connections, SQLAlchemy connectables, and sqlite3 connections. For a DB-API connection other than SQLite, the documented general integration path is SQLAlchemy. A generic DB-API connection is not necessarily interchangeable with a documented SQLAlchemy connection.
As an Amazon Associate I earn from qualifying purchases.
Read a local SQLite database into a DataFrame
Open the database file with sqlite3.connect(), then pass the connection and a SQL query to read_sql_query():
import sqlite3
import pandas as pd
with sqlite3.connect("example.db") as con:
df = pd.read_sql_query(
"SELECT id, name FROM customers",
con,
)
print(df.head())Replace example.db and the query with your file and table. The with block closes the connection when execution leaves the block. For SQLite specifically, pandas documents that read_sql() takes a SQL query; passing only a table name results in an error. See the pandas read_sql documentation.
#1 Best Overall
Connect to a server database with SQLAlchemy
Install pandas, SQLAlchemy, and the DBAPI driver for your database. Then create an engine with the URL format required by that database and driver. For example, SQLAlchemy documents PostgreSQL URLs using the psycopg2 driver; the exact URL and driver depend on the backend. Check SQLAlchemy’s engine documentation for URL formats.
import pandas as pd
from sqlalchemy import create_engine, text
# Set database_url through your application's secret-management approach.
engine = create_engine(database_url)
query = text(
"SELECT id, name FROM customers WHERE status = :status"
)
with engine.connect() as conn:
df = pd.read_sql_query(
query,
conn,
params={"status": "active"},
)
print(df.head())database_url represents a value your application supplies; don’t put real credentials directly in source code. The example uses a SQLAlchemy text() query with a named parameter. Close connections according to their lifecycle, and dispose of an engine when your application is finished with it. pandas assigns callers responsibility for closing SQLAlchemy connectables and disposing of engines; it closes string connection inputs automatically. pandas connection lifecycle guidance
Bind query values safely
Pass values separately with the params argument rather than building SQL by interpolating untrusted input. The placeholder syntax depends on the database driver; use the convention it supports. pandas warns that it does not sanitize SQL statements and forwards them to the underlying driver, which may or may not sanitize them. Read pandas’ SQL execution guidance.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →In the SQLAlchemy example above, :status is a named bind parameter and {"status": "active"} supplies its value. Keep SQL structure separate from data values; do not treat params as a way to insert arbitrary table or column names.
Shape and handle the query results
- Read only what you need: Select relevant columns and filter rows in SQL to avoid bringing unnecessary data into Python.
- Process large results in chunks: Set
chunksizeto receive an iterator of DataFrames instead of loading the entire result into one DataFrame. Choose a chunk size based on row width, query, and available memory; pandas does not prescribe a universal value. - Convert dates deliberately: Use
parse_dateswhen date conversion is needed, and check the documented formats and behavior for your data. - Control the DataFrame shape and types: The API also supports
index_coland dtype-related options. Verify types explicitly when downstream analysis relies on them.
For the full set of arguments and supported connection types, see the pandas read_sql API.
Troubleshoot connection and result errors
- Driver or URL mismatch: Confirm the installed driver matches the SQLAlchemy URL and selected database dialect. SQLAlchemy’s engine documentation describes URL forms.
- Connection failure: Check credentials, host, port, database name, and network access for the environment you are connecting from. These settings are database- and deployment-specific.
- Unsupported connection object: Pass a connection type pandas documents for your route. For server databases, use a SQLAlchemy connectable or a supported ADBC connection rather than assuming any DB-API object will work.
- Parameter error: Match placeholders to the driver’s syntax and provide the corresponding values through
params. - Memory pressure: Reduce the result in SQL or use
chunksizeto iterate over DataFrames. - Unexpected date or dtype values: Inspect the resulting DataFrame’s types and configure date parsing or dtype options as needed.
Write a DataFrame back to SQL
To send DataFrame records in the other direction, use DataFrame.to_sql() with a supported connection. Its API warns that it does not sanitize inputs supplied through to_sql, so treat names and other inputs carefully and consult the underlying driver guidance. pandas to_sql documentation
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →
