Though I haven't been really unhappy with SQLObject as my ORM choice, the urge to switch to SQLAlchemy for database access, referred to as the best Python solution out there, has continuously been growing.

The Bad

A printout of the SQLAlchemy tutorial by Robin Munn has been on my things-to-read stack for a while and I took a look at it severeal times - but somehow, it was still too much at once to grasp, even with years of working with SQL and a few with SQLObject's ORM. Also, I had something that did the job without such offending complexity. And even today I have to struggle against passing out when trying to find something in SQLAlchemy's documentation.

The Good

Then, probably when I took my sporadic peek at the new stuff in the Cheeseshop, I discovered Elixir. It provides a new attempt to build an ORM on top of SQLAlchemy, and it is one that closes the gap to SQLObject's interface. Finally, that seemed to be what I've been looking for quite some time.

As time passed, I based a few small web apps on it. But now, I eventually managed to switch homework, this very site, to using the SQLAlchemy/Elixir team.

I do like the way queries are built with SQLAlchemy (which is not handled by Elixir, but attached to entities created with it). And since Michael Bayer introduced methods for forwards-compatibility with the upcoming 0.4 release (currently in beta) for the 0.3 branch in version 0.3.9 (0.3.10 is the latest at the time of this writing), I'm in the comfortable position to learn the new query methods right away and forget about the deprecated ones I have used only a little so far. Very nice.

And the Ugly

Been used to SQLObject's set method, which allows for setting multiple entity attributes at once according to data passed as a dictionary, I was a little bit surprised to not find something like that in Elixir. It's similar to how the constructor works in both SQLObject and Elixir, but it updates an entity instead of creating it.

After desperatly searching the net, I have been enlightened that multiple attribute assignments to a single entity will be combined into a single UPDATE statement, but no shortcut is available for doing those at once. I felt a little disappointed, but that issue could at least quite easily be solved with this short piece of code:

from elixir import Entity


# Monkey-patch a SQLObject-like `set` method onto Elixir's `Entity` class.

def set_dict(self, **mapping):
    """Update multiple attributes with the mapping's data."""
    for attr, value in mapping.iteritems():
        setattr(self, attr, value)

Entity.set = set_dict


# Your entity definitions follow here.  Since the standard class has been
# extended, but not subclassed, you can still inherit from `Entity`.  Thus,
# there is no need to create unusual or modify usual entity definitions.

While the simple sites were no big deal, I did encounter serious headaches with homework. Changing syntactic differences was a snap, but when it came to modelling my tags system (which uses simple many-to-many relationships), it got dark.

Unfortunately, Elixir's has_and_belongs_to_many DSL statement (by the way, I'm referring to the yet current 0.3.0 release, with 0.4 on the way) has some rigid views. Although I was able to define the name of the intermediate tables to use for joining, Elixir didn't allow to set those for the column names of the primary keys. Instead, <table name>_<primary key> is used. My tables are named like news, links and tags, with the intermediate tables for tagged items called news_tags and links_tags and their two attributes being news_id/link_id and tag_id. I couldn't make Elixir to recognize the last two, I still encountered SQLErrors missing links_id and tags_id.

On the Elixir mailing list appeared suggestions on how to solve this by stepping downwards and using SQLAlchemy directly or by creating another Entity to emulate the intermediate table -- which is accompanied by additional syntax to access the data as expected when using the has_and_belongs_to_many approach. Therefore that's no good solution to me (and adding entity properties to do this, isn't either).

In the end, I renamed the tables. I can live with the change and the new names, but I'm not too happy with the fact that it's just some kind of workaround. And there might be many situations in which this is not acceptable at all.

Prospect

We'll see what SQLAlchemy and Elixir will bring with their upcoming 0.4 releases. The latter should not only be adjusted to work with the former, but will also introduce a new entity definition syntax that is more like the one used by other Python ORMs. Let's hope that won't cut the possibilities the current statement syntax allowed for. I'm still looking forward to implement acts_as_taggable (as described by Jonathan LaCour, one of Elixir's authors) or something similar for this site. Or, maybe, an approach like that will be already included in the interesting extensions that will ship with the next version.

Update (04-Jun-2012): The referenced set_dict code snippet was gone for some years. I dug it out of my version control system and inlined it into this article.