为什么 SQLite 不需要调用 commit() 来保存数据？

Question

我在某处读到，要将数据保存到 Python 中的 SQLite 数据库，需要调用 commit() 。但我从来不需要这样做。为什么？

Answer 1

这意味着您的 SQLite3 驱动程序处于自动提交模式。

了解提交模式

在事务数据库管理系统中，事务是

• 可恢复的数据访问操作序列（原子性属性），以隐藏事务中止的影响，这意味着中止事务后的数据库状态与未执行该事务的操作相同；
• serialisable（隔离属性）隐藏其他并发事务的影响，意味着并发执行多个事务后的数据库状态与顺序执行时相同。

根据 ISO/IEC 9075:2011 SQL 标准，如果当前没有活动事务，则事务由启动事务语句显式启动，或者隐式在

• 所有SQL架构语句之前启动；
• 一些 SQL 事务语句（保存点语句、提交语句、回滚语句）；
• 一些 SQL 数据语句（open 语句、close 语句、fetch 语句、select 语句、insert 语句、delete 语句、update 语句、merge 语句、truncate table 语句、分配扩展动态游标语句、分配接收游标语句、动态 open 语句、动态close语句、动态fetch语句、直接select语句、动态单行select语句、动态删除语句、可准备的动态删除语句、动态更新语句、可准备的动态更新语句、释放定位器语句、保留定位器语句）；
• 一些 SQL 动态语句（描述输入语句、描述输出语句、分配描述符语句、释放描述符语句、获取描述符语句、设置描述符语句、释放准备好的语句）。

事务由提交语句或回滚语句显式终止，或者由事务中止隐式终止（参见 ISO/IEC 9075-2:2011）。

所以几乎所有的SQL语句都是在事务中执行的，事务必须显式提交才能生效。处于此提交模式的数据库接口处于手动提交模式。手动提交模式是一种最佳实践，对于程序（非交互式会话）来说很好，但也可以对于用户来说很麻烦（交互式会话）。因此，大多数数据库接口还为交互式会话提供自动提交模式。自动提交模式下，在除启动事务语句之外的事务启动语句之前隐式发起的事务会在该语句之后隐式提交，而由启动事务语句显式发起的事务必须显式提交才能生效。

数据库接口特定于数据库引擎，因此通常使用更通用的接口与数据库引擎交互（例如开放数据库连接，Java 数据库连接、Python 数据库 API）。通用数据库接口对特定数据库接口的适配是由数据库驱动程序提供的。 SQLite 引擎 在 C 语言中具有特定的数据库接口。 SQLite3 驱动程序 使 Python 数据库 API 适应 SQLite API。

SQL 语句由数据库引擎解释。因此，当数据库引擎和数据库驱动程序处于相同的提交模式（手动提交模式或自动提交模式）时，数据库驱动程序可以将未修改的SQL语句传递给数据库引擎。但是，当它们处于不同的提交模式时，数据库驱动程序必须配置数据库引擎以匹配数据库驱动程序的提交模式，或者在将 SQL 语句传递到数据库引擎之前转换 SQL 语句以模拟数据库驱动的提交模式：

• 数据库驱动通过隐式发出 在每个连接打开、提交语句和回滚语句之后添加一个启动事务语句，以防止数据库引擎在自动提交模式下隐式提交事务，并通过 在每个连接关闭之前隐式发出回滚语句，以强制数据库引擎回滚最后一个事务（例如 A ; B; 开始交易; D;一个; B;开始交易； C; D；犯罪;开始交易； E; F；回滚；)。
• 数据库驱动程序模拟自动提交模式，通过在除启动事务语句以及启动事务语句和提交语句或回滚语句之外的每个事务启动语句之后隐式发出提交语句，使数据库引擎显式提交事务手动提交模式（例如A; B; START TRANSACTION; C; D; COMMIT; E; F; 转换为A; COMMIT; B; COMMIT; START TRANSACTION; C; D;提交；提交；

