Getting Django Working with MSSQL / pyodbc
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.