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.

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

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:

That’s it!

Installing on Ubuntu

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

Then we install pyodbc and 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:

You are ready to use MSSQL now!

Django Settings

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

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.

Tagged database, django, legacy, microsoft, mssql, odbc, osx, pyodbc, ubuntu, windows MSSQL | 2 Comments

5 open-source Python/Django apps we love

The Python/Django duet is fantastic, everyone knows that. However it can be even more awesome with these apps:

1. South

This is an intelligent schema and data migration tool. If you don’t use it yet, you will want to after reading this post. Okay… but what it is exactly?

When you create a Django app, you are using the “syncdb” command. After a model changes you have to delete the whole database and run “syncdb,” or manually do SQL changes directly in the database. It’s even more tragic when you already have production data. Not so fun, right?

Enter our hero: “South.” Its main objectives are to provide a simple, stable and database-independent migration layer to prevent all the hassles schema changes bring to your Django applications over time.

Simple example

Let’s create a model in our “southtut” app:

Then let’s create our first migration:


$ ./manage.py schemamigration southtut --initial
Creating migrations directory at '/home/andrew/Programs/litret/southtut/migrations'...
Creating __init__.py in '/home/andrew/Programs/litret/southtut/migrations'...
+ Added model southtut.Knight
Created 0001_initial.py. You can now apply this migration with: ./manage.py migrate southtut


And apply our new migration:


$ ./manage.py migrate southtut
Running migrations for southtut:
- Migrating forwards to 0001_initial.
> southtut:0001_initial
- Loading initial data for southtut.


Very similar to “syncdb,” for now. But now time for magic! Let’s modify the model:

To apply these changes we have to run these two commands:


$ ./manage.py schemamigration southtut --auto
$ ./manage.py migrate southtut


And that’s all! All data is in place with the new structure.

Read more here: http://south.aeracode.org/

2. Fabric

Fabric is the second must have app. Fabric is a Python library and command-line tool for streamlining the use of SSH for application deployment or systems administration tasks.

It provides a basic suite of operations for executing local or remote shell commands (normally or via sudo) and uploading/downloading files, as well as auxiliary functionality such as prompting the running user for input, or aborting execution.

Small example:

You are working on a Django app locally. After you finish working, you commit all changes to a repository. Now you want to deploy all the changes to staging or production server. This is what it normally looks like:

- open new console
- connect with server via SSH
- run virtual environment (if any)
- install any new requirements
- pull all changes from repository
- run syncdb, migrations (if South is used)
- collect static files
- delete *.pyc files (to be sure)
- restart apache/nginx or whatever server you have using
- restart memcache

10 steps and we are ready. But… how about doing same thing using only one line? Simple — use Fabric. With the proper configuration of all these steps we can close in this line:


$ fab production launch


Here you can read more: http://fabfile.org

3. Django compressor

What we would like to achieve in our website:

- every file is downloaded from server only ONCE (every next time cache is used)
- only one CSS and JS file is downloaded
- client browser always knows what file is actual and what file should be downloaded again

All of that is possible with Django Compressor.

Django Compressor combines and compresses linked and inline Javascript or CSS in a Django template into cacheable static files by using the “compress” template tag.

Example:

Which would be rendered all in one file:

More information here: https://github.com/jezdez/django_compressor

4. Tastypie

Sometimes we have to add an API to our application without the ability to modify the sources of that app. Tastypie is exactly what we need. It provides a convenient yet powerful and highly customizable abstraction for creating REST-style interfaces. It relies only on its own code and focuses on providing a REST-style API.
Let’s create a resource class for our model “Knight”:

Now we have to hook up a resource:

And voila! We have set up our REST interface. Our resource is available at these urls:

http://127.0.0.1:8000/api/knight/?format=json

http://127.0.0.1:8000/api/knight/1/?format=json

http://127.0.0.1:8000/api/knight/schema/?format=json

etc...


Of course, this is only one simple example. For safety, Tastypie ships with the authorization class and many more.

More information: http://tastypieapi.org/

5. Easy thumbnails

There are many apps for thumbnails. However, this one is the simplest and most powerful I’ve ever seen. We only have to add ‘easy_thumbnails’ in INSTALLED_APPS.

It’s great for quick thumbnails in template usage:

where:

- source must be a File object, usually an Image/FileField of a model instance,
- size in the format [width]x[height]
- list of options like sharpen, crop, and quality=90

And that’s all. The thumbnail is created dynamically (if it doesn’t exist) when the page is requested.

Of course, there is Model and Low Level usage too.

More information: https://github.com/SmileyChris/easy-thumbnails

Tagged compressor, django, fabric, open source, python, south, Tastypie, thumbnails, tivix | 1 Comment

Django Common App (Part 1)

Here at Tivix we’ve created a number of open source apps that are available for use.

In this post we’re going to talk specifically about the django-common app, which provides functionality commonly needed in a django application (in case it wasn’t obvious).




WWWRedirectMiddleware

WWWRedirectMiddleware redirects requests to make sure they are on the www subdomain or off the www subdomain, depending on your settings.  Besides adding ‘django_common.middleware.WWWRedirectMiddleware‘ to MIDDLEWARE_CLASSES, you need to set DOMAIN_NAME in the settings, either with www or not.  You need to also set IS_PROD to true, as you don’t want to redirect on localhost.




SSLRedirectMiddleware and NoSSLRedirectMiddleware

SSLRedirectMiddleware redirects requests to make sure that they are under https.  NoSSLRedirectMiddleware makes sure they are under http.




ssl_required decorator

If you don’t need ssl across the entire site (you oftentimes don’t) you can simply use the ssl_required decorator on the specific view that requires https.




disable_for_loaddata decorator

This decorator is used to wrap signals instead of views.  It disables signals from being fired when the loaddata command is run.  See: https://code.djangoproject.com/ticket/8399




EmailBackend

The EmailBackend is an authentication backend that works like the default ‘django.contrib.auth.backends.ModelBackend‘ except it finds users by email instead of username.  To use it simply add ‘django_common.auth_backends.EmailBackend‘ to AUTHENTICATION_BACKENDS in the settings.

These are just a few features available in the django_common app.  Stay posted for more posts on the django_common app, but of course the best way to find out all the functionality is to check out the app itself!

Tagged django, open source, overview | Leave a comment