Commit behavior and atomicity in python sqlite3 module

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.

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.

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.

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

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