SQLite 引擎始终处于自动提交模式（参见 SQLite 文档）。 SQLite3 驱动程序默认处于手动提交模式，因此必须模拟它（参见SQLite3 文档）。 SQLite3驱动程序目前并没有像上一段所述那样正确模拟手动提交模式，而是通过在除select语句之外的每个事务启动SQL数据语句之前隐式发出启动事务语句，因此SQL模式语句和select语句不是始终处于显式启动的事务中（参见 SQLite3 实现）。这不符合 Python 数据库 API 规范，因此使用 Connection 类的 isolation_level 属性配置的传统手动提交模式和自动提交模式将在 Python 3.12 中被弃用，其中在 Connection 类中引入了新的 autocommit 属性，用于配置符合 Python 数据库 API 的手动提交模式和自动提交模式（参见 CPython 问题 #83638 和 CPython 拉取请求#93823）。

示例 1. - 此 Python 3.11 程序在旧版手动提交模式下使用 SQLite3 驱动程序。

import sqlite3

connection = sqlite3.connect(':memory:', isolation_level='DEFERRED')
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is explicitly initiated here by a start transaction statement.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
cursor.execute('CREATE TABLE u (j INT)')
cursor.execute('INSERT INTO u VALUES (?)', (2,))
cursor.close()
connection.close()
# Transaction 2 is implicitly rolled back here.
assert statements == [
    'CREATE TABLE t (i INT)',
    'BEGIN DEFERRED',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

示例 2. — 此 Python 3.12 程序在手动提交模式下使用 SQLite3 驱动程序。

import sqlite3

connection = sqlite3.connect(':memory:', autocommit=False)
# Transaction 1 is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is True
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
cursor.execute('CREATE TABLE t (i INT)')
cursor.execute('INSERT INTO t VALUES (?)', (1,))
cursor.execute('CREATE TABLE u (j INT)')
cursor.execute('INSERT INTO u VALUES (?)', (2,))
cursor.close()
connection.close()
# Transaction 1 is explicitly rolled back here by a rollback statement.
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
    'ROLLBACK',
]

示例 3 - 此 Python 3.11 程序在旧版自动提交模式下使用 SQLite3 驱动程序。

import sqlite3

connection = sqlite3.connect(':memory:', isolation_level=None)
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is implicitly initiated here.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
# Transaction 2 is implicitly committed here.
# Transaction 3 is implicitly initiated here.
cursor.execute('CREATE TABLE u (j INT)')
# Transaction 3 is implicitly committed here.
# Transaction 4 is implicitly initiated here.
cursor.execute('INSERT INTO u VALUES (?)', (2,))
# Transaction 4 is implicitly committed here.
cursor.close()
connection.close()
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

示例 4. — 此 Python 3.12 程序在自动提交模式下使用 SQLite3 驱动程序。

import sqlite3

connection = sqlite3.connect(':memory:', autocommit=True)
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is implicitly initiated here.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
# Transaction 2 is implicitly committed here.
# Transaction 3 is implicitly initiated here.
cursor.execute('CREATE TABLE u (j INT)')
# Transaction 3 is implicitly committed here.
# Transaction 4 is implicitly initiated here.
cursor.execute('INSERT INTO u VALUES (?)', (2,))
# Transaction 4 is implicitly committed here.
cursor.close()
connection.close()
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

Answer 2

可能自动提交已打开，默认情况下 http://www.sqlite.org/c3ref/get_autocommit。 html

Answer 3

添加 isolation_level=None 进行连接（Ref )

db = sqlite.connect(":memory:", isolation_level=None)

Answer 4

连接对象还可以用作自动提交或回滚事务的上下文管理器。 11.13.7.3。在 docs.python

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

Answer 5

Python sqlite3 在“INSERT”或“UPDATE”之前自动发出 BEGIN 语句。之后它会自动提交任何其他命令或 db.close()