Fix behavior and atomicity in python sqlite3 module

Question

If I want to create a table and insert a new record in another table, to be made an atom in the sqlite module?

Link to docs at 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 non-DML, without a query (for example, nothing but SELECT or higher).

So, if you are in a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will execute implicitly before executing this command. There are two reasons for this. Firstly, some of these teams do not work inside the deal. Another reason is that sqlite3 needs to monitor the status of the transaction (if the transaction is active or not).

I am not sure if this second paragraph is intended to start transactions automatically, either manually or automatically.

Sqlite docs http://www.sqlite.org/lang_transaction.html tell us that manual transactions will not be committed until explicit COMMIT:

Transactions can be started manually using the BEGIN command. such transactions are usually saved 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() 

Will it be atomic, or will sqlite python commit after the create table statement? Is there any way to make it atomic?

Answer 1

Python SQLite3 libary inserts automatic commits, even if they are not needed.

To make an entire transaction atomic, use any other SQLite wrapper for Python, such as APSW .

Answer 2

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

You can verify this by opening the database in both the python interpreter and the sqlite3 command line sqlite3 . Once you issue the CREATE TABLE .. statement, you can run the .schema command in the .schema command-line sqlite3 and see the result of this statement.

Note that this means that everything you did in the transaction before the CREATE TABLE .. statement was also executed. To view it differently, the CREATE TABLE .. statement first completes the transaction, then starts a completely new transaction.