How to accomplish query notification on SQL Server with python

Question

I would like to set up a callback in Python when a table on SQL Server changes, similar to how its done for Oracle here.
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/oow10/python_db/python_db.htm#t11

Is there a library that allows me to do so in Python, an example would be appreciated.

Do you need a pure CPython solution or could you integrate a .NET component, either as an interface between Python and SQL Server, or by using IronPython? .NET has the SqlDependency class for monitoring data changes in SQL Server, so if you can use .NET in some way then you could use it, otherwise you may have to settle for polling. And it would be helpful to know which OS you're using and how you're connecting to SQL Server. — Pondlife
– Pondlife, Commented May 22, 2013 at 14:01
I'm on Windows, using pure python. I guess I could try to integrated a .Net Component. Do you know of an example? Thanks. — Roman
– Roman, Commented May 22, 2013 at 15:47
The documentation has sample code for using SqlDependency but I don't know of any 'standard' way to implement a Python to .NET bridge. You could write a .NET service that Python can consume via HTTP or sockets, but that's really a question of how your application is designed. — Pondlife
– Pondlife, Commented May 22, 2013 at 18:19
see here: stackoverflow.com/questions/21117431/… and here (MySQL but may give some clues); stackoverflow.com/questions/5771925/… — Don Smythe
– Don Smythe, Commented Feb 4, 2017 at 13:18
Just learned about pythonnet on github which works great python for .Net, but you may be needing the other way around. — owns
– owns, Commented May 9, 2017 at 7:39

Chris Beardsley · Accepted Answer · 2017-03-19 04:12:34Z

1

First, download the ODBC Driver for Linux Then Install pyodbc using pip

pip install pyodbc==3.1.1

Create a py file with this code:

import pyodbc
server = 'yourserver.database.windows.net'
database = 'yourdatabase'
username = 'yourusername'
password = 'yourpassword'
driver= '{ODBC Driver 13 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("select @@VERSION")
row = cursor.fetchone()
if row:
    print row

That's your basic connection and call. Then follow the procedures from your oracle link, "Using Continuous Query Notification"

But... maybe b/c I am a SQL guy and a security wonk, it seems you'd be better off to have SQL Server push change notifications to somewhere python can get to it.

answered Mar 19, 2017 at 4:12

Chris Beardsley

713 bronze badges

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

1 Comment

Ben Over a year ago

subscribe() support has been asked in pyodbc issues github.com/mkleehammer/pyodbc/issues/997, the answer is no.

Collectives™ on Stack Overflow

How to accomplish query notification on SQL Server with python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related