Python 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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0       rs   cursor fetchall   else          handle empty result set

User · Answer

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

[python] Python, how to check if a result set is empty?

Examples related to python

Examples related to resultset

Examples related to python-db-api