Python Error Handling with the Psycopg2 PostgreSQL Adapter 645

Introduction

This article will provide a brief overview of how you can better handle PostgreSQL Python exceptions while using the psycopg2 adapter in your code. Make sure that the psycopg2 package is installed on your machine using the PIP3 package manager for Python 3 using the following command:

We’ll also be building a function from scratch that prints detailed information about the psycopg2 exceptions by accessing several of its exception library attributes. It should be noted, however, that this is mostly for educational and debugging purposes, and it should be noted that, in the implementation phase your website or application, you may want to handle them less explicitly.

Catching and handling exceptions in Python

A Python script will terminate as soon as an exception or error is raised, but there is a try-except block (that works in a similar fashion to the try {} catch(err) {} code block in PHP or JavaScript) that will allow you to catch the exception, handle it, and then respond to it with more code within the except: part of the indentation block.

The following code allows you to catch all exceptions, as a wildcard, and print them out without having to explicitly mention the exact exception:

the except Exception as error: bit will allow you to handle any exception, and return the exception information as a TypeError class object using Python’s as keyword.

Exception libraries for the psycopg2 Python adapter

Some of the two most commonly occurring exceptions in the psycopg2 library are the OperationalError and ProgrammingError exception classes.

An OperationalError typically occurs when the parameters passed to the connect() method are incorrect, or if the server runs out of memory, or if a piece of datum cannot be found, etc.

A ProgrammingError happens when there is a syntax error in the SQL statement string passed to the psycopg2 execute() method, or if a SQL statement is executed to delete a non-existent table, or an attempt is made to create a table that already exists, and exceptions of that nature.

Complete list of the psycopg2 exception classes

Here’s the complete list of all of psycopg2 exception classes:

InterfaceError, DatabaseError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, and NotSupportedError.

Brief overview of PostgreSQL Error Codes

There is an extensive list of over 200 error codes on the postgresql.org website that describes, in detail, each five-character SQL exception.

In the psycopg2 adapter library you can return the code by accessing the exception’s pgcode attribute. It should be an alpha-numeric string, five characters in length, that corresponds to an exception in the PostgreSQL Error Codes table.

Here’s some example code showing how one can access the attribute for the PostgreSQL error code:

Import the exception libraries for the psycopg2 Python adapter

You’ll need to import the following libraries at the beginning of your Python script:

Get the psycopg2 version string

Older versions of the psycopg2 adapter may handle some exceptions differently. Here’s some code that imports the __version__ attribute string for the psycopg2 library and prints it:

Define a Python function to handle and print psycopg2 SQL exceptions

The code in this section will define a Python function that will take a Python TypeError object class and parse, both the psycopg2 and native Python, exception attributes from it in order to print the details of the exception:

Define the ‘print_psycopg2_exception()’ Python function

Use Python’s def keyword to define a new function and make it accept a TypeError Python object class as its only parameter:

Use Python’s built-in ‘sys’ library to get more detailed exception information

The next bit of code grabs the traceback information for the exception, including the line number in the code that the error occurred on, by calling the sys library’s exc_info() method:

Print the details for the psycopg2 exception

Use Python’s print() function to print the details of the psycopg2 exception that was passed to the function call:

Handle psycopg2 exceptions that occur while connecting to PostgreSQL

Now that the function has been defined it’s time to test it out by running some psycopg2 code. The following Python code attempts to make a connection to PostgreSQL in a try-except indentation block, and, in the case of an exception, passes the TypeError Python object to the print_psycopg2_exception() function defined earlier:

NOTE: The above code will give the connection object a value of None in the case of an exception.

If the username string, passed to the user parameter, doesn’t match any of the users for the PostgreSQL server then the function should print something that closely resembles the following:

Handle psycopg2 exceptions that occur while executing SQL statements

If the code to connect to PostgreSQL didn’t have any problems, and no exceptions were raised, then test out the function again. The following code purposely attempts to use a cursor object to execute() a SQL statement with bad syntax:

The print_psycopg2_exception() function should print a response that resembles the following:

The 42601 PostgreSQL code indicates that the exception resulted from a syntax error in the SQL statement:

Catch ‘InFailedSqlTransaction’ psycopg2 exceptions

This last bit of Python code will raise a InFailedSqlTransaction exception if the last PostgreSQL transaction, with the bad SQL statement, wasn’t rolled back using the connection object’s rollback() method:

Conclusion

The psycopg2 library adapter for PostgreSQL has an extensive list of exception Python classes, and this article only covered a few of them just to give a general idea of how you can handle such exceptions in your own Python script.

Just the Code