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.
Release 2.5.2, documentation updated on 21st February, 2008.
See About this document... for information on suggesting changes.
|