Python SQLite progress handler: interrupt one expensive statement

Operation contract

The connection starts with one accepted receipt. A recursive count query exceeds the fixture's forty callback allowance, so the progress handler returns a nonzero result and SQLite interrupts that statement. The handler is removed before a normal query verifies the existing row remains.

Failure boundary

The callback count is a virtual-machine instruction budget, not elapsed time, memory use, or a cross-machine service-level guarantee. Different SQLite builds or query plans may do different work per result. Use a wall-clock deadline and resource policy alongside it for a real request boundary.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE receipts (receipt_id INTEGER PRIMARY KEY)")
    connection.execute("INSERT INTO receipts VALUES (?)", (47,))
    connection.commit()
    callbacks = 0

    def stop_long_query():
        global callbacks
        callbacks += 1
        return int(callbacks >= 40)

    connection.set_progress_handler(stop_long_query, 1)
    try:
        connection.execute("WITH RECURSIVE numbers(value) AS ("
                           "SELECT 1 UNION ALL SELECT value + 1 FROM numbers WHERE value < 100000) "
                           "SELECT sum(value) FROM numbers").fetchone()
    except sqlite3.DatabaseError:
        print("query_interrupted", True)
    finally:
        connection.set_progress_handler(None, 0)
    print("receipt_kept", connection.execute("SELECT receipt_id FROM receipts").fetchone()[0])
finally:
    connection.close()

Output

query_interrupted True
receipt_kept 47

Costs and limits

Invoking Python after every virtual-machine step is deliberately expensive in this small fixture. A production interval should be measured against the accepted query workload, then combined with a deadline.

Common Mistakes

• Instruction callbacks do not measure wall-clock time.
• Remove a request-scoped handler before reusing its connection.
• Catching the interrupt without checking surrounding transaction state can hide a partial workflow.

Connected lessons

• Python SQLite writer contention: reject a busy transaction before changing state
• Python sqlite3 transactions: bind values and roll back failed batches
• Python SQLite authorizer: deny a statement on the connection that executes it

Test this contract.