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.
 |
 |
 |
Python Library Reference |  |
 |
 |
Release 2.5, documentation updated on 19th September, 2006.