Обсуждение: How to get a signal from the database when a INSERT INTO is done?
Hi all, I'm designing an application in Python and using PostgreSQL. This is a mixed question Python/PostgreSQL... I need to get a signal in my python application when a new insert is done. How can this be done, any clues? Best Regards, André
Re: How to get a signal from the database when a INSERT INTO is done?
От
hubert depesz lubaczewski
Дата:
On Tue, Feb 28, 2012 at 11:07:47AM +0000, Andre Lopes wrote: > Hi all, > > I'm designing an application in Python and using PostgreSQL. > > This is a mixed question Python/PostgreSQL... I need to get a signal > in my python application when a new insert is done. How can this be > done, any clues? listen/notify http://www.postgresql.org/docs/current/interactive/sql-listen.html http://www.postgresql.org/docs/current/interactive/sql-notify.html Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
On Tue, Feb 28, 2012 at 13:07, Andre Lopes <lopes80andre@gmail.com> wrote: > This is a mixed question Python/PostgreSQL... I need to get a signal > in my python application when a new insert is done. How can this be > done, any clues? As depesz mentioned, you can use the LISTEN and NOTIFY commands for this asynchronous signalling. You can add an after-insert trigger to send the NOTIFY. On the Python end, you have to call psycopg2 connection.poll() method periodically or in response to select() activation, and check the connection.notifies list. Note that this list can grow even when you run other queries -- not just on poll. There's an example here: http://initd.org/psycopg/docs/advanced.html#async-notify Regards, Marti
On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp <marti@juffo.org> wrote: > On Tue, Feb 28, 2012 at 13:07, Andre Lopes <lopes80andre@gmail.com> wrote: >> This is a mixed question Python/PostgreSQL... I need to get a signal >> in my python application when a new insert is done. How can this be >> done, any clues? > > As depesz mentioned, you can use the LISTEN and NOTIFY commands for > this asynchronous signalling. You can add an after-insert trigger to > send the NOTIFY. > > On the Python end, you have to call psycopg2 connection.poll() method > periodically or in response to select() activation, and check the > connection.notifies list. Note that this list can grow even when you > run other queries -- not just on poll. > > There's an example here: > http://initd.org/psycopg/docs/advanced.html#async-notify Using an asynchronous IO-driven framework such as eventlet or gevent you don't even need to poll the connection to look for events: you just get notified as soon as there is something to read. Here is an example: http://initd.org/psycopg/articles/2010/12/01/postgresql-notifications-psycopg2-eventlet/ It's enough to have a NOTIFY executed in a trigger on insert, maybe putting the id in the notify payload, and you get timely notifications about inserts in Python world. You may be able to do something similar in Twisted using txPostgres: when I asked Jan about that, IIRC he said they weren't ready yet, but it was several months ago, it may have changed since then. -- Daniele
On Tue, Feb 28, 2012 at 17:41, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp <marti@juffo.org> wrote: >> On the Python end, you have to call psycopg2 connection.poll() method >> periodically or in response to select() activation >> There's an example here: >> http://initd.org/psycopg/docs/advanced.html#async-notify > Using an asynchronous IO-driven framework such as eventlet or gevent > you don't even need to poll the connection to look for events As mentioned above and as demonstrated in the example, select() also does the job. Using such a fancy framework is usually an overkill. Regards, Marti
On Tue, Feb 28, 2012 at 3:45 PM, Marti Raudsepp <marti@juffo.org> wrote: > On Tue, Feb 28, 2012 at 17:41, Daniele Varrazzo > <daniele.varrazzo@gmail.com> wrote: >> On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp <marti@juffo.org> wrote: >>> On the Python end, you have to call psycopg2 connection.poll() method >>> periodically or in response to select() activation >>> There's an example here: >>> http://initd.org/psycopg/docs/advanced.html#async-notify > >> Using an asynchronous IO-driven framework such as eventlet or gevent >> you don't even need to poll the connection to look for events > > As mentioned above and as demonstrated in the example, select() also > does the job. Using such a fancy framework is usually an overkill. Yeah, the problem is usually if you have to do something else apart from listening from the notification. select() will block the entire application, so you would put it into a separate thread to have the app running on. From here to start benefiting from eventlet the step is very short. If a blocking behaviour is ok, then no problem using bare select(). -- Daniele
Daniele Varrazzo <daniele.varrazzo@gmail.com> writes: >> As mentioned above and as demonstrated in the example, select() also >> does the job. Using such a fancy framework is usually an overkill. > Yeah, the problem is usually if you have to do something else apart > from listening from the notification. select() will block the entire > application, so you would put it into a separate thread to have the > app running on. I don't know Python, but the C library function of select() has a timeout parameter that can be set to 0.