This is the follow up to my post on connecting Python to SQL Server on Mac. This describes how I set it up on Linux.

Quite a few bits of it are copied from the Mac instructions, so it might be a little repetetive if you’ve already read that. (No new jokes, I’m afraid).

Edit: I’ve updated this having tried the installation a few more times. I’m still not 100% sure, but I appear to have got it working several times in a row.

Summary

• Install unixodbc and unixodbc-dev
• Install the patched version of freetds
• Install tdsodbc
• Install the patched version of freetds again
• Configure unixodbc
• Install python
• Install sqlalchemy and pyodbc

Installing freetds on Linux

I’m basing this on a bare bones install of Debian Lenny (ie starting with the bare minimum you can install).

I initially setup sudo, git, svn, bzr, openssh and emacs22. I won’t go too much into them here. You might use different tools. I did place my /etc and /home under version control with git as soon as possible, which I highly recommend for experimental setups. I regret not putting /usr under version control as well.

At some point you will need the tools to compile source such as python2.6 and pyodbc. You might as well install them now.

sudo aptitude install build-essential

I also recommend installing checkinstall which allows you to install from source as if it were a proper debian package. This allows you to meet dependencies with it and remove it easily at a later date.

 sudo aptitude install checkinstall

Next you’ll need to install unixodbc. You’ll also need the unixodbc dev headers for later (eg compiling pyodbc).

sudo aptitude install unixodbc, unixodbc-dev

Then you need to install the patched version of freetds. I pass two options to configure which make it work better with SQL Server, but might stop it working as well with Sybase and others. I don’t really care about that at the moment, but bear it in mind.

mkdir src
cd src/
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-patched.tgz
tar zxvf freetds-patched.tgz
cd freetds-0.82.1.dev.20090618/
./configure --enable-msdblib  --with-tdsver=8.0
make
sudo checkinstall
# When asked edit the 'provides' (11) value to be 'freetds-common'
# This allows it to be used as a dependency for tdsodbc

Now install tdsodbc:

sudo aptitude install tdsodbc

Then install tdsodbc again:

cd src/freetds-0.82.1.dev.20090618/
sudo make clean
./configure --enable-msdblib  --with-tdsver=8.0
make
sudo checkinstall
# When asked edit the 'provides' (11) value to be 'freetds-common'

I’m not entirely sure about this, but it appears to work for me. It doesn’t entirely make sense though, so I suspect that there is something better that can be done here.

Now you’re ready to setup up your odbc drivers and data sources.

You’ll need to create two files, tds.driver.template and tds.dsn.template. It doesn’t really matter where you create them, as they are templates to create your odbcinst.ini and odbc.ini files, but it’s worth putting them somewhere safe so that you can refer back to them later.

touch tds.driver.template
touch tds.dsn.template

Edit them and add the following, adjusted for your servername, dbname etc.

# tds.driver.template
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so

# tds.dsn.template
[my_dsn]
Description     = Connection to windows virtual machine
Driver          = TDS
Trace           = No
Database        = my_database_name
Server          = MY-SERVER
Port            = 1433
TDS_Version     = 8.0

Note that in the driver template you are naming a driver. You then use that name in the dsn template as the value of the driver. This can be any name you want instead of TDS, but they must match. Alternatively, you can specify the path to the driver in the dsn template.

Now you are ready to install the two templates. You’ll need to run odbcinst for each template. Running odbcinst without any arguments will give you some useful info, but I’ll summarise what you need to know to get it working.

 sudo odbcinst -i -d -f tds.driver.template
 # -i says you want to install
 # -d says you are installing the driver
 # -f says what template you are using

 sudo odbcinst -i -s -l -f tds.dsn.template
 # -i says you want to install
 # -s says you want to install dsn
 # -l says you want to install system dsn
 # -f says what template you are using

These will copy the contents of the templates to /etc/odbcinst.ini and /etc/odbc.ini respectively. They also perform some piece of magic that I haven’t worked out yet (eg. if you duplicate the entry it adds to odbc.ini and change the name slightly, it doesn’t seem to work). However adding a new entry using odbcinst does work.

With the second call to odbcinst, you have a choice between installing system dsn to /etc/odbc.ini using -l or user dsn in ~/.odbc using -h. I recommend system dsn for development, and doing more research for production.

