psycopg2: How to know when cur.rowcount does not mean number of rows?

Question

I'm looking for a way to programatically determine the difference between cur.rowcount describing the number of rows available to fetch versus the number of rows affected.

For example:

>>> cur.execute('CREATE TABLE test (gid serial, val numeric);')
>>> cur.execute('INSERT INTO test (val) values (1), (2), (3);')
>>> cur.execute('SELECT * FROM test;'); cur.rowcount
3
>>> cur.execute('UPDATE test SET val = 1;'); cur.rowcount
3
>>> cur.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: no results to fetch

>>> cur.execute('DELETE FROM test;'); cur.rowcount
3

>>> cur.execute('DROP TABLE test;')
>>> cur.rowcount
-1

Using Python 2.7 and psycopg2 2.6.2.

Answer 1

The distinction is simple at least in theory rowcount would be returned for all the DQL (Data Query Language) and DML (Data Manipulation Language) queries (your SELECT, UPDATE, DELETE etc). You'll have -1 for DDL (Data Definition Language) queries - the (CREATE, DROP, ALTER etc).

In other words, you can expect rowcount to be defined (meaning >=0) if you actually manipulate data; if you work with the database/table schema - you'll have rowcount = -1.

As far as determining when the fetchall() would work or not, you can always go with the EAFP approach - try executing fetchall() and handle the ProgrammingError.

Also see:

• What are DDL and DML?

Answer 2

According to the docs, cur.description will be None for operations without a result set.