SQLite and Transactions
Persist validated data with parameterized queries, explicit transaction boundaries, and reliable connection cleanup.
By the end, you’ll be able to…
- Bind values through SQL parameters.
- Use Python 3.12 transaction control deliberately.
- Verify commit, rollback, and idempotent update behavior.
01Separate SQL structure from values
SQLite stores a relational database in a file or an in-memory connection. Tables define durable constraints that complement Python validation. A primary key establishes identity, a NOT NULL constraint rejects missing values, and a CHECK constraint can enforce a numeric invariant even when another caller bypasses your validation function.
Bind values using placeholders rather than interpolating them into SQL text. Parameters handle quoting and prevent a value from being interpreted as executable SQL. They do not parameterize table or column names. If identifiers must vary, choose them from a fixed allowlist and keep the structural SQL under application control. Never assume escaping a quote manually provides an equivalent safety boundary.
02A transaction groups one business change
A transaction defines which writes must succeed together. With autocommit=False in Python 3.12 and later, the connection uses explicit commit and rollback behavior; commit or rollback opens another transaction as needed. The connection context manager commits on normal exit and rolls back when an exception escapes the block. It does not close the connection, so close it in finally or use a separate closing context.
Choose the transaction boundary from the business invariant, such as replacing an entire import batch or transferring between two accounts. Committing every row can leave a half-imported batch. Keeping an unnecessarily long transaction open can increase contention. Parse and validate outside the write transaction where practical, then perform a short, clearly scoped set of database operations.
03Identity enables repeatable imports
A stable source identifier lets an import update an existing record instead of inserting duplicates on every run. INSERT ... ON CONFLICT can express that policy explicitly. Decide which fields the source owns, whether deleted source records should disappear locally, and how conflicting versions are handled. Idempotency is a domain property, not merely a particular SQL spelling.
Use ORDER BY whenever consumers or tests depend on row order. A SELECT without it has no promised presentation order. Fetching one row may return None, so check absence before indexing. For large result sets, iterate over a cursor or fetch in batches. Index columns according to measured query patterns, because each additional index consumes space and adds maintenance work during writes.
04Treat database access as a boundary
SQLite supports many readers but has write-concurrency constraints, so a thread pool does not automatically make database writes scale. Keep connection ownership clear and avoid sharing one connection across arbitrary workers. A locked-database timeout is a waiting policy, not a complete concurrency architecture. Choose a service database when the application’s workload and operations require it.
Test failures at the transaction boundary: one invalid row must not preserve earlier rows from the same batch. Use an in-memory database for fast behavioral tests and a temporary file for persistence checks. Record migration decisions as the schema evolves. Backups, recovery, permissions, and data retention remain operational requirements even when the storage engine is embedded in the Python standard library.
The ideas worth keeping.
- Use parameters for every external SQL value.
- Make the transaction boundary match the business invariant.
- Close connections separately and test rollback behavior.
Make it yours.
Type and test your code right here in the interactive workspace. Run your solution, check the output, and compare with the worked guide when ready.
Atomic transfer
Create two balances in an in-memory database. Transfer 3 units from account 1 to account 2 in one transaction and verify [7, 3].
Explore a worked solution
import sqlite3connection = sqlite3.connect(':memory:', autocommit=False)try: with connection: connection.execute('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER CHECK(balance >= 0))') connection.executemany('INSERT INTO accounts VALUES (?, ?)', [(1, 10), (2, 0)]) with connection: connection.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', (3, 1)) connection.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', (3, 2)) balances = [row[0] for row in connection.execute('SELECT balance FROM accounts ORDER BY id')] assert balances == [7, 3]finally: connection.close()The two updates share one commit boundary. A complete transfer service must also validate amount, account existence, authorization, and concurrent business rules.
Rollback a duplicate batch
Insert two rows with the same primary key in one transaction. Catch IntegrityError and prove no row remains.
Explore a worked solution
import sqlite3connection = sqlite3.connect(':memory:', autocommit=False)try: with connection: connection.execute('CREATE TABLE records (id INTEGER PRIMARY KEY)') try: with connection: connection.executemany('INSERT INTO records VALUES (?)', [(1,), (1,)]) except sqlite3.IntegrityError: pass assert connection.execute('SELECT COUNT(*) FROM records').fetchone()[0] == 0finally: connection.close()The exception escapes the transaction block, causing rollback. Catching it inside that block without rolling back could accidentally commit earlier successful work.
What stayed with you?
Check the ideas while they’re fresh. Your answers stay in this page and feedback appears as you go.
Atomic transfer
Create two balances in an in-memory database. Transfer 3 units from account 1 to account 2 in one transaction and verify [7, 3].
[Process exited with code 0 - No output]
Keep exploring.
The official references behind this chapter, when you want a second angle or the full details.
One chapter closer.
Reading is a start. Trying it yourself makes the connection.