Some projects require a MSSQL database (either legacy or for other reasons). Unfortunately Django doesn't officially support this database (yet).

In this tutorial we will show how to connect to MSSQL from Python/Django using pyodbc.

Installing on Mac OS X

First of all we must install two dependencies: freetds and libiodbc. Let's use MacPorts for this (http://www.macports.org/). The MacPorts Project is an open-source community initiative to design an easy-to-use system for compiling, installing, and upgrading either command-line, X11 or Aqua based open-source software on the Mac OS X operating system.

sudo port install freetds
sudo port install libiodbc

Now we have to install pyodbc and django-pyodbc (in your virtualenv of course!)

pip install pyodbc
pip install svn+http://django-pyodbc.googlecode.com/svn/trunk/#django-pyodbc

When everything is ready we can set up odbc. To do this open ODBC Manager -> Drivers -> Add… and enter this data (change path if needed):

Driver Name: FreeTDS
Driver file: /opt/local/var/macports/software/freetds/0.82_0/opt/local/lib/libtdsodbc.so

It appears as if the system stores ODBC configuration data in /Library/ODBC, but the Mac Ports stores configuration in /opt/local/etc. So lets do some symlinks:

sudo ln -s /Library/ODBC/odbc.ini /opt/local/etc/odbc.ini
sudo ln -s /Library/ODBC/odbcinst.ini /opt/local/etc/odbcinst.ini

That's it!

Installing on Ubuntu

First of all we must install two dependencies: freetds and libiodbc.

sudo aptitude install unixodbc unixodbc-dev freetds-dev tdsodbc

Then we install pyodbc and django-pyodbc:

pip install pyodbc
pip install svn+http://django-pyodbc.googlecode.com/svn/trunk/#django-pyodbc

When we have all in place, we must set up odbc. To do this edit odbcinst.ini file (change path if needed):

– sudo vim /etc/odbcinst.ini

and paste this configuration:

[FreeTDS]
    Description = TDS driver (Sybase/MS SQL)
    Driver = /usr/lib/odbc/libtdsodbc.so
    Setup = /usr/lib/odbc/libtdsS.so
    CPTimeout =
    CPReuse =

You are ready to use MSSQL now!

Django Settings

Last step is proper settings file. Here is an example:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'DB NAME',
        'HOST': 'HOST IP',
        'USER': 'USER',
        'PASSWORD': 'PSW',
        'PORT': 1433,
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
            'extra_params': "TDS_VERSION=8.0"
        }
     } 
}

Most important are ENGINE and OPTIONS.

'ENGINE' should be set to 'sql_server.pyodbc'
'driver' name is a name used in odbc configuration.
If you use remote db, you must use `'host_is_server': True` parameter.
Last important param is "TDS_VERSION=8.0". We must declare TDS protocol version.

After all these steps, you should be able to connect MSSQL successfully.

Troubleshooting

You might encounter an error when trying to read Decimal fields: MemoryError of fetching results. – on Mac OS X
HY003 Program type out of range (SQLGetData() ) when trying read numerical field (MSSQL) – on Ubuntu

It seems that problem was fixed by wesm at github: https://github.com/wesm/pyodbc/tree/getdata-decimal-bug

When you update pyodbc with this build (pyodbc 2.1.10-beta04) problems should dissapear! Hope this post was helpful and saved you some development time.