Catch python DatabaseErrors generically

Question

I have a database schema that might be implemented in a variety of different database engines (let's say an MS Access database that I'll connect to with pyodbc or a SQLite database that I'll connect to via the built-in sqlite3 module as an simple example).

I'd like to create a factory function/method that returns a database connection of the appropriate type based on some parameter, similar to the following:

def createConnection(connType, params):
    if connType == 'sqlite':
        return sqlite3.connect(params['filename'])
    elif connType == 'msaccess':
        return pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ={};'.format(params['filename']))
    else:
        # do something else

Now I've got some query code that should work with any connection type (since the schema is identical no matter the underlying DB engine) but may throw an exception that I'll need to catch:

db = createDatabase(params['dbType'], params)

cursor = db.cursor()

try:
    cursor.execute('SELECT A, B, C FROM TABLE')
    for row in cursor:
        print('{},{},{}'.format(row.A, row.B, row.C))

except DatabaseError as err:
    # Do something...

The problem I'm having is that the DatabaseError classes from each DB API 2.0 implementation don't share a common base class (other than the way-too-generic Exception), so I don't know how to catch these exceptions generically. Obviously I could do something like the following:

try:
    # as before
except sqlite3.DatabaseError as err:
    # do something
except pyodbc.DatabaseError as err:
    # do something again

...where I included an explicit catch block for each possible database engine. But this seems distinctly non-pythonic to me.

How can I generically catch DatabaseErrors from different underlying DB API 2.0 database implementations?

Answer 1

There is a number of approaches :

1. Use a catch-all exception and then work out what exception it is. If it is not in your list, raise the exception again (or your own). See: Python When I catch an exception, how do I get the type, file, and line number?

2. Perhaps you want to take the problem in a different way: your factory code should also provide the exception to test for.

3. A simpler approach in my view (and the one I use in practice), is to have a class for all database connections, and to subclass it for each specific database type/syntax. Inheritance allows you to take care of all specificities. For some reason, I never had to worry about this issue.