How to avoid caching in sqlalchemy?

Question

I have a problem with SQL Alchemy - my app works as a constantly working python application.

I have function like this:

def myFunction(self, param1):
   s = select([statsModel.c.STA_ID, statsModel.c.STA_DATE)])\
                        .select_from(statsModel)

   statsResult = self.connection.execute(s).fetchall()

   return {'result': statsResult, 'calculation': param1}

I think this is clear example - one result set is fetched from database, second is just passed as argument.

The problem is that when I change data in my database, this function still returns data like nothing was changed. When I change data in input parameter, returned parameter "calculation" has proper value.

When I restart the app server, situation comes back to normal - new data are fetched from MySQL.

I know that there were several questions about SQLAlchemy caching like:

How to disable caching correctly in Sqlalchemy orm session?

How to disable SQLAlchemy caching?

but how other can I call this situation? It seems SQLAlchemy keeps the data fetched before and does not perform new queries until application restart. How can I avoid such behavior?

How are you changing the data in the database ? Is that changed committed ? — icecrime, Aug 24 '12 at 11:56
I change it manualy. The database engine is MySQL. Yes, the change is committed. — Archarius, Aug 24 '12 at 12:04

score 20 · Answer 1 · edited May 03 '18 at 10:30

Calling session.expire_all() will evict all database-loaded data from the session. Any access of object attributes subsequent emits a new SELECT statement and gets new data back. Please see http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#refreshing-expiring for background.

If you still see so-called "caching" after calling expire_all(), then you need to close out transactions as described in my answer linked above.

score 2 · Answer 2 · edited May 23 '17 at 10:30

A few possibilities.

You are reusing your session improperly or at improper time. Best practice is to throw away your session after you commit, and get a new one at the last possible moment before you use it. The behavior that appears to be caching may in fact be due to a session lifetime being very long in your application.
Objects that survive longer than the session are not being merged into a subsequent session. The "metadata" may not be able to update their state if you do not merge them back in. This is more a concern for the ORM API of SQLAlchemy, which you do not appear so far to be using.
Your changes are not committed. You say they are so we'll assume this is not it, but if none of the other avenues explain it you may want to look again.

One general debugging tip: if you want to know exactly what SQLAlchemy is doing in the database, pass echo=True to the create_engine function. The engine will print all queries it runs.

Also check out this suggestion I made to someone else, who was using ORM and had transactionality problems, which resolved their issue without ever pinpointing it. Maybe it will help you.

score 1 · Answer 3 · answered May 27 '18 at 06:06

1

You need to change transaction isolation level to READ_COMMITTED http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#mysql-isolation-level

answered May 27 '18 at 06:06

E.Big

723
6
15

How to avoid caching in sqlalchemy?

3 Answers3

Linked