I am using Apache mod_wsgi in a flask-sqlalchemy, marshamllow application, connecting to a remote ms sql database using pyodbc, recently I was asked to add isolation_level 'SNAPSHOT' and I did that using apply_driver_hacks
class SQLiteAlchemy(SQLAlchemy):
def apply_driver_hacks(self, app, info, options):
options.update({
'isolation_level': 'SNAPSHOT',
})
super(SQLiteAlchemy, self).apply_driver_hacks(app, info, options)
the project is built to access image blob data from a ms sql server and display on a webpage, soon after adding the isolation level I see internal error generated for every few images, doing a ctrl+f5 displays the image but then there are other images not being displayed and this is in the error log mod_wsgi (pid=10694): Exception occurred processing WSGI script pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Transaction failed in database 'testdb' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level. (3951) (SQLExecDirectW)")
edited to add code below:
how would I do that with flask-sqlalchmey when not using create-engine
my app.py file
app = Flask(__name__)
app.config.from_object('config.ProductionConfig')
db.init_app(app)
ma.init_app(app)
my model.py file
class SQLiteAlchemy(SQLAlchemy):
def apply_driver_hacks(self, app, info, options):
options.update({
'isolation_level': 'SNAPSHOT',
})
super(SQLiteAlchemy, self).apply_driver_hacks(app, info, options)
# To be initialized with the Flask app object in app.py.
db = SQLiteAlchemy()
ma = Marshmallow()
If you were using the declaritive implementation you would have access to the create engine function (and the scoped session one).
But assuming you're using the Flask-SQLAlchemy implementation, this just calls sqlalchemy.create_engine under the hood (on this line).
Might be a hack for the latter, as there doesn't seem to be a way to pass engine related options in; they are defined specifically a few lines up at #558:
options = {'convert_unicode': True}
This looks like it could be slightly easier, because you can pass session options when you initialise SQLAlchemy: see this line. The create_scoped_session method expects a dictionary which can be passed to the __init__ method as session_options.
So when you initialise the library you could try something like:
db = SQLiteAlchemy(session_options={'isolation_level': 'SNAPSHOT'})