Don’t forget that you might have to add your db-server to your hosts file, depending on your network setup. Just edit /etc/hosts and add the ip address and name of your server underneath the entry for localhost.

Now you should be able to run queries on your database, using isql.

isql my_dsn username password

This should bring up an SQL prompt that allows you to execute queries on the server. Try a couple of queries to test it. If you don’t have a legacy database with data already in it, why the hell are you using SQL Server?

Connecting Python to SQL Server through pyodbc and freetds on Linux

You should now be ready to get python sorted out. If you’ve had any problems so far, let me know and I’ll try to help.

First, you need a python installed. I’ve installed 2.6 from source, which I’ll briefly cover, but you should also be able to use the python2.5 package that’s available.

In order to have all the functionality of python when installing from source, you will need some extra libraries:

sudo aptitude install libncursesw5-dev libreadline5-dev libssl-dev \
libgdbm-dev libbz2-dev libc6-dev libsqlite3-dev libdb-dev tk-dev
# That's all on one line, not two

I’ve used checkinstall to install python as a package. Alternatively you can install it to your home folder by passing --prefix=/home/username to configure.

To install it to your home folder, just download the source from the python website, unzip it, configure it, then use checkinstall.

wget http://www.python.org/ftp/python/2.6.2/Python-2.6.2.tgz
tar zxvf Python-2.6.2.tgz 
cd Python-2.6.2/
./configure
make
sudo checkinstall

I also strongly recommend using virtualenv and virtualenvwrapper. Create a virtualenv as soon as you have installed python and always create your other virtualenvs from that, so that you can easily get back to a clean python if you need to. (I’ll add instructions for that as a seperate post sometime). Install pyodbc into your virtualenv:

    easy_install pyodbc

If you installed the build-essential and unixodbc-dev earlier you should have what you need to compile it.

Now checkout the 0.6 branch of SQLAlchemy. This is currently a branch but should soon move to trunk. It’s fine for development. If you are using this as a guide to setting up your production server, you are insane.

svn checkout http://svn.sqlalchemy.org/sqlalchemy/branches/rel_0_6
# Make sure your virtualenv is activated then:
cd rel_0_6
python setup.py develop

You should now be able to run some basic tests using SQLAlchemy:

>>> import sqlalchemy as sa
>>> uri = "mssql://username:password@my_dsn"
>>> engine = sa.create_engine(uri)
>>> select_query = sa.text("SELECT * FROM MyTable")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[]
>>> insert_query = sa.text(
    "INSERT INTO MyTable (id, text_field) VALUES (:id, :text_field)")
>>> result = engine.execute(insert_query, 
        id="Brian", text_field="Naughty Boy")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[('Brian', 'Naughty Boy')]

That should all work. At this point you should probably try running some other tests. Particularly ones involving non-ascii chars. Then you can watch it crumble and die. (Actually if you’ve installed it correctly, it should work with non-ascii chars).

The UK Pound Sign (£) is a particularly good symbol to use for testing as it has a different byte representation between latin-1 and utf-8.

This is a draft document, and will be updated as and when I discover more. The Mac instructions are very draft as I haven’t had a chance to repeat them (I haven’t worked out how to run OS X Leopard in a virtual machine yet).

I have unfortunately had to work with a legacy SQL Server database, and was reluctant to use Windows to do the development. As such I have been trying to get my Mac development machine and a Linux server to connect to the database. It has been quite an adventure, and this is what I think I have worked out so far.

Installing freetds on Mac

I used MacPorts for installing packages. It’s not perfect but it’s a lot easier than installing from source when there is a lot of dependencies, and quite a bit of complex configuration has often been done for you.

Install freetds with the +mssql and +odbc variants.

sudo port install freetds+mssql+odbc

Also install unixODBC

sudo port install unixODBC

You now have a folder /opt/local/etc/freetds/ which should contain two templates tds.driver.template and tds.dsn.template as well as freetds.conf. You shouldn’t need to edit freetds.conf, although you can choose to specify the global tds version and charset (I have done neither of those and it works for me, so best leave it alone unless you have specific problems).

If you don’t have tds.driver.template or tds.dsn.template, create them.

sudo touch tds.driver.template
sudo touch tds.dsn.template

Edit them and add the following, adjusted for your servername, dbname etc.

