Python SQLite：参数化写入任务并验证事务回滚

本节目标

使用标准库 sqlite3 创建任务表，以参数化 SQL 写入标题，利用约束和事务回滚拒绝无效记录，并在结束时明确关闭连接。

知识与使用场景

SQLite 可以把结构化记录放进单个数据库文件。本节使用内存数据库以便反复运行，不留下测试文件；把连接参数换成文件路径才会持久保存。内存数据库在连接关闭后消失。

表结构使用主键区分任务，minutes 设置 NOT NULL 和非负 CHECK 约束。应用层校验有助于给出友好提示，数据库约束则在写入边界再次保证这条规则，两者各有职责。

插入语句使用问号占位符，参数通过元组单独传入。标题中的单引号属于数据，不需要自行拼接或替换。占位符用于值，不能直接拿来代替表名、列名或排序关键字；动态结构需要受控白名单。

完整示例

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes >= 0))")
    with connection:
        connection.execute("INSERT INTO tasks(title, minutes) VALUES (?, ?)", ("学习SQL的'参数'", 35))
    try:
        with connection:
            connection.execute("INSERT INTO tasks(title, minutes) VALUES (?, ?)", ("应回滚", 10))
            connection.execute("INSERT INTO tasks(title, minutes) VALUES (?, ?)", ("负数记录", -1))
    except sqlite3.IntegrityError:
        print("无效事务已回滚")
    rows = connection.execute("SELECT title, minutes FROM tasks ORDER BY id").fetchall()
    print(rows[0][0])
    print(f"保留记录：{len(rows)}，分钟：{rows[0][1]}")
finally:
    connection.close()

预期结果

无效事务已回滚
学习SQL的'参数'
保留记录：1，分钟：35

运行与逐步理解

第一次 with 中写入合法记录，正常退出后提交。第二次 with 先写入“应回滚”，再尝试写入 -1 分钟。CHECK 约束使第二条失败，异常离开 with 时，当前事务中的两条写入一起回滚。

最终只剩最初已经提交的记录，证明回滚边界是这次事务，而不是清空整张表。异常捕获放在 with 外部，让上下文管理器能观察到失败；如果在内部吞掉异常，前面的成功语句可能被提交。

本例使用 sqlite3.connect 的默认事务行为，适用于这里声明的 Python 环境。with connection 负责事务处理，并不负责关闭连接，所以 finally 显式调用 close。生产使用时应明确事务设置、连接生命周期与并发写入策略。

常见问题与边界

• SQL 字符串拼接会破坏数据与语句边界，用户标题必须作为绑定参数传入。
• 不能把每次插入都视为自动永久保存，需要理解当前连接的提交策略。
• fetchall 适合小结果集；大量记录应分页或逐行读取。
• SQLite 列类型并不自动等同严格的业务类型验证，本表的非负约束也不代替完整输入校验。

动手练习

把 -1 改为 5，预测记录数；再恢复 -1，确认“应回滚”不会留下。

参考答案

改成 5 后第二个事务正常提交，共保留 3 条记录。恢复 -1 并重新运行这个内存数据库示例，第二个事务全部回滚，再次只保留 1 条。若改用持久化文件，重复运行还需要处理建表和重复数据问题。