How do I set transaction level READ UNCOMMITED for all queries done through a SQL Alchemy engine object?
I set the isolation_level argument as notated here: http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine.params.isolation_level
by passing it into create_engine like so:
my_eng = create_engine(db_conn_string, isolation_level='READ_UNCOMMITTED')
but for my backend (MS SQL Server) I get the following error, perhaps unsurprisingly as the docs do say it is dialect specific. I'm just surprised theres no documentation for isolation levels in MS SQL!
TypeError: Invalid argument(s) 'isolation_level' sent to create_engine(),
using configuration MSDialect_pyodbc/QueuePool/Engine. Please check that
the keyword arguments are appropriate for this combination of components.
Furthermore, I do not see anything helpful in the dialects docs here:
http://docs.sqlalchemy.org/en/rel_1_0/dialects/mssql.html#dsn-connections
This is only available in the beta (pre-release) version of SQL Alchemy (currently at 1.1.0b2). Otherwise, the current release (1.0.14) does not have this feature. If you really want to use this feature, you can install the pre-release version like this:
pip install --pre --upgrade sqlalchemy
Source: http://docs.sqlalchemy.org/en/latest/intro.html#install-via-pip
Alternative solution is to issue the following SQL command directly:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Source: https://msdn.microsoft.com/en-us/library/ms173763.aspx
One way to do the latter in SQL Alchemy is:
query = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'
session.connection().connection.execute(query)