Launching a python script from an insert trigger

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?

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

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.