sfmagic.org Rewrite: Some Basic Queries

18:13 Mon 24 Dec 2007
[, , ]

It turns out that adding multiple WHERE clauses with SQLAlchemy’s SQL Expressions is very easy—you just tack them on using the where() function.

players_call = players_table.select()
players_call = players_call.where(players_table.c.id==request.GET.get('id'))
players = conn.execute(players_call)

That’s exactly what I would want from a syntax designed to make query generation a lot simpler.

I’m not sure about the efficiency of this approach versus explicit joins, but to get a list of the data about each tournament a player has played in (and the results for that player for each tournament) I’m doing this:

results_call = select([results_table, players_table, tournaments_table])
results_call = results_call.where(results_table.c.playerid==request.GET.get('id'))
results_call = results_call.where(players_table.c.id==results_table.c.playerid)
results_call = results_call.where(tournaments_table.c.tournament_id==results_table.c.tournament_id).apply_labels()
results = conn.execute(results_call).fetchall()

The first line grabs the three tables and produces every combination of each row for each of the rows in the other tables (something rather close to junk, in other words).

The second line restricts the playerid values in the results_table results to those that match the requested id.

The third and fourth lines constrain the results, so instead of all combinations from the three tables, we only get the combinations that we want, where the player ids match and the tournament ids match. Because there two tables use the same column name, ‘tournament_id’, we use the apply_labels() SQLAlchemy function to automatically put tablename_ in front of each column name thereafter.

The last line executes the select.

Those lines are rather easier than the SQL I wrote by hand in 2004, when I knew barely any SQL and was using temporary tables and a bunch of IN clauses to make the player query work. I suspect that the SQLAlchemy approach isn’t going to be any slower than that, although it could be slower than competently-written SQL. Hopefully, once I move to a better server and a better version of MySQL, that issue won’t arise.

Leave a Reply