python: how to get notifications for mysql database changes?

Question

In Python, is there a way to get notified that a specific table in a MySQL database has changed?

Answer 1

It's theoretically possible but I wouldn't recommend it:

Essentially you have a trigger on the the table the calls a UDF which communicates with your Python app in some way.

Pitfalls include what happens if there's an error?

What if it blocks? Anything that happens inside a trigger should ideally be near-instant.

What if it's inside a transaction that gets rolled back?

I'm sure there are many other problems that I haven't thought of as well.

A better way if possible is to have your data access layer notify the rest of your app. If you're looking for when a program outside your control modifies the database, then you may be out of luck.

Another way that's less ideal but imo better than calling an another program from within a trigger is to set some kind of "LastModified" table that gets updated by triggers with triggers. Then in your app just check whether that datetime is greater than when you last checked.