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() ?
- Escape LIKE condition ?
- 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