为什么 SQLite3 不需要 commit() 调用来保存数据? [英] Why doesn’t SQLite3 require a commit() call to save data?

问题描述

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

解决方案

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

自动提交和手动提交模式

数据库事务是一个恢复单元.在事务性数据库引擎中，所有 SQL 语句都在数据库中执行交易.

• 当 SQL 语句没有包含在一对开始事务(BEGIN 或 SAVEPOINT)和结束事务(COMMIT、ROLLBACK 或 RELEASE) SQL 语句，它在 SQL 语句边界隐式分隔的数据库事务中执行.据说 SQL 语句处于自动提交模式，因为它的数据库事务是自动分隔的.

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

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

最佳实践是始终使用手动提交模式，因为通过对 SQL 语句进行分组明确地进入数据库事务，因为恢复单元按预期分隔，所以避免了数据损坏.

数据库驱动程序高于数据库引擎，因此可以转换它们发送到的 SQL 语句底层数据库引擎.数据库驱动程序通常通过隐式发送 在任何连接和结束事务(COMMIT 或 ROLLBACK)SQL 语句之后给数据库引擎一个开始事务(BEGIN)SQL 语句(也就是说，在任何尚未存在于数据库事务中的 SQL 语句之前).这样，喜欢使用自动提交模式的用户必须明确告诉数据库驱动程序.

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

SQLite 数据库引擎

SQLite 数据库引擎默认在自动提交模式下运行:><块引用>

测试自动提交模式

---

int sqlite3_get_autocommit(sqlite3*);

如果给定的数据库连接是否处于自动提交模式，sqlite3_get_autocommit() 接口分别返回非零或零.自动提交模式默认开启.BEGIN 语句禁用自动提交模式.自动提交模式由 COMMIT 或 ROLLBACK 重新启用.

如果在多语句事务中的语句上发生某些类型的错误(错误包括 SQLITE_FULL、SQLITE_IOERR、SQLITE_NOMEM、SQLITE_BUSY 和 SQLITE_INTERRUPT) 然后事务可能会自动回滚.判断SQLite是否在出错后自动回滚事务的唯一方法就是使用这个函数.

如果另一个线程在此例程运行时更改了数据库连接的自动提交状态，则返回值未定义.

另见对象、常量和函数列表.

SQLite3 数据库驱动

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

<块引用>

.commit()

将任何待处理的事务提交到数据库.

注意，如果数据库支持自动提交功能，这必须最初关闭.可以提供一个接口方法将其返回

不支持事务的数据库模块应该实现此方法具有 void 功能.

因此，SQLite3 数据库驱动程序默认运行在手动提交模式下:

<块引用>

控制交易

---

底层 sqlite3 库默认以自动提交模式运行，但 Python sqlite3 模块默认不运行.

autocommit 模式意味着修改数据库的语句立即生效.BEGIN 或 SAVEPOINT 语句禁用 autocommit 模式，COMMIT、ROLLBACK，或结束最外层事务的 RELEASE，重新打开 autocommit 模式.

Python sqlite3 模块默认发出一个 BEGIN 语句隐含在数据修改语言 (DML) 语句之前(即INSERT/UPDATE/DELETE/REPLACE).

您可以通过connect()isolation_level参数来控制sqlite3隐式执行哪种BEGIN语句> 打电话，或通过连接的 isolation_level 属性.如果您指定 noisolation_level，使用一个普通的BEGIN，相当于指定 DEFERRED.其他可能的值是 IMMEDIATE 和独家.

您可以通过将 isolation_level 设置为 None 来禁用 sqlite3 模块的隐式事务管理.这将使底层 sqlite3 库在 autocommit 模式下运行.然后，您可以通过显式发出 BEGIN、ROLLBACK、SAVEPOINT 和 RELEASE 语句来完全控制事务状态.代码.

在 3.6 版更改: sqlite3 用于在 DDL 语句之前隐式提交打开的事务.现在已经不是这样了.

注意. — 出于向后兼容性原因，SQLite3数据库驱动只在数据修改(INSERT、UPDATE、DELETE> 或 REPLACE) SQL 语句，不在 数据定义 (CREATE, DROP) 之前，也不在 数据查询之前 (SELECT) SQL 语句，不符合 PEP 249 并且 有望尽快解决.

示例

以下 Python 程序说明了对 SQLite3 数据库驱动程序使用手动提交模式与自动提交模式的影响:

