Waiting for a row update or row insertion with sqlalchemy

Question

I have an application that displays the contents of a database that is updates every 5-10 minutes. Loading all the data in the database takes about 5 minutes. Rather than reloading all the data in the database with each refresh, I would like to load only new rows or fields of rows that have been updated.

1. Is it possible to check for changed or new fields with sqlalchemy (to poll for changes)?
2. Is it possible to wait for changed or new fields with sqlalchemy (with a blocking function call)?

The database is a postgres database, if that matters.

Answer 1

SQLAlchemy doesn't directly support Postgres LISTEN/NOTIFY, but you can get at it via psycopg2, which then gives you this sort of capability: http://initd.org/psycopg/docs/advanced.html#async-notify

(I got to this from this gist: https://gist.github.com/dtheodor/3862093af36a1aeb8104)

Psycopg2 can do a fully asynchronous delivery of notifications. I haven't personally had much luck with this inside SQLAlchemy, and I suspect it's a bit too 'low level for them to support. However, the docs have an example reproduced below that uses select() to wait for the connection to say something is happening and then uses poll() on it to see if there's a notification.

import select
import psycopg2
import psycopg2.extensions

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

curs = conn.cursor()
curs.execute("LISTEN test;")

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

This example has an optional 5 second timeout, but sometimes that timeout is pretty handy to check some other stuff at the same time.

One such use-case is when you're using NOTIFY to tell you about a table being updated. You set the timeout to something quite long. If you receive a NOTIFY or a timeout you do a SELECT to see what's changed in the table. That way, you'll do it right away if you get a NOTIFY, but will eventually do it anyway even if you don't.

Answer 2

ProstgeSQL have a mechanism NOTIFY / LISTEN that can be used with triggers to send notifications of insert, delete and update. The notification includes an argument such as the table and action.

It doesn't look like SQLAlchemy supports this functionality, probably since it's not SQL standard but Postgres specific.

Here is an example of checking for changes in a prostgres database with NOTIFY / LISTEN using python: PostgreSQL LISTEN/NOTIFY

Googling on NOTIFY in PostgreSQL and python might provide more help.