#pymssql

Archive from thesigb.com.

A couple weeks ago, I had the pleasure of querying a MSSQL 2000 database from Solaris using python. It was a bit of a hassle to get it all setup so it finally worked like I needed. I spent a couple days researching before I was finally successful. I thought I would try to remember my experience and share it here.

To make this a little more difficult, I didn't have root access on the machine I was working on. I had to do all compiling and installation from within my home directory.

First, you will need to download a few things to work with.

python2.4

pymssql

FreeTDS

Lets start off with FreeTDS, as it gives us the set of libraries we need to access a MSSQL server. Download and untar the source into your home directory somewhere.

$ mkdir src $ cd src $ wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz $ gunzip -c freetds-stable.tgz | tar -xvf -

Then you will need to configure and compile FreeTDS into your home directory. I'll create a special directory to install FreeTDS into.

$ mkdir ../freetds $ cd freetds-stable $ ./configure --prefix=/home/username/freetds --enable-msdblib --disable-libiconv $ make; make install; make clean

Notice that I used the --endable-msdblib and --disable-libiconv flags. The first flag enables some Microsoft specific behavior in the db-lib API. As for the second flag, you should try compiling and installing first without that flag and see if you have any success. My first compile didn't quite work. When I would try to connect to a database, it would give me errors (I don't recall exactly what they were, sorry). I spent the better part of an afternoon Googling and reading but still found no solution. I just happened to try the --disable-libiconv flag and everything seems to work correctly. I haven't run into any encoding issues, yet.

Next, its on to the pymssql install. Download and unpack pymssql as you did with FreeTDS above.

$ wget http://downloads.sourceforge.net/pymssql/pymssql-0.8.0.tar.gz $ gunzip -c pymssql-0.8.0.tar.gz | tar -xvf -

Next, we will build pymssql, but not install. This will allow us to manually place our pymssql library where we need.

$ cd pymssql-0.8.0 $ python setup.py build

If you get gcc or any other build errors, you will either have to troubleshoot those yourself, or get the admin to install the packages you need. You could also download and install whatever packages you may need into your home directory like we did with FreeTDS.

Once built, you should copy the pymssql.py and _mssql.so found in a subdirectory of build/ into a directory you have in your python path (or put a directory into your python path).

$ mkdir ../python-libs $ cp build/lib.solaris-2.10-i86pc-2.5/* ../python-libs/ $ export PYTHONPATH=$PYTHONPATH:/home/username/python

Add that last line into your .bash_profile, too. Next, we'll need to write a quick config file. You'll have to reference the FreeTDS docs to see how you'll want to configure your install.

$ vi ~/freetds/etc/freetds.conf

Once you have this all setup, you can crank up your python interpreter for a quick test. Quick note: 'hostname' is the name you defined in your freetds.conf file, not the entire hostname of the server.

>>> import pymssql >>> con = pymssql.connect(user="username", password="password", hostname="server", database="db1") >>> cur = con.cursor() >>> query = "select * from table1;" >>> cur.execute(query) >>> cur.fetchall()

If everything went as planned, you should see a bunch of data. If you get errors, you'll need to consult the appropriate documentation (or leave a comment and I can attempt to help).