How to disable SQLAlchemy caching?

The usual cause for people thinking there's a "cache" at play, besides the usual SQLAlchemy identity map which is local to a transaction, is that they are observing the effects of transaction isolation. SQLAlchemy's session works by default in a transactional mode, meaning it waits until session.commit() is called in order to persist data to the database. During this time, other transactions in progress elsewhere will not see this data.

However, due to the isolated nature of transactions, there's an extra twist. Those other transactions in progress will not only not see your transaction's data until it is committed, they also can't see it in some cases until they are committed or rolled back also (which is the same effect your close() is having here). A transaction with an average degree of isolation will hold onto the state that it has loaded thus far, and keep giving you that same state local to the transaction even though the real data has changed - this is called repeatable reads in transaction isolation parlance.

http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

This issue has been really frustrating for me, but I have finally figured it out.

I have a Flask/SQLAlchemy Application running alongside an older PHP site. The PHP site would write to the database and SQLAlchemy would not be aware of any changes.

I tried the sessionmaker setting autoflush=True unsuccessfully I tried db_session.flush(), db_session.expire_all(), and db_session.commit() before querying and NONE worked. Still showed stale data.

Finally I came across this section of the SQLAlchemy docs: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#transaction-isolation-level

Setting the isolation_level worked great. Now my Flask app is "talking" to the PHP app. Here's the code:

engine = create_engine(
    "postgresql+pg8000://scott:tiger@localhost/test",
    isolation_level="READ UNCOMMITTED"
)

When the SQLAlchemy engine is started with the "READ UNCOMMITED" isolation_level it will perform "dirty reads" which means it will read uncommited changes directly from the database.

Hope this helps

---

Here is a possible solution courtesy of AaronD in the comments

from flask.ext.sqlalchemy import SQLAlchemy

class UnlockedAlchemy(SQLAlchemy):
    def apply_driver_hacks(self, app, info, options):
        if "isolation_level" not in options:
            options["isolation_level"] = "READ COMMITTED"
    return super(UnlockedAlchemy, self).apply_driver_hacks(app, info, options)

Additionally to zzzeek excellent answer,

I had a similar issue. I solved the problem by using short living sessions.

with closing(new_session()) as sess:
    # do your stuff

I used a fresh session per task, task group or request (in case of web app). That solved the "caching" problem for me.

This material was very useful for me:

When do I construct a Session, when do I commit it, and when do I close it