Beispiel #1
0
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
Beispiel #2
0
    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
Beispiel #3
0
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)
Beispiel #4
0
    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