Query a MS SQL Server database with Python

2012-07-14

I won't try to hide my love of Python. It's great. I built this site with it. So of course I would want to access a MS SQL Server database with it. Here's how you do it.

Things you'll need before you set start:

Cython>=0.15.1 (I'm using Cython-0.16)
python-dev

Download the pymssql library

Although development has died off, pymssql works great. You can find it here: pymssql. The creators deem it as "A fast MS SQL Server client library for Python directly using C API instead of ODBC. It is Python DB-API 2.0 compliant. Works on Linux, *BSD, Solaris, Mac OS X and Windows."

Install the library

I'm on Ubuntu 12.04, which comes with python 2.7 by default. I used python-setuptools and the easy_install command to install an egg of the library:

sudo apt-get install python-setuptools
sudo easy_install pymssql-2.0.0b1_dev_20111018-py2.7-linux-x86_64.egg

Write a query script in python

Here's mine (it's a quick and dirty one based on the example on the pymssql page:

import pymssql
conn = pymssql.connect(host='my_host', 
    user='my_user', password='secret!', database='my_db')
cur = conn.cursor()

cur.execute('SELECT * FROM my_table')
row = cur.fetchone()
while row:
    print "%s, %s" % (row[0], row[1])
    row = cur.fetchone()

conn.close()

There it is! Nice and simple.