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][sa04] and [Django 0.97-pre][django097].

[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][farmerswife], 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][wiki] 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\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][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][django-sqlalchemy] 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.

[SQLAlchemy]: http://www.sqlalchemy.org/
[Django]: http://www.djangoproject.com/documentation/
[sa04]: http://www.sqlalchemy.org/docs/04/
[django097]: http://www.djangoproject.com/
[NetPublish]: http://www.extensis.com/en/products/asset_management/product_information.jsp?id=2020
[wiki]: http://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys
[Extensis Portfolio]: http://www.extensis.com/portfolio
[farmerswife]: http://reliablybroken.com/b/2008/06/choosing-sqlalchemy-over-django/
[exceptions]: http://www.djangoproject.com/documentation/db-api/#get-kwargs
[django-sqlalchemy]: http://gitorious.org/projects/django-sqlalchemy/