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

M.-A. Lemburg mal at egenix.com
Wed Sep 6 20:30:34 CEST 2006


Cristian Gafton wrote:
> On Wed, 6 Sep 2006, M.-A. Lemburg wrote:
> 
>> I'm not sure I understand your comment on escaping things - you
>> normally pass the statement (with the binding parameter markers)
>> and the binding parameters separately to the database.
> 
> That's not how the MySQL and PostgreSQL bindings I looked at work. Given a 
> cursor.execute(query, args), they go through various pains to escape the 
> args tuple, then simply do "query % args" and pass the resulting string as 
> a single query without parameters to the backends.

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.

It's not efficient, though, since the database will have to parse
the complete SQL statement for each row of data you pass to
.executexxx().

>> This allows the database to create an query plan for the statement
>> and then apply the parameters to this query plan one or more times.
>>
>> The main benefit is that you don't have to do any escaping in the
>> SQL statement, which as a side-effect, also prevent the typical
>> SQL injection vulnerabilities.
> 
>> Server side cursors vs. client side cursors is usually something
>> that's implemented and managed by the database driver - why should
>> the Python programmer have to think about this detail ?
> 
> 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.

>> The Python programmer can use .fetchone() or .fetchmany() to
>> indicate whether she wants to read rows in chunks or one-by-one.
>> The Python interface can then map these requests to whatever
>> the database driver has to offer.
> 
> 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() ?!

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.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 06 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::


More information about the DB-SIG mailing list