14

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.

Roman
  • 567
  • 3
  • 17
  • 1
    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](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx) 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 May 22 '13 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 May 22 '13 at 15:47
  • The documentation has [sample code](http://msdn.microsoft.com/en-us/library/62xk7953(v=vs.110).aspx) 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 May 22 '13 at 18:19
  • see here: https://stackoverflow.com/questions/21117431/how-to-receive-automatic-notifications-about-changes-in-tables and here (MySQL but may give some clues); https://stackoverflow.com/questions/5771925/python-how-to-get-notifications-for-mysql-database-changes – Don Smythe Feb 04 '17 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 May 09 '17 at 07:39

1 Answers1

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.

Chris Beardsley
  • 71
  • 3