If I want to create a table and insert a new entry in another table, can this
be made atomic in the sqlite module?
Refering to the 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 a non-DML, non-query statement (i. e. anything other than
SELECT or the aforementioned).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 docs http://www.sqlite.org/lang_transaction.html tell us that manual transactions
would not commit until an explicit COMMIT:
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()
Would this be atomic, or would python sqlite make a commit after the create table
statement?
Is there a way to make it atomic?
Best Answer
You cannot do this atomically. The Python SQLite library implicitly issues a
COMMIT
whenever you execute aCREATE TABLE ..
statement, because SQLite does not support executing theCREATE TABLE ..
statement while a transaction is active.You can test this by opening the database in both the python interpreter and the
sqlite3
command line tool. As soon as you issue theCREATE TABLE ..
statement, you can run a.schema
command in thesqlite3
command line tool and see the result of that statement.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, theCREATE TABLE ..
statement first commits, then starts a completely new transaction.