---
title: "pandas read_sql: Run SQL Queries Into a DataFrame"
description: "pd.read_sql(query, con) runs a SQL query (or reads a whole table) and returns a pandas DataFrame."
canonical: "https://www.aifordatabase.com/blog/pandas-read-sql/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-18
updated: 2026-11-18
---

# pandas read\_sql: Run SQL Queries Into a DataFrame

**TL;DR:** `pd.read_sql(query, con)` runs a SQL query (or reads a whole table) and returns a pandas DataFrame. Pass a SQLAlchemy engine or connection as `con`, put user values in `params` instead of f-strings, and use `chunksize` with a server-side cursor for big tables. If you want the answer without writing Python at all, AI for Database lets you ask the same question in plain English and shows the SQL it ran.

To read SQL into pandas, install pandas and SQLAlchemy plus your database driver, create an engine with `create_engine("postgresql+psycopg://user:pass@host/db")`, then call `df = pd.read_sql(text("SELECT ... WHERE id = :id"), engine, params={"id": 42})`. `read_sql` is a convenience wrapper: a query string goes to `read_sql_query`, a bare table name goes to `read_sql_table`. Everything below is checked against the pandas 3.1.1 docs.

> Try AI for Database free at aifordatabase.com (https://app.aifordatabase.com/signup, sample data included).

![A SQL query flows through a SQLAlchemy engine into a pandas DataFrame](https://cdn.sanity.io/images/upsr56wu/production/720626f0aa7850255484063cd41f0b1f4303e120-1344x326.png?w=1280)

FIG 01 — pandas never sanitizes SQL; the driver binds params.

## When is pandas the wrong tool, and what does AI for Database do instead?

pandas is the right tool when the result feeds Python: a model, a notebook, a data-cleaning script. It is the wrong tool when someone on the team just needs a number or a recurring report. That is the job AI for Database does:

*   **Ask in plain English.** Connect PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Supabase and more, type "revenue by country last 30 days", and get a table or chart. Every answer shows its SQL, so you can paste it into `read_sql` later.
*   **Read-only answers.** Queries run read-only, so nobody can drop a table by accident.
*   **Dashboards that refresh themselves.** Pin an answer and it re-runs on a schedule, instead of a cron job calling a pandas script.
*   **Workflows on data changes.** "When daily signups drop below 50, send a Slack alert" replaces a polling script.
*   **Price.** Start free with sample data; Pro is $10 per user per month (aifordatabase.com/pricing, as of 2026-10-23).

If you are writing Python anyway, keep reading.

## What do you need before running read\_sql?

*   Python 3.10+ and pandas 2.2 or newer (the docs used here are pandas 3.1.1).
*   SQLAlchemy 2.x for any database other than SQLite.
*   A driver: `psycopg` (PostgreSQL), `pymysql` (MySQL), `pyodbc` (SQL Server), or the standard library `sqlite3`.

## How to read SQL into pandas, step by step

1.  **Install the packages.**

```
pip install "pandas>=2.2" "sqlalchemy>=2.0" "psycopg[binary]"
```

1.  **Create an engine.** The URL picks the dialect and driver.

```
import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://analyst:secret@localhost:5432/shop")
```

1.  **Run a query into a DataFrame.**

```
df = pd.read_sql(text("SELECT id, email, created_at FROM customers LIMIT 100"), engine)
print(df.shape)
```

1.  **Pass parameters safely.** Use `params` with SQLAlchemy `text()` and `:name` placeholders. pandas forwards SQL to the driver without sanitizing it, so an f-string with user input is an injection risk.

```
sql = text("""
    SELECT id, total, created_at
    FROM orders
    WHERE created_at >= :since AND status = :status
""")
df = pd.read_sql(sql, engine, params={"since": "2026-10-01", "status": "paid"})
```

1.  **Parse dates and set an index.**

```
df = pd.read_sql(
    sql, engine,
    params={"since": "2026-10-01", "status": "paid"},
    parse_dates=["created_at"],
    index_col="id",
)
```

1.  **Choose dtypes.** `dtype_backend="pyarrow"` (added in 2.0) gives nullable Arrow types, so an integer column with NULLs stays integer instead of becoming float.

```
df = pd.read_sql(sql, engine, params={"since": "2026-10-01", "status": "paid"},
                 dtype_backend="pyarrow")
```

1.  **Close what you open.** pandas closes connections it built from a string URI, but you own SQLAlchemy engines and connections. Call `engine.dispose()` when a script ends.

![Parameters stay separate from the SQL text until the driver binds them](https://cdn.sanity.io/images/upsr56wu/production/16a3df44c6349db6402412ec51ab769c40436a37-1344x364.png?w=1280)

FIG 02 — Values never get pasted into the SQL string.

## What is the difference between read\_sql, read\_sql\_query and read\_sql\_table?

| Function | Takes | Notes |
| --- | --- | --- |
| `read_sql` | query or table name | Wrapper, kept for backward compatibility; routes to one of the two below |
| `read_sql_query` | a SELECT query | Tz-aware datetimes from `parse_dates` are converted to UTC |
| `read_sql_table` | a table name | Needs SQLAlchemy; supports `columns=[...]` and `schema` |

With a plain `sqlite3` connection only queries work: `pd.read_sql("orders", sqlite_conn)` fails because the table route needs SQLAlchemy. Use `read_sql_query` when you always pass SQL; the intent is clearer to the next reader.

## How do you read a large table in chunks?

`chunksize=N` returns an iterator of DataFrames with N rows each. The pandas docs warn that this alone usually does not lower peak memory, because most drivers buffer the whole result set. Pair it with a server-side cursor via SQLAlchemy `stream_results`:

```
with engine.connect().execution_options(stream_results=True) as conn:
    for chunk in pd.read_sql(text("SELECT * FROM events"), conn, chunksize=50_000):
        chunk.to_parquet(f"events_{chunk.index[0]}.parquet")
```

Better still: push the aggregation into SQL (`GROUP BY` in the database) so pandas only receives the summary rows.

## How do you write a DataFrame back with to\_sql?

```
rows = df.to_sql("orders_clean", engine, if_exists="append", index=False,
                 chunksize=1_000, method="multi")
```

*   `if_exists`: `fail` (default, raises ValueError), `replace` (drops the table), `append`, or `delete_rows` (empties it, keeps the schema).
*   `index=True` is the default and writes the DataFrame index as a column. Set `index=False` unless you want it.
*   `method="multi"` sends many rows per INSERT. Oracle does not support it.
*   The return value is the number of rows affected as reported by the driver.

## Common errors and fixes

### Why does pandas warn about a DBAPI2 connection?

`UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested.` You passed a raw `psycopg2` or `pymysql` connection. It often works, but it is untested. Fix: wrap it in a SQLAlchemy engine, or pass an ADBC connection (supported since pandas 2.2).

### AttributeError: 'OptionEngine' object has no attribute 'execute'

Old pandas (1.x) with SQLAlchemy 2.0. Upgrade pandas to 2.2+, which supports SQLAlchemy 2.

### ObjectNotExecutableError or "Not an executable object"

You passed a plain string to a raw SQLAlchemy `conn.execute()`. Wrap it: `text("SELECT ...")`.

### Big IDs come back slightly wrong

`coerce_float=True` turns `Decimal` values into float64, and integers above 2\*\*53 lose precision. Pass `coerce_float=False` or cast the column to text in SQL.

### ValueError: Table 'orders\_clean' already exists

`to_sql` defaults to `if_exists="fail"`. Choose `append`, `replace` or `delete_rows`.

## How do you verify it worked?

Check `df.shape` against `SELECT COUNT(*)` with the same filters, inspect `df.dtypes` (dates should be `datetime64`, not `object`), and for `to_sql` compare the returned row count to `len(df)`.

## Related guides

*   [SQL cheat sheet](https://www.aifordatabase.com/blog/sql-cheat-sheet/)
*   [SQL notebook options](https://www.aifordatabase.com/blog/sql-notebook/)
*   [How to schedule a query in Postgres](https://www.aifordatabase.com/blog/postgres-schedule-query/)
*   [Find duplicates with a SQL query](https://www.aifordatabase.com/blog/find-duplicates-sql-query/)

Sources: [pandas.read\_sql docs](https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html), [pandas.read\_sql\_query docs](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html), [DataFrame.to\_sql docs](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html), [SQLAlchemy server-side cursors](https://docs.sqlalchemy.org/en/20/core/connections.html#using-server-side-cursors-a-k-a-stream-results).

Try AI for Database free at aifordatabase.com: ask your database in plain English and see the SQL behind every answer. [Start free](https://app.aifordatabase.com/signup).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### Is pandas read\_sql safe from SQL injection?

Only if you use params. pandas does not sanitize SQL; the driver binds parameters. Never build the query with f-strings from user input.

### What parameter syntax should I use?

With SQLAlchemy text(), use :name. With a raw driver, follow its PEP 249 paramstyle: psycopg2 uses %(name)s, sqlite3 uses ?.

### Can read\_sql run INSERT or UPDATE?

It is built for queries that return rows. Run writes through conn.execute(text(...)) inside engine.begin(), or use to\_sql.

### Does read\_sql work with MySQL, SQL Server and Snowflake?

Yes, through any SQLAlchemy dialect: mysql+pymysql://, mssql+pyodbc://, or the snowflake-sqlalchemy package.

### Can I get the same answers without Python?

Yes. Connect the database to AI for Database, ask the question in plain English, and copy the SQL it shows into read\_sql when you need it in a notebook.

Read next

*   [HOW TO QUERY SQLITE WITHOUT SQL IN 2026 →](https://www.aifordatabase.com/blog/query-sqlite-without-sql-2026/)
*   [HOW TO RUN AD HOC DATABASE QUERIES WITHOUT WRITING SQL →](https://www.aifordatabase.com/blog/ad-hoc-database-queries-without-sql/)
*   [HOW TO GIVE AI READ-ONLY ACCESS TO YOUR DATABASE (2026) →](https://www.aifordatabase.com/blog/give-ai-read-only-database-access/)

Source: https://www.aifordatabase.com/blog/pandas-read-sql/
