27

Let's say if I execute the following command.

insert into hello (username) values ('me')

and I ran like

cursor.fetchall()

I get the following error

psycopg2.ProgrammingError: no results to fetch

How can I detect whether to call fetchall() or not without checking the query is "insert" or "select"?

Thanks.

moeseth
  • 1,855
  • 5
  • 23
  • 47
  • is the first line looks like `cursor.execute("insert into hello (username) values ('me')")`? – Compadre Jul 29 '16 at 11:29
  • 1
    Chris B is correct, but there's a general concern that an arbitrary query (could be `insert` or `select` or something else) has got this far into the code without knowing what type of query it is. Where is the SQL being entered? Are you protecting against SQL Injection? I strongly suggest that the code should be aware of the type of query being handled and have different code paths for handling `inserts` vs `selects`, ideally using a known set of queries - possibly stored procedures - and apart from the case where a select query returns no description this question shouldn't be an issue. – Davos Nov 29 '18 at 05:06

5 Answers5

32

Look at this attribute:

cur.description

After you have executed your query, it will be set to None if no rows were returned, or will contain data otherwise - for example:

(Column(name='id', type_code=20, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None),)

Catching exceptions is not ideal because there may be a case where you're overriding a genuine exception.

  • 2
    Worked for me for a while, but now I am facing a situation when the query returns no rows, but description attribute is not None. Weird. – Denys Mar 21 '18 at 08:42
  • 1
    `cur.statusmessage` can be used for this purpose. This will return `SELECT 0` or `INSERT 0 1`. – Tirtha R Dec 28 '18 at 04:06
  • I really don't understand, my answer below notwithstanding, why the questioner's program isn't distinguishing between update and retrieval queries. I would point out, however, that in the case of a SELECT that retrieves no rows you do still get a valid `description` (making introspection is possible). – holdenweb Mar 22 '19 at 08:16
  • Thank you for your answer - it works perfectly! The other answers that mention `statusmessage` - *is not correct*. As en example in case inserting the data into table `INSERT ... VALUES... (1,3,4) RETURNING sid;`. - The `statusmessage` always returns the `INSERT 0 1` for my sql statement with or without `RETURNING sid` at the end; - The 'description` property - will return the column list and it's type. in case when INSERT SQL statement without `RETURNING sid` will be equal to `None`; – Shmalex Feb 08 '21 at 21:25
4

Check whether cursor.pgresult_ptr is None or not.

cursor.execute(sql)
if cursor.pgresult_ptr is not None:
    cursor.fetchall()
Sealander
  • 3,467
  • 4
  • 19
  • 19
itdv
  • 41
  • 3
3

The accepted answer using cur.description does not solve the problem any more. cur.statusmessage can be a solution. This returns SELECT 0 or INSERT 0 1. A simple string operation can then help determine the last query.

Tirtha R
  • 1,148
  • 1
  • 14
  • 24
0

The problem is that what turns out to be None is the result of cur.fetchone() So the way to stop the loop is :

cursor.execute("SELECT * from rep_usd")
output = cursor.fetchone()
while output is not None:
    print(output)
    output = DBCursor.fetchone()

cursor.description will never be None!

Alvaro Torijano
  • 19
  • 5
0

The current best solution I found is to use cursor.rowcount after an execute(). This will be > 0 if the execute() command returns a value otherwise it will be 0.

bcsta
  • 1,963
  • 3
  • 22
  • 61
  • `cursor.rowcount` doesn't work for "SELECT ... INTO ..." so I won't recommend using this code for general purposes. – Innewit Dec 12 '22 at 13:15