Just as a note, if you're using ON DELETE CASCADE with passive_deletes=True, SQLA will still explicitly delete whatever objects might already be loaded into collections, so that the Session stays in sync with what's currently in it. the CASCADE then occurs after this when SQLA deletes the parent row.
On Oct 27, 2008, at 4:57 PM, GustaV wrote: > > The reason I ask that is that it is not really safe to use both DB > cascades and SA delete management. > > The time comes quite fast when you don't know if an object is deleted > by SA or by the DB. So you don't know if you got into after_flush or > not in any case. > So : is there a true speed penalty to do it completely on the SA side? > > On 27 oct, 21:23, GustaV <[EMAIL PROTECTED]> wrote: >> Ok it looks good... >> >> I assume it also means I can't rely on ON DELETE CASCADE anymore, and >> I then must use passive_deletes=False. >> At least on those relations. >> >> In a general point of view, using MySQL InnoDB tables, is ON DELETE >> CASCADE more efficient than SA stuff? >> >> Thanks a lot for your help >> >> On 23 oct, 04:00, Michael Bayer <[EMAIL PROTECTED]> wrote: >> >>> I do this kind of thing...theres lots of variants on how to do this >>> but the idea of __after_flush__ is that everything, including >>> collections, have been committed (but the new, dirty and deleted >>> lists >>> have not been reset yet): >> >>> class OnFlushExt(orm.SessionExtension): >>> def after_flush(self, session, flush_context): >>> for obj in list(session.new) + list(session.dirty) + >>> list(session.deleted): >>> if hasattr(obj, '__after_flush__'): >>> obj.__after_flush__(session.connection()) >> >>> class MyObject(object): >>> def __after_flush__(self, connection): >>> connection.execute( >>> mytable.update().where(mytable.c.id==self.id).\ >>> values(object_count= >> >>> select >>> ([func >>> .count >>> (child_table.c.id)]).where(child_table.c.parent_id==mytable.c.id) >>> ) >>> ) >> >>> you could also batch all the updates into a single UPDATE statement >>> within after_flush(), that would be a lot more efficient (i.e. where >>> mytable.c.id.in_([all the ids of the changed parent objects]) ). >> >>> On Oct 22, 2008, at 5:41 PM, GustaV wrote: >> >>>> Ok... I'm not sure to understand the way you do it... >> >>>> Does it mean I should check the add and delete of regions in the >>>> transaction in after_flush() and issue sql directly (not using the >>>> ORM) to update the count in Country? >> >>>> On 22 oct, 20:46, Michael Bayer <[EMAIL PROTECTED]> wrote: >>>>> On Oct 22, 2008, at 2:05 PM, GustaV wrote: >> >>>>>> Hi all! >> >>>>>> In a 1-N relation between a country and its regions, I'm using an >>>>>> attribute extension to update the current count of regions into >>>>>> countries. It works very well when I append or remove regions >>>>>> from >>>>>> country. >>>>>> But if I delete one of the region directly >>>>>> (session.delete(region)), >>>>>> the country doesn't know it has lost one... >> >>>>>> I tried to use a MapperExtension.before_delete to manually remove >>>>>> the >>>>>> said region from the country, but it is not marked as "dirty" and >>>>>> then >>>>>> not updated... >> >>>>>> Any way to do it properly? >> >>>>> for a one-to-many relation, its often easy enough to just have a >>>>> cascade rule from country->region such that region is deleted >>>>> automatically when removed from the parent. This is the >>>>> typical way >>>>> to go about deletions from relations, since session.delete() >>>>> does not >>>>> cascade "backwards" to all owning collections. >> >>>>> although when I deal with columns that "count" something that is >>>>> elsewhere represented in the database, I often issue these using >>>>> SQL >>>>> within a SessionExtension.after_flush(). This removes the need to >>>>> worry about catching attribute events and just directly sets the >>>>> correct value based on the state of the transaction post-flush. > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