导入 sqlite3# 手动提交模式(默认).connection = sqlite3.connect(test.sqlite")游标 = connection.cursor()cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)") # 按原样发送(数据定义.)cursor.execute("INSERT INTO t VALUES (?)", (5,)) # 作为开始发送；...(数据模式.)游标.close()connection.close() # 没有 COMMIT 语句的连接关闭(常见错误)connection = sqlite3.connect(test.sqlite")游标 = connection.cursor()cursor.execute("SELECT * FROM t") # 表被持久化(完整事务)assert cursor.fetchall() == [] # 数据没有被持久化(部分事务)游标.close()连接.close()# 自动提交模式.connection = sqlite3.connect(test.sqlite",isolation_level=None)游标 = connection.cursor()cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)") # 按原样发送cursor.execute("INSERT INTO t VALUES (?)", (5,)) # 按原样发送游标.close()连接.close()connection = sqlite3.connect(test.sqlite",isolation_level=None)游标 = connection.cursor()cursor.execute("SELECT * FROM t") # 表被持久化assert cursor.fetchall() == [(5,)] # 数据被持久化游标.close()连接.close()

注意.——如果使用内存数据库而不是磁盘数据库(通过传递参数 ":memory:" 而不是 "test.sqlite" 到函数 sqlite3.connect)，因为在内存中连接关闭时，数据库将被删除.

I read somewhere that to save data to a SQLite3 database in Python, the method commit of the connection object should be called. Yet I have never needed to do this. Why?

解决方案

It means that your SQLite3 database driver operates in auto-commit mode.

Auto-commit and manual commit modes

A database transaction is a unit of recovery. In transactional database engines, all SQL statements are executed in a database transaction.

• When an SQL statement is not enclosed in a pair of start-transaction (BEGIN or SAVEPOINT) and end-transaction (COMMIT, ROLLBACK or RELEASE) SQL statements, it is executed in the database transaction implicitly delimited by the boundaries of the SQL statement. The SQL statement is said to be in auto-commit mode, since its database transaction is automatically delimited.

• When an SQL statement is enclosed in a pair of start-transaction (BEGIN or SAVEPOINT) and end-transaction (COMMIT, ROLLBACK or RELEASE) SQL statements, it is executed in the database transaction explicitly delimited by the pair of SQL statements. The SQL statement is said to be in manual commit mode, since its database transaction is manually delimited.

In other words, at the database engine level, the auto-commit mode is the default.

The best practice is to always use the manual commit mode, because by grouping SQL statements into database transactions explicitly, data corruption is avoided since units of recovery are delimited as intended.

Database drivers are above database engines and therefore can transform the SQL statements that they send to underlying database engines. A database driver often enforces the manual commit mode by implicitly sending to the database engine a start-transaction (BEGIN) SQL statement after any connection and end-transaction (COMMIT or ROLLBACK) SQL statement (that is to say before any SQL statement that is not already in a database transaction). That way, users who prefer to use the auto-commit mode have to tell the database driver explicitly.

In other words, at the database driver level, the manual commit mode is often the default.

SQLite database engine

The SQLite database engine operates by default in auto-commit mode:

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 database driver

PEP 249 requires that Python database drivers operate by default in manual commit mode:

.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.

Consequently, the SQLite3 database driver operates by default in manual commit mode:

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.

Note. — For backwards compatibility reasons, the SQLite3 database driver initiates the manual commit mode only before data modification (INSERT, UPDATE, DELETE or REPLACE) SQL statements, not before data definition (CREATE, DROP) nor data query (SELECT) SQL statements, which is not PEP 249 compliant and will hopefully be addressed soon.

Example

The following Python program illustrates the implications of using the manual commit mode versus the auto-commit mode with the SQLite3 database driver:

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 (data def.)
cursor.execute("INSERT INTO t VALUES (?)", (5,))  # sent as BEGIN; … (data mod.)
cursor.close()
connection.close()  # connection closed without COMMIT statement (common mistake)

connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")  # the table was persisted (full transaction)
assert cursor.fetchall() == []  # the data wasn’t persisted (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")  # the table was persisted
assert cursor.fetchall() == [(5,)]  # the data was persisted
cursor.close()
connection.close()

Note. — The second assertion would fail if an in-memory database was used instead of an on-disk database (by passing the argument ":memory:" instead of "test.sqlite" to the function sqlite3.connect), since in-memory databases are dropped when the connection is closed.

这篇关于为什么 SQLite3 不需要 commit() 调用来保存数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！