我在某处读到，要在 Python 中将数据保存到 SQLite3 数据库，应该调用连接对象的方法 commit。然而我从来不需要这样做。为什么？

最佳答案

这意味着您的 SQLite3 数据库驱动程序以自动提交模式运行。

提交模式

一个 database transaction是恢复单位。在交易中 database engines , 所有 SQL 语句都在一个数据库事务中执行。

• 当 SQL 语句未包含在一对 start-transaction(BEGIN 或 SAVEPOINT)和 end-transaction(COMMIT、ROLLBACK 或 RELEASE) SQL 语句，它在由 SQL 语句边界分隔的数据库事务中隐式执行。据说 SQL 语句处于自动提交模式，因为它的数据库事务是自动分隔的。

• 当 SQL 语句包含在一对开始事务(BEGIN 或 SAVEPOINT)和结束事务(COMMIT, ROLLBACK 或 RELEASE) SQL 语句，它在由一对 SQL 语句分隔的数据库事务中显式 执行。据说该 SQL 语句处于手动提交模式，因为它的数据库事务是手动分隔的。

换句话说，在数据库引擎级别，自动提交模式是默认的。

最佳做法是 always use the manual commit mode ，因为通过将 SQL 语句显式分组到数据库事务中，可以避免数据损坏，因为恢复单元是按预期分隔的。

Database drivers位于数据库引擎之上，因此可以转换它们发送到底层数据库引擎的 SQL 语句。数据库驱动程序通常通过 implicitly sending 强制执行手动提交模式。在任何连接和结束事务(COMMIT 或 ROLLBACK)SQL 语句(即也就是说，在任何尚未在数据库事务中的 SQL 语句之前)。这样，喜欢使用自动提交模式的用户必须明确告诉数据库驱动程序。

换句话说，在数据库驱动程序级别，手动提交模式通常是默认的。

SQLite 数据库引擎

SQLite database engine默认以自动提交模式运行:

Test For Auto-Commit Mode

int sqlite3_get_autocommit(sqlite3*);

The sqlite3_get_autocommit() interface returns non-zero or zero if the given database connection is or is not in autocommit mode, respectively. Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK.

If certain kinds of errors occur on a statement within a multi-statement transaction (errors including SQLITE_FULL, SQLITE_IOERR, SQLITE_NOMEM, SQLITE_BUSY, and SQLITE_INTERRUPT) then the transaction might be rolled back automatically. The only way to find out whether SQLite automatically rolled back the transaction after an error is to use this function.

If another thread changes the autocommit status of the database connection while this routine is running, then the return value is undefined.

See also lists of Objects, Constants, and Functions.

SQLite3 数据库驱动

PEP 249要求 Python 数据库驱动程序默认以手动提交模式运行:

.commit()

Commit any pending transaction to the database.

Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.

Database modules that do not support transactions should implement this method with void functionality.

因此，SQLite3 database driver默认以手动提交模式运行:

Controlling Transactions

The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not.

autocommit mode means that statements that modify the database take effect immediately. A BEGIN or SAVEPOINT statement disables autocommit mode, and a COMMIT, a ROLLBACK, or a RELEASE that ends the outermost transaction, turns autocommit mode back on.

The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).

You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. If you specify no isolation_level, a plain BEGIN is used, which is equivalent to specifying DEFERRED. Other possible values are IMMEDIATE and EXCLUSIVE.

You can disable the sqlite3 module’s implicit transaction management by setting isolation_level to None. This will leave the underlying sqlite3 library operating in autocommit mode. You can then completely control the transaction state by explicitly issuing BEGIN, ROLLBACK, SAVEPOINT, and RELEASE statements in your code.

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

注意。 — 为 backwards compatibility reasons , SQLite3 数据库驱动只在数据修改 (INSERT, UPDATE, DELETE or REPLACE) SQL 语句，不在数据定义之前(CREATE, DROP)也不在数据查询 (SELECT) SQL 语句，不符合 PEP 249 和 will hopefully be addressed soon .

示例

以下 Python 程序说明了在 SQLite3 数据库驱动程序中使用手动提交模式与自动提交模式的含义:

import sqlite3

# Manual commit mode (the default).

connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")  # sent as is (DDL)
cursor.execute("INSERT INTO t VALUES (?)", (5,))  # sent as BEGIN; … (DML)
cursor.close()
connection.close()  # connection closed without COMMIT statement (common error)

connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")  # table persisted (full transaction)
assert cursor.fetchall() == []  # data did not persist (partial transaction)
cursor.close()
connection.close()

# Auto-commit mode.

connection = sqlite3.connect("test.sqlite", isolation_level=None)
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")  # sent as is
cursor.execute("INSERT INTO t VALUES (?)", (5,))  # sent as is
cursor.close()
connection.close()

connection = sqlite3.connect("test.sqlite", isolation_level=None)
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")  # table persisted
assert cursor.fetchall() == [(5,)]  # data persisted
cursor.close()
connection.close()

Note. — 如果使用 in-memory 数据库而不是 on-disk 数据库(通过传递参数 ":memory:" 而不是 "test.sqlite" 到函数 sqlite3.connect)，因为内存数据库被删除当连接关闭时。

关于python - 为什么 SQLite3 不需要调用 commit() 来保存数据？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4699605/