SQLite Basics: Cancel Expensive Queries with a Progress Handler

A long-running query can cooperate with a cancellation request. A progress handler lets Python ask SQLite to interrupt work between groups of virtual-machine instructions. The example uses a deterministic callback budget rather than a timing-sensitive deadline.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party Python packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-progress-cancellation.py:

import sqlite3

connection = sqlite3.connect(":memory:")
callbacks = 0

def cancel_after_budget():
    global callbacks
    callbacks += 1
    return int(callbacks >= 3)

try:
    connection.set_progress_handler(cancel_after_budget, 100)
    try:
        connection.execute(
            "WITH RECURSIVE numbers(n) AS ("
            "VALUES(1) UNION ALL SELECT n + 1 FROM numbers WHERE n < 1000000"
            ") SELECT sum(n) FROM numbers"
        ).fetchone()
    except sqlite3.OperationalError as error:
        assert error.sqlite_errorcode == sqlite3.SQLITE_INTERRUPT
        assert callbacks == 3
        print("Query cancelled:", error.sqlite_errorname)
        print("Callback count:", callbacks)
    else:
        raise AssertionError("Expected the query to be interrupted")
    finally:
        connection.set_progress_handler(None, 0)

    answer = connection.execute("SELECT 6 * 7").fetchone()[0]
    assert answer == 42
    print("Connection reused:", answer)
finally:
    connection.close()

Run it:

python3 -B demonstrate-progress-cancellation.py

Observed output:

Query cancelled: SQLITE_INTERRUPT
Callback count: 3
Connection reused: 42

Read the result

The callback returns zero twice and a nonzero value on its third invocation. The query then raises the expected interruption error. Clearing the handler in a finally block allows the short follow-up query to run normally.

Apply it carefully

The instruction interval is approximate; it is not milliseconds, rows, or a portable query-cost measurement. A real cancel button can set a flag that the callback reads, or a deadline can be checked with a monotonic clock. Keep the callback short and do not execute SQL on its connection from inside it. Only one progress handler is installed per connection. An interrupted write may affect transaction state, so write applications need explicit failure handling; this example deliberately interrupts a read-only query.

The official reference documents the API behavior. These checks exercise the controlled fixture above.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- demonstrate-progress-cancellation.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.