1

I have a simple postgres database, here's an example table

id created_at whatever
1 2020-12-22 21:47:20.159781 something
2 2020-12-22 22:13:46.872718 anything

how can I do that whenever something changes in it, it triggers a function in my script?

Improve this question
1
  • The standard approach is to check the database for changes every so often (perhaps by adding a last_modified column in your table, and fetching rows that have been modified since the last time you checked). Postgres does support calling PG_SQL functions when tables are modified, but I don't know off the top of my head how you would get those to trigger your python code. Commented Dec 23, 2020 at 19:32

1 Answer 1

Reset to default
1

Postgres has the notify and listen procedures you can read up on here: notify and listen. Their use in Python is best (and I believe only) through pyscopg2

pip install psycopg2

import select
import psycopg2
import psycopg2.extensions


cnct = psycopg2.connect(DSN)
cnct.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

cursor = cnct.cursor()
cursor.execute("LISTEN channel;")
print("Pending notification from 'channel'...")


while True:
    if select.select([cnct],[],[],5) == ([],[],[]):
        print("Channel timed out.")
    else:
        cnct.poll()
        while cnct.notifies:
            notif = cnct.notifies.pop(0)
            print("Received NOTIFY:", notif.pid, notif.channel, notif.payload)

Forgot to mention!: This information was from what I versed myself with on the Psycopg2 Documentation and you might need to more elaborately familiarize yourself with using Pyscopg2 there.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lot for the answer, appreciate it. I'm actually using asyncpg as I'm more of an async guy, I think the equivalent for those functions is asyncpg.add_listener, I'm not sure tho
Ah, perhaps I should have figured that too; and you're very welcome! Seems to be the similar mechanism, yes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.