在python sqlite3模块中提交行为和原子性

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

如果我想创建一个表并在另一个表中插入一个新条目，这可以在sqlite模块中成为原子吗？

Refering to the docs at http://docs.python.org/2/library/sqlite3.html:

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

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

默认情况下，sqlite3模块在数据修改语言（DML）语句（即INSERT / UPDATE / DELETE / REPLACE）之前隐式打开事务，并在非DML非查询语句之前隐式提交事务（即SELECT或之前所提）。

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

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

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

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

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

Sqlite docs http://www.sqlite.org/lang_transaction.html告诉我们手动事务在显式COMMIT之前不会提交：

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

可以使用BEGIN命令手动启动事务。此类事务通常会持续到下一个COMMIT或ROLLBACK命令。

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()

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 sqlite会在create table语句之后进行提交？有没有办法让它成为原子？

2 个解决方案

#1

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

即使在不需要的情况下，Python SQLite3库也会插入自动提交。

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

要使整个事务成为原子，请使用任何其他Python SQLite包装器，例如APSW。

#2

You cannot do this atomically. The Python SQLite library implicitly issues a COMMIT whenever you execute a CREATE TABLE .. statement, because SQLite does not support executing the CREATE TABLE .. statement while a transaction is active.

你不能原子地这样做。每当执行CREATE TABLE ..语句时，Python SQLite库都会隐式发出COMMIT，因为SQLite不支持在事务处于活动状态时执行CREATE TABLE ..语句。

You can test this by opening the database in both the python interpreter and the sqlite3 command line tool. As soon as you issue the CREATE TABLE .. statement, you can run a .schema command in the sqlite3 command line tool and see the result of that statement.

您可以通过在python解释器和sqlite3命令行工具中打开数据库来测试它。一旦发出CREATE TABLE ..语句，就可以在sqlite3命令行工具中运行.schema命令并查看该语句的结果。

Note that this means that anything you did in the transaction before the CREATE TABLE .. statement will also have been committed. To look it in another way, the CREATE TABLE .. statement first commits, then starts a completely new transaction.

请注意，这意味着您在CREATE TABLE ..语句之前在事务中执行的任何操作也将被提交。要以另一种方式查看它，CREATE TABLE ..语句首先提交，然后启动一个全新的事务。

#1