9

I have a table (MySQL) with a row in it.

I can read it fine with: 

self._session.query(Automatic).\
             filter(Automatic.do_when <= time()).\
             limit(limit).\
             all()

However, if I then delete the row from table (with the mysql client or phpMyAdmin), the row is still returned by the code above. I don't know if this is related to the question "How to disable SQLAlchemy caching?".

Edit: Adding a

self._session.commit()

after makes no difference.

Community
  • 1
  • 1
Prof. Falken
  • 24,226
  • 19
  • 100
  • 173
  • 3
    Yes, it is indeed related to the question to which you have linked; as the top-rated (and accepted) answer states, you need to `session.commit()` your transaction. – eggyal May 16 '13 at 11:31
  • @eggyal, so even a read operation is a transaction? I put a session.commit() after the code above. No cigar. – Prof. Falken May 16 '13 at 11:33
  • 1
    No, you need to commit the delete operation; as that answer also explains, any other transactions which began before the commit operation will still see the unmodified data due to MySQL's (default) consistent read isolation level. – eggyal May 16 '13 at 11:37
  • But phpmyadmin has autocommit right? @eggyal I also run the statement again *well* after the row was deleted in phpmyadmin. (Many seconds.) – Prof. Falken May 16 '13 at 11:39
  • 5
    Sorry, I missed that you were performing the `DELETE` from outside of sqlalchemy; yes, it will be committed. Your problem lies in the fact that the read operation is occurring within a transaction that began before the `DELETE` operation was committed and (due to the isolation level) cannot see the changes that have occurred since. You could, for example, commit before the read operation in order to begin a fresh transaction upon a then-current snapshot. – eggyal May 16 '13 at 11:42
  • @eggyal, so commit *before* reading? – Prof. Falken May 16 '13 at 11:43

1 Answers1

17

Edit: Adding commit() before reading did the trick, as per eggyal's explanation.

self._session.commit()
self._session.query(Automatic).\
             filter(Automatic.do_when <= time()).\
             limit(limit).\
             all()
Community
  • 1
  • 1
Prof. Falken
  • 24,226
  • 19
  • 100
  • 173
  • 2
    Worked perfectly for me! – Pitto Jan 21 '16 at 03:22
  • 3
    This also worked for me, though I don't know if I should rejoice or cry. – minexew Aug 14 '17 at 18:12
  • 2
    Worked! It's really wired that you need to do this... I migrate my db from postgres to mysql and encountered this issue. Previously in postgres you didn't need to do that... – Shagru Jun 11 '18 at 09:00