Re: Which Python library - psycopg2 or pygresql?

On 18 Apr, 14:12, Karsten(dot)Hilb(dot)(dot)(dot)(at)gmx(dot)net (Karsten Hilbert) wrote:
>
> 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.

But if you want to process all of the rows, and you don't want the
client to suck them all down at once, then you need to use the
database system's cursor support.

> 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.

As I noted, the problem is arguably shared between the database system
(because cursors don't work with certain statements that you might
use, and there's no way of finding out without trying) and the
database adapter (because it doesn't try to support the behaviour
implied by the DB-API). Inventing names for cursors, although tedious,
is the easy part in all this.

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

It doesn't.

> 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.

Well, the DB-API doesn't seem to be moving in any real direction these
days, anyway. I've wanted and even proposed code for a single
parameter standard, and the progress on that matter has been glacial:
it's too controversial to do what ODBC and JDBC have been doing for
years, apparently. Still, I don't really see that doing the equivalent
of a cursor.fetchall for something like cursor.fetchone is appropriate
when "all" might be millions of rows, but that's just my view.

Paul