CODEWITHSUNDEEP
pythonsqlsql-serverpymssqlpython-db-api
michael g
michael g

Reputation: 103

How do I specify Transaction Isolation Level for MS SQL backend in Sql Alchemy Python

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

Upvotes: 0

Views: 3807

Answers (2)

craba
craba

Reputation: 207

I notice this is an old post, but wonder if the answer to this question is to make sure the right isolation level is switched on at the database level

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

https://learn.microsoft.com/en-au/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Once the isolation level is enabled at the database level then you can set the session isolation level.

Hope that helps.

Upvotes: 0

Mahdi
Mahdi

Reputation: 1852

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)

Upvotes: 4

Related Questions