Connecting django to a legacy database

At work we use Microsoft’s SQL Server to store data. It’s clunky and inefficient to move data in and out, which has worked in the past because the data needs to be secure. What’s more secure than a black hole? Well, I want to create an analytics dashboard and pull some data to get quick statistics for creating compliance reports*¹. I’d like to ideally create something and iOS native app someday but for now I’ll go with a mobile web version**².

I tried ALL DAY to get a microsoft solution to work… tried WCF web services, tried ASP.Net MVC application on Windows 7 with Visual Studio 2010 with no luck for either. Felt like a waste of a day, until I gave python a try tonight. Guess third time’s the charm. I used this guide by Tivix during installation. Please note that they used Django 1.3 at the time of writing the guide.

#Steps:

Install MacPorts (http://www.macports.org/) to quickly install two dependencies: freetds and libiodbc. After you have those, use pip to install the django-pyodbc extension. The link below is for Django 1.4. Verify that you have Django version 1.4, otherwise, use the link provided in the guide above for django-pyodbc:

 python -c "import django; print(django.get_version())"

In the shell, type the following commands to install the packages. You may have to prefix them with ‘sudo’ depending on how your mac is set up.

 sudo port install freetds
 sudo port install libiodbc
 pip install pyodbc
 pip install pip install https://github.com/avidal/django-pyodbc/archive/django-1.4.zip

Download ODBC Manager (http://www.odbcmanager.net/) and add the database driver.

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 

(you may have to use spotlight to search for the correct file location! add simlinks to those places)

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

In the settings.py file, add the database information:

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"
		}
	}
}

Run this command to grab the database schema from your legacy database.

 python manage.py inspectdb > models.py

That’s it!