I've written about the idea of using List Comprehensions to do SQL queries in Python before. I've recently been learning about and fiddling with Erlang, and I ran into something quite amazing. Erlang includes a module called Query List Comprehensions (QLC). QLC seems to be exactly what I was wanting to see in Python, and in some ways even cooler. It is even possible to generate the equivalent of a view using rules with Erlang's QLC.

I think examples best highlight the power of this model:

[ P.name || P <- table(person) ] % Names for all people
[ P.name || P <- table(person), P.age > 30] % Names for all people over 30
[ P.name || P <- table(person), A <- table(address), P.id = A.person, 
  A.zip = 10001 ] % All people whose address is in zipcode 10001

Pythonists will see their familar List Comprehension with a slightly modified syntax, in Python they might look like:

[ p.name for p in table(person) ]
[ p.name for p in table(person) if p.age > 30 ]
[ p.name for p in table(person) for a in table(address) if p.id == a.person and a.zip == 10001 ]

Especially cool is that qlc is not specific to any one database engine but instead simply requires that 1-4 functions be implemented for any object that implements an iterator. QLC can either use a (slower) next() and test method where it iterates through every entry, or it can use a lookup_fun which implements a faster lookup method. This is where an SQL query generator could be placed, allowing for much faster lookups.

Rules are a very cool feature that makes it possible to implement views. The same place where a view would be considered useful it is possible to use a Rule. Rules can make for more terse syntax and even increase legability. Here is an example of a rule:

in_zipcode_10001(P, Person) :-
    P <- table(person),
    A <- table(address),
    A.zip = 10001,
    P.id = A.person.
[ P.name || P <- rule(in_zipcode_10001) ]
[ P.name || P <- rule(in_zipcode_10001), P.age > 30 ]

I certainly won't be turning in my Python badge anytime soon, but I'm really impressed with some of the database querying capabilites presented in Erlang, and I think it's now more important then ever to try to implement similar functionality in the Python ORM world.