Tag Archives: sqlalchemy

Using SQLAlchemy with Django

This post is an introduction to using SQLAlchemy and Django together to make a simple web-based asset catalogue. The examples make use of SQLAlchemy 0.4 and Django 0.97-pre.

Extensis Portfolio is digital asset management software that has been available on Macinctosh (and Windows) for donkeys years. The most expensive edition of Portfolio replaces the native data store with an SQL back-end (currently supported databases are MySQL, MSSQL and Oracle). Extensis also sells NetPublish, a web front-end to a Portfolio catalogue.

For various reasons I decided to write a custom web front-end for Portfolio using Django. As with Farmers Wife, the existing database schema was not well-suited to Django’s ORM. More than one table used a compound primary key which Django does not support (there is a page discussing how support would be implemented on the Django Wiki).

Defining models with SQLAlchemy

The meat of the application consists of two models:

  • Items represent items in the catalogue. Each picture is an Item, and has properties for things like file type, file size, path to file on disk.
  • Keywords represent keywords related to an Item, using a many-to-many relationship.

I defined two tables named item_table and keyword and a third that is used to hold the many-to-many relationship:

item_table = Table('item_table', metadata,
    Column('Record_ID', Integer(), primary_key=True, nullable=False, key="id"),
    Column(u'Filename', MSString(length=249), key="filename"),
    Column(u'Path', MSString(length=249), key="path"),
    Column(u'Created', MSDateTime(timezone=False)),
    Column(u'Last_Modified', MSDateTime(timezone=False)),
    Column(u'File_Size', MSInteger(length=11)),
    # Additional columns omitted here

keyword_table = Table('keyword', metadata,
    Column('Record_ID', Integer(), primary_key=True, nullable=False, key="id"),
    Column(u'Keyword', MSString(length=249), nullable=False, key="keyword"),

item_keyword_table = Table('Item_Keyword', metadata,
    Column(u'Item_ID', MSInteger(length=11), ForeignKey('item_table.id'), primary_key=True, nullable=False, default=PassiveDefault(u'0')),
    Column(u'Keyword_ID', MSInteger(length=11), ForeignKey('keyword.id'), primary_key=True, nullable=False, default=PassiveDefault(u'0')),

Of note here is how one can rename the table columns for use in the table definition (roughly equivalent to the db_column field option in Django). I prefer using the short name id for the primary key in place of the original designer’s choice of Record_ID.

Putting primary_key=True on more than one column for the item_keyword_table tells SQLAlchemy to use a compound primary key. And notice the ForeignKey relationship uses our customized column names for the related tables.

Then we need classes to associate the Item and Keyword objects with the underlying tables,

class Keyword(object): pass
class Item(object): pass

mapper(Keyword, keyword_table)
mapper(Item, item_table, properties=dict(
    keywords = relation(Keyword, secondary=item_keyword_table, backref="items", lazy=False, order_by=[keyword_table.c.keyword]),

The second statement above added a keywords property to an Item object to define the many-to-many relationship with a Keyword object. Specifying lazy=False causes the keywords related to an item to be fetched when the item itself is loaded – the default behaviour is to leave the related queries until the related object is accessed.

Making models more Django-friendly

The mapped classes for Keyword and Item behave in ways similar to a Django model. Any features that are missing can be easily put in place.

One can provide the pk shortcut for a model using a Python property:

class Keyword(object):
    # Extra pk property to access primary key
    def pk(self):
        return self.id
    pk = property(pk)

And get_absolute_url is just as straight-forward:

class Item(object):
    # Match a URL pattern like '^(?P<item_id>\d+)/$'
    def get_absolute_url(self):
        return reverse('catalogue_item', kwargs={'item_id':unicode(self.pk)})

These extra methods help SQLAlchemy model objects behave almost interchangeably with Django model objects in your template and view methods.

Using the Django paginator class with SQLAlchemy Query objects requires one to override a single method calculating the total number of hits:

from django.core.paginator import ObjectPaginator as DjangoPaginator

class ObjectPaginator(DjangoPaginator):
    """SQLAlchemy compatible flavour of Django's ObjectPaginator."""
    def _get_hits(self):
        if self._hits is None:
            self._hits = self.query_set.offset(0).limit(0).count()
        return self._hits

Using the models in views

Unfortunately there’s no simple means for using Django’s generic view methods (or the admin application) with SQLAlchemy’s query methods. Django’s views rely on accessing model._meta from the queryset and use an incompatible query syntax (albeit a syntax with similar intent).

But there’s nothing to stop you using SQLAlchemy query sets in custom views and in the template context.

The following view method shows the object detail page for an Item object; the primary key of the item is used in the URL and passed to the view as item_id:

# Session, Item are imported from a module with the model definitions
def item(request, item_id):
    """Detail page for a Portfolio item."""
    item_id = int(item_id)
    session = Session()
    item = session.query(Item).filter_by(id=item_id).one()
    return render_to_response("catalogue/item.html", {'object':item})

To complete the example one ought to handle the situation where there is not exactly one matching item. If SQLAlchemy throws sqlalchemy.exceptions.InvalidRequestError: Multiple rows returned for one() the view should in turn throw the corresponding Django exceptions so that the middleview machinery can operate as normal.

The future

Many parts of the API changed (or were deprected for compatibility) when SQLAlchemy progressed from 0.3 to the current 0.4 branch, and it is interesting how some of those changes seemed to be bringing SQLAlchemy’s ORM approach closer to Django’s ORM. I expect more similarities to appear on both sides in future releases.

The Django/SQLAlchemy branch has the explicit goal of combining the two without changing the Django model API, which would eliminate the need for any of the code in this post.

Until that gets merged to trunk, I hope you find this useful.

Choosing SQLAlchemy over Django

In the DJUGL post-meet pub chat Simon Willison was curious about people’s experiences combining Django with SQLAlchemy. I’ve used SQLAlchemy’s ORM with Django in two projects; on both occasions I quickly chose to substitute Django’s ORM with SQLAlchemy’s because I was dealing with an existing SQL schema which I could not alter and which did not fit well with Django’s ORM.

The first project was to produce reports on data exported by a scheduling application called Farmers Wife. The database has three dozen tables or so, with some tables using compound keys, other tables lacking primary keys altogether and one relation in particular using combined parts of columns to refer to other tables.

I gave an involuntary, rather hysterical giggle when I discovered that particular corner of the data model.

I tried mapping the data using Django’s models but quickly found that the schema was simply too irregular to fit Django’s requirements for meaningful relations between tables (this project started a bit before the 0.96 branch was released).

I decided to try SQLAlchemy. The immediate benefit in using SQLAlchemy was its introspection of table definitions allowed me to start mapping objects with very few lines of code yet having column data available as properties of objects.

N.B. The examples in this post are for SQLAlchemy 0.3 (current release is 0.4.6).

Here is a MySQL definition for a table named objects_users3:

mysql> describe objects_users3;
| Field                | Type        | Null | Key | Default | Extra |
| id                   | varchar(32) | YES  | UNI | NULL    |       | 
| name                 | varchar(64) | YES  |     | NULL    |       | 
| icon                 | varchar(32) | YES  |     | NULL    |       | 
| buy_hour             | float(11,2) | YES  |     | NULL    |       | 
| sell_hour            | float(11,2) | YES  |     | NULL    |       | 
| buy_day              | float(11,2) | YES  |     | NULL    |       | 
| sell_day             | float(11,2) | YES  |     | NULL    |       | 
| daybased             | tinyint(4)  | YES  |     | NULL    |       | 
| ref                  | varchar(64) | YES  |     | NULL    |       | 
| password             | varchar(32) | YES  |     | NULL    |       | 
| firstname            | varchar(32) | YES  |     | NULL    |       | 
| lastname             | varchar(32) | YES  |     | NULL    |       | 
| email                | varchar(64) | YES  |     | NULL    |       | 
| active               | tinyint(4)  | YES  |     | NULL    |       | 
| permission           | int(11)     | YES  |     | NULL    |       | 
| textnote             | text        | YES  |     | NULL    |       | 
| tel_home             | varchar(64) | YES  |     | NULL    |       | 
| tel_work             | varchar(64) | YES  |     | NULL    |       | 
| tel_cell             | varchar(64) | YES  |     | NULL    |       | 
| stock_inform         | tinyint(4)  | YES  |     | NULL    |       | 
| lib_write            | tinyint(4)  | YES  |     | NULL    |       | 
| mediaorders          | tinyint(4)  | YES  |     | NULL    |       | 
| muleaccess           | tinyint(4)  | YES  |     | NULL    |       | 
| days_in_liueu_offset | int(11)     | YES  |     | NULL    |       | 
| reportaccess         | tinyint(4)  | YES  |     | NULL    |       | 
| aux_hour             | float(11,2) | YES  |     | NULL    |       | 
| aux_day              | float(11,2) | YES  |     | NULL    |       | 
27 rows in set (0.20 sec)

27 different rows, 27 different attributes I would need to define in my Django models.py in order to access all the values.

Here’s how to use SQLAlchemy to introspect a table definition for the same table:

from sqlalchemy import BoundMetaData, Column, Table, mapper

metadata = BoundMetaData("mysql://name:passwd@hostname/DatabaseName")

objects_users3 = Table('objects_users3', metadata,
    Column('id', String(32), primary_key=True),

And we’re done.

The next piece of the puzzle is to map a Python class to this table:

class ObjectUser3(object):

mapper(ObjectUser3, objects_users3)

And we’re done.

These few lines give us a class that provides similar functionality to that of django.db.models.Model. You can use the class to create a new ObjectUser3 or to retrieve one or more existing ObjectUser3 objects with column filters, etc.

In SQLAlchemy 0.3 one uses the ORM within the context of a session, which has a query method that returns an object that can be used to retrieve the objects from the database:

>>> session = create_session()
>>> q = session.query(ObjectUser3)
>>> users = q.all()
>>> len(users)
>>> me = q.get_by(name='David')
>>> me.lastname

Note how one can specify the column for filtering the results using named arguments, just like Django.

Like Django, SQLAlchemy provides means for defining relationships and allows one to add whatever additional methods one chooses to the model class. Unlike Django, SQLAlchemy provides a comprehensive (if somewhat daunting) set of tools for generating SQL queries, allowing one to move between manipulating the SQL table data and manipulating Python objects constructed from that data without having to manually write any SQL at all.

And therein lies the major difference between SQLAlchemy and the Django ORM: the former is intended to be a toolkit for SQL, whereas Django provides a system for storing Python objects and exposes relatively little of its query construction tools.

SQLAlchemy 0.4 improves things for an existing developer and for a developer coming from Django. The sessions are simpler to work with. The Query objects have changed to support slicing syntax like Django’s QuerySets.

I find Django’s models simpler to write, easier to understand than the equivalent SQLAlchemy approach. The business of defining relations between models exposes a little more of the underlying SQL concepts when working with SQLAlchemy, but then that’s precisely why it was such a great choice for this project; SQLAlchemy allows one to customize its default object mapping behaviour in ways that Django does not. For example one of the more mind-bending features allows one to specify a custom class for handling collections of related objects, so what would be a simple list could just as easily be treated as a dictionary where the key is determined by a column’s value.

SQLAlchemy combines the convenience of a good ORM engine with an incredibly flexible SQL abstraction. For gnarly databases it rocks.

I want to write more about how Django and SQLAlchemy fit together, but I’ll leave that to a discussion of the second project.