How to check if a result set is empty?

Question

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?

Lorsung

What database is this? I would not expect an exception here, rather .fetchall() returns an empty list. – Crumpton

returns empty list for Django 2.1 and SQLite – Pothunter

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

Crumpton

in my script, I execute a list of sql statements, measure their runtime and number of hits. I cannot know beforehand if a statement is select or insert, is there a way for me to know after each query execution whether there is a resultset at all? Thanks a lot. – Lorsung

@TaoVenzke: Use try: results = cursor.fetchall() then catch the exception with an except handler? Not all databases throw an exception in this case, what database are you using here? – Crumpton

I am using SAP HANA database. I did it with TRY, and an exception is caught. But I would like to avoid exception in this case. cursor.rowcount is quite confusing: it returns -1 for select statement; it returns NOT 0 for insert statement, the number it returns appears to be the number of records inserted, but can I really trust it? – Lorsung

Depends on the database, and I am not familiar with SAP. -1 could be that the database doesn't know beforehand how many rows are to be returned. INSERTS may also give a rowcount indeed, that'd be how many rows were added. Unless documented otherwise, the number should be reliable. – Crumpton

There WILL sometimes be results for DML statements. For example Postgres: INSERT INTO some_table (id) values (1) RETURNING id;. You should update your answer. – Diamante

@PålThingbø: good point, I wasn't aware of RETURNING back when I wrote this. – Crumpton

The exact concept is explained here. I wonder how u voted it as correct. If the query is a select statement and there is no result, the rowcount will be -1 not 0. So if you work with 0 to check if the cursor is empty or not, you'll face an issue #47540912 – Pistol

@user3665906: when you use a data query statement (invariably, a SELECT, but also when executing a stored procedure), and you get an empty resultset, then cursor.rowcount is going to be 0. For data manipulation statements (UPDATE, INSERT), with no RETURNING clause, you can expect the rowcount to be -1, yes, but that depends on the specific database adapter, really. – Crumpton

@user3665906: I've added a note about this. – Crumpton

I'm a little lost on what is the recommendation for testing empty cursor. If you take OP's example using a select statement, you can assume a result of 1+ or None.unfortunately, the question is ambiguous on what is the OP's real need, count or empty. But for us peeps from from the future, this answer addresses count, but what about empty? Maybe not row? – Molly

@xtian: Just stick with COUNT() and so get an actual number, guaranteed. – Crumpton

Answer 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")

Pinkard

It's better to use is None than == None. Isn't it? – Ostrom

And we loose track of one row if we don't notice – Orpiment

Answer 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

Catlin

Works very fine with Oracle ! – Demagoguery

Answer 4

Notice: This is for MySQLdb module in Python.

For a SELECT statement, there shouldn't be an exception for an empty recordset. Just an empty list ([]) for cursor.fetchall() and None for cursor.fetchone().

For any other statement, e.g. INSERT or UPDATE, that doesn't return a recordset, you can neither call fetchall() nor fetchone() on the cursor. Otherwise, an exception will be raised.

There's one way to distinguish between the above two types of cursors:

def yield_data(cursor):
    while True:
        if cursor.description is None:
            # No recordset for INSERT, UPDATE, CREATE, etc
            pass
        else:
            # Recordset for SELECT, yield data
            yield cursor.fetchall()
            # Or yield column names with
            # yield [col[0] for col in cursor.description]

        # Go to the next recordset
        if not cursor.nextset():
            # End of recordsets
            return

Halftone

Answer 5

I had a similar problem when I needed to make multiple sql queries. The problem was that some queries did not return the result and I wanted to print that result. And there was a mistake. As already written, there are several solutions.

if cursor.description is None:
    # No recordset for INSERT, UPDATE, CREATE, etc
    pass
else:
    # Recordset for SELECT

As well as:

exist = cursor.fetchone()
if exist is None:
  ... # does not exist
else:
  ... # exists

One of the solutions is:

The try and except block lets you handle the error/exceptions. The finally block lets you execute code, regardless of the result of the try and except blocks. So the presented problem can be solved by using it.

s = """ set current query acceleration = enable;
        set current GET_ACCEL_ARCHIVE = yes;
        SELECT * FROM TABLE_NAME;"""

query_sqls = [i.strip() + ";" for i in filter(None, s.split(';'))]
for sql in query_sqls:
    print(f"Executing SQL statements ====> {sql} <=====")
    cursor.execute(sql)
    print(f"SQL ====> {sql} <===== was executed successfully")
    try:
        print("\n****************** RESULT ***********************")
        for result in cursor.fetchall():
            print(result)
        print("****************** END RESULT ***********************\n")
    except Exception as e:
        print(f"SQL: ====> {sql} <==== doesn't have output!\n")
        # print(str(e))

output:

Executing SQL statements ====> set current query acceleration = enable; <=====
SQL: ====> set current query acceleration = enable; <==== doesn't have output!

Executing SQL statements ====> set current GET_ACCEL_ARCHIVE = yes; <=====
SQL: ====> set current GET_ACCEL_ARCHIVE = yes; <==== doesn't have output!

Executing SQL statements ====> SELECT * FROM TABLE_NAME; <=====

****************** RESULT ***********************

       ----------   DATA   ----------

****************** END RESULT ***********************

The example above only presents a simple use as an idea that could help with your solution. Of course, you should also pay attention to other errors, such as the correctness of the query, etc.

Rant

Answer 6

if you're connecting to a postgres database, the following works:

result = cursor.execute(query)

if result.returns_rows:
    # we got rows!
    return [{k:v for k,v in zip(result.keys(), r)} for r in result.rows]
else:
    return None

Bight

Answer 7

You can do like this :

count = 0
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=serverName;"
                      "Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute(SQL query)
for row in cursor:
    count = 1
    if true condition:
        print("True")
    else:
        print("False")
if count == 0:
    print("No Result")

Thanks :)

Benford

When posting an answer, try to explain why this answer solves the problem, along with the code itself. – Discriminative

Answer 8

For reference, cursor.rowcount will only return on CREATE, UPDATE and DELETE statements:

 |  rowcount
 |      This read-only attribute specifies the number of rows the last DML statement
 |      (INSERT, UPDATE, DELETE) affected.  This is set to -1 for SELECT statements.

Spica

Answer 9

My function is worjing good for me

option = cursor.execute("SELECT value FROM options WHERE key = '{}'".format(key))
    if option.fetchone() is not None:
        return cursor.execute("SELECT value FROM options WHERE key = '{}'".format(key)).fetchone()

Weedy

1. Using .format() or other generic ways of string substitution for passing parameters to SQL queries is a very bad practice (SQL injection!). Please use parametrized SQL queries like for example in the answer by Piney here: https://mcmap.net/q/48816/-how-to-check-if-a-result-set-is-empty --- 2. It could be worth to check if running a different query instead of your first one can be more efficient. I can imagine for example using COUNT() or FIRST_VALUE(). – Embus

When I use the code i get this error sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 9 supplied. – Weedy