Re: Which Python library - psycopg2 or pygresql?

On Thu, Apr 17, 2008 at 04:06:57AM -0700, Paul Boddie wrote:

> One caveat: psycopg2 doesn't (or didn't) use cursors in a transparent
> fashion like pyPgSQL does. If you're traversing potentially large data
> sets, this will mean that psycopg2 will download all the result data
> into the client process unless you start introducing explicit DECLARE
> CURSOR statements in all the right places. Although this might not be
> an issue if you're determined to only support PostgreSQL and psycopg2,
> it's worth noting that the behaviour is somewhat counter-intuitive
> from the perspective of people with experience of other database
> systems: attempting to fetch a single row (or a limited number of
> rows) may cause you to discover that the client has acquired all of
> them
If one wants to operate on one/a range of row(s) but the
code fetches "all" rows (for various values of all) then I'd
suspect there's something missing in the SQL statement, say,
a LIMIT or appropriate WHERE conditions - regardless of
whether a cursor is used or not.

If you refer to whether server-side cursors are used one
must explicitly request them from psycopg2 by using the
"name" argument to the connection.Cursor() call. Combine
that with a Python generator and one should end up with
truly on-demand single-row fetching.

Unfortunately, I am not entirely sure how and when psycopg2
uses (database) cursors when no name argument is supplied.

IMO the cursor concept of the DB-API is broken anyhow -
everything is forced to go through a (DB-API) cursor no
matter whether a database-side cursor would be wanted or not
and there's no provision for controlling the latter via the
API itself.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346