47 · Pandas + SQLite: read_sql and to_sql
One line to write a DataFrame into a SQL table; one line to query it back. `df.to_sql` and `pd.read_sql` turn SQLite into an effortless queryable store for any Pandas workflow — great for persisting model eval results or pulling training data from a lightweight DB.
Pandas and SQLite compose perfectly: `df.to_sql('table', conn)` serializes a DataFrame into SQL rows in one call, and `pd.read_sql('SELECT ...', conn)` hydrates the result of any query back into a DataFrame. The two-way bridge means you can use Pandas for data wrangling and SQL for filtering and aggregation — whichever fits the task — without any manual conversion.
Without this:
Without `to_sql`/`read_sql` you'd write a loop over DataFrame rows, calling `cur.execute` for each one, and then manually reconstruct a DataFrame from `fetchall()` results. Both directions are tedious and error-prone — the bridge removes the boilerplate entirely.
The last lesson covered sqlite3 raw — cursors, parameterized queries, commit cycles. This lesson bridges SQLite with Pandas: the easiest way to slip a DataFrame into a queryable store and pull it back is df.to_sql and pd.read_sql, and they accept a plain sqlite3 connection directly.
df.to_sql(name, conn, ...)
Key keyword arguments:
| argument | meaning |
|---|---|
| if_exists="fail" | raise if the table already exists (default) |
| if_exists="replace" | drop and recreate the table, then insert |
| if_exists="append" | insert rows into an existing table |
| index=False | don't write the DataFrame index as a column (usually what you want) |
| chunksize=N | write N rows per INSERT — helpful for very large DataFrames |
pd.read_sql(sql, conn, ...)
Runs any SQL string and returns a DataFrame. Column names come from the query's column aliases. Pass chunksize=N to get an iterator of DataFrames instead of one big result — the same lazy pattern as pd.read_csv(..., chunksize=N).
SQLAlchemy connection strings
Pandas also accepts SQLAlchemy Engine objects and connection-string URLs like "sqlite:///mydb.db". That path is required for databases beyond SQLite (PostgreSQL, MySQL). For pure SQLite work a bare sqlite3 connection is simpler.
Python (in browser)
`sqlite3` is stdlib — only `pandas` needs to be listed in packages. `to_sql` infers column types from the DataFrame dtypes automatically. `read_sql` returns a fresh DataFrame with dtypes inferred from the SQLite column types.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
Python (in browser)
`if_exists` controls what happens when the table already exists. `"replace"` silently destroys existing data — useful for snapshots but dangerous for accumulators. `"append"` adds rows without touching the schema.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
SQLAlchemy is not installed in the Pyodide sandbox used by this bootcamp, so the create_engine examples are read-along only. The bare `sqlite3` connection shown in the runnable cells is fully functional for SQLite.
What does `df.to_sql('t', conn, if_exists='append')` do when the table `t` does **not** yet exist?
- `df.to_sql('name', conn, if_exists='replace', index=False)` writes a DataFrame to a SQL table in one call.
- `pd.read_sql('SELECT ...', conn)` runs any SQL query and returns a DataFrame — column names come from the query.
- `if_exists='replace'` drops and recreates; `if_exists='append'` adds rows. Both create the table if it doesn't exist.
- `pd.read_sql(..., chunksize=N)` returns a lazy iterator of DataFrames — use it to stream large tables without loading everything into RAM.
- For production pipelines or non-SQLite databases, route Pandas through a SQLAlchemy `Engine` (`create_engine('postgresql://...')`).
The canonical ML use-case: pull training data from a production SQLite (or PostgreSQL via SQLAlchemy) with `pd.read_sql('SELECT features, label FROM events WHERE split=\'train\'', conn)`, train a model, then write evaluation metrics back with `metrics_df.to_sql('eval_runs', conn, if_exists='append', index=False)`. Lightweight feature stores for prototyping almost always follow this exact pattern — a SQLite file per experiment, one `to_sql` per artifact, one `read_sql` to compare runs.
If you remove it: Without `read_sql`/`to_sql` you manually iterate cursor rows into lists and then call `pd.DataFrame(...)` — adding 10–20 lines of boilerplate per query. For write paths you loop over `df.itertuples()` calling `cur.execute` row-by-row, which is both slow and easy to get wrong with type coercion.