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

[Cristian Gafton]

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

>> 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.
>
> I suppose that the authors who did had good reasons in doing so.

As I have just mentioned at the beginning of this thread, I started off 
with PyGreSQL, which does not support bind parameters and changed it. I 
assume in this particular case it's  because of historical reasons - older 
versions of PostgreSQL did not have support for bind parameters.

I can not figure out a single good reason why currently none of the MySQL 
bindings support bind paramaters and rely instead on awful parameter 
escaping techniques.

> Sounds rather specific to a certain database backend. Most databases
> we work with (MS SQL Server, Oracle, SAP/Max DB, Sybase, DB2 to name
> a few) tend to postpone execution and sending of the results until
> the very last moment.

MySQL can do that too, but then the query takes over the connection - you 
can not run other queries while you retrieve the result set using the 
so-called "use result" strategy. Which is why in most cases probably it is 
safer to download everything in one shot and free up the connection for 
other work as soon as possible.

> In database applications you rarely want huge result sets
> in one go. You typically try to read in the data in chunks where the
> actual fetching of the chunks is done using multiple SQL statements,

That's my point exactly - the only one that knows what to expect back from 
the backend is the application writer, because on the more popular 
databases (like MySQL and PostgreSQL), fetching results in chunks adds a 
sizeable cost in extra rountrips and speed of retrieving the results.

>>> 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.
>
> If that's the case for PostgreSQL, perhaps you need to add a
> non-standard method to choose the fetch strategy before doing
> the .executexxx() ?!

Okay, that's what I was proposing with
 	cursor = db.itercursor()

which would set up the cursor to iterate through the results, in a similar 
fashion to what dict.iteritems() does compared to dict.items(). I take it 
you agree with that approach then?

Cristian
-- 
Cristian Gafton
rPath, Inc.