Launching a python script from an insert trigger

Question

We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to have the python script fire in real time, so there is no polling delay. (This serves sales people who are on the phone with customers.)

We really do not want a 1 minute polling loop. Or 30 seconds. We want the record to show in the db and for things to happen right away.

The fast way to make this fly is to have it fire when a specific record type is inserted into a table.

Can we fire a python script from a trigger?

Per Aaron's note below, we know that this is a Very Bad Thing™, but this table gets very very little use (0-12 inserts a day). Polling the table fails to meet our business need (we need the .py to run immediately -- it does much more than send an email).

We believe a way to meet our business need is to set up the .net version of python on the SQL Server, and then have T-SQL call the python script the way it calls C# stuff... but we have no idea how to actually do this! (ergo this question).

Docs/details?

---

I asked a follow-up question on Stack Overflow: How do I create a Python CLR procedure in SQL Server?

---

The question under the question: You have a piece of python. You want it to fire from a SQL trigger, but you know that is a Very Bad Thing. So what do you do to actually accomplish the same effect without having python code in the middle of a SQL operation?

What is the non-trigger, non-polling approach to solving this need?

(The same effect = "insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event, without polling the table")

Answer 1

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thing^TM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

Answer 2

"insert/update/delete happens in a table and a python script is triggered within 2 seconds of the db event,

First off, if you use a trigger to write a message into a table dedicated for this purpose, you could continuously run the pooling process with a 1sec wait, or even less. The key is to make the polling query cheap enough (<1ms), and not interfere with any other transaction (thus the dedicated "queue table").

EG have your polling process run a batch like this:

declare @TriesRemaining int = 25
while not exists (select * from queue_table)
begin
  if @TriesRemaining <= 0
    break;
  set @TriesRemaining -= 1
  waitfor delay '0:0:1'
end
delete top (1)  
from queue_table
output deleted.*

To wait up to 25sec for a row to appear in the table, polling every second. On timeout it simply returns an empty resultset.

without polling the table

The simplest thing then is to use Service Broker, together with an Internal Activation Procedure that invokes the Python through xp_cmdshell, or an external process that loops on a blocking RECEIVE on the target service broker queue. This is how Database Mail works under the hood.

Answer 3

To minimize the impact of running the Python script synchronously from your trigger, you can wrap your Python code into a BaseHTTPServer:

import BaseHTTPServer

class MyHTTPHandler(BaseHTTPServer.BaseHTTPRequestHandler):
    def do_POST(self):
        print "Serving %s" % self.path
        # Your code here
        self.send_response(200, "OK")

def run(server_class=BaseHTTPServer.HTTPServer,
        handler_class=MyHTTPHandler):
    server_address = ('', 8000)
    httpd = server_class(server_address, handler_class)
    httpd.serve_forever()

if __name__ == "__main__":
    run()

You can then send an HTTP request from your trigger to the daemon above, as shown for example in this SO Q&A. The request handler can even spawn a separate thread for running your Python logic asynchronously.