13.13.5 Controlling Transactions

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/INSERT/UPDATE/DELETE/REPLACE).

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 pysqlite needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of "BEGIN" statements pysqlite implicitly executes (or none at all) via the isolation_level parameter to the connect call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain "BEGIN" statement, or set it to one of SQLite's supported isolation levels: DEFERRED, IMMEDIATE or EXCLUSIVE.

As the sqlite3 module needs to keep track of the transaction state, you should not use OR ROLLBACK or ON CONFLICT ROLLBACK in your SQL. Instead, catch the IntegrityError and call the rollback method of the connection yourself.

See About this document... for information on suggesting changes.