Use pd.read_sql_query() to read a SQL query into a pandas DataFrame, passing your query and a database connection. For large results, process chunks individually and use a streaming connection where your database driver supports it.
Welcome back, data folk, to our 3-part series on managing and analyzing data with SQL, Python and pandas. In our first post, we went into the differences, similarities, and relative advantages of using SQL vs. pandas for data analysis.
One of the points we really tried to push was that you don’t have to choose between them. SQL and pandas both have a place in a functional data analysis tech stack. We suggested doing the really heavy lifting directly in the database instance via SQL, then doing the finer-grained data analysis on your local machine using pandas.
Today, we’re going to get into the specifics and show you how to pull the results of a SQL query directly into a pandas DataFrame, how to do it efficiently, and how to keep a huge query from melting your local machine by managing chunk sizes.
Quick answer: read a SQL query into pandas
If you already have a SQLAlchemy engine and a pokemon table, this is the basic pattern:
import pandas as pd
from sqlalchemy import text
with engine.connect() as conn:
df = pd.read_sql_query(
text("SELECT * FROM pokemon"),
con=conn,
)
print(df.head())
The connection closes when the with block ends, while df remains available for analysis. The next sections show how to install the packages, create the engine, and prepare sample data.
Requirements to follow along
Before we dig in, there are a couple different Python packages that you’ll need to have installed in order to replicate this work on your end:
- pandas: provides DataFrames and the SQL-reading functions.
- SQLAlchemy: lets you interact with SQL databases directly in your Python code. We’ll use its engine and connection features; you don’t need to define ORM models.
- Psycopg2: a PostgreSQL adapter for Python. You’ll need this in addition to SQLAlchemy for the PostgreSQL connection shown here.
Install these via pip or whatever your favorite Python package manager is before trying to follow along here. In a virtual environment, the installation command for this tutorial is:
python -m pip install pandas sqlalchemy psycopg2-binary
The binary Psycopg2 package is convenient for a local tutorial. For production deployment, follow Psycopg’s installation guidance for your environment. Use mutually compatible, supported package versions rather than combining an old pandas installation with a newer SQLAlchemy release.
This is a Postgres-specific set of requirements. If you favor another dialect of SQL, though, you can adapt this guide by installing an adapter that will allow you to interact with MySQL, Oracle, or another database through SQLAlchemy.
And, of course, in addition to all that you’ll need access to a SQL database, either remotely or on your local machine. The examples below use small tables you can create in an empty practice database.
Setting up the connection
Once you’ve got everything installed and imported and have decided which database you want to pull your data from, you’ll need to open a connection to your database source. Start by creating a SQLAlchemy engine:
import os
import pandas as pd
from sqlalchemy import URL, create_engine, text
url = URL.create(
"postgresql+psycopg2",
username=os.environ["PGUSER"],
password=os.environ["PGPASSWORD"],
host=os.environ.get("PGHOST", "localhost"),
port=int(os.environ.get("PGPORT", "5432")),
database=os.environ["PGDATABASE"],
)
engine = create_engine(url)
Set PGUSER, PGPASSWORD, and PGDATABASE in your terminal, notebook environment, or deployment settings before running this code. Set PGHOST and PGPORT too if your database uses a different address or port. Keep passwords out of shared notebooks and source files.
URL.create() handles special characters in passwords without requiring you to build and escape a connection string manually. The engine manages database connections; engine.connect() opens one when the query needs it. For a hosted database, apply the provider’s required TLS settings as well.
Create a small practice dataset
Run this SQL once in an empty practice database using your SQL client. It creates the pokemon and sales tables used below, plus a view. If you already have equivalent tables, use those instead.
CREATE TABLE pokemon (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
type TEXT NOT NULL
);
INSERT INTO pokemon VALUES
(1, 'Bulbasaur', 'Grass'),
(4, 'Charmander', 'Fire'),
(7, 'Squirtle', 'Water');
CREATE TABLE sales (
product_name TEXT,
product_id INTEGER,
quantity INTEGER,
unit_price NUMERIC(10, 2),
total_price NUMERIC(10, 2),
sold_at TIMESTAMP
);
INSERT INTO sales VALUES
('Notebook', 123456, 2, 5.00, 10.00, '2026-01-10 09:00:00'),
('Notebook', 123456, 3, 5.00, 15.00, '2026-01-20 12:00:00'),
('Pen', 654321, 1, 2.00, 2.00, '2026-02-01 10:00:00');
CREATE VIEW my_view AS
SELECT id, name FROM pokemon;
Pulling the results of a basic SQL query into a pandas DataFrame
Now that we’ve got the connection set up, we can start to run some queries. Let’s use the pokemon dataset created above.
The simplest way to pull data from a SQL query into pandas is to make use of pandas’ read_sql_query() function. So if you wanted to pull all of the pokemon table in, you could simply run:
with engine.connect() as conn:
df = pd.read_sql_query(
text("SELECT * FROM pokemon ORDER BY id"),
con=conn,
)
print(df)
This executes the SQL query through the connection we defined with the con argument and stores the returned results in a DataFrame called df. With the sample data, you’ll get three rows and the columns id, name, and type.
And those are the basics, really. If you only came here looking for a way to pull a SQL query into a pandas DataFrame, that’s all you need to know. If you want to learn a bit more about slightly more advanced implementations, though, keep reading.
pandas read_sql_query vs read_sql
You might have noticed two similarly named functions: pandas.read_sql_query and pandas.read_sql. Either one will work for what we’ve shown you so far.
read_sql combines the functionality of read_sql_query and read_sql_table, which allows pandas to read a whole SQL table into a DataFrame.
| Function | Use it when |
|---|---|
pd.read_sql_query() | You have a SQL query, including filters, joins, or aggregates. |
pd.read_sql_table() | You want to read a table by name through a SQLAlchemy connection. |
pd.read_sql() | You want one function that accepts either a query or a table name. |
For this tutorial, read_sql_query() makes the intent explicit. If you use a raw sqlite3 connection, pass a query rather than only a table name. See the pandas SQL reference for supported connection types.
Can pandas read a SQL view?
Yes! It’s the same as reading from a SQL table. If you’ve saved your view in the SQL database, you can query it using whatever name you assigned to the view:
with engine.connect() as conn:
view_df = pd.read_sql_query(
text("SELECT * FROM my_view ORDER BY id"),
con=conn,
)
Here, my_view is the view created in the sample setup. Create views through your SQL client or a separate SQLAlchemy execution step. read_sql_query() is for statements that return rows, not for creating database objects.
Parameterized queries with SQL and pandas
Now suppose you wanted to make a generalized query string for pulling data from your SQL database so that you could adapt it for various different queries by swapping variables in and out.
Pass changing values through params; don’t insert them into SQL with .format() or f-strings. Bound parameters handle values separately from the SQL statement and avoid treating input as SQL code.
For example, to pull different date ranges from our sales table into separate DataFrames:
from datetime import datetime
query = text("""
SELECT product_name, product_id, quantity,
unit_price, total_price, sold_at
FROM sales
WHERE product_id = :product_id
AND sold_at >= :start_date
AND sold_at < :end_date
ORDER BY sold_at
""")
params = {
"product_id": 123456,
"start_date": datetime(2026, 1, 1),
"end_date": datetime(2026, 2, 1),
}
with engine.connect() as conn:
time_df = pd.read_sql_query(query, con=conn, params=params)
This returns the two January notebook sales. The inclusive start and exclusive end make adjacent date ranges easier to combine without counting a boundary twice. The sample uses timestamps without time zones; match parameter types and time zones to your own database columns.
Now you have a general-purpose query that you can use to pull various different date ranges from a SQL database into pandas DataFrames. Parameters bind values, not table names or column names; choose identifiers from a controlled set instead of accepting arbitrary input.
Handling dates and missing values
Database drivers may already return timestamp values as Python datetime objects. If you need pandas to parse a date column, use parse_dates. Nullable dtypes are useful when an integer column also contains missing values:
with engine.connect() as conn:
typed_df = pd.read_sql_query(
text("SELECT product_id, quantity, sold_at FROM sales"),
con=conn,
parse_dates=["sold_at"],
dtype={"quantity": "Int64"},
)
print(typed_df.dtypes)
The capital I in Int64 selects pandas’ nullable integer dtype. For monetary data, remember that the default coerce_float=True can convert Decimal values to floating point. Use coerce_float=False when retaining decimals matters, and inspect the resulting dtypes before calculations. These options are documented in read_sql_query().
Controlling the amount of data you pull down
If you’re working with a very large database, you may need to be careful with the amount of data that you try to feed into a pandas DataFrame in one go.
DataFrames are stored in memory, and processing the results of a SQL query requires even more memory, so not paying attention to the amount of data you’re collecting can cause memory errors pretty quickly.
Luckily, pandas has a built-in chunksize parameter that you can use to control this sort of thing. With chunksize, the function returns an iterator of DataFrames, not one DataFrame. Process each chunk as it arrives:
sql_query = text("SELECT quantity FROM sales")
n = 10_000
quantity_total = 0
with engine.connect().execution_options(stream_results=True) as conn:
for chunk in pd.read_sql_query(sql_query, con=conn, chunksize=n):
quantity_total += chunk["quantity"].sum()
print(quantity_total) # 6 for the sample data
Here, sql_query is your query and n is the desired number of rows in each chunk. Keep the connection open until you finish iterating. Smaller chunks generally reduce the size of each DataFrame, but they are not a universal speed improvement.
Chunking alone does not guarantee that the database driver avoids buffering the whole result. SQLAlchemy’s PostgreSQL/Psycopg2 connection supports streaming results through a server-side cursor. Support and restrictions vary by driver; use this setting for the SELECT workflow shown here. SQLAlchemy’s streaming documentation explains the distinction.
Of course, if you want to collect multiple chunks into a single larger DataFrame, you can collect them and concatenate them. Only do that when the complete result fits in memory. Appending every chunk to dfs and calling pd.concat(dfs) defeats the purpose of limiting how much data you hold at once.
For this particular example, SELECT SUM(quantity) FROM sales would do the aggregation in the database and return one row. Use chunk processing when you actually need Python-side work on individual records.
Optimizing your pandas-SQL workflow
In playing around with read_sql_query, you might have noticed that it can be a bit slow to load data, even for relatively modestly sized datasets. Start by selecting only the columns and rows you need, and let SQL handle joins and aggregations where practical.
If you really need to speed up your SQL-to-pandas pipeline, another approach worth benchmarking on PostgreSQL is exporting the query result with COPY and reading the CSV into pandas. This keeps the approach from the original article, with credit to Tristan Crockett, while adding resource cleanup:
import tempfile
from contextlib import closing
def read_sql_tmpfile(query, db_engine):
# Use a trusted, fixed SELECT statement without a trailing semicolon.
copy_sql = f"COPY ({query}) TO STDOUT WITH (FORMAT CSV, HEADER TRUE)"
with closing(db_engine.raw_connection()) as raw_conn:
with raw_conn.cursor() as cur:
with tempfile.TemporaryFile(
mode="w+", encoding="utf-8", newline=""
) as tmpfile:
cur.copy_expert(copy_sql, tmpfile)
tmpfile.seek(0)
return pd.read_csv(tmpfile)
copy_df = read_sql_tmpfile(
"SELECT id, name, type FROM pokemon ORDER BY id", engine
)
This function uses Psycopg2’s copy_expert() API. It is not a drop-in example for Psycopg 3 or other drivers. The formatted query must be trusted SQL you control, never a string assembled from untrusted values; use the parameterized approach above for ordinary dynamic queries.
The function still loads the final CSV into one DataFrame, and CSV parsing may change type or missing-value handling. Benchmark with your own result set rather than assuming a fixed percentage improvement. This sort of thing comes with tradeoffs in simplicity and readability, so it might not be for everyone.
Current pandas also supports ADBC connections for some databases, including PostgreSQL, as another route to investigate for larger transfers. That requires an additional driver. Keep the SQLAlchemy approach unless testing shows a worthwhile benefit for your workload.
Common errors and fixes
| Problem | What to check |
|---|---|
ModuleNotFoundError for a database driver | Install the driver in the same environment or notebook kernel that runs pandas. The engine above needs psycopg2, not just SQLAlchemy. |
| Authentication or connection failure | Check host, port, credentials, database name, network access, and required TLS settings. |
| Table or view does not exist | Check the database and schema. Qualify the name, such as public.pokemon, when necessary. |
| Execution errors after a package upgrade | Check pandas/SQLAlchemy compatibility and use a connection plus text() as shown. Avoid blindly downgrading packages. |
| Memory usage keeps growing | Process chunks independently, check streaming support, and reduce the query result in SQL. |
When you have finished all queries in a short-lived script or notebook session, engine.dispose() releases idle pooled connections. Close active connections first. A long-running application will usually keep its engine for reuse.
Wrapping up
That’s it for the second installment of our SQL-to-pandas series! Hopefully you’ve gotten a good sense of the basics of how to pull SQL data into a pandas DataFrame, as well as how to add more sophisticated approaches into your workflow to speed things up and manage large datasets.
Use bound parameters for changing values, and process chunks without collecting the entire result when memory is limited. Next, read the third installment on loading pandas DataFrames into a SQL database.
Comments 0 Responses