python-2.7 - 使用 Python sqlite3 进行交易

Question

我正在尝试将一些代码移植到使用 sqlite 数据库的 Python，并且我正在尝试让事务正常工作，但我真的很困惑。我对此感到很困惑；我在其他语言中经常使用 sqlite，因为它很棒，但我就是无法弄清楚这里出了什么问题。

这是我的测试数据库的架构(将被输入到 sqlite3 命令行工具中)。

BEGIN TRANSACTION;
CREATE TABLE test (i integer);
INSERT INTO "test" VALUES(99);
COMMIT;

这是一个测试程序。

import sqlite3

sql = sqlite3.connect("test.db")
with sql:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
        """)

您可能会注意到其中的故意错误。这会导致 SQL 脚本在执行更新后的第二行失败。

根据文档，with sql 语句应该围绕内容设置隐式事务，只有在 block 成功时才会提交。然而，当我运行它时，我得到了预期的 SQL 错误...但是 i 的值设置为从 99 到 1。我希望它保持在 99，因为应该回滚第一个更新。

这是另一个测试程序，它显式调用了 commit() 和 rollback()。

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
    """)
    sql.commit()
except sql.Error:
    print("failed!")
    sql.rollback()

它的行为方式完全相同 --- i 从 99 变为 1。

现在我显式调用 BEGIN 和 COMMIT:

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.execute("begin")
    c.executescript("""
            update test set i = 1;
            fnord;
            update test set i = 0;
    """)
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")

这也失败了，但方式不同。我明白了:

sqlite3.OperationalError: cannot rollback - no transaction is active

但是，如果我将对 c.execute() 的调用替换为 c.executescript()，那么它有效(我仍然在 99)!

(我还应该补充一点，如果我将 begin 和 commit 放在对 executescript 的内部调用中，那么它在所有情况下都会正确运行，但不幸的是我不能在我的应用程序中使用这种方法。此外，更改 sql.isolation_level 似乎对行为没有影响。)

有人可以向我解释这里发生了什么吗？我需要明白这一点；如果我不能信任数据库中的事务，我就无法使我的应用程序工作...

Python 2.7、python-sqlite3 2.6.0、sqlite3 3.7.13、Debian。

Answer 1

对于任何想使用 sqlite3 lib 而不管它的缺点的人，我发现如果你做以下两件事，你可以保持对事务的一些控制:

1. 设置 Connection.isolation_level = None(根据 docs ，这意味着自动提交模式)
2. 完全避免使用executescript，因为根据docs它“首先发出 COMMIT 语句”——即麻烦。事实上，我发现它会干扰任何手动设置的交易

那么，您的测试的以下改编对我有用:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
c = sql.cursor()
c.execute("begin")
try:
    c.execute("update test set i = 1")
    c.execute("fnord")
    c.execute("update test set i = 0")
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")