在python sqlite3模块中提交行为和原子性 [英] Commit behavior and atomicity in python sqlite3 module

问题描述

如果我想创建一个表并在另一个表中插入一个新条目，可以吗? 在sqlite模块中被原子化?

If I want to create a table and insert a new entry in another table, can this be made atomic in the sqlite module?

请参考 http://docs.python.org/2/library上的文档/sqlite3.html :

默认情况下，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).

我不确定这第二段是否应适用于自动启动 交易或手动和自动交易.

I'm not sure if this second paragraph is meant to apply to automatically started transactions or to both manual and automatic ones.

Sqlite文档 http://www.sqlite.org/lang_transaction.html 告诉我们，手动交易 直到明确的COMMIT才会提交:

Sqlite docs http://www.sqlite.org/lang_transaction.html tell us that manual transactions would not commit until an explicit COMMIT:

可以使用BEGIN命令手动启动事务.这样的 事务通常会持续到下一次COMMIT或ROLLBACK为止 命令.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command.

所以假设我们有这样的东西:

So suppose we have something like this:

con = sqlite3.connect(fdb) 
cur = con.cursor()

sql = 'begin transaciton'
cur.execute(sql)    

sql = 'CREATE TABLE some-table ...
cur.execute(sql)

# *** is there an implicit commit at this point ?! ***

sql = 'INSERT INTO  another-table ...
cur.execute(sql)

con.commit()

这是原子的，还是python sqlite在create table语句之后进行提交? 有没有办法使它原子化?

Would this be atomic, or would python sqlite make a commit after the create table statement? Is there a way to make it atomic?

推荐答案

Python SQLite3库在不需要的地方也插入自动提交.

The Python SQLite3 libary inserts automatic commits even where none are needed.

要使整个交易具有原子性，请使用任何其他Python SQLite包装器，例如 APSW .

To make your entire transaction atomic, use any other Python SQLite wrapper, such as, e.g., APSW.

这篇关于在python sqlite3模块中提交行为和原子性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！