Chapter 15 · Embedding SQLite in Applications
Python sqlite3: A Beginner-to-Production Workflow
Use Python’s standard sqlite3 module with current transaction-control guidance, parameter binding, row factories, explicit connection initialization, repository-style CRUD, atomic service operations, and temporary-file tests.
Learning outcomes
Python's standard sqlite3 module is a DB-API 2.0 wrapper around an SQLite library. It is ideal for learning because it ships with Python, but it also demonstrates a crucial production rule: Python's version and the SQLite library version are separate facts.
Inspect Python and linked SQLite versions at runtime and avoid assuming the standalone CLI version.
Open and initialize file-backed connections with foreign keys and a deliberate transaction policy.
Bind parameters, use sqlite3.Row, and distinguish execute/executemany/executescript responsibilities.
Use current Connection.autocommit guidance and understand what the connection context manager does and does not do.
Build a small repository/service layer with CRUD and one multi-statement transaction.
Test with temporary database files and explain why separate :memory: connections do not automatically share state.
First inspect the exact runtime
import sqlite3import sysprint("Python:", sys.version.split()[0])print("SQLite library:", sqlite3.sqlite_version)with sqlite3.connect(":memory:") as con: print(con.execute("SELECT sqlite_version()").fetchone()[0])The generation environment runs Python 3.13.5 linked to SQLite 3.46.1. The course documentation baseline is SQLite 3.53.4. Your Python build may report a different library, so feature gates belong in tests/startup diagnostics rather than assumptions.
Current Python transaction control: prefer autocommit explicitly
Python 3.12 introduced Connection.autocommit. Current Python 3.14 documentation recommends controlling transactions through that attribute/connection parameter. The current default is still LEGACY_TRANSACTION_CONTROL, so production code should choose intentionally instead of depending on the default forever.
| autocommit value | High-level behavior | commit()/rollback() |
|---|---|---|
False | PEP 249 transaction control; sqlite3 ensures a transaction is open and uses BEGIN DEFERRED | Meaningful; after commit/rollback a new transaction is opened. |
True | Underlying SQLite autocommit mode | commit()/rollback() have no effect. |
LEGACY_TRANSACTION_CONTROL | Pre-3.12 behavior controlled by isolation_level | Legacy compatibility; isolation_level matters here. |
Python’s Connection.autocommit policy and SQLite’s low-level autocommit state are related but not identical terms. Connection.in_transaction reports whether the underlying SQLite connection currently has an open transaction.
Open, initialize, verify
from pathlib import Pathimport sqlite3MIN_SQLITE = (3, 37, 0) # Example floor if this app requires STRICT.def open_db(path: Path) -> sqlite3.Connection: # Start in SQLite autocommit mode so connection PRAGMAs that cannot # change inside a transaction can be established first. con = sqlite3.connect(path, timeout=5.0, autocommit=True) con.row_factory = sqlite3.Row con.execute("PRAGMA foreign_keys = ON") con.execute("PRAGMA busy_timeout = 5000") enabled = con.execute("PRAGMA foreign_keys").fetchone()[0] if enabled != 1: con.close() raise RuntimeError("foreign key enforcement is not enabled") runtime = tuple(map(int, sqlite3.sqlite_version.split('.'))) if runtime < MIN_SQLITE: con.close() raise RuntimeError(f"SQLite {MIN_SQLITE}+ required; got {runtime}") # Now choose the recommended PEP 249 transaction policy. Setting this # to False opens a transaction (BEGIN DEFERRED) immediately. con.autocommit = False return conFor a real project, choose a minimum version from the features you actually require. Do not use the course baseline as an arbitrary minimum if your app works correctly on older patched branches.
With autocommit=False, Python keeps a transaction open. SQLite documents that PRAGMA foreign_keys cannot be changed while a transaction is active. Configure/verify that PRAGMA first while the connection is in SQLite autocommit mode, then switch the Python connection to autocommit=False.
Create the FieldNotes schema safely
Schema setup is SQL code controlled by the application, so placeholders are not used for table/column names. Use idempotent migration/versioning logic in a real application; this small lesson uses CREATE TABLE IF NOT EXISTS only to keep the lab focused on the driver.
SCHEMA = """CREATE TABLE IF NOT EXISTS device( device_id INTEGER PRIMARY KEY, device_code TEXT NOT NULL UNIQUE, status TEXT NOT NULL CHECK(status IN ('active','inspection_due','retired')), last_service_at TEXT);CREATE TABLE IF NOT EXISTS maintenance_note( note_id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL REFERENCES device(device_id), noted_at TEXT NOT NULL, note_text TEXT NOT NULL);"""def ensure_schema(con: sqlite3.Connection) -> None: con.executescript(SCHEMA) con.commit()executescript() is convenient for trusted multi-statement SQL, but do not use it to interpolate untrusted data. Also note that Python's transaction behavior around executescript() differs from ordinary execute(); keep migration transaction ownership explicit and test it.
Parameters and row factories keep application code predictable
def create_device(con, code: str, status: str) -> int: cur = con.execute( "INSERT INTO device(device_code,status) VALUES (?, ?)", (code, status), ) return cur.lastrowiddef get_device(con, device_id: int): row = con.execute( """ SELECT device_id, device_code, status, last_service_at FROM device WHERE device_id = ? """, (device_id,), ).fetchone() return None if row is None else dict(row)The trailing comma in (device_id,) makes a one-element tuple. Do not write (device_id) and assume it is a parameter sequence. Named placeholders are also supported, but Python's DB-API parameter style is not identical to every other driver, so follow the specific module documentation.
Context manager: transaction helper, not connection closer
Using a Connection in a with statement commits the transaction if the block exits successfully and rolls it back if an exception occurs. It does not close the connection. Close it explicitly, use contextlib.closing(), or structure your application so ownership is obvious.
def record_service(con, device_id: int, when: str, text: str) -> int: # With autocommit=False, the connection context manager commits on success # or rolls back on an exception. It does not close con. with con: row = con.execute( """ INSERT INTO maintenance_note(device_id, noted_at, note_text) VALUES (?, ?, ?) RETURNING note_id """, (device_id, when, text), ).fetchone() changed = con.execute( "UPDATE device SET last_service_at=? WHERE device_id=?", (when, device_id), ).rowcount if changed != 1: raise LookupError(f"device {device_id} not found") return row[0]If the UPDATE finds no device, raising an exception causes the note INSERT to roll back with the same transaction. That is the application invariant from Lesson 1 made concrete.
Exceptions: catch at the boundary that can decide recovery
try: note_id = record_service(con, 1, "2026-08-12T06:30:00Z", "Bearing inspected")except sqlite3.IntegrityError as exc: # Constraint violation: inspect/log policy-safe details. print("integrity error:", exc) print("sqlite code:", getattr(exc, "sqlite_errorcode", None)) print("sqlite name:", getattr(exc, "sqlite_errorname", None))except sqlite3.OperationalError as exc: # Could include busy/locked, read-only, I/O, etc.; classify before retrying. raiseelse: print("created note", note_id)Do not broadly catch Exception, silently continue, and then commit partial state. Retry only errors that your transaction/idempotency design makes safe to retry.
A small repository/service layer
class FieldNotesRepository: def __init__(self, con: sqlite3.Connection): self.con = con def list_notes(self, device_id: int): rows = self.con.execute( """ SELECT note_id, device_id, noted_at, note_text FROM maintenance_note WHERE device_id=? ORDER BY noted_at, note_id """, (device_id,), ).fetchall() return [dict(r) for r in rows] def set_status(self, device_id: int, status: str) -> bool: cur = self.con.execute( "UPDATE device SET status=? WHERE device_id=?", (status, device_id), ) return cur.rowcount == 1Decide explicitly whether repository methods commit. A useful design is that repositories issue statements while a service/use-case layer owns transaction boundaries. Hidden commits inside low-level methods make multi-step atomic operations difficult to compose.
Temporary-file tests beat accidental persistence
import tempfilefrom pathlib import Pathdef test_record_service(): with tempfile.TemporaryDirectory() as tmp: path = Path(tmp) / "fieldnotes-test.db" con = open_db(path) try: ensure_schema(con) with con: device_id = create_device(con, "PUMP-007", "active") note_id = record_service( con, device_id, "2026-08-12T06:30:00Z", "Bearing inspected", ) assert note_id > 0 assert get_device(con, device_id)["last_service_at"] is not None assert len(FieldNotesRepository(con).list_notes(device_id)) == 1 finally: con.close():memory: is excellent for some unit tests, but each ordinary sqlite3.connect(':memory:') call creates a separate database. If code under test opens new connections, a temporary file often models production connection lifetime more faithfully.
Failure lab: verify rollback rather than assuming it
# Count before.before = con.execute("SELECT count(*) FROM maintenance_note").fetchone()[0]try: record_service(con, 999999, "2026-08-12T07:00:00Z", "should roll back")except (sqlite3.IntegrityError, LookupError): passafter = con.execute("SELECT count(*) FROM maintenance_note").fetchone()[0]assert after == beforeDepending on schema/statement order, the foreign key may reject the INSERT before the explicit missing-row check. Either way, the test should verify the business result: no orphan or partial service record remains.
Checkpoint and production notes
Python integration check
Explain the API behavior, not just the syntax.
- Which value should current Python code set intentionally instead of relying on the legacy transaction default?
- Does
with con:close the connection? - Why should
PRAGMA foreign_keys=ONbe part of connection initialization? - Why can two
:memory:connections surprise a test suite? - Where should a multi-statement business transaction usually be owned?
- How do you discover the SQLite version Python actually uses?
Review the answers
Choose autocommit deliberately; current docs recommend that API. A connection context manager commits/rolls back but does not close the connection. Foreign-key enforcement is connection-specific unless compile defaults say otherwise, so initialize/verify it. Ordinary :memory: databases are per connection. Service/use-case code should own multi-statement transaction boundaries. Use sqlite3.sqlite_version and/or SELECT sqlite_version().
Bridge to JavaScript
Python's API is synchronous but often used in worker/server architectures that decide where blocking is acceptable. Node.js makes that architectural boundary impossible to ignore because the current built-in node:sqlite API is explicitly synchronous.