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 JOIN
s 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.
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).
comments
Philippe Normand - Tue 19 Apr 2005 04:15:25
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 ;)
anders pearson - Tue 19 Apr 2005 08:22:58
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.Dax Reyes - Sun 05 Jun 2005 11:08:06
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.
seanharper - Tue 18 Jul 2006 21:09:37
NameError: name 'Node' is not defined
no name - Fri 24 Oct 2008 23:28:28
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?