Replacing Django's ORM with SQLAlchemy

July 23, 2008. Filed under 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 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 syncdb to create the tables for our project. We won't be able to use python loaddata or python 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 gold1.

Using SQLAlchemy with Django

  1. 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 install
  2. If you are using a Python before 2.5 you'll need to install pysqlite and SQLite as well.

  3. Now lets make sure the install worked correctly.

    >>> import sqlalchemy
    >>> sqlalchemy.__version__

    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.

  4. Create a Django project and app. startproject loose_coupling
    cd loose_coupling
    python startapp with_sqlalchemy
  5. Create a couple of template directories.

    mkdir templates
    cd with_sqlalchemy
    mkdir templates templates/with_sqlalchemy
  6. Next we need to make a few simple changes in our 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.

  7. Now we'll edit the loosely_coupled/ file.

    from django.conf.urls.defaults import *
    urlpatterns = patterns(
        (r'^$', 'with_sqlalchemy.views.index'),

    Normally we'd use include a file from the with_sqlalchemy app into the project's file, but we're trying to keep things simple.

  8. 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, so go ahead and open up with_sqlalchemy/

    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)
        def __init__(self, name, extension):
   = name
            self.extension = extension
        def __repr__(self):
            return u"Language(%s, %s)" % (, self.extension)

    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.

  9. And now we'll write our 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()

    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, 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 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')]

    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():
        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).

  10. 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:

        <title> Loosely Coupled Django </title>
      {% block content %}
      {% endblock %}

    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.

  11. Now everything is finished, just waiting for us to test.

    python runserver

    Then navigate over the 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).

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