Asyncpg: Get number of rows in cursor ? (equivalent to psycopg2#cursor.rowcount)

[rmedaer]

• asyncpg version: 0.15
• PostgreSQL version: 9.6
• Python version: 3.6.5
• Platform: Debian Buster
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: no
• Can the issue be reproduced under both asyncio and uvloop?: N/A

__Warning:__ It's may be a duplicate of #311

I would like to get the number of rows from the last query (a SELECT query). An equivalent to cursor.rowcount in psycopg2.

I saw in #311 that we can get the query status line (as a string) from execute function. Actually, I'm not able to use it since I need a cursor. Don't know very well binary protocol of pg.. I don't even know if it is possible to get it.

The purpose of this issue is to avoid doing multiple queries (for instance with count(*) statement).

[elprans]

An equivalent to cursor.rowcount in psycopg2.

A Cursor in asyncpg should not be confused with psycopg2's cursor instances. In asyncpg a Cursor instance represents a true server-side cursor object of the kind you get with SQL DECLARE (https://www.postgresql.org/docs/10/static/sql-declare.html). In PostgreSQL there is no way to obtain the number of rows returned by a cursor query without "scrolling" to the end, as the server itself does not know until it executes the query plan to completion.

In your case, it seems like you need an explicit _scrollable_ cursor:

# declare the cursor
await conn.execute('DECLARE my_cursor SCROLL CURSOR FOR <query>')
# MOVE to the end to get the total row count
row_count = await conn.execute('MOVE LAST IN my_cursor')
# MOVE back to the start
await conn.execute('MOVE FIRST IN my_cursor')
# Fetch a page
results = await conn.fetch('FETCH RELATIVE <page_size> IN my_cursor')

[rmedaer]

Hi @elprans

Many thanks !

It's probably more a PostgreSQL question but I take the opportunity to challenge this (the scrollable cursor) with a secondary query (using count): which one will be fastest ?

Kind regards,

[rmedaer]

I just tested your code and I have following notes:

1. Declare the cursor

# declare the cursor
await conn.execute('DECLARE my_cursor SCROLL CURSOR FOR <query>')

It's OK but it has to be done in a transaction.

2. MOVE to the end to get the total row count

# MOVE to the end to get the total row count
row_count = await conn.execute('MOVE LAST IN my_cursor')

It always responses with MOVE 1 even if there are 1000 rows, meaning that actually I can't get the number of rows.

The only way (I guess) to get the number of rows is to add ROW_NUMBER() in my query AND do a fetch of the row with FETCH LAST IN my_cursor.

3. MOVE back to the start

# MOVE back to the start
await conn.execute('MOVE FIRST IN my_cursor')

This is moving the cursor position to first row => row 0; so when we fetch next 10 rows its fetching from row 1. Instead we have to use:

MOVE ABSOLUTE 0 IN my_cursor;

4. Fetch a page

# Fetch a page
results = await conn.fetch('FETCH RELATIVE <page_size> IN my_cursor')

According to PostgreSQL doc it's fetching the nth row (relative to cursor). Actually what we expect is to have the n following rows. This can be done with:

FETCH <page_size> IN my_cursor;

[rmedaer]

Actually there is a way to get total number of rows (step 2). Instead of doing a MOVE LAST we can use MOVE FORWARD.

For instance:

MOVE FORWARD ALL IN my_cursor;

(e.g. it returns MOVE 101 instead of MOVE 1)

Here is a complete example:

-- Start a transaction
BEGIN;
-- Declare a new cursor with our query
DECLARE my_cursor SCROLL CURSOR FOR SELECT generate_series(0, 100);
-- Move forward to get number of rows
MOVE FORWARD ALL IN my_cursor;
-- Come back at the beginning of the cursor
MOVE ABSOLUTE 0 IN my_cursor;
-- Fetch the first page (e.g. 10)
FETCH 10 IN my_cursor;
-- Commit the transaction
COMMIT;

Using asyncpg:

async with connection.transaction():
  await connection.execute('DECLARE my_cursor SCROLL CURSOR FOR SELECT generate_series(0, 100)')
  row_count = await connection.execute('MOVE FORWARD ALL IN my_cursor')

  await connection.execute('MOVE ABSOLUTE 0 IN my_cursor')
  results = await connection.fetch('FETCH 10 IN my_cursor')

Maybe one remaining question is: is there a function in asyncpg to parse query result (for instance MOVE 101) ?

• If yes, which one ?
• If no, it might be useful to add this feature... What's your feeling @elprans ?

[elprans]

is there a function in asyncpg to parse query result

There is none currently.

might be useful to add this feature

Sure, I'm not against having a helper function for this.