How can I use a try/except to check if an SQL query returns anything?

Question

I have written a small program which creates reports in a text file on the behaviours of students. However, I want my program to be able to break the -try- statement if no records are found. How do I do this?

I am writing it in Python and using sqlite3.

Answer 1

You have 3 options, none require try:

1. for results that return only a single row, use cursor.fetchone(); this returns either the row, or None if there were no matches:

   result_row = cursor.fetchone()
   if result_row:
       # there was a result
   

2. for multiple rows, just loop over the cursor; if there were no results the loop will not throw an exception, just not iterate:

   for row in cursor:
       # do something with `row`
       # nothing will happen if there were 0 results
   

   If you wanted to detect if there were 0 results, you could set a flag variable:

   has_results = False
   for row in cursor:
       has_results = True
       # do something with `row`
   
   if not has_results:
       raise ValueError('No results for that query')
   

3. For a smaller set of expected results, you can use cursor.fetchall(); this returns an empty list if there were no results:

   rows = cursor.fetchall()
   for row in rows:
       # do something with `row`
       # nothing will happen if there were 0 results
   

   Don't use this to process a large number of rows; just use option #2 for that.

   This option does give you the choice to do something else if there were 0 results:

   rows = cursor.fetchall()
   if not rows:
       raise ValueError('No results for that query')
   

If you have to use try, then provoke an exception. For options #1 and #3, all you need to do is indexing:

result_row = cursor.fetchone()
try:
    result_column = result_row[0]
except TypeError:
    # no result, `result_row` was `None`

or

rows = cursor.fetchall()
try:
    first_row = rows[0]
except IndexError:
    # no results, `rows` was an empty list

For option #2, next() will throw a StopIteration if there is no next result:

try:
    first_row = next(cursor)
except StopIteration:
    # no results, nothing to iterate over