How to disable caching correctly in Sqlalchemy orm

Question

I have I thread in a daemon, that loops and performs the following query:

    try:
        newsletter = self.session.query(models.Newsletter).\
               filter(models.Newsletter.status == 'PROCESSING').\
               limit(1).one()
    except sa.orm.exc.NoResultFound:
        self.logger.debug('No PROCESSING newsletters found. Sleeping...')
        self.sleep()
        return
    # (...) more code to do with found newsletter

Where the sleep method just stops the execution of this thread for the configured time and the return statement returns to the main loop. However I found, that if I change any newsletter's status to 'PROCESSING' while the daemon is running, nothing happens, ie. the query still raises NoResultFound. If I restart the daemon however, it will find the newsletter. So I see, that the results of this query must be cached. What can I do to invalidate the cache? session.expire_all() doesn't work. I could also create new Session() object every iteration, but don't know if it's a good approach regarding system resources.

Answer 1

SQLAlchemy doesn't cache on itself. Unless you explicitly implemented a cache, like this one.

Pass echo=True to your sessionmaker and look into the logging output.

Answer 2

Hmm I already found the answer, you apparently need to explicitly do session.commit() to get it to update, or you need to set autocommit=True on the session, through for example the sessionmaker.

sessionmaker(bind=self.engine, autocommit=True)

However I haven't tested the session.commit() way

So this isn't a caching issue it seems to be just the way transactions work

Answer 3

don't use autocommit=True and expire_on_commit=True

for state in self.identity_map.all_states():
    state.expire(state.dict, self.identity_map._modified)

you can: after Query :db.session.commit()

Answer 4

The problem in your code is due to database using REPEATABLE READ isolation level by default, so the query returns the same result unless you call commit() or rollback() (or use autocommit=True as Xeross suggested) or manually change isolation level.

Yes, SQLAlchemy does cache mapped objects (not query results!), because ORM pattern requires single object for each identity. By default SQLAlchemy uses weak identity map as cache, so object is automatically expunged from session when there is no references left to it. Note, that subsequent queries will update state of cached objects with new data, so no need to worry about this cache.