python sqlite“开始交易"；和“提交"命令 [英] python sqlite "BEGIN TRANSACTION" and "COMMIT" commands

问题描述

如果我想通过 python 在我的数据库中启动一个事务，我是否必须像这样明确地执行 sql 命令BEGIN TRANSACTION":

If I want to start a transaction in my database through python do I have to execute the sql command 'BEGIN TRANSACTION' explicitly like this:

import sqlite3

conn = sqlite3.connect(db)
c = conn.cursor()

c.execute('BEGIN TRANSACTION;')
##... some updates on the database ...
conn.commit() ## or c.execute('COMMIT'). Are these two expressions the same?

当我建立连接或开始事务时，数据库是否被其他客户端锁定以防止更改?

Is the database locked for change from other clients when I establish the connection or when I begin the transaction or neither?

推荐答案

只有事务锁定数据库.

然而，Python 试图变得聪明并且自动开始交易:

However, Python tries to be clever and automatically begins transactions:

默认情况下，sqlite3 模块在数据修改语言 (DML) 语句(即 INSERT/UPDATE//>DELETE/REPLACE)，并在非 DML、非查询语句(即除 SELECT 或上述内容之外的任何内容)之前隐式提交事务.

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

因此，如果您在事务中并发出类似 CREATE TABLE ...、VACUUM、PRAGMA、 的命令sqlite3 模块将在执行该命令之前隐式提交.这样做有两个原因.首先是其中一些命令在事务中不起作用.另一个原因是 sqlite3 需要跟踪事务状态(如果事务处于活动状态).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

您可以通过 isolation_level 参数来控制 sqlite3 隐式执行哪种 BEGIN 语句(或根本不执行)connect() 调用，或通过连接的 isolation_level 属性.

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

如果您想要自动提交模式，请将 isolation_level 设置为 None.

If you want autocommit mode, then set isolation_level to None.

否则将其保留为默认值，这将产生一个简单的BEGIN"语句，或将其设置为 SQLite 支持的隔离级别之一:DEFERRED"、IMMEDIATE"或EXCLUSIVE".

Otherwise leave it at its default, which will result in a plain "BEGIN" statement, or set it to one of SQLite’s supported isolation levels: "DEFERRED", "IMMEDIATE" or "EXCLUSIVE".

这篇关于python sqlite“开始交易"；和“提交"命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！