changing isolation level

[ssfking]

How do you change the isolation level of a session/transaction without going down to the sqlalchemy layer?

[fjbsantiago]

I don't think it is possible yet. This was the simplest way I found to do it.

from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

From: http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl

[cript0nauta]

Hi! I wrote a custom SQLAlchemy class to do this, since create_engine is called inside Flask-SQLAlchemy code. Maybe someone could find this useful:

from flask_sqlalchemy import (
    SQLAlchemy as OriginalSQLAlchemy,
    _EngineConnector
)

class SQLAlchemy(OriginalSQLAlchemy):
    """Override to fix issues when doing a rollback with sqlite driver
    See http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#serializable-isolation-savepoints-transactional-ddl
    and https://bitbucket.org/zzzeek/sqlalchemy/issues/3561/sqlite-nested-transactions-fail-with
    for furhter information"""

    # TODO: only do this on sqlite, not on postgres nor other RDBMS!

    def make_connector(self, app=None, bind=None):
        """Creates the connector for a given state and bind."""
        return CustomEngineConnector(self, self.get_app(app), bind)


class CustomEngineConnector(_EngineConnector):
    """Used by overrideb SQLAlchemy class to fix rollback issues"""

    def get_engine(self):
        # Use an existent engine and don't register events if possible
        uri = self.get_uri()
        echo = self._app.config['SQLALCHEMY_ECHO']
        if (uri, echo) == self._connected_for:
            return self._engine

        # Call original metohd and register events
        rv = super(CustomEngineConnector, self).get_engine()
        with self._lock:
            @event.listens_for(rv, "connect")
            def do_connect(dbapi_connection, connection_record):
                # disable pysqlite's emitting of the BEGIN statement entirely.
                # also stops it from emitting COMMIT before any DDL.
                dbapi_connection.isolation_level = None

            @event.listens_for(rv, "begin")
            def do_begin(conn):
                # emit our own BEGIN
                conn.execute("BEGIN")
        return rv


db = SQLAlchemy()

[DenisKuplyakov]

Are there any progress on this? We were surprissed by postgres default of "READ COMMITED" instead of "READ REPEATED" today, but it looks there is no way to pass parameter.

[brodul]

There is a PR open that will allow passing engine options to the constructor. #487

For now there is a simple clean workaround:

from flask_sqlalchemy import SQLAlchemy


class SQLiteAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        options.update({
            'isolation_level': 'READ COMMITTED',
        })
        super(SQLiteAlchemy, self).apply_driver_hacks(app, info, options)


db = SQLiteAlchemy(app)

[rsyring]

The ability to customize the session will be taken care of by #166.