On Sun, Aug 13, 2017 at 2:37 PM, Ronuk Raval <ronuk.ra...@gmail.com> wrote: > On Sunday, August 13, 2017 at 1:35:47 PM UTC-4, Jonathan Vanasco wrote: >> >> When I've been unable to handle things like this with cascades (which you >> already using), I use `session.flush(objects=SPECIFIC_OBJECTS_LIST)` to only >> flush a single object. That usually gets me out of these situations. > > > Unfortunately, the code I'm working with is in a deeply nested callstack > where the current transaction can be in all sorts of states. An arbitrary > flush can lead to constraint violations and I'd have to change quite a bit > of code to make it work.
The unit of work makes decisions about table and row ordering based on dependencies between mappers, and dependencies between mappers are indicated exclusively through the relationship() construct (also inheritance, which doesn't apply here). Usually, I build a relationship() and work with that, translating whatever the direct manipulation of FK attributes is into something in terms of the relationship. However here, that's not really possible. The closest thing this resembles is that you are altering the "key" of A and the it would cascade to "B" - the ORM assumes you need to use ON UPDATE CASCADE for that kind of thing, which takes care of it, or if you are using foreign keys that are deferred or non-enforcing, the ORM can cascade the value but it would be doing it from A first then B which is the opposite of what you want here. ON UPDATE CASCADE can't be used here anyway because this is an odd use of MATCH SIMPLE on the FK and where normally setting some of the columns to NULL means you're no longer referencing some row elsewhere, so the NULL does not cascade (PG's behavior, tried it, doesn't work). The use case I can find for MATCH SIMPLE seems to be that the FK would no longer refer to a row that matches the whole constraint, like the example in https://dba.stackexchange.com/questions/148530/what-would-i-use-a-match-simple-foreign-key-for . The use of NULL as a meaningful boolean value is possibly straying outside of normal relational practice. In particular it's not even possible to make a relationship() that represents this because PG does not seem to allow me to use the "IS" operator here, which would be necessary for the NULL values to line up but it keeps throwing syntax errors for things like "a.archived IS b.archived", not sure why. Onto the workarounds: The flush() as mentioned earlier per object can be linked to setting the "archived" flag using a hybrid like this: from sqlalchemy.ext.hybrid import hybrid_property class B(Base): __tablename__ = 'b' _archived = sa.Column("archived", sa.Boolean, nullable=True) @hybrid_property def archived(self): return self._archived @archived.setter def archived(self, value): self._archived = value sa.orm.object_session(self).flush([self]) alternatively you can emit the UPDATE directly, so that no other aspects of B are affected, and skip history events so that the B isn't added to the flush unless something else changes: @archived.setter def archived(self, value): sa.orm.object_session(self).execute( self.__table__.update().values(archived=value) ) sa.orm.attributes.set_committed_value( self, '_archived', value) I'd go for the latter as that puts the UPDATE and manipulation of this attribute in total stealth mode, not affecting or affected by anything ORM-level. > > I'm not opposed to doing that work but would like to explore the possibility > of making the unit of work UPDATE order match my expectations. If that's not > possible then c'est la vie. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
