How do you change the SQL isolation level from Python using MySQLdb?

Question

The documentation I've run across researching this indicates that the way to do it for other databases is to use multiple statements in your query, a la:

>>> cursor = connection.cursor()
>>> cursor.execute("set session transaction isolation level read uncommitted; 
                    select stuff from table; 
                    set session transaction isolation level repeatable read;")

Unfortunately, doing that yields no results, as apparently the Python DB API (or maybe just this implementation of it?) doesn't support multiple recordsets within a single query.

Has anyone else had success with this in the past?

Guttering

Answer 1

I don't think this works for the MySQLdb driver; you'll have to issue separate queries:

cur = conn.cursor()
cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
cur.execute("SELECT @@session.tx_isolation")
print cur.fetchall()[0]
cur.execute("SELECT * FROM bar")
print cur.fetchall()
cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ")
cur.execute("SELECT @@session.tx_isolation")
print cur.fetchall()[0]

# output
('READ-UNCOMMITTED',)
(('foo',), ('bar',))
('REPEATABLE-READ',)

The MySQLdb cursor's execute() method only sees the first query up to the semicolon:

cur.execute("SELECT * FROM bar WHERE thing = 'bar'; SELECT * FROM bar")
print cur.fetchall()

# output
(('bar',),)

Kioto

That's exactly what I needed. Weird, I thought I had tried that before, but apparently I got my signals crossed somewhere. Thanks for your help! – Guttering

For MySQL 8.0, it should be cur.execute("SELECT @@session.transaction_isolation"). – Cornerwise

Answer 2

cur.executemany("SELECT * FROM bar WHERE thing = 'bar'; SELECT * FROM bar")
print cur.fetchall()

use cur.executemany to run multiple sql statements with ; separated.

Pileous