catch odbc exception

Question

I need to recognize in my application whether table doesn't exist or has no rows to take appropriate action. Can I catch this two errors separately ?

>>>cursor.execute("delete from TABLE")

Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
dbi.internal-error: [IBM][CLI Driver][DB2] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000
 in EXEC

OR

Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
dbi.program-error: [IBM][CLI Driver][DB2] SQL0204N  "SK77531.TCS_EXCEPTIONS" is an undefined name.  SQLSTATE=42704
 in EXEC

Answer 1

From the Python documentation:

A try statement may have more than one except clause, to specify handlers for different exceptions.

For example:

try:
    do_something_crazy
except AttributeError:
    print 'there was an AttributeError'
except NameError:
    print 'there was a NameError'
except:
print 'something else failed miserably'

The last except acts as a catch-all here, and is only executed if an exception different than an AttributeError or NameError occurs.  In production code it's best to steer clear from such catch-all except clauses, because in general you'll want your code to fail whenever an error that you didn't expect occurs.

In your specific case you'll need to import the different exceptions that can be raised from the dbi module, so you can check for them in different except clauses.

So something like this:

# No idea if this is the right import, but they should be somewhere in that module
import dbi

try:
    cursor.execute("delete from TABLE")
except dbi.internal-error:
    print 'internal-error'
except dbi.program-error:
    print 'program-error'

As you'll see in the above-lined documentation page, you can opt to include additional attributed in each except clause.  Doing so will let you access the actual error object, which might be necessary for you at some point when you need to distinguish between two different exceptions of the same class.  Even if you don't need such a fine level of distinction, it's still a good idea to do a bit more checking than I outlined above to make sure you're actually dealing with the error you think you're dealing with.

All that said and done about try/except, what I'd really recommend is to search for a method in the database library code you're using to check whether a table exist or not before you try to interact with it.  Structured try/excepts are very useful when you're dealing with outside input that needs to be checked and sanitized, but coding defensively around the tentative existence of a database table sounds like something that's going to turn around and bite you later.