我试图端口一些代码到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)!
(我还要补充一点,如果我把begin
和commit
内部呼叫内部executescript
那么它在所有情况下都正确的行为,但遗憾的是我不能用这种方法在我的申请。此外,改变sql.isolation_level
的出现使没有区别的行为。)
能向我解释的人这里发生了什么? 我需要了解这一点; 如果我不能在数据库中信任的交易,我不能让我的应用程序的工作...
Python 2.7版,蟒蛇-sqlite3的2.6.0,sqlite3的3.7.13,Debian的。
Python的DB API尝试是聪明, 开始和自动提交事务 。
我会建议使用不使用Python DB API,如DB驱动APSW 。
对于任何想谁与sqlite3的LIB工作,无论它的缺点,我发现,你可以保持交易的一些控制,如果你做到以下两点:
- 设置
Connection.isolation_level = None
(按照文档 ,这意味着自动提交模式) - 避免使用
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")
每对文档 ,
连接对象可以作为上下文管理自动提交或回滚事务。 在例外的情况下,事务回滚; 否则,事务被提交:
因此,如果你让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,)
如预期。
这就是我认为是基于我的Python的sqlite3的绑定的阅读以及官方文档SQLITE3发生。 简短的回答是,如果你想有一个合适的交易,你应该坚持这个成语:
with connection:
db.execute("BEGIN")
# do other things, but do NOT use 'executescript'
相反,我的直觉, with connection
不叫BEGIN
在进入的范围。 事实上,它没有做的一切事情__enter__
。 只有当你有效果__exit__
范围, 选择每个COMMIT
或ROLLBACK
取决于范围是否正常或异常退出 。
因此,做正确的事情是要始终明确标注使用您的交易开始BEGIN
。 这使得isolation_level
交易中无关紧要的 ,因为幸运的是虽然它只有一个效果启用自动提交模式 ,并自动提交模式始终是交易区块内抑制 。
另一个怪癖是executescript
,它总是发出COMMIT
运行脚本之前 。 这很容易搞乱了交易,所以你的选择是要么
- 用了整整一个
executescript
一个事务中,没有别的,或 - 避免
executescript
完全; 你可以调用execute
,只要你想多次,受到一个语句-per- execute
限制。
您可以使用连接的上下文管理。 然后它会自动回滚事务中的异常的情况下,或以其他方式提交它们。
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
普通.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,))
如果失败这样的读 - 修改 - 写事务可以重试几次。
这是一个有点老线程,但如果它帮助我发现,连接对象上执行回滚的伎俩。