为什么SQLite3仍然在Python中没有commit()调用的情况下仍保存数据? [英] Why does SQLite3 still save data without commit() calls in Python?

问题描述

我在某处读到要将数据保存到Python中的SQLite3数据库中，您必须调用连接对象的commit()方法.我从不这样做，但是数据仍然保存到我的数据库中……为什么?

I read somewhere that to save data to a SQLite3 database in Python you have to call the commit() method of the connection object. I never do this but data still get saved to my database… Why?

推荐答案

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

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

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

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

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

• When an SQL statement is not within 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.

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

When an SQL statement is within 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.

最佳做法是始终使用手动提交模式，因为通过对SQL语句进行分组明确地将其放入数据库事务中，避免了数据损坏，因为恢复单元已按预期定界.

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.

数据库驱动程序位于数据库引擎之上，因此可以转换它们发送到的SQL语句基础数据库引擎.数据库驱动程序通常通过隐式发送来强制实施手动提交模式. a>向数据库引擎发送在任何连接和结束事务处理(COMMIT或ROLLBACK)SQL语句之后的开始事务(BEGIN)SQL语句(也就是说，在尚未存在于数据库中的任何SQL语句之前)数据库事务).这样，喜欢使用自动提交模式的用户必须明确告知数据库驱动程序.

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数据库引擎默认在自动提交模式下运行:

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

测试自动提交模式

int sqlite3_get_autocommit(sqlite3*);

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

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.

如果多语句事务中的语句上发生某些类型的错误(包括SQLITE_FULL，SQLITE_IOERR，SQLITE_NOMEM，SQLITE_BUSY和SQLITE_INTERRUPT的错误)，则该事务可能会自动回滚.找出错误后SQLite是否自动回滚事务的唯一方法是使用此功能.

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数据库驱动程序默认运行在手动提交模式下:

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.

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

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

控制交易

底层的sqlite3库默认情况下在自动提交模式下运行，但Python sqlite3模块默认情况下不运行.

Controlling Transactions

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

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

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.

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

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

您可以通过对connect()调用的isolation_level参数来控制隐式执行sqlite3哪种类型的BEGIN语句，或者 通过连接的isolation_level属性.如果不指定 isolation_level，使用普通的BEGIN，等效于 指定DEFERRED.其他可能的值是IMMEDIATE和 EXCLUSIVE.

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.

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

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.

在3.6版中进行了更改: sqlite3用于在DDL语句之前隐式提交未处理的事务.情况不再如此.

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

注意.-用于向后兼容的原因，则SQLite3数据库驱动程序仅在数据修改(INSERT，UPDATE，DELETE或REPLACE)SQL语句之前启动手动提交模式，而不在数据定义(CREATE，DROP)或数据查询(SELECT)SQL语句，它们不符合PEP 249，并且将希望很快得到解决.

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.

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

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 INTEGER)")  # sent as is (DDL)
cursor.execute("INSERT INTO t VALUES (?)", (5,)) # sent as: BEGIN; statement (DML)
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
assert cursor.fetchall() == []  # the data were not persisted
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 INTEGER)")  # 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 were persisted
cursor.close()
connection.close()

注意. —如果使用内存数据库而不是磁盘数据库，则第二个断言将失败(通过":memory:"参数而不是sqlite3.connect函数的"test.sqlite")，因为在关闭连接时会删除内存数据库.

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

这篇关于为什么SQLite3仍然在Python中没有commit()调用的情况下仍保存数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！