Fork me on GitHub

SQLAlchemy, a brave new World

SQLAlchemy becomes an essential technology for any python developper interacting with relational databases. As a Django developper, I have sat on my laurels for long, being completely satisfied with the Django ORM. It was time to explore its challenger.

First, before it sounds like I crush my favorite framework : when Django ORM was developped, there was no SQLAlchemy, or almost no good python ORM at all.

Here are some of the things you might want to know first:

  • Django ORM documentation is clean and well organized
  • Dango ORM was not really meant to be used outside django apps
  • Django ORM has limitations when it comes to Model Inheritance
  • Django ORM does not manage models migrations without extra stuff like South
  • Django ORM support for multiple databases was introduced in version 1.2
  • Django ORM does not always manage connection pooling (e.g. with Oracle)
  • SQLAlchemy is light and framework independant
  • SQLAlchemy is stripped to a minimum set of (clean and well-implemented) features
  • SQLAlchemy is very flexible and supports mapping of objects using declarations or metadata
  • SQLAlchemy documentation is bloated (API reference is mixed-in with long explanation and use cases)
  • SQLAlchemy requires a better knowledge of advanced python mechanisms and architecture

In order to explore SQLAlchemy (SA) I created pyfspot (sources are on github): a very small application to manage the database of the F-Spot photo manager. It is not supposed to save lives, but that will at least be:

  • a pretext for me to dive into the API
  • a small and useful tool
  • an example of SA in action for any developper interested
  • a base for a full F-Spot management application (</dreamer>)

I discovered a few concrete things:

# get() does not throw exception
tag = session.query(Tag).get(5)
# filter().one() does ...
try:
    tag = session.query(Photo).filter_by(id=1337).one()
except sqlalchemy.orm.exc.NoResultFound:
    # d'oh!
    pass
  • Invert condition like Django exclude() ?
session.query(Photo).filter(~Photo.base_uri.endswith('/'))
  • Escape LIKE condition ?
Photo.base_uri.like('%\\%%', escape="\\")
  • Intersections of many-to-many ?
# A tag
tag = session.query(Tag).filter_by(name='foo').one()
# A set of photos
photoset = session.query(Photo).filter(~Photo.base_uri.endswith('/'))
# intersect() won't work
photoset.intersect(tag.photos)
AttributeError: 'InstrumentedList' object has no attribute 'c'
# Use any()
photoset.filter(Photo.tags.any(id=tag.id))

Well, those were my first steps. As expected, it did not feel so well to relearn how to walk. But at least I am now ready to get my bearings in SQLAlchemy's world.

Original post at Makina Corpus

Comments !

social