Re: [Pypgsql-users] Determining size of result set?

[Billy G. A. <bg...@mu...>]

Lars Kellogg-Stedman wrote:
> Hello all,
> 
> I've just started using pyPgSQL, and I've run into a frustrating problem 
> -- how do I determine the size of a result set *before* calling one of 
> the fetchxxx() functions?
> 
> The rowcount attribute doesn't appear to be terribly useful.  As far as 
> I can tell, it simply tells me the number of rows returned by the 
> previous fetchxxx() call -- a value which I can also get just by calling 
> len() on the return from fetchxxx().  Before a fetch call, rowcount 
> always contains -1.
> 
> Is there any other way of getting at this information?

This behavior is caused by the fact that pyPgSQL uses PostgreSQL Portals (i.e. cursors) by default.  When portals are used, there is no way to determine the number of rows in the result until the rows are fetched, and the rowcount will be set to the number of rows fetched using the fetchxxx() call.  This was done so that individual (or groups of) rows could be processed without have to transfer all of the rows to the client at one time.  They can be retrieved from the back-end one at a time (or in small groups using fetchmany).  If you really need to know the number of rows before you process them, you have three options:

1.  Use a 'select count(*) from ...' query to get the number of rows that would be
    returned.

2.  Execute a fetchall() which will retrieve all the rows from the Postgresql portal.

3.  Disable the use of PortgreSQL portals by setting PgSQL.noPostgresCursor to 1. 
    This will cause PgSQL to simulate a cursor by eading the entire result set into
    memory and handing out the results as needed.  Note:  The results are stored in
    a PQresult object (see the libpq documentation),

I hope this helps.
-- 
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |