sqlAlchemy does not recognise changes to DB made outside of session

Question

Something peculiar I've noticed is that any changes committed to the DB outside of the session (such as ones made in MySQL's Workbench) are not recognised in the sqlAlchemy session. I have to close and open a new session for sqlAlchemy to recognise it.

For example, a row I deleted manually is still fetched from sqlAlchemy.

This is how I initialise the session:

engine   = create_engine('mysql://{}:{}@{}/{}'.format(username, password, host, schema), pool_recycle=3600)
Session  = sessionmaker(bind=engine)
session  = Session()
metadata = MetaData()

How can I get sqlAlchemy to recognise them?

My sqlAlchemy version is 0.9.4 and my MySQL version is 5.5.34. We use only sqlAlchemy's Core (no ORM).

Answer 1

To be able to read committed data from others transactions you'll need to set transaction isolation level to READ COMMITTED. For sqlalchemy and mysql:

To set isolation level using create_engine():

engine = create_engine(
    "mysql://scott:tiger@localhost/test",
    isolation_level="READ COMMITTED")

To set using per-connection execution options:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="READ COMMITTED")

source