Triggers in Python/SQL [duplicate]

Question

I have created a python Code that is supposed to run on some data(to generate data points) as soon as the data is filled into the SQL database.

Is it possible to have a MySQL send a trigger event when a data is added into a table that can be detected by a python script so that action A be taken by the python script?

Appreciate any help on this. Thanks in advance!

Some more advanced DBMS allow procedural code to be written in other languages than their very own procedural extensions. For Python support Postgres and maybe SQL Server are worth a look at. You might still not be able to just call your script but bring it or large portions of it to the database and execute that there. Maybe that's something you want to research. — sticky bit
– sticky bit, Commented Apr 7, 2021 at 10:01

aneroid · Accepted Answer · 2021-04-07 09:59:43Z

1

Is it possible to have a MySQL send a trigger event when a data is added into a table that can be detected by a python script so that action A be taken by the python script?

No, not directly. Triggers call Stored Procedures, etc. which are written in SQL in that DB engine's specific syntax. They don't call external programs. I'm not aware of any Databases which do.

Wrt not-external programs, MySQL let's you create UDF's in C/C++ which can be used in triggers.

The only way to use your Python code as if it was a trigger is in the same code which executes the insert/update, etc. For example something like:

def insert_into_table(x, y, z):
    cursor.execute('INSERT INTO ...')
    exec_insert_trigger(cursor)

def exec_insert_trigger(cursor_obj):
    """your trigger code here"""
    cursor.execute(...)

Or you can try to convert your Python code to C using Cython and then use that in a MySQL trigger.

edited Apr 7, 2021 at 9:59

answered Apr 7, 2021 at 9:54

aneroid

16.7k3 gold badges42 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Triggers in Python/SQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related