Why not just create an SQL view, and map the view as an
SQLObject? This is often a good solution for classes of problems
where query performance is more important than insert performance,
like most web apps that I have seen.
First, SQLite doesn't support Views (as I understand) and since
this is intended to be a sample that is distributed with a book
(yes, TurboGears will be appearing in another book, but the primary
subject is Ajax), I really want to stick with something that can be
installed and run quickly: hence SQLite.
Fair enough. I didn't notice that you were using SQLite :)
Mostly, I don't think this is *about* the query. The query runs
just fine. The problem seems to be that SQLObject's transactions
get all out of whack when I'm doing a real query.
Also fair. I have had lots of issues with the way that SQLObject
handles both connections, and transactions. It just seems wrong to
me, so I totally understand your gripe (and share it).
I was just trying to provide a way around the query aspect of the
problem.
Maybe one day SQLObject, or something else, will come along to
make building reporting-like queries easier than writing raw SQL,
but thats a pretty tall order.
You might want to take a look at SQLAlchemy <http://sqlalchemy.org/
>. I think it's a much more full fledged solution to the problem.
I've been working with the author Michael Bayer to add a
declarative layer (similar to SQLObject) that would make it more
attractive to many python developers.
I have looked at SQLAlchemy, and I like what I see. I have installed
it and played with it quite a bit, and have even written a few little
TurboGears test applications with it.
I find it to excel at the complex, and falter at the simple. I am
extremely excited to hear that you are working on a more declarative
SQLObject-like layer to SQLAlchemy, as it would address my *one* big
gripe with it: its just too complex for the simple stuff.
That being said, the way that SQLAlchemy handles connections and
transactions seems a lot more straightforward and easy to understand
(and follow... SQLObject is far too odd and magical in this sense).
I can't wait to see the SQLAlchemy declarative layer. Please share
it as soon as you have something to show! :)
--
Jonathan LaCour
http://cleverdevil.org
