python: how to get notifications for mysql database changes?

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.

Answer #2:

If by changed you mean if a row has been updated, deleted or inserted then there is a workaround.

You can create a trigger in MySQL

DELIMITER $$

CREATE TRIGGER ai_tablename_each AFTER INSERT ON tablename FOR EACH ROW
BEGIN
  DECLARE exec_result integer;

  SET exec_result = sys_exec(CONCAT('my_cmd '
                                    ,'insert on table tablename '
                                    ,',id=',new.id));
  IF exec_result = 0 THEN BEGIN
    INSERT INTO table_external_result (id, tablename, result) 
      VALUES (null, 'tablename', 0)
  END; END IF;
END$$

DELIMITER ;

This will call executable script my_cmd on the server. (see sys_exec fro more info) with some parameters.

my_cmd can be a Python program or anything you can execute from the commandline using the user account that MySQL uses.

You’d have to create a trigger for every change (INSERT/UPDATE/DELETE) that you’d want your program to be notified of, and for each table.
Also you’d need to find some way of linking your running Python program to the command-line util that you call via sys_exec().

Not recommended
This sort of behaviour is not recommend because it is likely to:

1. slow MySQL down;
2. make it hang/timeout if my_cmd does not return;
3. if you are using transaction, you will be notified before the transaction ends;
4. I’m not sure if you’ll get notified of a delete if the transaction rolls back;
5. It’s an ugly design

Links
sys_exec: http://www.mysqludf.org/lib_mysqludf_sys/index.php

Answer #3:

Yes, may not be SQL standard. But PostgreSQL supports this with LISTEN and NOTIFY since around Version 9.x

http://www.postgresql.org/docs/9.0/static/sql-notify.html

Answer #4:

Not possible with standard SQL functionality.

Answer #5:

It might not be a bad idea to try using a network monitor instead of a MySQL trigger. Extending a network monitor like this:

http://sourceforge.net/projects/pynetmontool/

And then writing a script that waits for activity on port 3306 (or whatever port your MySQL server listens on), and then checks the database when the network activity meets certain filter conditions.

It’s a very high level idea that you’ll have to research further, but you don’t run into the DB trigger problems and you won’t have to write a cron job that runs every second.