thraxil.org:

raw queries with SQLObject

by anders pearson Mon 18 Apr 2005 16:29:42

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:

# 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.</code>

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

comments

Actually count(), max() and avg() are available. I've not used them for a while, so i don't remember exactly how to use them .. But they're sitting somewhere ;)

yeah, you can do stuff like Table.select().max("some_column"), but you can't build anything much more complex than that. the specific query that triggered my figuring out how to bypass SQLObject was one like "SELECT n.id,count(k.id) as count FROM ... GROUP BY n.id ORDER BY count DESC LIMIT 5" but it's also come in useful when i wanted to take advantage of some of postgresql's date formatting methods or access a database that uses stored procedures. in general, it just gives me a warm fuzzy feeling to know that as a last resort, i can always drop back to plain old SQL.

Thanks a lot for this! Im pretty new to python, sqlobject and sql and while practicing I am stuck on how to return specific columns from different tables using sqlobject 'queries' (ie i need something like select product.id, product.name, product.variant, stock.quantity, price_log.price, price_log.price*stock.quantity AS amount from stock,product,outlet,price_log where outlet.name = 'MAIN' AND outlet.id = stock.outlet_id AND product.id = stock.product_id AND price_log.product_id = product.id AND price_log.current = True ). Though I am not sure if I will be able to find the sqlobject way soon. At least for now I get to solve the problem at hand.

NameError: name 'Node' is not defined

Once you have the list of tuples is there an easy way to convert them into a list of objects that you defined in the model?


formatting is with Markdown syntax. Comments are not displayed until they are approved by a moderator. Moderators will not approve unless the comment contributes value to the discussion.

namerequired
emailrequired
url
remember info?