Sqlite3ConnectionManager(driver, pragmas, path, options)¶
SQLite doesn’t really have isolation levels in the traditional sense; as far as that goes, it always operates in SERIALIZABLE mode. Instead, the connection’s
isolation_levelparameter determines how autocommit behaves and the interaction between it at the SQLite and Python levels:
- If it is ``None``, then the Python Connection object has nothing to do with transactions. SQLite operates in its default autocommit mode, beginning and ending a (read or write, as needed) transaction around every statement execution. - If it is set to IMMEDIATE or DEFERRED, than the Python Connection object watches each statement sent to ``Cursor.execute`` or ``Cursor.executemany`` to see if it's a DML statement (INSERT/UPDATE/DELETE/REPLACE, and prior to Python 3.6, basically anything else except a SELECT). If it is, and the sqlite level is not already in a transaction, then the Connection begins a transaction of the specified type before executing the statement. The Connection COMMITs when commit() is called or when a DDL statement is executed (prior to Python 3.6). For SELECT statements, the connection remains in its current state, which would be autocommit (read committed) unless a transaction has been opened.
Now, those are all write statements that theoretically would begin a write transaction and take a database lock, so it would seem like there’s no difference between IMMEDIATE and DEFERRED. But there is: recall that temporary tables are actually in a temporary database, so when you write to one of those, sqlite’s internal transaction locks only apply to it. A DEFERRED transaction thus allows other connections to write to their own temporary tables, while an IMMEDIATE transaction blocks other from writing to their temporaries.
We thus tell Python SQLite to operate in DEFERRED mode; for load connections, we must explicitly execute the BEGIN to get a consistent snapshot of the database, but for store, we can let the Python Connection detect when we execute a write operation and begin the transaction then, letting SQLite upgrade the locks to explicit mode when we attempt a write to the main database.
Taking an exclusive lock on the main database can be accomplished with any UPDATE statement, even one that doesn’t match any actual rows.
pragmas (dict) – A map from string pragma name to string pragma value. These will be executed at connection open time. Except for WAL, and a few other critical values, we allow changing just about all the settings, letting the user turn off just about all the safety features so that the user can tune to their liking. The user can also set the
max_page_countto operate as a quota system.
open(isolation=None, read_only=False, deferrable=False, replica_selector=None, application_name=None, **kwargs)¶
Open a database connection and return (conn, cursor).
Open a connection to be used for the pre-pack phase. Returns (conn, cursor).
restart_load(conn, cursor, needs_rollback=True)¶
Reinitialize a connection for loading objects.