How to create an SQL function that calls a script in python?

Asked

Viewed 365 times

0

I need to create a software that at each insertion in a table a python script detects that there has been a change in this table and execute certain commands. I have done an implementation that is all the time testing if there has been a change in the table, but it forces me to make a while Truethat spins in the background forever. I did so:

import urllib
import urllib2
import webbrowser
import popular3
while True:


    tabela    = popular3.Banco()
    if (tabela.existeMudancaNaTabela()):
        #Faz alguns comandos
        pass




    print "Esperando Mudança no Banco..."

I need to create a function in SQL itself which detects that there has been an insertion of data in a table and, soon after, calls the execution of a sricpt . py or a . exe or any other. Is that even possible?

php sql python database .exe
  • 1

    Search on Trigger for database, can help you

  • Which database you are using?

  • I use Mysql.

  • https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

  • Is it possible to make a Trigger call an external python script? From what I researched it only does internal actions with sql command.

  • probably not: https://stackoverflow.com/questions/1467369/invoking-a-php-script-from-a-mysql-trigger

  • Vlw, I think we can do with UDF library.

  • If your server is Linux, you can create a Cron to run your scripts. In windows also has something similar, is the Task Scheduler, just configure it and it runs something from time to time.

  • Switch to Postgresql and use PL/Python to develop the Trigger function.

Show 4 more comments

1 answer

1


If your database is Postgresql

you can put a PL/Python function right inside the database - the documentation is here: https://www.postgresql.org/docs/9.0/static/plpython.html

I don’t know if Python inside postgresql will have limitations (like filesystem access, importing third-party modules that don’t have to do with postgres, etc...) but it probably has no limitations: it is very difficult to create effective limitations of this kind in Python - and to have a "appearance" of security, it is better to document that functions in Python can do everything, and increase the range of choices.

If the system you insert into the database is in Python

That brings us to something else: if the system is inserting in the for database in Python, then the best Python Orm (like Sqlalchemy) has an event system - you would harvest your Python code as an Handler for Sqlalchemy event.

If it is neither Postgresql nor Python insertion:

You will need to send a signal out of the bank somehow from scripts used as Trigger within the bank in other languages. This would require a lot of specific configuration, and could be problematic when the database is on a separate machine from your Python script (Let’s assume that you put a Rigger to make a socket call, which activates your Python script - when it’s in production and the network gets to the bank set up securely, it would be a headache to have this call from the database back to your server where the Python script is).

So, the idea of having a Python script that actively looks at the database at each short interval of time is not bad at all. What you can do instead of doing a long query that looks at the entire table is:

  • create a simple table, just to tell if there has been change or not - could be a table with a únic align and a single column type "bool(changed)"
  • Place a Rigger in the bank to set an "altered=true" in that table each time one of the changes you are interested in happening.
  • Put your script looking at the database every 1 or 2 seconds, checking this simple table - and only do more extensive queries if there has been a change

Browser other questions tagged php sql python database .exe

You are not signed in. Login or sign up in order to post.