Use row_factory for readable SQLite inspection output
Also published in our Blogger archive.
Direct answer
sqlite3.Row is a row factory that lets a selected row be accessed by column name as well as position. Set it where inspection output is produced, then select explicit columns. The example reads row["id"] and row["state"], so a reviewer can connect the report field to the SQL projection without remembering tuple offsets.
The useful edge case is a changed query order. Name-based access keeps this rendering code correct when SELECT state, id replaces SELECT id, state, while an index-based formatter can silently swap meanings. The assertions prove the intended keys and values in a tiny database before JSON serialization.
A row factory does not validate that a column exists, sanitize values, or make arbitrary rows JSON serializable. Accessing a missing name still raises an error, and a query with duplicate column labels can make a report ambiguous. Keep aliases unique at the reporting boundary. This example also does not change how writes, commits, or transaction isolation behave; it only changes the Python representation of fetched rows.
Complete example
import json
import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE jobs (id INTEGER, state TEXT)")
connection.execute("INSERT INTO jobs VALUES (7, 'queued')")
connection.row_factory = sqlite3.Row
row = connection.execute("SELECT id, state FROM jobs").fetchone()
assert row["id"] == 7
assert row["state"] == "queued"
reordered = connection.execute("SELECT state, id FROM jobs").fetchone()
assert reordered["id"] == 7 and reordered["state"] == "queued"
record = {"id": row["id"], "state": row["state"]}
assert record == {"id": 7, "state": "queued"}
print(json.dumps(record, sort_keys=True))Expected stdout:
{"id": 7, "state": "queued"}Sources
- SQLite transaction documentation
Prepared with AI assistance. The example uses synthetic data; its stated limits apply.