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:

• Django inspectdb equivalent ?
• Django fixtures equivalent ?
• Django model.DoesNotExist ?

# 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

#python, #django, #sqlalchemy - Posted in the Dev category