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?

samplebias · Accepted Answer · 2011-04-16 02:11:01Z

17

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',),)

answered Apr 16, 2011 at 2:11

samplebias

38k6 gold badges110 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jodonnell Over a year ago

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!

Rockallite Over a year ago

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

Ruli · Accepted Answer · 2020-12-08 08:45:35Z

0

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

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

edited Dec 8, 2020 at 8:45

Ruli

2,83113 gold badges36 silver badges48 bronze badges

answered Dec 8, 2020 at 0:19

BalaramPradhan

1

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related