Get warning messages through psycopg2

Question

I want to call a plpgsql function through psycopg2 and see the warning messages. I.e, I have this function:

create or replace function test_warning() returns void as $$
begin
raise warning 'this is only a test';
end; 
$$
language plpgsql;

and call it so in python:

import psycopg2
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.callproc("test_warning")
# or so:
cursor.execute('SELECT test_warning()')

Unfortunately the warning message as defined in plpgsql does not appear anywhere in the python output. Is there a way to get the warning message printed in the python output?

Clodoaldo Neto · Accepted Answer · 2014-12-02T11:47:41.077

27

The notices member of the connection is a list of the session's messages sent to the client up to that point:

for notice in conn.notices:
    print notice

http://initd.org/psycopg/docs/connection.html#connection.notices

To get the last notice:

print conn.notices[-1]

If an exception is raised inside a function, and not caught, no warning will be received. That is because a function wraps an implicit transaction and everything inside that transaction is rolled back including warnings.

edited Dec 02 '14 at 11:47

answered Dec 01 '14 at 23:08

Clodoaldo Neto

118,695
26
233
260

2

Thank you very much, this works. But what if I use the same connection for many cursors? I tried resetting notices so: `conn.notices={}` but I got the exception TypeError: readonly attribute. And can I get the warning while the function is executed? Say, the function raises a warning and then a exception: I would like to see the warning before the exception is raised. – Tom-db Dec 02 '14 at 07:51
4

@Tomm you can clear the notices with `del conn.notices[:]`. You cannot have the notices while executing in sync mode. Maybe in async mode yes but I've never tried it. – piro Dec 05 '14 at 12:34

score 6 · Answer 2 · answered Dec 05 '18 at 09:53

I don't have the reputation to comment on Clodoaldo's answer, but my solution for getting the latest notices (note that a query can generate several notices) is pretty straightforward:

def execute_query(query, conn):
    logger = logging.getLogger('test')
    nr_notices = len(conn.notices)
    cursor = conn.cursor()
    cursor.execute(query)
    for notice in conn.notices[nr_notices:]:
        logger.info(f'NOTICE: {notice}.')

Get warning messages through psycopg2

2 Answers2

Linked