Getting error messages from psycopg2 exceptions

Question

This is my first project using psycopg2 extensively. I'm trying to find a way to extract the psql error message for whenever a connection attempt fails. I've tested the code below will work if all the variables are set correctly, however whenever an error condition occurs (e.g. user chooses a database that doesn't exist), Python will give me the following:

I am unable to connect to the database
None
Traceback (most recent call last):
  File "./duplicate_finder.py", line 163, in <module>
    main(sys.argv[1:])
  File "./duplicate_finder.py", line 142, in main
    print e.diag.message_detail
AttributeError: 'OperationalError' object has no attribute 'diag'

Is there a simple, catch-all method to catch whatever error message psql generates when a connection fails, or do I need to write except blocks for multiple psycopg2 exceptions?

Extract from my script:

import sys, getopt, os, time, csv, psycopg2

    ...
    ...

    conn_string = "host=" + dbhost + " dbname=" + database + " user=" + dbuser + " password=" + dbpass
    try:
        conn = psycopg2.connect(conn_string)
    except psycopg2.Error as e:
        print "Unable to connect!"
        print e.pgerror
        print e.diag.message_detail
        sys.exit(1)
    else:
        print "Connected!"
        cur = conn.cursor()
        cur.execute("SELECT id, lastname, firstname, location FROM test ORDER BY ctl_upd_dttm DESC;")
        print cur.fetchone()
        ...
        conn.close()

sdemurjian · Answer 1 · 2015-08-17T18:24:31.323

When I try to catch exceptions, e.pgerror is always None for connection errors. The following code block gets around this by directly printing 'e'.

try:
    conn = psycopg2.connect(conn_string)
except psycopg2.OperationalError as e:
    print('Unable to connect!\n{0}').format(e)
    sys.exit(1)
else:
   print('Connected!')
   # do stuff

For example, in the case of password authentication failure:

Unable to connect!
FATAL:  password authentication failed for user "user"

I realize this question is a year old but hopefully might help someone in the future

Alvaro Rodriguez Scelza · Answer 2 · 2020-07-01T20:57:41.183

8

Ended up here because of

class 'psycopg2.errors.InvalidCursorName'

on Django. If that's your case, be sure to makemigrations

edited Jul 01 '20 at 20:57

answered Mar 10 '20 at 23:12

Alvaro Rodriguez Scelza

3,643
2
32
47

score 5 · Accepted Answer · answered Jun 20 '14 at 17:19

5

You are catching all exceptions with the base class psycopg2.Error. Your problem is probably that the diag attribute is new in psycopg2 2.5. What is your version?

>>> print psycopg2.__version__
2.5.1 (dt dec pq3 ext)

answered Jun 20 '14 at 17:19

Clodoaldo Neto

118,695
26
233
260

>>> import psycopg2 >>> print psycopg2.__version__ 2.0.14 (dt dec ext pq3) Looks like I'm a few versions behind. Unfortunately, looks like that's the most up-to-date version according to my distro's (Scientific Linux) repos + epel. Might have to go the source route with this . . . – sirjames2004 Jun 20 '14 at 18:26
Just curious, before I embark on a potentially hazardous quest of manually upgrading psycopg2, is there a way to get the psql connect error messages in older versions of psycopg2, or am I stuck with just giving them a generic message when something goes wrong? – sirjames2004 Jun 20 '14 at 21:51
@sirjames2004 I don't know. But installing with `setup` or `pip` is quite easy: http://initd.org/psycopg/install/. Just remember to `yum remove psycopg2` first. – Clodoaldo Neto Jun 20 '14 at 22:05
@sirjames2004 And you need Python 2.5+ for psycopg 2.5 – Clodoaldo Neto Jun 20 '14 at 22:30

Hans Ginzel · Answer 4 · 2021-03-15T10:01:59.817

2

Since Python 3.9 (.removesuffix(), f-strings) I use

except psycopg2.Error as e:
  log.error(f"{type(e).__module__.removesuffix('.errors')}:{type(e).__name__}: {str(e).rstrip()}")
  if conn: conn.rollback()

where log is logger.

Connection errors lives directly in psycopg2 module while syntax errors in psycopg2.errors submodule. There is an unwanted newline at the end of every psycopg2 error message.

edited Mar 15 '21 at 10:01

answered Mar 15 '21 at 09:52

Hans Ginzel

8,192
3
24
22

Getting error messages from psycopg2 exceptions

4 Answers4