Connect to SQL Server with Python

Recently I was tasked to rewrite some Perl cgi scripts.  My time is valuable to me, so I rewrote them in Python!  One of the functions of the cgi scripts was to connect to a Microsoft SQL Server 2008 r2 instance.  A quick glance at the perl scripts showed me we were using ODBC to connect to SQL Server.  After a few hours of reading online, I settled upon what I believe is the best method, using pyodbc.
pyodbc is an open source library for python, available at https://code.google.com/p/pyodbc/  Installation is pretty straight forward, but I will outline the steps here and help you connect to SQL Server as well.

My setup is a RHEL 6 x86_64 VM.  Running Apache 2.2, and tested with Python 2.6.6.  The SQL Server is pretty much setup out of the box, not special features required to connect with ODBC using standard install.  Just make sure if you’re trying to connect with a domain account or an sql account, you have the proper authentication settings enabled in SQL server.  By default, SQL Server has only “Windows Authentication” meaning SQL-specific accounts such as SA won’t be able to connect.  This tutorial assumes you know how to setup and install an SQL Server instance, or you already have one running.

Build and Install pyodbc

First, let’s install the necessary packages:

> sudo yum install gcc gcc-c++ python-devel freetds unixODBC unixODBC-devel

*Note, freetds is available from the EPEL repo if you’re using RHEL 6 or CentOS.  For more information, see http://fedoraproject.org/wiki/EPEL

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
sudo rpm -Uvh epel-release-6*.rpm

 
Next, download and install pyodbc from the project page.  I used version 3.0.6, but any current version should be the same.  Download

Upload that zip file to your server, unzip it, and cd into the newly created directory.
As root, run python setup.py build install
You shouldn’t see any errors if it was successful.
Finally, enable the driver for use by FreeTDS, edit /etc/odbcinst.ini and add the following:

1 2 3

[FreeTDS] Driver = /usr/lib64/libtdsodbc.so.0 UsageCount = 1

Connect to SQL Server using pyodbc

Okay, now that we have pyodbc installed and setup, we should be able to make a test connection to the server.  Be sure to edit the connection values appropriately for your setup.  My example shows Windows Authentication.

1 2 3 4 5 6 7 8

import pyodbc cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=dev-sql02;PORT=1433;UID=EXAMPLE\\myusero;PWD=xxx;DATABASE=fx_staging;UseNTLMv2=yes;TDS_Version=8.0;Trusted_Domain=EXAMPLE;') cursor = cnxn.cursor() cursor.execute("select state, district from participants") row = cursor.fetchone() if row: print row

Okay, if that was successful, here are a couple more examples to get you rolling.  Disclaimer:  I am not a full time DBA, so these may not be the most efficient, but they work well enough for me.   As stated above, I am using pyodbc for a cgi script, so you’ll notice my basic exception handling prints a form consisting of a couple back buttons.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

def getConnDefaults(myuser,mypass):  myDriver = 'DRIVER={FreeTDS};'  myServer = 'SERVER=my-server.example.com;'  myPort = 'PORT=1433;'  myUID = 'UID=EXAMPLE\\'+myuser+';'  myPWD = 'PWD='+mypass+';'  myDB = 'DATABASE=database_name;'  mySec = 'UseNTLMv2=yes;'  myTDSv = 'TDS_Version=8.0;'  myDom = 'Trusted_Domain=EXAMPLE;'  myConn = myDriver+myServer+myPort+myUID+myPWD+myDB+mySec+myTDSv+myDom  return myConn def doSelect(myuser,mypass,mystatement):  import pyodbc  myconnection = getConnDefaults(myuser,mypass);  try:  cnx = pyodbc.connect(myconnection)  cursor = cnx.cursor()  cursor.execute(mystatement)  myresult = cursor.fetchall()  cnx.close()  except:  print "Unable to connect to DB"  print """  <FORM><INPUT Type="button" VALUE="Back" onClick="history.go(-1);return true;"></FORM>  <FORM><INPUT Type="button" VALUE="ReturnHome" onClick="location.href='/';return true;"></FORM>"""  exit()  return myresult def doUpdateDB(myuser,mypass,mystatement):  import pyodbc  myconnection = getConnDefaults(myuser,mypass);  try:  cnx = pyodbc.connect(myconnection)  cursor = cnx.cursor()  cursor.execute(mystatement)  myresult = str(cursor.rowcount)+" Updated Successfully!"  cursor.commit()  cnx.close()  except:  print "Unable to connect to DB"  print """ <FORM><INPUT Type="button" VALUE="Back" onClick="history.go(-1);return true;"></FORM>  <FORM><INPUT Type="button" VALUE="Return Home" onClick="location.href='/';return true;"></FORM>""" exit()  return myresult

Tips and questions are welcome, thank you for reading.