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.