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:

pip3 install psycopg2

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:

try:
    a = 1234
    print (a + " hello world")
except Exception as error:
    print ("Oops! An exception has occured:", error)
    print ("Exception TYPE:", type(error))

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.

Screenshot of Python IDLE returning exception information in a try-except indentation block

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:

    try:
        cursor.execute("INVALID SQL STATEMENT")
    except Exception as err:
        print ("Oops! An exception has occured:", error)
        print ("Exception TYPE:", type(error))

Import the exception libraries for the psycopg2 Python adapter

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

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

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:

# import the psycopg2 library's __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ("psycopg2 version:", psycopg2_version, "\n")

Screenshot of Python's IDLE getting the version string for psycopg2

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:

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception(err):

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:

    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()

    # get the line number when exception occured
    line_num = traceback.tb_lineno

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:

    # print the connect() error
    print ("\npsycopg2 ERROR:", err, "on line number:", line_num)
    print ("psycopg2 traceback:", traceback, "-- type:", err_type)

    # psycopg2 extensions.Diagnostics object attribute
    print ("\nextensions.Diagnostics:", err.diag)

    # print the pgcode and pgerror exceptions
    print ("pgerror:", err.pgerror)
    print ("pgcode:", err.pgcode, "\n")

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:

# declare a new PostgreSQL connection object
try:
    conn = connect(
        dbname = "python_test",
        user = "WRONG_USER",
        host = "localhost",
        password = "mypass"
    )
except OperationalError as err:
    # pass exception to function
    print_psycopg2_exception(err)

    # set the connection to 'None' in case of error
    conn = None

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:

psycopg2 ERROR: FATAL:  password authentication failed for user "WRONG_USER"
FATAL:  password authentication failed for user "WRONG_USER"
 on line number: 43
psycopg2 traceback: <traceback object at 0x7fa361660a88> -- type: <class 'psycopg2.OperationalError'>

extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7fa3646e1558>
pgerror: None
pgcode: None

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:

# if the connection was successful
if conn != None:

    # declare a cursor object from the connection
    cursor = conn.cursor()
    print ("cursor object:", cursor, "\n")

    # catch exception for invalid SQL statement
    try:
        cursor.execute("INVALID SQL STATEMENT")
    except Exception as err:
        # pass exception to function
        print_psycopg2_exception(err)

        # rollback the previous transaction before starting another
        conn.rollback()

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

psycopg2 ERROR: syntax error at or near "INVALID"
LINE 1: INVALID SQL STATEMENT
        ^
 on line number: 90
psycopg2 traceback: <traceback object at 0x7f58dd244188> -- type: <class 'psycopg2.errors.SyntaxError'>

extensions.Diagnostics: <psycopg2.extensions.Diagnostics object at 0x7f58e0018558>
pgerror: ERROR:  syntax error at or near "INVALID"
LINE 1: INVALID SQL STATEMENT
        ^

pgcode: 42601

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

Screenshot of the PostgreSQL Error Codes web page in a browser tab

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:

    # returns 'psycopg2.errors.InFailedSqlTransaction' if rollback() not called
    try:
        cursor.execute("SELECT * FROM some_table;")
    except errors.InFailedSqlTransaction as err:
        # pass exception to function
        print_psycopg2_exception(err)

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

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

# import the psycopg2 library's __version__ string
from psycopg2 import __version__ as psycopg2_version
```python
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connectDoes NOT need TextBroker to re-write

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError, errorcodes, errors

# import the psycopg2 library's __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ("psycopg2 version:", psycopg2_version, "\n")

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception(err):
    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()

    # get the line number when exception occured
    line_num = traceback.tb_lineno

    # print the connect() error
    print ("\npsycopg2 ERROR:", err, "on line number:", line_num)
    print ("psycopg2 traceback:", traceback, "-- type:", err_type)

    # psycopg2 extensions.Diagnostics object attribute
    print ("\nextensions.Diagnostics:", err.diag)

    # print the pgcode and pgerror exceptions
    print ("pgerror:", err.pgerror)
    print ("pgcode:", err.pgcode, "\n")

try:
    conn = connect(
        dbname = "python_test",
        user = "objectrocket",
        host = "localhost",
        password = "mypass"
    )
except OperationalError as err:
    # pass exception to function
    print_psycopg2_exception(err)

    # set the connection to 'None' in case of error
    conn = None

# if the connection was successful
if conn != None:

    # declare a cursor object from the connection
    cursor = conn.cursor()
    print ("cursor object:", cursor, "\n")

    # catch exception for invalid SQL statement
    try:
        cursor.execute("INVALID SQL STATEMENT")
    except Exception as err:
        # pass exception to function
        print_psycopg2_exception(err)

        # rollback the previous transaction before starting another
        conn.rollback()

    # execute a PostgreSQL command to get all rows in a table
    # returns 'psycopg2.errors.InFailedSqlTransaction' if rollback() not called
    try:
        cursor.execute("SELECT * FROM some_table;")
    except errors.InFailedSqlTranroughsaction as err:
        # pass exception to function
        print_psycopg2_exception(err)

    # close the cursor object to avoid memory leaks
    cursor.close()

    # close the connection object also
    conn.close()

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.