10

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")

5
  • You are changing the question five years later? Full of conflicts. Polling the table doesn’t meet your business need because the py needs to run immediately, but in the update you say a 2 second delay is acceptable? Confusing. If a 2 second delay is acceptable then I think so is polling the table. Dec 4, 2018 at 18:31
  • 1
    @AaronBertrand I agree that this question does not conform to everyones view of reality. But if we take a moment, and assume the questioner is intelligent and sincere in his need for a non-actual-trigger-but-acts-like-a-trigger quest, we (as an SE community) either can help find a way fwd (or dismiss the question, which doesn't actually make the need/problem go away). fwiw. Dec 4, 2018 at 22:12
  • That’s fine, but you have to pick which problem to solve, and then fix the question (or maybe start a new one if the answer you got 5 years ago was acceptable then but no longer acceptable today, whether it’s because your requirements have since changed). Currently you say you don’t want polling or a trigger, and you also say that it must be immediate and a 2 second delay is fine. Dec 4, 2018 at 22:19
  • 1
    Now, this is a scenario, where NoSQL doesn't come handy unlike DBMS, because DBMS can manage triggers and contribute as an application layer(more than a data storage) Jan 6, 2019 at 19:10
  • @samsmith Did you go through this answer? Jan 6, 2019 at 19:14

3 Answers 3

12

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 thingTM, 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?

4
  • Aaron, All your points are valid, but that is my problem. I want to fire python from a trigger, and I have no problem with the issues. (I can have the trigger on the real table push a value into a "job" table, and a trigger on the "job" table run python...) Dec 13, 2013 at 19:46
  • 4
    Also, your trigger -> other table -> other trigger idea still suffers from the same problem, only now it's worse. The original transaction still has to wait for all of that cascading activity to complete. Dec 13, 2013 at 20:08
  • good call RE cascading the issue. Won't go there! Dec 13, 2013 at 20:36
  • OP enhanced, to reframe the question Dec 4, 2018 at 15:39
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.

1
2

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.

1
  • Nice answer!!! Most of the mid 90's mgr applications(that I worked) were polling the database, with a polling interval. Jan 6, 2019 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.