Make SQLite connection cleanup reliable in a worker helper

By Batu · English technical notes

Also published in our Blogger archive.

Direct answer

A worker owns two separate responsibilities: completing or undoing its transaction, and releasing the operating-system resource behind its SQLite connection. Python’s sqlite3 connection context manager handles transaction completion around its block; it does not replace an explicit close for a long-lived helper. Put close() in finally so an exception on any path reaches it.

The duplicate primary-key insert is the useful edge case. The inner with connection: block rolls back its inserts when IntegrityError escapes the block, so the later count is zero. Reopening the disposable file proves that the rollback was persisted as the observed state. This is more precise than merely catching an exception and continuing with an unknown transaction.

The example does not cover process termination, cancellation from another thread, or a connection shared between workers. SQLite connections also have thread-affinity defaults. If a worker has retries, put the retry decision outside this cleanup boundary and test it with the same failure that caused the rollback.

Complete example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    database = Path(directory) / "worker.sqlite3"
    connection = sqlite3.connect(database)
    try:
        connection.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY)")
        try:
            with connection:
                connection.execute("INSERT INTO jobs VALUES (1)")
                connection.execute("INSERT INTO jobs VALUES (1)")
        except sqlite3.IntegrityError:
            pass
        assert connection.execute("SELECT COUNT(*) FROM jobs").fetchone()[0] == 0
    finally:
        connection.close()

    reopened = sqlite3.connect(database)
    assert reopened.execute("SELECT COUNT(*) FROM jobs").fetchone()[0] == 0
    reopened.close()
    print("duplicate_insert=rolled_back connection=closed")

Expected stdout:

duplicate_insert=rolled_back connection=closed

Sources

- sqlite3 — DB-API 2.0 interface for SQLite

- SQLite transaction documentation

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.