Accessing MSSQL using Python on Ubuntu

I started using Python a while ago and one of the things I need to do is to be able to connect to Microsoft SQL server from Linux servers. I was a bit surpised that I could only find some relics from projects not alive any more or the commercial MxODBC from eGenix [1]. Then I ran into pymssql and decided to give a try. It worked almost at once on my virtualized Ubuntu Gutsy test box.

First I installed the prerequisites: freetds and the python headers:

aptitude install python2.5-dev freetds-dev

There might be some other packages that are needed but that was the only ones I needed to install. Then I decompressed the latest release from pymssql [2] at sourceforge and installed:

tar -xvzf pymssql-0.8.0.tar.gz
cd pymssql-0.8.0
python setup.py install

The compilation went fine straight away so I wanted to test it. I did so by creating my own test script and it is as follows (ok, not very nice perhaps but I do get an indication whether it works or not):

#!/usr/bin/python
import _mssql
mssql=_mssql.connect('mssql.server.com','databaseuser','password')
mssql.select_db('Northwind')

query="select firstname,lastname,birthdate from dbo.Employees;"
if mssql.query(query):
    rows=mssql.fetch_array()
    rowNumbers = rows[0][1]
    print "Number of rows fetched: " + str(rowNumbers)
    for row in rows:
        for i in range(rowNumbers):
	    print str(i) + "\t" + row[2][i][0] + "\t" + row[2][i][1] + "\t" + str(row[2][i][2]) 
else:
    print mssql.errmsg()
    print mssql.stdmsg()
mssql.close()

After setting execute permissions on the script and running it the following showed up:

Number of rows fetched: 9
0    Nancy    Davolio      1948-12-08 00:00:00
1    Andrew   Fuller       1952-02-19 00:00:00
2    Janet    Leverling    1963-08-30 00:00:00
3    Margaret Peacock      1937-09-19 00:00:00
4    Steven   Buchanan     1955-03-04 00:00:00
5    Michael  Suyama       1963-07-02 00:00:00
6    Robert   King         1960-05-29 00:00:00
7    Laura    Callahan     1958-01-09 00:00:00
8    Anne     Dodsworth    1966-01-27 00:00:00

If you want to run any of the test scripts that are included in the pymssql package you have to remove the DOS linefeeds in the files. Do that by:

sed "s/\r//" test.py > clean_test.py