TopolearnMost production data lives in databases; moving it to and from pandas correctly is a daily task.
`pd.read_sql(query, con)` runs a query and returns a DataFrame. The connection can be a SQLAlchemy engine or connection (any supported database), an ADBC driver connection (pandas 2.2+), or a plain sqlite3 connection. Other raw DB-API connections are not officially supported and emit a warning.
Good practice:
- Push work into SQL. Filter, join and aggregate in the database, which has indexes and doesn't have to ship millions of rows over the network. Pull only the result you need.
- Use parameters, never string formatting, for values:
pd.read_sql("SELECT * FROM t WHERE id = ?", con, params=(42,))for sqlite3 (placeholder style depends on the driver; with SQLAlchemy usetext("... :id")andparams={"id": 42}). Formatting values into the SQL string invites SQL injection. parse_dates=["created_at"]anddtype=fix types;index_col=sets the index;dtype_backend="pyarrow"gives Arrow types.chunksize=50_000returns an iterator of DataFrames, so large results can be processed in pieces.
`df.to_sql("table", con, if_exists="append", index=False)` writes rows. if_exists is "fail" (default), "replace" (drops and recreates the table, losing indexes and constraints) or "append". dtype= maps columns to SQL types, chunksize= batches inserts, and method="multi" uses multi-row INSERTs. For bulk loads of millions of rows, a database's native loader (PostgreSQL COPY, ADBC ingest) is far faster.
Watch type mapping at the boundary: SQL NULL in an integer column arrives as float NaN with classic dtypes (nullable or Arrow backends fix this), and time zones and decimals need explicit handling. Inspect dtypes after reading.
Example
import sqlite3
import pandas as pd
con = sqlite3.connect(":memory:")
orders = pd.DataFrame({
"id": [1, 2, 3, 4],
"customer": ["ana", "bo", "ana", "cy"],
"amount": [50.0, 20.0, 70.0, None],
"created": ["2024-05-01", "2024-05-02", "2024-05-03", "2024-05-03"],
})
orders.to_sql("orders", con, index=False, if_exists="replace")
min_amount = 30
q = (
"SELECT customer, COUNT(*) AS n, SUM(amount) AS total, MAX(created) AS last "
"FROM orders WHERE amount >= ? GROUP BY customer"
)
summary = pd.read_sql(q, con, params=(min_amount,), parse_dates=["last"])
print(summary)
# customer n total last
# 0 ana 2 120.0 2024-05-03
for chunk in pd.read_sql("SELECT * FROM orders", con, chunksize=2):
print(len(chunk), end=" ") # 2 2Remember
read_sqlaccepts SQLAlchemy, ADBC or sqlite3 connections- Filter and aggregate in SQL; bring back only what you need
- Pass values with
params=, never string formatting to_sql(if_exists='append', index=False);replacedrops the table
Watch out for
- Building queries with f-strings from user input, opening the door to SQL injection.