After insert/update events

[Paddy Mullen]

I have been trying to create a nice decorator for tasks that are methods of models.  I want the tasks to run after specific conditions (after_update/insert, with predicates).

I was able to set this up through a series of  event hooks starting with  "mapper_configured". 

The problem I have run into is, listening for "after_insert" on a subclass extending DeclarativeBase, only results in calls after insert has been called for, but not after it has been executed.  I can listen on the engine to "after_execute", which does seem to give a callback after commit/flush has actually been called, but at this I have no declarativeBase references to the objects that have been inserted, only to the raw sql.

Am I missing something?

Here are the example files
https://github.com/paddymul/sqlalchemy_garden/blob/master/lib/deferred_task.py - the library

https://github.com/paddymul/sqlalchemy_garden/blob/master/schemas/deferred_schema.py - an example usage

Thanks,
Paddy

[jo]

This is the way I solved the problem... (how to backup a table row into
another table before delete or update it) ...if it can help:

from sqlalchemy.orm import MapperExtension
class History(MapperExtension):
def __init__(self):
MapperExtension.__init__(self)
self.methods = ('before_update','before_delete')

def clone(self, mapper, connection, instance, action):
tablename=mapper.mapped_table.name
tablename_bak=tablename+'_bak'
rec = connection.execute(select([tbl[tablename]], tbl[tablename].c.id ==
getattr( instance, '%s_id'%tablename))).fetchone()
dd=dict()
modified=False
for k,v in rec.items():
dd[ k.lower() ] = v
if action=='D': #if delete request...
connection.execute(tbl[tablename_bak].insert(values=dd))
elif action=='U': #if update request...
for k in instance.c: #check for differences, to save it only if it was
modified...
if getattr( instance,str(k).replace('.','_')) != dd[str(k).split('.')[1]]:
modified=True
break
if modified is True:
connection.execute(tbl[tablename_bak].insert(values=dd))
return

def before_update(self, mapper, connection, instance):
return self.clone(mapper, connection, instance, 'U')

def before_delete(self, mapper, connection, instance):
return self.clone(mapper, connection, instance, 'D')

I call it in this way:

from ... import History
mapper(Anagrafica,
tbl['anagrafica'],
column_prefix = 'anagrafica_',
extension=History(),
)

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/IpLW9LroG6IJ.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.

--
Jose Soares _/_/
Sferacarta Net
Via Bazzanese 69 _/_/ _/_/_/
40033 Casalecchio di Reno _/_/ _/_/ _/_/
Bologna - Italy _/_/ _/_/ _/_/
Ph +39051591054 _/_/ _/_/ _/_/ _/_/
fax +390516131537 _/_/ _/_/ _/_/ _/_/
web:www.sferacarta.com _/_/_/ _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l�integrit� e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

[Paddy Mullen]

MapperExtensions seem to be a deprecated interface to the same functionality that I am using with events http://docs.sqlalchemy.org/en/latest/orm/interfaces.html . 

I guess my fundamental problem is that I don't see a straightforward way to get a reference to a model instance in an "after_insert" or "after_update" event, after the changes have actually been committed to the database.  Updates aren't as big a problem as inserts.  For inserts, you can't easily enqueue the id of the model, because that ID hasn't been assigned yet.

I don't want to have to worry about firing off an enqueued task in every place I modify a model.  I think it is better to encapsulate that logic in one place.

> sqlalchemy+unsubscribe@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Apr 11, 2012, at 1:24 AM, Paddy Mullen wrote:

>
> The problem I have run into is, listening for "after_insert" on a subclass extending DeclarativeBase, only results in calls after insert has been called for, but not after it has been executed.

I'm not familiar with this behavior, can you illustrate ? after_insert happens after all INSERT statements for that particular entity. are you not observing that?

[Michael Bayer]

On Apr 11, 2012, at 11:07 AM, Paddy Mullen wrote:

MapperExtensions seem to be a deprecated interface to the same functionality that I am using with events http://docs.sqlalchemy.org/en/latest/orm/interfaces.html . 

I guess my fundamental problem is that I don't see a straightforward way to get a reference to a model instance in an "after_insert" or "after_update" event, after the changes have actually been committed to the database.  Updates aren't as big a problem as inserts.  For inserts, you can't easily enqueue the id of the model, because that ID hasn't been assigned yet.

The instance that was just inserted is passed to after_insert() as "target" (or "instance" in the old system), and the primary key is absolutely there in the after_insert() event, regardless of if the old or the new event system is in use:

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import event

Base= declarative_base()

class A(Base):

    __tablename__ = "a"

    id = Column(Integer, primary_key=True)

@event.listens_for(A, "after_insert")

def assert_pk_is_there(mapper, conn, target):

    print "PK!", target.id

    assert target.id is not None

e = create_engine("sqlite://")

s = Session(e)

Base.metadata.create_all(e)

s.add(A())

s.commit()

classics-MacBook-Pro:sqlalchemy classic$ python test.py

PK! 1

[Paddy Mullen]

I could have sworn I saw conflicting behaviour.

 My after_insert would run, and the instance would have an id, it would then enqueue a task in celery.  On the other side (in another process), celery would run that task, make a query against that database for that ID, and the ID wouldn't be in the database.

I think I can reproduce this in a single process too.  I hope this is an error in my code.

Let me work on a testcase.

Thanks,
Paddy

[Michael Bayer]

On Apr 11, 2012, at 12:12 PM, Paddy Mullen wrote:

> I could have sworn I saw conflicting behaviour.
>
> My after_insert would run, and the instance would have an id, it would then enqueue a task in celery. On the other side (in another process), celery would run that task, make a query against that database for that ID, and the ID wouldn't be in the database.

oh, that's completely different. Your flush is inside of a transaction. Your newly generated id *will not* be exposed to the outside world until the transaction commits. If you're spawning off celery tasks that are dependent on the newly flushed data, you *must* use after_commit() for that. Not just for isolation purposes, but what if your transaction failed and was rolled back - then celery would be failing there as well.

[Paddy Mullen]

 

Thanks, That was the problem.  I think I have it working now.  I will update my repo for you to take a look at later. 

Thanks, that was the problem.  I think I have it working now.  I will update my repo for future examples later today.

To make it work, I listen to the after_insert event and append callback functions onto the "do_its" property of  the model instance.
Then I listen to "after_flush" on the session object, here I iterate through dirty and new objects and grab their "do_its" appending them onto the session.
I also listen to "after_commit" on the session object.  Here I iterate through "do_its" and call them, then I clear the "do_its" list on the session object.
I should also clear the "do_its" on the session object on the "after_soft_rollback" event.

It seems like I might have a common usecase here.  What would you think of adding  "after_insert_committed", "after_update_committed", and after "delete_committed" events to the Table object.  I would be interested in taking a crack at it.

Thanks,
Paddy

 

 

[Michael Bayer]

On Apr 11, 2012, at 5:11 PM, Paddy Mullen wrote:

>
> It seems like I might have a common usecase here. What would you think of adding "after_insert_committed", "after_update_committed", and after "delete_committed" events to the Table object. I would be interested in taking a crack at it.

At some point I'll be adding features to the Session events such that filters can be applied, which will then emit a "sub-event" based on filtering for all objects with certain characteristics - a certain class, or their presence in .new, .dirty, .deleted (these are public lists). These will be available for events like before_flush and after_flush.

Within after_commit(), things are trickier as we have only a list of items that entered the session via .new and items that entered the session via .deleted, but not the public version of these lists, they're separate lists used just to maintain things in case we rollback. These collections don't cover objects that were garbage collected, so there really can't be such an event that will "work" the way everyone would expect it to.

So your best bet is to use before_flush, after_flush, or even after_insert/after_update, and populate a collection that's associated with the Session.