# tds.driver.template
[TDS]
Description     = FreeTDS Driver for Linux & MSSQL on Win32
Driver          = /opt/local/lib/libtdsodbc.so
Setup           = /opt/local/lib/libtdsS.so

# tds.dsn.template
[my_dsn]
Description     = Connection to windows virtual machine
Driver          = TDS
Trace           = No
Database        = my_database_name
Server          = MY-SERVER
Port            = 1433
TDS_Version     = 8.0

Note that in the driver template you are naming a driver. You then use that name in the dsn template as the value of the driver. This can be any name you want instead of TDS, but they must match. Alternatively, you can specify the path to the driver in the dsn template.

Now you are ready to install the two templates. You’ll need to run odbcinst for each template. Running odbcinst without any arguments will give you some useful info, but I’ll summarise what you need to know to get it working.

 sudo odbcinst -i -d -f tds.driver.template
 # -i says you want to install
 # -d says you are installing the driver
 # -f says what template you are using

 sudo odbcinst -i -s -l -f tds.dsn.template
 # -i says you want to install
 # -s says you want to install dsn
 # -l says you want to install system dsn
 # -f says what template you are using

These will copy the contents of the templates to /Library/ODBC/odbcinst.ini and /Library/ODBC/odbc.ini respectively. They also perform some piece of magic that I haven’t worked out yet (eg. if you duplicate the entry it adds to odbc.ini and change the name slightly, it won’t work). However adding a new entry using odbcinst does work.

With the second call to odbcinst, you have a choice between installing system dsn to /Library/ODBC/odbc.ini using -l or user dsn in ~/.odbc using -h. I recommend system dsn for development, and doing more research for production.

Now you should be able to run queries on your database, using isql.

isql my_dsn username password

This should bring up an SQL prompt that allows you to execute queries on the server. Try a couple of queries to test it. If you don’t have a legacy database with data already in it, why the hell are you using SQL Server?

Connecting Python to SQL Server through freetds on Mac

You should now be ready to get python sorted out. If you’ve had any problems so far, let me know and I’ll try to help.

First, you need a python installed. I’m using python2.6, but 2.5 should work fine, and maybe 2.4. I’m not going to go into installing python in too much depth, but I recommend not using the Mac system python, and also not using MacPorts to install python. I also strongly recommend using virtualenv and virtualenvwrapper. Create a virtualenv as soon as you have installed python and always create your other virtualenvs from that, so that you can easily get back to a clean python if you need to.

Install pyodbc into your virtualenv:

    easy_install pyodbc

Pyodbc needs to be compiled, so you will need a version of GCC. Either install the latest version through MacPorts (this takes a very long time) or install XCode which includes Apple’s own version of GCC. You may also need the python-dev headers and/or the unixodbc-dev headers, depending on how you installed python and unixodbc.

If you have gcc errors and they include the string “-arch i386 -arch ppc” somewhere in them, then python is probably trying to build a universal build but doesn’t have the ppc headers. Edit /lib/python2.x/config/MakeFile and delete every instance of “-arch ppc”. (This is assuming you are running an Intel Mac of course).

Now checkout the 0.6 branch of SQLAlchemy. This is currently a branch but should soon move to trunk. It’s fine for development. If you are using this as a guide to setting up your production server, you are insane.

svn checkout http://svn.sqlalchemy.org/sqlalchemy/branches/rel_0_6
# Make sure your virtualenv is activated then:
cd rel_0_6
python setup.py develop

You should now be able to run some basic tests using SQLAlchemy:

>>> import sqlalchemy as sa
>>> uri = "mssql://username:password@my_dsn"
>>> engine = sa.create_engine(uri)
>>> select_query = sa.text("SELECT * FROM MyTable")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[]
>>> insert_query = sa.text(
    "INSERT INTO MyTable (id, text_field) VALUES (:id, :text_field)")
>>> result = engine.execute(insert_query, 
        id="Brian", text_field="Naughty Boy")
>>> result = engine.execute(select_query)
>>> result.fetchall()
[('Brian', 'Naughty Boy')]

That should all work. At this point you should probably try running some other tests. Particularly ones involving non-ascii chars. Then you can watch it crumble and die.

The UK Pound Sign (£) is a particularly good symbol to use as it has a different byte representation between latin-1 and utf-8.