[DB-SIG] New take on PostgreSQL bindings for Python
Cristian Gafton
gafton at rpath.com
Fri Sep 8 01:25:50 CEST 2006
On Thu, 7 Sep 2006, Ricardo Bugalho wrote:
> I think you're mixing up DB-API specs with implementation.
> Mostly, the beneficts of what you pointed out could be implemented by
> smarter bindings, without changing the DP-API spec. And some already do.
In a certain way, you are right. My point is, the very fact that we have
such varied assumptions and implementations of "DB API 2.0" bindings,
points to the fact that the spec as a whole needs refining and it needs to
be more precise.
>> - parsed statements. On large loops this is a real gain.
>
> Explicit prepared statments should be a good addition for the DB-API.
> But you can also take advantage of prepared statments by caching query
> requests.
> In a simple way, you could cache the last statement executed in that
> session. Thus, you could implement
> cursor.execute("SELECT * FROM foo WHERE id = %(id)s", locals())
> as something like
> // currentStatment = "SELECT FROM foo WHERE id = $1"
> if (strcmp(lastStatment, currentStatement) != 0) then {
> lastStatement = currentStatement
> preparedStatment = PQprepare(..., " ", currentStatment, ...);
> }
> result = PQexecPrepared(..., " ", ...);
As somebody else already pointed out, blindly doing this automatically for
some backends can result in performance losses. Of course, in the end it
is just a matter of coding; I don't find it particularly appealing because
the bindings author is required to implement some magic behavior which can
hurt the application developer. We don't need more assumed semantics when
it would be fairly easy to provide the application writer with the control
he needs over the behavior of the bindings he's using.
>> - server side cursors. Currently, most bindings for most databases have to
>> decide what to do after an cursor.execute() call - do they automatically
>> retrieve all the resulting rows in the client's memory, or do they
>> retrieve it row by row, pinging the server before every retrieval
>
> DB-API's cursors have always been supposed to be based server side
> cursors. That's the whole point about having cursors.
> If some bindings don't use server side cursors when they're available,
> it's their own problem.
Well, then it should be spelled out in that fashion. Current wording in
the specification leaves things kind of unclear. I don't believe the
driver authors are to blame - just take a look around at any bindings for
any backend and count the number of extensions people felt compelled to
add in. The desire to provide a richer functionality and more control to
the application developer is certainly there. The problem is that DB API
leaves too many questions unanswered and skips many implementation details
and guidelines.
Cristian
--
Cristian Gafton
rPath, Inc.
More information about the DB-SIG
mailing list