sqlalchemy session not recognizing changes in mysql database (done by other processes)

Question

Application consists of:

• main process (python+sqlalchemy) that periodically check db (sleeps most of the time)
• child processes that write to db
• web app that write to db

Problem is that the main process session doesn't seem to register changes in the db done outside that session. How do ensure it does? (as of now I am closing and reopening the session every time the process awakes and does its check).

Answer 1

I am closing and reopening the session every time the process awakes and does its check

SQLAlchemy will not work like this. Changes are tracked in the session.

someobj = Session.query(SomeClass).first()

puts someobj into Session internal cache. When you do someobj.attr = val, it marks the change in the Session associated with someobj.

So if you pulled object1 from some session1, then closed session1, object1 is not associated with any session anymore and is not tracked.

Best solution would be to commit right upon the change:

object1 = newsession.add(object1)
newsession.commit()

Otherwise you will have to manage own objects cache and merge all of them upon each check.