How to disable SQLAlchemy caching?

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)

I have tried session.commit(), session.flush() none worked for me.

After going through sqlalchemy source code, I found the solution to disable caching.
Setting query_cache_size=0 in create_engine worked.

create_engine(connection_string, convert_unicode=True, echo=True, query_cache_size=0)

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

This was happening in my Flask application, and my solution was to expire all objects in the session after every request.

from flask.signals import request_finished
def expire_session(sender, response, **extra):
    app.db.session.expire_all()
request_finished.connect(expire_session, flask_app)

Worked like a charm.

One thing that might be relevant for others is when you are creating the session and engine is to pass expire_on_commit=True into the sessionmaker call.

sessionmaker(bind=engine, expire_on_commit=True)

I was experiencing something where a sqlalchemy relationship was not begin refreshed after creation and committing (since in my relationship I define an order_by function, but my secondary objects were not in the expected order, and this fixed the issue.

First, there is no cache for SQLAlchemy. Based on your method to fetch data from DB, you should do some test after database is updated by others, see whether you can get new data.

(1) use connection:
connection = engine.connect()
result = connection.execute("select username from users")
for row in result:
    print "username:", row['username']
connection.close()
(2) use Engine ...
(3) use MegaData...

please folowing the step in : http://docs.sqlalchemy.org/en/latest/core/connections.html

Another possible reason is your MySQL DB is not updated permanently. Restart MySQL service and have a check.

import sqlalchemy as db
db.create_engine(db_string, query_cache_size=0, pool_size=30, max_overflow=0, pool_pre_ping=True, echo=True, echo_pool=True)

we can solve this in sqlalchemy version greater than 1.3.13, below this version it doesn't support disabling statement cache.