How do i get PostgreSQL notifications work with python?

Question

i was trying to follow this example from stackoverflow using pg_notify with python. I am not getting this to work. Nothing happens and python does not receive the notification.

Python 3.85 Using Psycopg2. PostgreSQL 13

python & postgresql: reliably check for updates in a specific table

First. I created a python function that listened to the postgreSQL. Then i went to pgadmin and executed

select pg_notify('process', 'update');

My python function is below

def dblisten(connection):
    cur = connection.cursor()
    print("inside")
    cur.execute("LISTEN process")
    while True:
        print("listening")
        select.select([connection],[],[])
        connection.poll()
        events = []
        while connection.notifies:
            notify = connection.notifies.pop().payload
            print ("Got NOTIFY:", datetime.datetime.now(), notify.pid, notify.channel, notify.payload)
if __name__ == '__main__':  # If it's executed like a script (not imported)
connection = psycopg2.connect(host='host', user='user',
                            password='password')
connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        dblisten(connection)

connection = psycopg2.connect(host='host', user='user', password='password') connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) — user2238704
– user2238704, Commented May 19, 2021 at 14:02
I works for me. Or at least, something happens when I send the notice. But what happens is some NameErrors. Maybe I mis-guessed about what your imports are supposed to be. — jjanes
– jjanes, Commented May 19, 2021 at 18:06
One thing that's bitten me with this before - both sides must be connected to the same database: if the python connection is connected to db1 then pg_notify must be called from within db1 too. — snakecharmerb
– snakecharmerb, Commented May 19, 2021 at 18:22

akpp · Accepted Answer · 2024-12-13 23:40:23Z

I've get an error AttributeError: 'str' object has no attribute 'pid'
small fix in order to run it: notify = connection.notifies.pop().payload -> notify = connection.notifies.pop()

Here is a full example that was taken from documentation and a bit changed, it is without timeout, thanks @snakecharmerb for link listen.py

import select

import psycopg2.extensions

CHANNEL = 'process'
# DSN = f'postgresql://{USER}:{password}@{HOST}/{DBNAME}'


def listen():
    curs = conn.cursor()
    curs.execute(f"LISTEN {CHANNEL};")

    print("Waiting for notifications on channel 'test'")
    while True:
        select.select([conn], [], [], 5)
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print("Got NOTIFY:", notify.pid, notify.channel, notify.payload)


if __name__ == '__main__':
    conn = psycopg2.connect(host=HOST, dbname=DBNAME, user=USER, password=PASSWD)
    # conn = psycopg2.connect(DSN) # alternative connection
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    listen()

Run listener python listen.py
Via pgAdmin Query Tool or Postgres Shell run select pg_notify('process', 'update'); or just NOTIFY process, 'This is the payload';

note: it should be the same DB, for listener and notifier

Collectives™ on Stack Overflow

How do i get PostgreSQL notifications work with python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related