Change isolation level in records query or close transaction

Question

I'm trying to build some autotest scripts using Kenneth Reitz's records library. I need to check the MySQL database for updates here and there.

db_url_mysql = 'mysql://user:[email protected]:3306/DB'
db = records.Database(db_url_mysql)
rows = db.query("SELECT * FROM Users").all()

However, I encountered that sometimes simple queries return an empty dataset, ignoring updates in tables. I could totally see the updates in GUI clients or even using records in alternative python console. I found that records use SQLAlchemy with default isolation level "REPEATABLE READ". Repeatable reads ignore all updates until an end of the transaction, so I could not see them.

Is there any way I can change that? I probably need to close a transaction and open a new one, or perhaps change isolation level for this connection, but how can I do it?

Answer 1

Now I can answer to my question.

With the release of records 0.5.0 (Novemmber 2016) there is now a possibility to pass arguments to SQLAlchemy driver. There was no such possibility in 0.4.3 which I used.

So, to set up isolation level on connection we should use

db_url_mysql = 'mysql://user:[email protected]:3306/DB'
db = records.Database(db_url_mysql,isolation_level="READ_UNCOMMITTED")