In the DJUGL post-meet pub chat Simon Willison was curious about people’s experiences combining Django with SQLAlchemy. I’ve used SQLAlchemy’s ORM with Django in two projects; on both occasions I quickly chose to substitute Django’s ORM with SQLAlchemy’s because I was dealing with an existing SQL schema which I could not alter and which did not fit well with Django’s ORM.
The first project was to produce reports on data exported by a scheduling application called Farmers Wife. The database has three dozen tables or so, with some tables using compound keys, other tables lacking primary keys altogether and one relation in particular using combined parts of columns to refer to other tables.
I gave an involuntary, rather hysterical giggle when I discovered that particular corner of the data model.
I tried mapping the data using Django’s models but quickly found that the schema was simply too irregular to fit Django’s requirements for meaningful relations between tables (this project started a bit before the 0.96 branch was released).
I decided to try SQLAlchemy. The immediate benefit in using SQLAlchemy was its introspection of table definitions allowed me to start mapping objects with very few lines of code yet having column data available as properties of objects.
Here is a MySQL definition for a table named
mysql> describe objects_users3; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | id | varchar(32) | YES | UNI | NULL | | | name | varchar(64) | YES | | NULL | | | icon | varchar(32) | YES | | NULL | | | buy_hour | float(11,2) | YES | | NULL | | | sell_hour | float(11,2) | YES | | NULL | | | buy_day | float(11,2) | YES | | NULL | | | sell_day | float(11,2) | YES | | NULL | | | daybased | tinyint(4) | YES | | NULL | | | ref | varchar(64) | YES | | NULL | | | password | varchar(32) | YES | | NULL | | | firstname | varchar(32) | YES | | NULL | | | lastname | varchar(32) | YES | | NULL | | | email | varchar(64) | YES | | NULL | | | active | tinyint(4) | YES | | NULL | | | permission | int(11) | YES | | NULL | | | textnote | text | YES | | NULL | | | tel_home | varchar(64) | YES | | NULL | | | tel_work | varchar(64) | YES | | NULL | | | tel_cell | varchar(64) | YES | | NULL | | | stock_inform | tinyint(4) | YES | | NULL | | | lib_write | tinyint(4) | YES | | NULL | | | mediaorders | tinyint(4) | YES | | NULL | | | muleaccess | tinyint(4) | YES | | NULL | | | days_in_liueu_offset | int(11) | YES | | NULL | | | reportaccess | tinyint(4) | YES | | NULL | | | aux_hour | float(11,2) | YES | | NULL | | | aux_day | float(11,2) | YES | | NULL | | +----------------------+-------------+------+-----+---------+-------+ 27 rows in set (0.20 sec)
27 different rows, 27 different attributes I would need to define in my
models.py in order to access all the values.
Here’s how to use SQLAlchemy to introspect a table definition for the same table:
from sqlalchemy import BoundMetaData, Column, Table, mapper metadata = BoundMetaData("mysql://name:passwd@hostname/DatabaseName") objects_users3 = Table('objects_users3', metadata, Column('id', String(32), primary_key=True), autoload=True, )
And we’re done.
The next piece of the puzzle is to map a Python class to this table:
class ObjectUser3(object): pass mapper(ObjectUser3, objects_users3)
And we’re done.
These few lines give us a class that provides similar functionality to
django.db.models.Model. You can use the class to create a new
ObjectUser3 or to retrieve one or more existing ObjectUser3
objects with column filters, etc.
In SQLAlchemy 0.3 one uses the ORM within the context of a session, which
query method that returns an object that can be used to retrieve
the objects from the database:
>>> session = create_session() >>> q = session.query(ObjectUser3) >>> users = q.all() >>> len(users) 17 >>> me = q.get_by(name='David') >>> me.lastname 'Buxton'
Note how one can specify the column for filtering the results using named arguments, just like Django.
Like Django, SQLAlchemy provides means for defining relationships and allows one to add whatever additional methods one chooses to the model class. Unlike Django, SQLAlchemy provides a comprehensive (if somewhat daunting) set of tools for generating SQL queries, allowing one to move between manipulating the SQL table data and manipulating Python objects constructed from that data without having to manually write any SQL at all.
And therein lies the major difference between SQLAlchemy and the Django ORM: the former is intended to be a toolkit for SQL, whereas Django provides a system for storing Python objects and exposes relatively little of its query construction tools.
SQLAlchemy 0.4 improves things for an existing developer and for a
developer coming from Django. The sessions are simpler to work with. The
Query objects have changed to support slicing syntax like Django’s
I find Django’s models simpler to write, easier to understand than the equivalent SQLAlchemy approach. The business of defining relations between models exposes a little more of the underlying SQL concepts when working with SQLAlchemy, but then that’s precisely why it was such a great choice for this project; SQLAlchemy allows one to customize its default object mapping behaviour in ways that Django does not. For example one of the more mind-bending features allows one to specify a custom class for handling collections of related objects, so what would be a simple list could just as easily be treated as a dictionary where the key is determined by a column’s value.
SQLAlchemy combines the convenience of a good ORM engine with an incredibly flexible SQL abstraction. For gnarly databases it rocks.
I want to write more about how Django and SQLAlchemy fit together, but I’ll leave that to a discussion of the second project.