A More Robust Database Interface

The PostgreSQL server, like other client-server DBMSs, is complex. Many things can go awry. Here is a sampling of possible problems, as reported by psycopg:

psycopg2.OperationalError: FATAL:  database "None" does not exist

psycopg2.OperationalError: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

psycopg2.InterfaceError: connection already closed

psycopg2.ProgrammingError: column "film_id" does not exist
LINE 1: select * from film where film_id=19777
                                 ^

psycopg2.DataError: integer out of range

psycopg2.IntegrityError: duplicate key value violates unique constraint "film_pkey"

psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

The first version of dblib.py, extracted from the minimalist command line interface, paid little or no attention to such errors. For example, connection errors simply caused a Python traceback and exit. The fetch functions caught exceptions, but returned None, implying to the caller that simply no rows were found. The more recent version (v0.2.1) used with the WSGI interface didn’t improve on much, therefore causing either HTTP 500 Internal Server errors or misleading the user about the problem.

This installment of the continuing database user interface tutorial aims to correct these shortcomings.

Since eventually we’ll deal with more logical entities than just films, the bl.py module has been moved to a bl subdirectory and renamed film.py. Instead of standalone functions, we now have a Film class, similar to the one present in the command line interface, but with the functions implemented as instance methods of the class (the get_all function is a classmethod). Each method catches database exceptions and re-raises them.

The dblib.py module now includes all the other elements of the PostgreSQL connection string. The connect method uses a DictConnection factory to allow easier (more readable) reference to row attributes. The execute method takes an additional argument, expcount, which indicates the number of rows expected to be affected by the database statement (with 2 meaning “more than 1″) and raises a DatabaseError if the rowcount does not match expectations.  The method also does a rollback automatically in case of an error (thus avoiding the InternalError seen above), and connects to the database if not already connected. The fetch methods raise exceptions rather than returning None.

Finally, the top level film.py module has been reorganized to respond to the changes above. The FilmHandler methods no longer have to connect prior to invoking a business logic or database method. The methods also catch exceptions and deal with them appropriately.

The new code is available on GitHub tagged as v0.2.2.

About these ads

5 thoughts on “A More Robust Database Interface”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s