当前位置： 文江博客话题详情

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

发布于 2024-10-12 01:05:41 字数 86 浏览 4 评论 0原文

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

原文

I read somewhere that to save data to an SQLite database in Python, a commit() call is required. Yet I have never needed to do this. Why?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

擦肩而过的背影 2024-10-19 01:05:41

这意味着您的 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 语句以模拟数据库驱动的提交模式：

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)',
]

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

Understanding commit modes

In transactional database management systems, a transaction is a sequence of data access operations that is

  • recoverable (atomicity property) to hide the effects of an abortion of the transaction, meaning that the database state after an aborted transaction is the same as if no operations of the transaction were performed;
  • serialisable (isolation property) to hide the effects of other concurrent transactions, meaning that the database state after concurrently executing multiple transactions is the same as if they were sequentially executed.

According to the ISO/IEC 9075:2011 SQL standard, if no transaction is currently active, a transaction is explicitly initiated by a start transaction statement, or implicitly initiated before

  • all SQL-schema statements;
  • some SQL-transaction statements (savepoint statement, commit statement, rollback statement);
  • some SQL-data statements (open statement, close statement, fetch statement, select statement, insert statement, delete statement, update statement, merge statement, truncate table statement, allocate extended dynamic cursor statement, allocate received cursor statement, dynamic open statement, dynamic close statement, dynamic fetch statement, direct select statement, dynamic single row select statement, dynamic delete statement, preparable dynamic delete statement, dynamic update statement, preparable dynamic update statement, free locator statement, hold locator statement);
  • some SQL-dynamic statements (describe input statement, describe output statement, allocate descriptor statement, deallocate descriptor statement, get descriptor statement, set descriptor statement, deallocate prepared statement).

And a transaction is explicitly terminated by a commit statement or rollback statement, or implicitly terminated by an abortion of the transaction (cf. ISO/IEC 9075-2:2011).

So almost all SQL statements are executed in a transaction, and the transaction must be explicitly committed to take effect. A database interface that is in this commit mode is in manual commit mode. Manual commit mode is a best practice which is fine for programs (non-interactive sessions) but can be cumbersome for users (interactive sessions). For that reason, most database interfaces also provide an auto-commit mode for interactive sessions. In auto-commit mode, a transaction implicitly initiated before a transaction-initiating statement other than the start transaction statement is implicitly committed after the statement, whereas a transaction explicitly initiated by a start transaction statement must be explicitly committed to take effect.

A database interface is specific to a database engine so more generic interfaces are commonly used to interact with a database engine (e.g. Open Database Connectivity, Java Database Connectivity, Python Database API). The adaptation of a generic database interface to a specific database interface is provided by a database driver. The SQLite engine has a specific database interface in C. The SQLite3 driver adapts the Python Database API to the SQLite API.

SQL statements are interpreted by a database engine. So when a database engine and database driver are in the same commit mode (manual commit mode or auto-commit mode), the database driver can pass unmodified SQL statements to the database engine. However when they are in different commit modes, the database driver has to either configure the database engine to match the commit mode of the database driver, or transform SQL statements before passing them to the database engine to emulate the commit mode of the database driver:

  • The database driver emulates manual commit mode by implicitly issuing a start transaction statement after each connection opening, commit statement, and rollback statement to prevent the transaction from being implicitly committed by the database engine in auto-commit mode, and by implicitly issuing a rollback statement before each connection closing to force the last transaction to be rolled back by the database engine (e.g. A; B; START TRANSACTION; C; D; COMMIT; E; F; is transformed into START TRANSACTION; A; B; START TRANSACTION; C; D; COMMIT; START TRANSACTION; E; F; ROLLBACK;).
  • The database driver emulates auto-commit mode by implicitly issuing a commit statement after each transaction-initiating statements other than start transaction statements and statements within a start transaction statement and commit statement or rollback statement to make the transaction be explicitly committed by the database engine in manual commit mode (e.g. A; B; START TRANSACTION; C; D; COMMIT; E; F; is transformed into A; COMMIT; B; COMMIT; START TRANSACTION; C; D; COMMIT; E; COMMIT; F; COMMIT;).

The SQLite engine is always in auto-commit mode (cf. the SQLite documentation). The SQLite3 driver is by default in manual commit mode so has to emulate it (cf. the SQLite3 documentation). The SQLite3 driver currently does not emulate manual commit mode properly like described in the previous paragraph, but by implicitly issuing a start transaction statement before each transaction-initiating SQL-data statements other than select statements, so SQL-schema statements and select statements are not always in an explicitly initiated transaction (cf. the SQLite3 implementation). That is not Python Database API-conformant so the legacy manual commit mode and auto-commit mode configured with the isolation_level attribute of the Connection class will be deprecated in Python 3.12 with the introduction of a new autocommit attribute in the Connection class for configuring the Python Database API-conformant manual commit mode and auto-commit mode (cf. CPython issue #83638 and CPython pull request #93823).

Example 1. — This Python 3.11 program uses the SQLite3 driver in legacy manual commit mode.

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)',
]

Example 2. — This Python 3.12 program uses the SQLite3 driver in manual commit mode.

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',
]

Example 3. — This Python 3.11 program uses the SQLite3 driver in legacy auto-commit mode.

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)',
]

Example 4. — This Python 3.12 program uses the SQLite3 driver in auto-commit mode.

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)',
]
回复 原文
古镇旧梦 2024-10-19 01:05:41

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

Probably autocommit is on, it is by default http://www.sqlite.org/c3ref/get_autocommit.html

回复 原文
自找没趣 2024-10-19 01:05:41

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

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

Add isolation_level=None to connect (Ref)

db = sqlite.connect(":memory:", isolation_level=None)
回复 原文
时光礼记 2024-10-19 01:05:41

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

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

also connection objects can be used as context managers that automatically commit or rollback transactions. 11.13.7.3. on docs.python

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))
回复 原文
话少情深 2024-10-19 01:05:41

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

Python sqlite3 issues a BEGIN statement automatically before "INSERT" or "UPDATE". After that it automatically commits on any other command or db.close()

回复 原文
~没有更多了~

关于作者

淡莣

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文