[DB-SIG] New take on PostgreSQL bindings for Python

[Cristian Gafton]

On Wed, 6 Sep 2006, M.-A. Lemburg wrote:

> If the database drivers don't provide a mechanism to pass in
> statements and parameters separately, that's a possible way to
> implement bound parameters.

But the database drivers in most cases *do* provide such a mechanism. I 
tend to blame the DB API's lack of clear specification on how to handle 
bind parameters that has made some take the "easy" way out.

>> Because it is only the programmer that knows "I am expecting 1 million
>> rows out of this query, you'd better now load it all up in RAM at once"
>
> Right, but in that case, the programmer would just do a .fetchall(),
> so the interface can infer this from the type of .fetchxxx() method.

You're missing the point. Usually a programmer does something like:
 	cursor.execute(...)
 	cursor.fetchXXX()

The problem is that in some cases the entire result set is downloaded in 
the client RAM before returning from the *execute* call. the fetchXXX 
calls come too late to infer anything. You have to know before the execute 
if you want to open up a server side cursor or you want the execute call 
to return and malloc a whole bunch of memory on your local stack.

>> Not all database drivers are rich enough, or smart enough, or sufficiently
>> envolved (MySQL and PostgreSQL are such examples); you either retrieve all
>> results at once at a cost of client memory or you retrieve in chunks using
>> FETCH at the cost of speed. Again, it is the application programmer that
>> knows which is appropiate for which case.
>
> Maybe I'm missing something, but doesn't the programmer let the
> database module know by using either .fetchmany() or
> .fetchall() ?!

It doesn't. The C level APIs of the databases are written in such a way 
that at the end of the low level on-the-wire execute() call you are making 
you get returned the entire result set. There is nothing fetchXXX can do 
to help you there.

> Database drivers normally do not fetch any rows from a result set
> until you actually make a call to do so. In some cases, they don't
> even execute the SQL statement until you do.

You've probably been spoiled by Oracle...

Cristian
-- 
Cristian Gafton
rPath, Inc.