Handle resultless statements when rowcount > 0

[andrewrabert]

When, for example, an insert without return is executed, attempting to access the queries.Result object's rows raises a psycopg2.ProgrammingError with a message of 'no results to fetch'. The existing check in queries is based on cursor.rowcount and thus fails in this example as rowcount contains the number of rows affected - not necessarily returned.

This commit adds handling of said exception and matches the behaviors if the rowcount were 0.

[gmr]

Can you check if there is a corresponding constant for the exception in psycopg2.errorcodes instead of doing string comparison? Ideally it'd be something like:

except psycopg2.ProgrammingError as err:
    if err.pgcode == psycopg2.errorcodes.NO_DATA:

I'm not sure what the error code # is, but here's where they are enumerated:

https://github.com/psycopg/psycopg2/blob/master/lib/errorcodes.py

[andrewrabert]

Both pgcode and pgerror are None.

This example replicates the issue in the queries dev env without any mocking. In addition to printing exception values, I also printed cur values for both the successful and failed queries. The only noticeable difference between the two is that the successful query's description attribute is set while the failed is None.

Code

import os
import psycopg2

conn = psycopg2.connect(
    host=os.environ['PGHOST'],
    port=os.environ['PGPORT'],
    user='postgres'
)


# create table
with conn.cursor() as cur:
    cur.execute('DROP TABLE IF EXISTS example; CREATE TABLE example (id INTEGER)')
    conn.commit()

# insert
with conn.cursor() as cur:
    cur.execute('INSERT INTO example (id) VALUES (0) RETURNING *')
    for attr in dir(cur):
        if not attr.startswith('__'):
            print(attr, getattr(cur, attr))
    print()
    conn.commit()
    cur.fetchall()

print()

# insert no return
with conn.cursor() as cur:
    cur.execute('INSERT INTO example (id) VALUES (0)')
    for attr in dir(cur):
        if not attr.startswith('__'):
            print(attr, getattr(cur, attr))
    print()

    conn.commit()
    try:
        cur.fetchall()
    except psycopg2.ProgrammingError as e:
        print()
        for attr in dir(e):
            if not attr.startswith('__'):
                print(attr, getattr(e, attr))
        print()
        raise

Output

arraysize 1
binary_types None
callproc <built-in method callproc of psycopg2.extensions.cursor object at 0x7fec53722428>
cast <built-in method cast of psycopg2.extensions.cursor object at 0x7fec53722428>
close <built-in method close of psycopg2.extensions.cursor object at 0x7fec53722428>
closed False
connection <connection object at 0x7fec56312c28; dsn: 'host=127.0.0.1 port=32768 user=postgres', closed: 0>
copy_expert <built-in method copy_expert of psycopg2.extensions.cursor object at 0x7fec53722428>
copy_from <built-in method copy_from of psycopg2.extensions.cursor object at 0x7fec53722428>
copy_to <built-in method copy_to of psycopg2.extensions.cursor object at 0x7fec53722428>
description (Column(name='id', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),)
execute <built-in method execute of psycopg2.extensions.cursor object at 0x7fec53722428>
executemany <built-in method executemany of psycopg2.extensions.cursor object at 0x7fec53722428>
fetchall <built-in method fetchall of psycopg2.extensions.cursor object at 0x7fec53722428>
fetchmany <built-in method fetchmany of psycopg2.extensions.cursor object at 0x7fec53722428>
fetchone <built-in method fetchone of psycopg2.extensions.cursor object at 0x7fec53722428>
itersize 2000
lastrowid 0
mogrify <built-in method mogrify of psycopg2.extensions.cursor object at 0x7fec53722428>
name None
nextset <built-in method nextset of psycopg2.extensions.cursor object at 0x7fec53722428>
query b'INSERT INTO example (id) VALUES (0) RETURNING *'
row_factory None
rowcount 1
rownumber 0
scroll <built-in method scroll of psycopg2.extensions.cursor object at 0x7fec53722428>
scrollable None
setinputsizes <built-in method setinputsizes of psycopg2.extensions.cursor object at 0x7fec53722428>
setoutputsize <built-in method setoutputsize of psycopg2.extensions.cursor object at 0x7fec53722428>
statusmessage INSERT 0 1
string_types None
typecaster None
tzinfo_factory <class 'psycopg2.tz.FixedOffsetTimezone'>
withhold False


arraysize 1
binary_types None
callproc <built-in method callproc of psycopg2.extensions.cursor object at 0x7fec53722520>
cast <built-in method cast of psycopg2.extensions.cursor object at 0x7fec53722520>
close <built-in method close of psycopg2.extensions.cursor object at 0x7fec53722520>
closed False
connection <connection object at 0x7fec56312c28; dsn: 'host=127.0.0.1 port=32768 user=postgres', closed: 0>
copy_expert <built-in method copy_expert of psycopg2.extensions.cursor object at 0x7fec53722520>
copy_from <built-in method copy_from of psycopg2.extensions.cursor object at 0x7fec53722520>
copy_to <built-in method copy_to of psycopg2.extensions.cursor object at 0x7fec53722520>
description None
execute <built-in method execute of psycopg2.extensions.cursor object at 0x7fec53722520>
executemany <built-in method executemany of psycopg2.extensions.cursor object at 0x7fec53722520>
fetchall <built-in method fetchall of psycopg2.extensions.cursor object at 0x7fec53722520>
fetchmany <built-in method fetchmany of psycopg2.extensions.cursor object at 0x7fec53722520>
fetchone <built-in method fetchone of psycopg2.extensions.cursor object at 0x7fec53722520>
itersize 2000
lastrowid 0
mogrify <built-in method mogrify of psycopg2.extensions.cursor object at 0x7fec53722520>
name None
nextset <built-in method nextset of psycopg2.extensions.cursor object at 0x7fec53722520>
query b'INSERT INTO example (id) VALUES (0)'
row_factory None
rowcount 1
rownumber 0
scroll <built-in method scroll of psycopg2.extensions.cursor object at 0x7fec53722520>
scrollable None
setinputsizes <built-in method setinputsizes of psycopg2.extensions.cursor object at 0x7fec53722520>
setoutputsize <built-in method setoutputsize of psycopg2.extensions.cursor object at 0x7fec53722520>
statusmessage INSERT 0 1
string_types None
typecaster None
tzinfo_factory <class 'psycopg2.tz.FixedOffsetTimezone'>
withhold False


args ('no results to fetch',)
cursor None
diag <psycopg2.extensions.Diagnostics object at 0x7fec5635c600>
pgcode None
pgerror None
with_traceback <built-in method with_traceback of ProgrammingError object at 0x7fec562cc268>

Traceback (most recent call last):
  File "t.py", line 38, in <module>
    cur.fetchall()
psycopg2.ProgrammingError: no results to fetch