How do I read from and write to a SQL database with pandas?

The short version

pd.read_sql(query, connection) runs a query and returns a DataFrame. df.to_sql(table, connection) writes one back. Do as much filtering and aggregation as you can in the SQL, so you transfer less data. Pass values as parameters, never by formatting them into the query string.

Reading a query

import pandas as pd
import sqlite3

con = sqlite3.connect("shop.db")

df = pd.read_sql(
    "SELECT city, SUM(amount) AS total FROM orders GROUP BY city",
    con,
)

The result arrives as an ordinary DataFrame, ready for analysis.

Push the work into SQL

The most common inefficiency is reading a whole table and then filtering in pandas.

# wasteful: transfers every row, then throws most away
df = pd.read_sql("SELECT * FROM orders", con)
leeds = df[df["city"] == "Leeds"]

The database is built for filtering and aggregating, and it avoids moving rows you will never use. Let it do the work.

# better: the database filters, pandas receives only what it needs
df = pd.read_sql("SELECT * FROM orders WHERE city = ?", con, params=("Leeds",))

On a large table the difference is enormous, in both time and memory.

Always use parameters

Note the ? and the params argument above. Never build the query with an f-string.

# WRONG: opens the door to SQL injection and quoting bugs
pd.read_sql(f"SELECT * FROM orders WHERE city = '{city}'", con)

If city contains a quote or a semicolon, this breaks or, worse, executes something you did not intend. Parameters pass the value as data, so it can never be interpreted as SQL.

Writing back

df.to_sql("summary", con, index=False, if_exists="replace")

index=False stops the DataFrame index becoming a stray column. if_exists controls what happens when the table is already there: "fail" (the default), "replace" (drop and recreate) or "append" (add rows). Choose deliberately, because "replace" destroys the existing table.

Reading in chunks

For a query returning more rows than fit in memory, chunksize gives you an iterator of frames.

for chunk in pd.read_sql("SELECT * FROM big", con, chunksize=100_000):
    process(chunk)

As with CSV chunking, reduce each chunk to a small summary rather than collecting them all, or you defeat the purpose.

Dates and types

Databases and pandas do not always agree on types. Use parse_dates to get real datetimes back, and check df.dtypes after reading, because a date arriving as text will sort alphabetically and cause silent errors later.

pd.read_sql("SELECT * FROM orders", con, parse_dates=["order_date"])

Beyond SQLite

The same functions work with PostgreSQL, MySQL and others through SQLAlchemy, which pandas accepts in place of a raw connection. The API is identical, so the habits above (push work into SQL, always parameterise, check dtypes) transfer unchanged.