Replacing Django's ORM with SQLAlchemy

Published on July 23, 2008. django (72)

In the first example of Django's loose coupling we looked at replacing the templating system with Jinja2. In this article we're going to look at replacing the standard Django ORM with SQLAlchemy. This is a pretty big leap, and before we jump in too deeply lets take a look at how switching to SQLAlchemy is going to impact our application.

In the introduction to this series, I said that Django's implementation follows the loose coupling philosophy, but we may have to add several grains of salt to that statement as we consider the changes caused by switching from Django's ORM to SQLAlchemy. First, we're going to no longer be able to use the Django models.py files to describe our models. Instead, we'll be using SQLAlchemy's ORM for defining tables and fields (which will look pretty similar).

Following from that, we won't be able to use a simple python manage.py syncdb to create the tables for our project. We won't be able to use python manage.py loaddata or python manage.py dumpdata to create fixtures to use as initial data or scaffolding for the Django testing framework. If we're strict about only using SQLAlchemy, we won't even be able to use the contributed resources like the Django Admin, the sessions middleware, or the authentication middleware.

The take away message here is that while Django is loosely coupled, it is loosely coupled does not imply decoupling has no consequences. Changing some systems, like template rendering, have very few consequences, but others--like the Django ORM--are a far from plug and play. This is unavoidable, because perfect decoupling places additional onus upon the individual developer, and would make it slower (and more complex) to get Django projects up and running. When you first begin using Django it is difficult to gauge which subsystems will be costly to decouple, and that is why its highly recommendable to start out using the default Django stack, and then deviate responsibly as you gain more experience.

