使用 Python sqlite3 进行交易 [英] Transactions with Python sqlite3

问题描述

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

I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused. I'm really confused by this; I've used sqlite a lot in other languages, because it's great, but I simply cannot work out what's wrong here.

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

Here is the schema for my test database (to be fed into the sqlite3 command line tool).

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 脚本在执行更新后在第二行失败.

You may notice the deliberate mistake in it. This causes the SQL script to fail on the second line, after the update has been executed.

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

According to the docs, the with sql statement is supposed to set up an implicit transaction around the contents, which is only committed if the block succeeds. However, when I run it, I get the expected SQL error... but the value of i is set from 99 to 1. I'm expecting it to remain at 99, because that first update should be rolled back.

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

Here is another test program, which explicitly calls commit() and 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()

这完全相同——我从 99 变为 1.

This behaves in precisely the same way --- i gets changed from 99 to 1.

现在我明确调用 BEGIN 和 COMMIT:

Now I'm calling BEGIN and COMMIT explicitly:

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")

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

This fails too, but in a different way. I get this:

sqlite3.OperationalError: cannot rollback - no transaction is active

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

However, if I replace the calls to c.execute() to c.executescript(), then it works (i remains at 99)!

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

(I should also add that if I put the begin and commit inside the inner call to executescript then it behaves correctly in all cases, but unfortunately I can't use that approach in my application. In addition, changing sql.isolation_level appears to make no difference to the behaviour.)

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

Can someone explain to me what's happening here? I need to understand this; if I can't trust the transactions in the database, I can't make my application work...

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

Python 2.7, python-sqlite3 2.6.0, sqlite3 3.7.13, Debian.

推荐答案

Python 的 DB API 尝试变得智能，而 自动开始和提交事务.

Python's DB API tries to be smart, and begins and commits transactions automatically.

我建议使用不使用 Python DB API 的 DB 驱动程序，例如 apsw.

I would recommend to use a DB driver that does not use the Python DB API, like apsw.

这篇关于使用 Python sqlite3 进行交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！