Python sqlite3.Connection.set_progress_handler 怎么给长查询加中止点：回调频率、取消标记与连接复用

SQLite 遇到大表扫描、复杂排序或临时表时，Python 调用方常常只能等 execute() 返回。sqlite3.Connection.set_progress_handler() 提供了一个更可控的中止点：SQLite 每执行一批虚拟机指令就调用一次 Python 回调，回调返回非零值时当前语句会被中止。它适合做“用户取消”和“应用层预算”检查，但不是精确的毫秒级超时器。

先把中止目标说清楚：取消一条查询，而不是杀掉连接

假设订单后台允许用户取消一次“按地区、状态和金额排序”的导出查询。我们希望取消只影响当前语句，连接还可以继续执行下一条查询。直接关闭连接虽然粗暴，但会让连接池拿到一个失效对象，也会把连接级资源回收和业务取消混在一起。

set_progress_handler(callback, n) 的 n 表示大约每执行 n 条 SQLite 虚拟机指令调用一次回调。回调返回 0 表示继续，返回非零值表示让 SQLite 终止当前语句。不同查询的指令数量不同，所以它不能直接换算成固定的时间。

最小实现：用线程安全标记让回调只负责判断

取消动作通常发生在另一个请求线程，或者由后台任务设置一个事件。回调应当尽量短：读取一个已经准备好的标记即可，不要在其中执行日志格式化、网络请求或再次访问同一个连接。

import sqlite3
import threading

cancelled = threading.Event()

def stop_when_cancelled() -> int:
    return 1 if cancelled.is_set() else 0

def find_orders(conn: sqlite3.Connection, region: str) -> list[tuple]:
    conn.set_progress_handler(stop_when_cancelled, 10_000)
    try:
        return conn.execute(
            """
            SELECT order_id, amount
            FROM orders
            WHERE region = ? AND status = ?
            ORDER BY amount DESC
            """,
            (region, "paid"),
        ).fetchall()
    finally:
        conn.set_progress_handler(None, 0)

这里的 finally 有两个作用：语句成功时清除 handler，语句被中止时也清除 handler。否则同一个连接下一次执行普通查询，仍可能读取已经置位的取消事件。

回调频率怎么定：先看响应窗口，再测开销

把 n 写成 1 并不等于“最及时”。长查询会频繁进入 Python 回调，解释器切换和事件读取可能反过来拖慢查询。把它调得很大又会让用户点击取消后等待更久。

更实用的做法是先给出一个中等间隔，例如 10_000，再用真实数据测三个指标：正常查询耗时、取消按钮到 OperationalError 的延迟、回调次数。SQLite 的虚拟机指令量随 SQL、索引和数据分布变化，不能把某次测试的间隔直接当成所有查询的时间预算。

progress_calls = 0

def progress() -> int:
    global progress_calls
    progress_calls += 1
    return int(cancelled.is_set())

conn.set_progress_handler(progress, 10_000)
try:
    rows = conn.execute("SELECT ...").fetchall()
except sqlite3.OperationalError as exc:
    # 记录查询被中止；不要把所有 OperationalError 都当成用户取消
    if cancelled.is_set():
        raise RuntimeError("query cancelled by caller") from exc
    raise
finally:
    conn.set_progress_handler(None, 0)

异常边界：中止结果要和 SQL 错误分开

被 progress handler 中止的语句通常会以 sqlite3.OperationalError 的形式回到调用方。这个异常类型也可能代表锁冲突、语法问题或数据库损坏，因此不能只写一个宽泛的 except sqlite3.OperationalError 就返回“用户取消”。要结合取消事件、请求上下文或本次查询的状态字段判断。

如果业务要求取消后继续使用连接，先确认连接仍能执行一个轻量探针，再归还连接池。不要在 handler 回调内部关闭连接，也不要让回调抛出业务异常；异常转换应当发生在 execute() 外层。

连接复用的权限边界：注册和清除必须成对出现

连接池里的连接是共享资源，最容易出现的故障是 A 请求注册了 handler，查询完成后忘记清理，B 请求借到连接后突然被 A 的取消标记中止。把注册动作封装在查询函数内部，并在 finally 里清除，是比依赖调用方记忆更可靠的边界。

def run_with_cancel(conn, sql, params, event, step=10_000):
    def progress():
        return 1 if event.is_set() else 0

    conn.set_progress_handler(progress, step)
    try:
        return conn.execute(sql, params).fetchall()
    finally:
        conn.set_progress_handler(None, 0)

# 归还连接前，确保下一个调用者看到的是干净连接
conn.execute("SELECT 1").fetchone()

如果项目用的是多线程访问连接，还要遵守连接创建时的线程约束，不要因为 handler 本身很短就忽略 check_same_thread 和连接池的借还规则。progress handler 解决的是语句中止，不会自动解决并发访问同一连接的问题。

日志审计和发布前检查：至少覆盖三条路径

排查这类功能时，日志里建议记录查询标识、handler 间隔、是否收到取消、执行结果和连接清理状态，不要把完整 SQL 参数直接写入日志。测试至少覆盖以下三条路径：

• 正常短查询：返回结果，handler 在 finally 中清除，连接可以执行下一条语句。
• 长查询中途取消：回调返回非零，外层识别为调用方取消，连接探针成功。
• 真正的 SQL/锁错误：即使取消标记没有置位，也要保留原始数据库错误，不能误报成用户取消。

最后做一次连接复用测试：先运行一个会触发取消的查询，再用同一连接执行 SELECT 1 和一条普通业务查询。如果第二条查询仍被中止，说明清理时机或事件生命周期出了问题。

相关问题

set_progress_handler 能做精确的 500 毫秒超时吗？

不能把它当成精确计时器。它按虚拟机指令次数触发，适合检查取消标记或粗粒度预算；若需要严格的时间控制，应在应用层记录截止时间，并在回调中轻量判断。

回调返回非零后能否只取消当前游标、保留部分结果？

中止的是当前语句，调用方通常会收到异常，不能把它当成可靠的部分结果提交机制。需要断点续查时，应把查询拆成可记录游标的批次。

为什么不用直接调用 interrupt？

连接级中断适合明确拥有该连接的场景；progress handler 更方便把取消检查放进一次查询的生命周期，并在语句结束时清除。两者都要结合连接归属和并发模型选择。

这项 API 的价值不在于让 SQLite 突然具备数据库级超时，而在于给长查询增加一个可验证的协作式中止点。把回调保持轻量、把异常判断放在外层、把 handler 清理写进 finally，再用连接复用测试验收，才是可以长期维护的实现。