How to disable caching correctly in Sqlalchemy orm session?

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

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.

Answer 2

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.