Accessing a SQL Server (MSSQL) database from Django

Last update on July 23, 2014.

MS SQL does not play well with others, and getting to work well with Django is difficult. This article will document the steps needed to setup FreeTDS and ODBC, and then get Django (version 1.4!) to play well with these programs. Everything will be done a fresh install (LiveCD actually) of Ubuntu 12.04. It shouldn't be too much trouble to replicate these steps on others operating systems


Installing FreeTDS and ODBC


We will need FreeTDS, ODBC,  and some basic development tools (yea, we're going to have to compile something). After enabling all the Ubuntu repositories in the software center, including source, execute:

apt-get install freetds-common freetds-bin tdsodbc unixodbc subversion
apt-get build-dep pyodbc

This will install  all the stuff for freetds and odbc. Next, append...

[MSSQL-PYTHON]
host = <IP/Hostname of the SQL Server host>
port = 1433
tds version = 8.0

to  /etc/freetds/freetds.conf. Pay special attention to the "MSSQL-PYTHON" part. This is the "DSN" of the FreeTDS configuration (we will get another one for the odbc configuration). Of course, replace everything between the <> with the appropriate IP or hostname.

Next we will configure ODBC. Make sure /etc/odbc.ini looks something like...

[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server
[MSSQL-PYTHON]
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Description = Hi! This is a description of the MSSQL-PYTHON driver
Trace = No
Database = <Database schema to connect to>
Server = <IP/Hostname of the SQL Server host>
Port = 1433


Please note that the location of the "Driver" will depend greatly on you're system. To find it, do a "find / -name libtdsodbc.so" and use that path instead of the one I provided. Now is a good time to do some basic sanity testing, and make sure this is working before we move onto the hard part.

isql -v MSSQL-PYTHON <Username> <Password>
SELECT TOP 1 FROM <SomeTable>

Replacing everything between <>'s with appropriate values, of course.  If you can connect without an error, everything *should* be fine. If you can execute a SQL query, you're most definitely on the right track. Good job!

Install pyodbc, django


The next step is setup python and django. I will be doing this in a virtualenv because it provides the cleanest environment.  First, install virtualenv if you don't have it

apt-get install python-virtualenv 

Then create the environment, activate it, and install django, pyodbc, and django-pyodbc

virtualenv --no-site-packages env
. ./env/bin/activate
easy_install django pyodbc

For the django-pyodbc adapter, we need to clone it directly from the source repo (it doesn't have a Ubuntu package or a Pypy package). Then we build and execute it.

svn checkout http://django-pyodbc.googlecode.com/svn/trunk/ django-pyodbc-read-only
cd django-pyodbc-read-only
python setup.py build install

Then we should be good to start our project, and try settings up the database. Start the project with

django-admin.py startproject django_project
cd django_project

Then comes the fun part. Django-odbc will complain if you don't have some variables set in the "global" settings space, but will use variables from the DATABASES configuration dictionary. Here is what you're database settings should look like in django_project/settings.py

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': '<Database name>',                      # Or path to database file if using sqlite3.
        'USER': '<Database user>',                      # Not used with sqlite3.
        'PASSWORD': '<Database password>',                  # Not used with sqlite3.
        'HOST': 'MSSQL-PYTHON',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '1433',                      # Set to empty string for default. Not used with sqlite3.
        'OPTIONS': {
                'host_is_server': False,
                'dsn': 'MSSQL-PYTHON',
        },
    }
}
DATABASE_NAME = '<Database name>'
DATABASE_HOST = 'MSSQL-PYTHON'
DATABASE_PORT = '1433'
DATABASE_USER = '<Database user>'
DATABASE_PASSWORD = '<Database password>'
DATABASE_OPTIONS = {
        'host_is_server': False,
        'dsn': 'MSSQL-PYTHON',
}

After you fill all that out, you should be able to connect to the database. I tested this using "python ./manage inspectdb", although that didn't finish (big database), so I can't say whether it works or not.


** At the time of writing, there was a small bug in the django-pyodbc. You need to modify the following file <Directory that contains the 'env' folder we setup earlier>/local/lib/python2.7/site-packages/sql_server/pyodbc/operations.py, around line 10ish. Look for something that looks like:

     def __init__(self, connection):
        super(DatabaseOperations, self).__init__()
        self.connection = connection
        self._ss_ver = None


And change it too

    def __init__(self, connection):
        super(DatabaseOperations, self).__init__(connection)
        self.connection = connection
        self._ss_ver = None

And everything *should* be good.


Cheers! It worked!

Next entry

Similar entries

comments powered by Disqus