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

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    quot  quot  quot  set current query acceleration   enable          set current GET ACCEL ARCHIVE   yes          SELECT   FROM TABLE NAME  quot  quot  quot   query sqls    i strip      quot   quot  for i in filter None  s split        for sql in query sqls      print f quot Executing SQL statements      gt   sql   lt       quot       cursor execute sql      print f quot SQL      gt   sql   lt       was executed successfully quot       try          print  quot  n                   RESULT                         quot           for result in cursor fetchall                print result          print  quot                    END RESULT                         n quot       except Exception as e          print f quot SQL       gt   sql   lt      doesn t have output  n quot             print str e    output  Executing SQL statements      gt  set current query acceleration   enable   lt       SQL       gt  set current query acceleration   enable   lt      doesn t have output   Executing SQL statements      gt  set current GET ACCEL ARCHIVE   yes   lt       SQL       gt  set current GET ACCEL ARCHIVE   yes   lt      doesn t have output   Executing SQL statements      gt  SELECT   FROM TABLE NAME   lt                           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

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

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

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

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

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

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

[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