def ddl_transaction(db: sqlite3.Connection) -> Iterator[sqlite3.Connection]:
"""Automatically commit/rollback transactions containing DDL statements.
Usage:
with ddl_transaction(db):
db.execute(...)
db.execute(...)
Note: ddl_transaction() does not work with executescript().
Normally, one would expect to be able to use DDL statements in a
transaction like so:
with db:
db.execute(ddl_statement)
db.execute(other_statement)
Initially, this worked around https://bugs.python.org/issue10740;
the sqlite3 transaction handling would trigger an implicit commit
if the first execute() was a DDL statement, which prevented it from
being rolled back if there was an exception after it.
This was fixed in Python 3.6, but there are still some cases that behave
in the same way, e.g.:
db = sqlite3.connect(':memory:')
try:
with db:
db.execute("create table t (a, b);")
1 / 0
except ZeroDivisionError:
pass
# table t exists even if it shouldn't
https://docs.python.org/3.5/library/sqlite3.html#controlling-transactions
"""
# initialy from https://github.com/lemon24/boomtime/blob/master/boomtime/db.py
isolation_level = db.isolation_level
try:
db.isolation_level = None
db.execute("BEGIN;")
yield db
db.execute("COMMIT;")
except Exception:
db.execute("ROLLBACK;")
raise
finally:
db.isolation_level = isolation_level
def _configure_db_connection(self, connection: sqlite3.Connection):
'''
Configure connection-level settings on the SQLite database.
'''
# set database-specific settings
connection.isolation_level = None # autocommit mode; transactions can be explicitly created with BEGIN/COMMIT statements
connection.row_factory = sqlite3.Row # return dictionaries instead of tuples from SELECT statements
def _onSqlite3Connect(dbapiConnection: sqlite3.Connection,
connectionRecord: sqlalchemy.pool._ConnectionRecord) -> None:
assert isinstance(dbapiConnection, sqlite3.Connection)
# Prevent pysqlite from emitting BEGIN and COMMIT statements.
dbapiConnection.isolation_level = None
# Enable foreign keys
with closing(dbapiConnection.cursor()) as cursor:
cursor.execute("PRAGMA foreign_keys=ON;")
cursor.execute("PRAGMA busy_timeout = 300000;") # in ms, so 5min (way longer than should be needed)
def __init__(self, native_connection: sqlite3.Connection):
"""
Constructor
:param native_connection: Native connection object
"""
Connection.__init__(self)
# Disable automatic transactions and save the connection object
native_connection.isolation_level = None
self.__native_connection = native_connection
self.__in_transaction = False
