Tuesday, July 15, 2008

Flexible SQLObject queries

Although SQLObject is considered an ORM (Object Relational Mapper), it can still be used for traditional SQL queries. The question is, why bother adding this ORM overhead when the queries can be executed directly by a SQL library?

For anything but the most trivial queries, SQL syntax is still the superior method to manipulate data sets. When using a SQL library in Python, without an ORM, the result sets need to be constructed. This boiler plate code can be handled by SQLObject while still having the ability to use more elegant, or extremely complex in some cases, SQL. For example, if you have defined a couple SQLObject classes, blogAccount, and blogEntry, we can do somthing similar to:

result=blogEntry.select("""blog_account.id=blog_entry.blog_account_id
AND blog_account.user_id=%s"""%getCurrentUserID(),\
clauseTables=['blog_account'])

This result set will contain a list of blogEntry instances. This is useful because no code was needed to construct the result set. This is obviously not the worlds most complex query, but it does illustrate the idea that fancy SQL queries can still be combined with the concept of an ORM.