与Python sqlite3的交易(Transactions with Python sqlite

2019-08-20 01:49发布

我试图端口一些代码到Python使用SQLite数据库,而我试图让交易工作,我得到真的很困惑。 我真的被这个迷惑; 我使用的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语句应该建立一个隐含的交易周围的内容,这要是块成功,则只是承诺。 然而,当我运行它,我得到预期的SQL错误...但我的价值是从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()

这表现在完全相同的方式---我得到改变,从99到1。

现在,我打电话BEGIN和明确承诺:

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

(我还要补充一点,如果我把begincommit内部呼叫内部executescript那么它在所有情况下都正确的行为,但遗憾的是我不能用这种方法在我的申请。此外,改变sql.isolation_level的出现使没有区别的行为。)

能向我解释的人这里发生了什么? 我需要了解这一点; 如果我不能在数据库中信任的交易,我不能让我的应用程序的工作...

Python 2.7版,蟒蛇-sqlite3的2.6.0,sqlite3的3.7.13,Debian的。

Answer 1:

Python的DB API尝试是聪明, 开始和自动提交事务 。

我会建议使用使用Python DB API,如DB驱动APSW 。



Answer 2:

对于任何想谁与sqlite3的LIB工作,无论它的缺点,我发现,你可以保持交易的一些控制,如果你做到以下两点:

  1. 设置Connection.isolation_level = None (按照文档 ,这意味着自动提交模式)
  2. 避免使用executescript可言,因为根据文档为“问题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")


Answer 3:

每对文档 ,

连接对象可以作为上下文管理自动提交或回滚事务。 在例外的情况下,事务回滚; 否则,事务被提交:

因此,如果你让Python的退出与语句在发生异常时,该交易将回滚。

import sqlite3

filename = '/tmp/test.db'
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    sqls = [
        'DROP TABLE IF EXISTS test',
        'CREATE TABLE test (i integer)',
        'INSERT INTO "test" VALUES(99)',]
    for sql in sqls:
        cursor.execute(sql)
try:
    with sqlite3.connect(filename) as conn:
        cursor = conn.cursor()
        sqls = [
            'update test set i = 1',
            'fnord',   # <-- trigger error
            'update test set i = 0',]
        for sql in sqls:
            cursor.execute(sql)
except sqlite3.OperationalError as err:
    print(err)
    # near "fnord": syntax error
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)

产量

(99,)

如预期。



Answer 4:

这就是我认为是基于我的Python的sqlite3的绑定的阅读以及官方文档SQLITE3发生。 简短的回答是,如果你想有一个合适的交易,你应该坚持这个成语:

with connection:
    db.execute("BEGIN")
    # do other things, but do NOT use 'executescript'

相反,我的直觉, with connection BEGIN在进入的范围。 事实上,它没有做的一切事情__enter__ 。 只有当你有效果__exit__范围, 选择每个COMMITROLLBACK取决于范围是否正常或异常退出 。

因此,做正确的事情是要始终明确标注使用您的交易开始BEGIN 。 这使得isolation_level交易中无关紧要的 ,因为幸运的是虽然它只有一个效果启用自动提交模式 ,并自动提交模式始终是交易区块内抑制 。

另一个怪癖是executescript ,它总是发出COMMIT运行脚本之前 。 这很容易搞乱了交易,所以你的选择是要么

  • 用了整整一个executescript一个事务中,没有别的,或
  • 避免executescript完全; 你可以调用execute ,只要你想多次,受到一个语句-per- execute限制。


Answer 5:

您可以使用连接的上下文管理。 然后它会自动回滚事务中的异常的情况下,或以其他方式提交它们。

try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))

except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

见https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager



Answer 6:

普通.execute()的预期与舒适的默认自动提交模式下工作,并with conn: ...上下文管理做自动提交回滚-除了受保护的读-修改-写入事务 ,这是在解释这个答案的结束。

sqlite3的模块的非标准conn_or_cursor.executescript()不参加(默认)部分自动提交模式(因此通常不与工作with conn: ...上下文管理器),但在转发剧本不成熟。 为此,它只是犯了潜在的挂起 在启动自动提交事务,“走出原始”之前。

这也意味着,如果没有一个“BEGIN”的剧本里面executescript()出现错误或以其他方式因而没有回滚选项就没有这个交易,和。

因此,与executescript()我们最好使用一个明确的BEGIN(就像你inital模式创建脚本没有为“原始”模式sqlite的命令行工具)。 而这种互动显示怎么回事一步一步:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
OperationalError: near "FNORD": syntax error
>>> list(conn.execute('SELECT * FROM test'))
[(1,)]
>>> conn.rollback()
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

该脚本没有达到“提交”。 因而我们可以认为目前的中间状态,并决定回滚(或提交不过)

因此,一个工作尝试-除了回滚通过excecutescript()看起来是这样的:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> try: conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
... except Exception as ev: 
...     print("Error in executescript (%s). Rolling back" % ev)
...     conn.executescript('ROLLBACK')
... 
Error in executescript (near "FNORD": syntax error). Rolling back
<sqlite3.Cursor object at 0x011F56E0>
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

(注意通过脚本回滚这里,因为没有.execute()接手提交控制)


在这里,在与受保护的读-修改-写事务的更困难的问题结合自动提交模式下的笔记-这让@Jeremie说:“ 我们发现写在源码/ Python的交易所有很多很多的事情,这是让我做我想做的唯一 “在其中包括了一个例子的注释(对数据库的独占读取锁)。 c.execute("begin") 虽然sqlite3的一般不会使一个长期的封锁独占读取锁除了实际回写的持续时间,但更聪明的5级锁来实现对重叠的变化足够的保护。

with conn:自动提交上下文不已经把或触发一个足够强大的锁在保护的读-修改-写的sqlite3的5级锁定方案 。 这种锁定仅当发出第一数据修改命令隐含制成 - 从而为时已晚。 只有明确的BEGIN (DEFERRED) (TRANSACTION)会触发通缉行为:

第一次读对数据库操作创建一个共享锁,初次写入创建一个RESERVED锁。

所以,它采用一般方式编程语言(而不是一个特殊的原子SQL UPDATE子句)受保护的读 - 修改 - 写事务看起来是这样的:

with conn:
    conn.execute('BEGIN TRANSACTION')    # crucial !
    v = conn.execute('SELECT * FROM test').fetchone()[0]
    v = v + 1
    time.sleep(3)  # no read lock in effect, but only one concurrent modify succeeds
    conn.execute('UPDATE test SET i=?', (v,))

如果失败这样的读 - 修改 - 写事务可以重试几次。



Answer 7:

这是一个有点老线程,但如果它帮助我发现,连接对象上执行回滚的伎俩。



文章来源: Transactions with Python sqlite3