(Full disclosure: it is possible to use the Django ORM and SQLAlchemy together. This means you could still take advantage of the Django sessions middleware (and most other stuff), while using SQLAlchemy for some parts of your app. This tutorial won't build that Frankenstein, since it's only interested in exploring the loose coupling aspect, but it wouldn't be prohibitively difficult to do so. Although, it would be awkward in some regards.)

Now lets start turning silver into gold¹.

Using SQLAlchemy with Django

First we need to download SQLAlchemy. The easiest way to do this is to use easy_install.
```
easy_install SQLAlchemy
```
But you can also grab a copy of the 0.5 release at their download page.. This tutorial is using the 0.5beta2, but as long as you don't grab a 0.4 release things should work correctly.
If you chose to manually download the files, you'll then need to install them:
```
tar zxvf SQLAlchemy-0.5.0beta2.tar.gz
cd SQLAlchemy-0.5.0beta2
python setup.py install
```
If you are using a Python before 2.5 you'll need to install pysqlite and SQLite as well.
Now lets make sure the install worked correctly.
```
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.5.0beta2'
```
If SQLAlchemy doesn't import correctly, or if the version isn't some derivative of 0.5, then you'll need to revisit steps one and two.

Create a Django project and app.

django_admin.py startproject loose_coupling
cd loose_coupling
python manage.py startapp with_sqlalchemy

Create a couple of template directories.

mkdir templates
cd with_sqlalchemy
mkdir templates templates/with_sqlalchemy

Next we need to make a few simple changes in our settings.py file. We'll be changing INSTALLED_APPS, TEMPLATE_DIRS, and MIDDLEWARE_CLASSES.

MIDDLEWARE_CLASSES = ('django.middleware.common.CommonMiddleware',)
INSTALLED_APPS = ('loose_coupling.with_sqlalchemy',)
import os
ROOT_PATH = os.path.dirname(__file__)
TEMPLATE_DIRS = (os.path.join(ROOT_PATH,'templates'),)

Since we're not using the Django ORM, we won't use any of the database reliant middleware or apps.

Now we'll edit the loosely_coupled/urls.py file.
```
from django.conf.urls.defaults import *
urlpatterns = patterns(
'loose_coupling',
(r'^$', 'with_sqlalchemy.views.index'),
)
```
Normally we'd use include a urls.py file from the with_sqlalchemy app into the project's urls.py file, but we're trying to keep things simple.

Now we're going to use the SQLAlchemy ORM to create a simple table to play around with. Because the file already exists, we're going to throw the table definition into the models.py, so go ahead and open up with_sqlalchemy/models.py.

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Language(Base):
tablename = 'languages'
id = Column(Integer, primary_key=True)
name = Column(String)
extension = Column(String)
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">extension</span><span class="p">):</span>
    <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
    <span class="bp">self</span><span class="o">.</span><span class="n">extension</span> <span class="o">=</span> <span class="n">extension</span>

<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
    <span class="k">return</span> <span class="s">u&quot;Language(</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">)&quot;</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">extension</span><span class="p">)</span>

We can see that SQLAlchemy can define models very similar to those we'd define for Django. We won't delve into it here, but SQLAlchemy can also get much more low level by defining database tables and then mapping them onto a Python class. You could even skip out on using ORM completely and take advantage of their interface for constructing SQL.

And now we'll write our views.py file. First, we have some imports to begin our file with.
```
from django.shortcuts import render_to_response
import sqlalchemy, sqlalchemy.orm
from models import Base, Language
```
Then we need to setup SQLAlchemy to make our Language class available.
```
engine = sqlalchemy.create_engine('sqlite:///loose.sqlite')
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
```
The sqlite:///loose.sqlite instruction creates an SQLite database at the relative path loose.sqlite, i.e. within the with_sqlalchemy directory. SQLAlchemy makes it equally easy to specifiy an absolute path, or to create the database in memory. If we wanted to make this more Django-like, we could have specified the DATABASE_NAME setting in settings.py, and then loaded the value like this:
```
from django.conf import settings
engine = sqlalchemy.create_engine(settings.DATABASE_ENGINE)
```
Also, the Base.metadata.create_all(engine) only really needs to be done once when SQLAlchemy first creates the table (although doing it multiple times doesn't hurt anything), but that would involve creating a counter-part to the python manage.py syncdb functionality in a script, and would make the example more complex, so we're skipping that at the moment.
Next, we'll create two really simple utility functions that we'll use to populate our data:
```
def is_empty():
    return len(session.query(Language).all()) <= 0
def populate():
new_langs = [Language('Python','py'),Language('Ruby', 'rb'),
Language('Common Lisp', 'lisp'),Language('Objective-C', 'm')]
session.add_all(new_langs)
session.commit()
```
These simple functions are going to be used to populate our database with some data. It also provides a minimal example of the SQLAlchemy querying and object creation syntaxes, which are very similar to those in the Django ORM.
Finally, we need to write the index function.
```
def index(request):
    if is_empty():
        populate()
    langs = session.query(Language).all()
    return render_to_response('with_sqlalchemy/index.html',{'langs':langs})
```
In a better designed application we wouldn't need to make sure we'd created the test data each time we call the index view, and then the view would look like this:
```
def index(request):
    langs = session.query(Language).all()
    return render_to_response('with_sqlalchemy/index.html',{'langs':langs})
```
Which looks almost identical to what it would have looked like writing it with the Django ORM (just a slight syntax difference for retrieving data).
And now we need to create the with_sqlalchemy/index.html template that we are rendering. First we'll create a base template, located in loose_coupling/templates, and named base.html. It will look like this:
```
<html>
  <head>
    <title> Loosely Coupled Django </title>
  </head>
  <body>
  {% block content %}
  {% endblock %}
  </body>
</html>
```
And next we'll create the index.html template. Open up with_sqlalchemy/templates/with_sqlalchemy/index.html, and fill it in:
```
{% extends 'base.html' %}
{% block content %}
{% for lang in langs %}
  <p>{ { lang }}</p>
{% endfor %}
{% endblock %}
```
The most important thing to notice about these templates is that we are passing them instances generated by the SQLAlchemy ORM, but it looks identical to how it would look if the template was dealing with the Django ORM: the template language is ignorant of the details of the Django ORM, and can be used to anything from normal Django Model instances to normal Python dicts.
Now everything is finished, just waiting for us to test.
```
python manage.py runserver
```
Then navigate over the http://127.0.0.1:8000/. You'll see a simple display of the four programming languages and their extension.

You can grab a copy of the project here. It just so happens to be a Git repository, but you can use it without having/using Git.

Now, this example is much less transparently useful than the previous one working with Jinja2, but it still demonstrates two important points about Django. First, Django is Python, so you can do anything in Django that you could do outside of Django. Second, Django is loosely coupled, but sometimes it is loosely coupled with consequences, and decoupling some subsystems--especially the ORM--isn't painless.

As a final point, I'd like to apologize for the disservice that is done to SQLAlchemy in this entry. SQLAlchemy is an extremely full-featured and versatile system, and the usage here makes it look like a mirror image of the Django ORM. It isn't. Not by a long shot. SQLAlchemy can abstract things similarly to the Django ORM, but makes it much easier to get dirty, and also exposes more advanced functionality like connection pools.

Okay, and a final final point: I think this tutorial speaks for itself, but I wouldn't recommend decoupling from the Django ORM without an extremely compelling reason. It is the most coupled of all the subparts of Django, and certainly not trivial to replace.

For those who feel disappointed that I've been unfair to Django by not looking at using both the Django ORM and the SQLAlchemy ORM together, have heart, the next example in this series will take a look at something along those lines (but featuring a slightly more realistic situation).

I felt like I was obligated to make at least one lame alchemy related joke and/or pun. Consider the itch scratched.↩