python - 用作上下文管理器的 sqlite3 连接的事务不是原子的

Question

According to the documentation ,

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

我知道 with 语句中的所有内容都应该是原子事务。现在考虑这段代码

import sqlite3
con = sqlite3.connect(':memory:')

try:
  with con:
    con.execute('create table foo (id integer primary key)')
    con.execute('insert into foo values (1)')
    con.execute('insert into foo values (1)')
except sqlite3.Error:
  print('transaction failed')

try:
  rec = con.execute('select count(*) from foo')
  print('number of records: {}'.format(rec.fetchone()[0]))
except sqlite3.Error as e:
  print(e)

返回

transaction failed
number of records: 0

一方面，由于重复的值，交易失败。另一方面，表 foo 存在，即使它是空的，这意味着第一个插入已被回滚。表创建不应该也回滚吗？

“手动”进行交易会产生预期的结果:

import sqlite3
con = sqlite3.connect(':memory:')

con.execute('begin')
try:
  con.execute('create table foo (id integer primary key)')
  con.execute('insert into foo values (1)')
  con.execute('insert into foo values (1)')
  con.execute('commit')
except sqlite3.Error:
  con.execute('rollback')
  print('transaction failed')

try:
  rec = con.execute('select count(*) from foo')
  print('number of records: {}'.format(rec.fetchone()[0]))
except sqlite3.Error as e:
  print(e)

返回

transaction failed
no such table: foo

为什么会出现这种差异？

Answer 1

从 Python 3.6 开始，DDL 或数据定义语言语句(例如 CREATE TABLE)不会启动事务。这意味着任何此类语句都会在您执行时自动提交。

请参阅Controlling transactions section :

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).

[...]

Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.

这意味着如果您希望 DDL 语句成为事务的一部分，则必须显式启动事务。

使用连接作为上下文管理器仍然只在退出时发出提交或回滚，它不会启动事务；相反，遇到的第一个 DML 语句将启动一个。如果您希望 DDL 成为事务的一部分，请在顶部添加 begin 语句:

try:
    with con:
        con.execute('begin')  # explicit, rather than implicit, transaction start
        con.execute('create table foo (id integer primary key)')
        con.execute('insert into foo values (1)')
        con.execute('insert into foo values (1)')
except sqlite3.Error:
    print('transaction failed')