raw queries with SQLObject

By anders pearson 18 Apr 2005

i use SQLObject for all my programming that involves a database. i think it hits a real sweet spot on the simplicity/power curve.

using it basically involves creating some classes that inherit from SQLObject for each table in the database and giving them each attributes that correspond to the columns of the tables. it doesn’t really let you forget that you’re using a database to store your stuff, but it saves you from having to hand code all the simple SELECT and UPDATE type SQL queries. i really like this approach; it’s more of a representation of database tables and rows as objects than an attempt to force fit objects into a relational database.

it’s got a powerful enough query syntax to cover about 95% of anyone’s needs. for four of the remaining five percent, the ability to pass in raw SQL for the WHERE clause covers you. so you can do LEFT OUTER JOINs to your heart’s content when you really need to.

what it’s missing out of the box though, is the ability to specify which columns get fetched. since it returns a list of objects instead of raw results, it will always fetch the columns it needs to build those objects. 99% of the time that’s fine. but it prevents you from using a lot of SQL’s built in functions like COUNT(), MAX(), or AVG(), which are useful for various reporting type queries and for doing really complex queries with GROUP BY and HAVING constructs.

but all hope is not lost!

i had to dig around in the source code a bit to figure out exactly how to do it, but it’s actually pretty easy to directly access SQLObject’s internal database connection methods and make raw queries directly. it just looks like:

:::python
# Node is a class that inherited from SQLObject
results = Node._connection.queryAll(
    """SELECT [some really complex and nasty SQL
    that needs aggregate functions, etc.]""")
# results will then be a list of tuples,
# one tuple per row returned.

there’s also a queryOne() method that just returns one tuple if that’s all you want.

the only thing you don’t want to do is use that stuff for updates, inserts, or deletes since it bypasses SQLObject’s caching and you could end up with weird inconsistencies between what’s in the database and what’s in memory. use SQObject’s regular interface for anything that modifies the database.

anyway, this post is here so google will find it and other people wanting to do raw queries won’t have to dig through the source code (although it’s pretty easy to understand if you’re so inclined).

Tags: programming python database sqlobject