Connect to SQL Server with Python

0
by on July 5, 2013 at 2:23 pm

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: 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

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:

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

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, zip from addresses")
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.


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.

in How-To, Red Hat

, , , ,

You can skip to the end and leave a response. Pinging is currently not allowed.

Categories