How to change SQL isolation level from Python using MySQLdb?

Question

The documentation that I conducted during the research indicates that the way to do this for other databases is to use multiple queries 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, this does not give any results, because, apparently, the Python DB API (or maybe only its implementation) does not support multiple recordsets in a single query.

Has anyone else been successful in the past?

Answer 1

I do not think this works for the MySQLdb driver; You will have to issue separate requests:

 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 execute () method of the MySQLdb cursordd method sees only the first query before the semicolon:

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