Detect whether to fetch from psycopg2 cursor or not?

Question

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.

Encephalogram

is the first line looks like cursor.execute("insert into hello (username) values ('me')")? – Impartible

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. – Parity

Answer 1

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.

Keep

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. – Argument

cur.statusmessage can be used for this purpose. This will return SELECT 0 or INSERT 0 1. – Urson

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). – Fustic

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; – Dentistry

Answer 2

Check whether cursor.pgresult_ptr is None or not.

cursor.execute(sql)
if cursor.pgresult_ptr is not None:
    cursor.fetchall()

Orthodoxy

It won't let me make a small edit, but I believe in should be is – Destroy

you are right of coures, thanks! – Orthodoxy

Answer 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.

Urson

Answer 4

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.

Ketone

cursor.rowcount doesn't work for "SELECT ... INTO ..." so I won't recommend using this code for general purposes. – Cassirer

Answer 5

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!

Shipmate

DBCursor is a typo. you probably mean just cursor – Dismiss