Run python script on Database event

Thanks for your answers, i found a solution here:

http://crazytechthoughts.blogspot.fr/2011/12/call-external-program-from-mysql.html

A Trigger must be defined to call an external function once the DB Table is modified:

DELIMITER $
CREATE TRIGGER Test_Trigger
AFTER INSERT ON SFCRoutingTable
FOR EACH ROW
BEGIN
DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd = CONCAT('python /home/triggers.py');
SET result = sys_exec(cmd);
END;
$
DELIMITER ;

Here, to call my python script, I use 'sys_exec' which is a UDF (User Defined Function). You can download the library from here: https://github.com/mysqludf/lib_mysqludf_sys

You can use 'Stored Procedures' in your database a lot of RDBMS engines support one or multiple programming languages to do so. AFAIK postgresql support signals to call external process to. Google something like 'Stored Procedures in Python for PostgreSQL' or 'postgresql trigger call external program'

And if what you need is to keep the python script running and listen to changes in a certain table.

1. You can create a listener table ex. 'trigger_table' in your database with only one value.

2. Create a trigger that will change the value in the 'trigger_table' table every time a change occurs in some table.

3. And last create a python script that will check this table if the value has been changed every n-seconds (depending how fast you need updates) taking into consideration that its only one value and you have a good internet connection (If the database is online), everything should be running quite fast. And execute a function when the value has been changed.

Create MqSQL trigger: https://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

This is the SQL Code (Btw I am working with MS SQL but you should get the idea about how it should be set-up):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.new 
   ON  dbo.rc 
   AFTER INSERT,DELETE,UPDATE
AS 

--Declare the variable and set the value from the change_table
DECLARE @PurchaseName AS CHAR(1)
SELECT @PurchaseName = _check
FROM dev.dbo.change_table
WHERE _check_1 IS NOT NULL

IF @PurchaseName = 'Y'
BEGIN
  -- If the condition is TRUE then execute the following statement
  UPDATE dev.dbo.change_table SET _check = 'N' WHERE _check_1 IS NOT NULL
END
ELSE
BEGIN
   -- If the condition is False then execute the following statement
    UPDATE dev.dbo.change_table SET _check = 'Y' WHERE _check_1 IS NOT NULL
END
GO

The change_table has only two columns and one row- see the picture below.

change_table picture

This is the python code:

import pyodbc
import pandas as pd

# Connect to SQL Server
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=<YOUR SERVER>;DATABASE=<YOUR DATABASE>;UID=<YOUR USER ID>;PWD=<YOUR PASSWORD>')
previous_value = ""
while True:
    current_value = str(pd.read_sql_query('SELECT _check FROM change_table',cnxn)['_check'].tolist()[0])
    if current_value != previous_value:
        prev_value = current_value
        #Write your code here

The SQL code above will change the value in the change_table on every INSERT, UPDATE or DELETE in the 'rc' table, while the python code will check the value 8 times every second to see if a change has happen. Btw that 8 times per second is on a 100MB/s optic internet and the server is on the other side of the Earth, sooo... it should do the job.