Psycopg2: execute_batch(), execute_values() give wrong rowcount

[yamagoya]

After running either execute_batch() or execute_values() cursor.rowcount has the value of the number of rows affected by the last internal (to the function) operation, not the total actually performed by the function.

The program below illustrates: it reports row counts of 2 and 1. I would have expected 5 and 5.

As far as I can tell, only the innards of those two functions have access to the row counts returned by Postgresql so if they don't provide the information to me, there is no easy way for me to get it.

Would it be possible for them to internally sum the rowcounts from Postgresql and set the cursor.rowcount value to the accumulated value before returning?

That seems to me to be the most consistent behavior since both functions beneficially hide the multiple statement execution internally and getting the rowcount from the last operation seems like an abstraction leak (in addition to simply not being very useful). But an alternative (less desirable IMO) would be to return the accumulated counts since currently the return value is None.

import psycopg2, psycopg2.extras

dbconn = psycopg2.connect("dbname=test user=postgres")
cur = dbconn.cursor()
cur.execute ("DROP TABLE IF EXISTS test;"
             "CREATE TABLE test (id INT);")
data = [(1,), (2,), (3,), (4,), (5,)] 

n = psycopg2.extras.execute_values(cur, 
  "INSERT INTO test VALUES %s", data, page_size=3)
print ("insert rowcount = %s" % cur.rowcount)

n = psycopg2.extras.execute_batch(cur, 
  "UPDATE test SET id=0 WHERE id=%s", data, page_size=3)
print ("update rowcount = %s" % cur.rowcount)

Output:

insert rowcount = 2
update rowcount = 1

[yamagoya]

Oops, my apologies, I just read issue #491; the rowcount issue was discussed there. But I'm still not sure of the rationale or if the decision is cast in stone.

[dvarrazzo]

Yes, it is. Don't count on it. The entire point of having separate method is that we couldn't have implemented rowcount so we would have broken executemany() semantics.

[yamagoya]

OK, thanks, I think I understand now. For anyone else as dense as me, my understanding is...

pscopg2 implements execute_batch() by creating multiple sql statements from the parameters list, joining them together with semi-colons and sending them all as a single string (all as documented) to Postgresql using a single call to PG's PQexec() function. Postgresql documents the latter as returning only the results (rowcount of rows affected) of the last statement executed of the multiple statements. So psycopg2 never gets the results of any of the earlier statements in the string so it can't return or sum those values to calulate a total row count.

The part I was missing was that psycopg2 does not have access to the all the counts.

I think a comment about the rowcount in execute_batch() and execute_values() in the documentation would be apropos. To me and I presume others, it was/is not immediately clear from the docs that the rowcount isn't the total in lieu of any information to the contrary.

Lastly, perhaps when execute_batch() sends multiple statements to the server it could set cursor.rowcount to -1 (per the DBAPI since in fact it doesn't know the actual rowcount) and leave it unchanged (and accurate) when there was exactly one statement in the string sent. But because execute_batch is a helper function and not a DBAPI standard method, you may feel it shouldn' t be messing with the cursor rowcount at all. Just thought I'd throw the idea out there before leaving...

[dvarrazzo]

We can fix the documentation reinforcing the idea that rowcount is not to rely on.

[dvarrazzo]

Added line in the function docs. Yes, it would be nice to set the rowcount to -1/None after these functions, but rowcount is read-only and these are external functions, not methods. So it would be a matter of either make the property read/write or to add an hidden method to set it and I don't see any worth in it.