On Friday, October 8, 2004, at 06:40 PM, Ian Bicking wrote:
> Colin Stewart wrote:
>> Using the db driver's quoting would also mean that the SQL can be
>> cached as prepared statements by the DB. For some (e.g. Oracle) that
>> can lead to a pretty significant speed up in itself.
> I don't know enough about prepared statements to know if it would
> help. If it's possible to prepare, up front, three or four statements
> for every class, then it could help. If you have to prepare a
> statement, then use it several times, then prepare another statement,
> it's unlike to help, since SQLObject doesn't know enough to predict
> what statements are going to be used in sequence often enough to help.
> Also, the postgres and mysql drivers (and probably sqlite) don't have
> any prepared statements, and do all the quoting on the client side.
> I'm most interested in those backends, so there's not a huge amount to
> be gained. They probably do the quoting in C, though, with some
> performance gain there.
My interest is in PostgreSQL, so I'll comment on that. psycopg claims
to support the DB-API v2, and thus should have prepared statements.
So here's a unit test that demonstrates, perhaps, a decent way of
hooking up bare SQL to a method (untested, I stayed up till 4am last
night playing Mao, and I'm off to Heathrow in 10 minutes, so please
forgive me if this code is a bit dirty. :) This may need to be molded
around slightly to solve your problem but I hope it illustrates an
interesting new idea and gets some thinking going on about this problem:
def __init__(self, id, name, address):
self.id = id
self.name = name
self.address = address
def __init__(self, results):
self.results = results
for r in self.results:
yield USCitizen(r, r, r)
usaddresses = SQLMethod(
sql = """\
SELECT p.id, p.name, a.address
FROM person p, address a
WHERE p.id = a.personid
AND a.country_code = 'US'""",
result = USAddressesResult)
usaddresses = classmethod(usaddresses)
uscitizens = list(Person.usaddresses())
self.assertEquals(uscitizens.name, "Ford Prefect")
self.assertEquals(uscitizens.address, "Somewhere near
The benefits gained from this feature would be:
1. The speed of working with raw SQL.
2. Being able to get a resultset that actually does what you need
(since the current concept that "an element in a result set maps to one
row in a table" often doesn't make sense, as each row returned from a
SELECT query is often *not* a row in a table.)
3. You could, of course, use placeholders in the SQL statement and then
the SQLMethod method would accept that many params.
Thoughts, comments, feedback welcome.