How to check if a result set is empty?

I have a sql statement that returns no hits. For example, 'select * from TAB where 1 = 2'.

I want to check how many rows are returned,

cursor.execute(query_sql)

rs = cursor.fetchall()

Here I get already exception: "(0, 'No result set')"

How can I prevend this exception, check whether the result set is empty?

Solution 1:

cursor.rowcount will usually be set to 0.

If, however, you are running a statement that would never return a result set (such as INSERT without RETURNING, or SELECT ... INTO), then you do not need to call .fetchall(); there won't be a result set for such statements. Calling .execute() is enough to run the statement.

Note that database adapters are also allowed to set the rowcount to -1 if the database adapter can't determine the exact affected count. See the PEP 249 Cursor.rowcount specification:

The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface.

The sqlite3 library is prone to doing this. In all such cases, if you must know the affected rowcount up front, execute a COUNT() select in the same transaction first.

Solution 2:

I had issues with rowcount always returning -1 no matter what solution I tried.

I found the following a good replacement to check for a null result.

c.execute("SELECT * FROM users WHERE id=?", (id_num,))
row = c.fetchone()
if row == None:
   print("There are no results for this query")

Solution 3:

MySQLdb will not raise an exception if the result set is empty. Additionally cursor.execute() function will return a long value which is number of rows in the fetched result set. So if you want to check for empty results, your code can be re-written as

rows_count = cursor.execute(query_sql)
if rows_count > 0:
     rs = cursor.fetchall()
else:
     // handle empty result set