46 · SQLite with sqlite3: connection, cursor, CRUD
sqlite3 is in the standard library — zero-config, single-file, fully ACID. Master the connection → cursor → execute → commit lifecycle, learn to use parameterized queries (the only safe way to build SQL), and see why injection attacks happen and how parameters stop them.
SQLite lives inside your process — no server, no install, no port. A single `.db` file holds a relational database with full SQL support, ACID transactions, and concurrent reads. The `sqlite3` module ships with every CPython installation, so there is truly **nothing to install**. The pattern is always the same: open a connection, get a cursor, execute SQL with `?` placeholders for data, commit, close — or let the `with` context manager handle the last two steps for you.
Without this:
Without knowing SQLite you'll reach for JSON files or CSVs to persist experiment results and config — and then fight serialization bugs, concurrent write corruption, and the lack of any query language. SQLite gives you all of that for free, in one import.
SQLite is the world's most widely deployed database engine — it powers iOS, Android, Firefox, WhatsApp, and countless embedded systems. The key insight: it is not a server. The database lives in a single ordinary file, and the query engine runs inside your own process via the sqlite3 C library, which Python wraps in its standard-library module of the same name.
Why does this matter for Python/ML work?
- Zero infrastructure —
import sqlite3is all you need. No Docker container, no credentials, no port to open. - Full SQL —
JOIN,GROUP BY,window functions, indexes, foreign keys. Not a toy. - ACID transactions — writes are safe even if your process crashes mid-operation.
- Single file = portable — copy the
.dbfile anywhere and the database travels with it.
The connection lifecycle
conn = sqlite3.connect(path) # open or create
cur = conn.cursor() # statement runner
cur.execute(sql, params) # run SQL
conn.commit() # flush to disk
conn.close() # release file handle
Use ':memory:' as the path for an ephemeral in-process database that vanishes when the connection closes — ideal for tests and one-shot scripts. Use '/tmp/foo.db' or any real path for persistence.
Reading results
After a SELECT, the cursor becomes an iterator over rows:
cur.fetchone()— next row as a tuple, orNoneif the result set is empty.cur.fetchall()— all remaining rows as a list of tuples.for row in cur:— iterate lazily (memory-friendly for large results).
Context manager shortcut
with sqlite3.connect(path) as conn: commits automatically on a clean exit and rolls back on an exception. You still call conn.cursor() inside the block; the with only handles the transaction boundary, not conn.close().
Python (in browser)
`executemany` inserts a sequence of rows in one call — more efficient than a loop of single `execute` calls. Note how every value is passed as a tuple element, never interpolated into the SQL string itself.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
SQLite's Python driver blocks multi-statement `execute()` calls, so the DROP TABLE never fires — but the principle holds across every database. The parameterized form is the **only** correct way regardless of the DB engine.
Python (in browser)
The `with sqlite3.connect(...) as conn:` block commits when the `with` body completes successfully and rolls back if an exception is raised — making error handling automatic. You still need to call `conn.close()` explicitly when you're truly done.
Python runs entirely in your browser via Pyodide (~6 MB on first Run, cached after).
Gotcha #3 is especially tricky: `(42)` is just `42` in Python (the parentheses are grouping, not a tuple). You need a trailing comma — `(42,)` — to create a single-element tuple.
What does `cursor.fetchone()` return when the query matches zero rows?
- `sqlite3` is in the standard library — `import sqlite3`, no installation needed.
- The pattern: `connect` → `cursor` → `execute` → `commit` → `close`. Use `':memory:'` for ephemeral DBs.
- Always use `?` placeholders for data — never f-strings or string concatenation in SQL.
- `with sqlite3.connect(...) as conn:` auto-commits on success and auto-rolls back on exceptions.
- `fetchone()` returns `None` (not an empty tuple) when no rows match.
Lightweight experiment trackers (the kind you build yourself before reaching for MLflow) almost always store run metadata in SQLite: hyperparameters, metrics per epoch, timestamps, artifact paths. SQLite is also the storage format behind many ML evaluation harnesses, DuckDB's on-disk format, and config DBs for ML serving infrastructure. If you've used Weights & Biases offline mode or a Hydra config cache, SQLite was likely involved.
If you remove it: Without a queryable store you fall back to JSON files or CSVs — neither supports indexed lookups, ad-hoc aggregation, or safe concurrent writes. You end up re-implementing half a database in pandas, and you still lose data if two processes write simultaneously.