I've always really liked the idea of Object relational mappers. I just don't like writing SQL, I find it annoying and cumbersome and with some more complex situations I have to actually draw out the connections being made to do it correctly. On the otherhand objects make sense to me so why not apply fields to attributes, rows to objects, and tables to classes. It certainly works well enough for getting the information in the first place but doing queries for the information never seens to come across in an as elegent way.

SQLAlchemy tries to provide a powerful but pythonic way to make queries on an SQL database. SQLAlchemy is interesting in that mapping is more of a secondary feature and you'll often lay out your tables and then put mappers on them instead of mapping them via their class definition. They also avoid the ActiveRecord concept which is more common in other ORM. SQLAlchemy's mechanism for querying seems powerful but also a bit difficult to use and just doesn't feel right to me. It aims to be pythonic but it just seems messy because of being unable to overload operators in python. It also tends to focus on the table instead of the mapper definitions, so if you're used to working via the mapper having to work via the table can be confusing. Some examples of SQLAlchemy queries:

s = users.select(and_(users.c.age < 40, users.c.name != 'Mary'))
run(s)
s = users.select(or_(users.c.age < 40, users.c.name != 'Mary'))
run(s)

Notice that work is being done via the table.c.col instead of via MapperClass.col, and secondly that functions like and_() and or_() have been made. These functions are because SQLAlchemy can't overload these keywords. The inability to overload isn't all of the problem though. The way the statements are seems off. I propose that an ORM would be better served to overload the iterator/generator mechanism so that a query can be used directly in a for loop, or in a list-comprehension. Consider a similar set of queries:

for a in User.select(user.age < 40 and user.name != 'Mary'):
    ... do stuff ...
users = [ u for u in User.select(user.age < 40 and user.name != 'Mary') ]
users = [ u for u in User if u.age < 40 and u.name != 'Mary' ]

So these are three potentially implementable ways of doing SQL queries in a more Pythonic way. The first and second case have been similarly implemented by someone on ASPN with their recipe for Generator expressions for database requests. The implementation is both scary and impressive. To be able to deal with non-overloadable keywords he walks the actual parse tree. The problem is of course that it's not entirely clear how safe or even how fast that is. Still he provides a foundation for generating SQL on a generator. I believe it would be possible to implement the third case at least partially without having to walk the parse tree.

I've recently been playing to see just how this could be done, and realized that there is a reasonably easy way to get a taste'' for all the compare operators being called in the if part of the list comprehension. Here is the code I threw together to prove this to myself:

class Idea(object):
        def __init__(self):
                self.contains_val = None
                self.begin = True

        def __iter__(self):
                return self

        def __contains__(self, item):
                self.contains_val = item
                if not self.begin:
                        return True
                else:
                        return False

        def next(self):
                if self.begin and not self.contains_val:
                        return self
                elif self.begin:
                        self.begin = False
                        return self.contains_val

                raise StopIteration

This will return the value of the contains operation for an expression like: [ a for a in Idea() if 'blah' in a ]

So with that in mind it would be possible to generate at least simple queries and then have the iterator return the results for them and have the compare operators simply return True in all cases (since we already know the data is correct). The function calls to do the comparisons (even though they'd just return True) might increase the time this took, and it's not entirely clear how well it would be able to deal with and, not, and or (in which case it might be necessary to generate ball park'' queries which would be comperable, though that might not work in all cases). It's also possible that the same kind of parse tree walking could be done in this case.

Iterator/Generator based SQL query generation could potentially provide a way to do database queries in the various Python ORMs that is comfortable to anyone used to doing searches on lists and dictionaries, and is an area in which there should be further development.