UNTESTED - Real autocommit mode for Postgres/Flask-SQLAlchemy

autocommit.py

##################################################
# Real autocommit mode for psycopg2
# ie, does not auto-start a transacation.
# from http://oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/
# UNTESTED
##################################################
class AUTOCOMMITAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        if not "isolation_level" in options:
            options["isolation_level"] = "AUTOCOMMIT"  # For example
        return super(AUTOCOMMITAlchemy, self).apply_driver_hacks(app, info, options)

db = AUTOCOMMITAlchemy(session_options={'autocommit': True, 'autoflush': False})

dconns_by_trans = {}
options = {"autocommit":False}

db = SQLAlchemy()

@event.listens_for(db.session, 'after_begin')
def receive_after_begin(session, transaction, connection):
    """When a transaction begins, turn autocommit off."""
    dbapi_connection = connection.connection.connection
    if transaction.nested:
        assert not dbapi_connection.autocommit
        return
    assert dbapi_connection.autocommit
    dbapi_connection.autocommit = False
    dconns_by_trans.setdefault(transaction, set()).add(
        dbapi_connection)

@event.listens_for(db.session, 'after_transaction_end')
def receive_after_transaction_end(session, transaction):
    """Restore autocommit where this transaction turned it off."""
    if transaction in dconns_by_trans:
        for dbapi_connection in dconns_by_trans[transaction]:
            assert not dbapi_connection.autocommit
            dbapi_connection.autocommit = True
        del dconns_by_trans[transaction]