Ubuntitis

I work as a project manager for a company that uses almost exclusively Microsoft technology. This means that all our sites are created using .Net and they use MS SQL.

We are a very small company but we have tons of projects. A request for an automatic report recently came up but all of my .Net developers were busy and I knew the report would be very easy to do if the sites were powered by Django so I decided to see if I could use Python on Django to create the report. The only problem was how to connect to the MS SQL DB from a Linux machine using Django. The following are the steps I took to overcome that problem.

The following steps have been tested  on Ubuntu Jaunty and using Django’s latest development version. If you need help installing Django go here http://docs.djangoproject.com/en/dev/topics/install/#installing-development-version

1. Download and install tdsodbc,  unixODBC, g++ and python-dev(sudo aptitude install odsodbc unixODBC python-dev g++)
2. Download pyodbc
3. Build pyodbc by running the following command within the folder where you stored the downloaded pyodbc code:sudo python setup.py build
4. Install pydobc by running:sudo python setup.py installYou can test the installation of pydobc by running a python shell and trying to import the pyodbc module(import pyodbc). If you get an error, pyodbc was not installed correctly.
5. Check out the latest pyodbc code from here(http://code.google.com/p/django-pyodbc/source/checkout)
6. Create a symbolic link on your system’s site-packages folder to where you stored the djang-odbc code.  You can find out the site-packagess folder for your system by running:python -c “from distutils.sysconfig import get_python_lib; print get_python_lib()”.
7. Test that the python interpreter can load django-pyodbc by running a python shell and executing the following “import sql_server”
8. Now we need to create an ODBC DSN for the database you are trying to connect to. Using you favorite editor open the file /etc/odbc.ini and add the following:[MSSQL-PYTHON]
   Driver                  = /usr/lib/odbc/libtdsodbc.so
   Server                  = [Connection String to DB. eg: 127.0.0.1\SQLEXPRESS]
9. On the settings.py file of the django project you need the following:DATABASE_ENGINE = ‘sql_server.pyodbc’
   DATABASE_NAME = ‘DB_Name’
   DATABASE_USER = ‘user_name’
   DATABASE_PASSWORD =’DB_password’
   DATABASE_OPTIONS = {
   ‘driver’: ‘FreeTDS’,
   ‘dsn’: ‘MSSQL-PYTHON’, # ODBC DSN name defined in your odbc.ini
   }
10. You are done!

I will write another post explaining how I coded my models to work with the existing tables, specially to handle manyTomany relationships

This entry was posted on Thursday, July 9th, 2009 at 10:44 am and is filed under . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.