OK, I'm really blogging this because I had a brain aneurism mapping from SQL to SQLAlchemy and I really don't want to go through that again. SQLAlchemy is a rather good ORM for python. I use it with Turbogears so I can work with python types and to a certain extent so I can forget what database is doing all the work.
Here is the query that I wanted to run:
SELECT COUNT(gigs.venueid) AS count, venues.name FROM gigs, venues WHERE gigs.venueid = venues.id GROUP BY venues.name ORDER BY venues.name
Now I'm certainly not that comfortable with SQL but I think that is a pretty simple query with an implicit join. In techno english is says:
Give (SELECT) me a table with two columns: count and Venue Names. Fill that table FROM the gigs and venues tables adding entries WHERE the gigs venue index matches a venue index. GROUP everything by the venue name (otherwise you get a SQL error to do with counting) and make sure the table is in venue name alphabetical ORDER.
In real english just tell me how many gigs each venue has ever or ever will host. OK, so now we all know what I'm looking for but how do you get SQLAlchemy to get me that. Well it turns out to be pretty easy IF you know how (funny how that is always the case isn't it!)
select([Venue.c.name, func.count(Gig.c.venueid)],
Venue.c.id == Gig.c.venueid,
group_by=[Venue.c.name],
order_by=[Venue.c.name]).execute().fetchall()
I think it is quite readable but I didn't know func.count existed and I couldn't for some reason get anywhere with the docs. It is also the first time I had used the explicit select call. Before I had performed much simpler queries on the 'mapped' python object like:
Venue.get_by(id=100)
Venue.select(Venue.c.name.like("%Barn%"))
which return python Venue objects (one of the nice feature of ORM's). The explicit select returns an array of tuples where each tupple is (venue name, count) eg:
[(u"Dunstable Community Hall", 4), (u"Farnborough Civic Amenity Hall", 101), (u"Argonaut Dungeon", 666) ]
This makes sense as this count query isn't really returning any objects but mining the database for data.
No more complaining!