I see, thanks for the clarification about noload. Setting aside my specific issue, if using the session in before_insert/before_update events is not a good idea, what is generally the best way to perform validation across multiple models? Is it to just read the relationships from the model that's being validated, relying on the automatic loading of those relationships?
On Friday, October 26, 2018 at 3:20:01 PM UTC-7, Mike Bayer wrote: > > also, your test seems to be making use of the "before_insert" and > "before_update" events, inside which it's generally not a good idea to > use the Session since you're in the middle of a flush, and also that > event hook is before the SQL is emitted in any case, and addtionally > the state of objects in memory has not been expired yet, so you're > going to see all stale data. you'd want to turn on echo=True at > least and see what's happening when. > On Fri, Oct 26, 2018 at 2:20 PM <[email protected] <javascript:>> > wrote: > > > > I have the following three models (with other columns omitted): > > > > > > class ContainerSlot(db.Model): > > __tablename__ = 'container_slots' > > > > id = db.Column(db.Integer, primary_key=True) > > tube_id = db.Column(db.Integer, db.ForeignKey('tubes.id'), > unique=True, index=True) > > container_id = db.Column(db.Integer, db.ForeignKey('containers.id'), > nullable=False, index=True) > > > > tube = db.relationship('Tube', uselist=False, > back_populates='container_slot', lazy='noload') > > container = db.relationship('Container', uselist=False, > innerjoin=True, back_populates='container_slots', lazy='joined') > > > > > > > > class Container(db.Model): > > __tablename__ = 'containers' > > > > id = db.Column(db.Integer, primary_key=True) > > flag = db.Column(db.Boolean) > > container_slots = db.relationship('ContainerSlot', lazy='noload', > back_populates='container', order_by='ContainerSlot.id') > > > > > > class Tube(db.Model): > > __tablename__ = 'tubes' > > > > id = db.Column(db.Integer, primary_key=True) > > flag = db.Column(db.Boolean) > > container_slot = db.relationship('ContainerSlot', uselist=False, > back_populates='tube', lazy='joined') > > > > > > A container has many container slots, and a container slot has a tube. A > container slot can be "empty" (it doesn't have a tube), so its tube_id is > nullable. > > > > I'm trying to validate that the flag is consistent between a container > and its tubes. That is, if a tube is in a container (i.e there is a > container slot with its ID as tube_id), then the tube's flag must match the > value of its container's flag. > > > > Ideally, the only way to update these fields without failing the > validation is through endpoints which perform updates across tables in a > single transaction, ensuring that the state is consistent. > > > > For example, I have an endpoint which sets the flag to false for a list > of tube IDs, and removes them from their container > > > > tube_ids = args['tube_ids'] > > > > load_options = [db.noload('*'), > db.joinedload(Tube.container_slot).noload('*')] > > tubes = > Tube.query.options(load_options).filter(Tube.id.in_(tube_ids)).all() > > container_slots = [t.container_slot for t in tubes if t.container_slot] > > for tube in tubes: > > tube.flag = False > > > > for cs in container_slots: > > cs.tube_id = None > > > > db.session.bulk_save_objects(tubes) > > db.session.bulk_save_objects(container_slots) > > db.session.commit() > > > > Here is a validation which is failing > > > > @event.listens_for(Tube, 'before_insert') > > @event.listens_for(Tube, 'before_update') > > def validate_tube(mapper, connection, tube): > > if tube.flag is None: > > return > > > > row = (db.session.query(Container.flag, Container.name) > > .join(Container.container_slots) > > .join(ContainerSlot.tube) > > .filter(Tube.id == tube.id) > > .one_or_none()) > > > > (container_flag, name) = row if row else (None, None) > > > > if tube.flag is True and container_flag is False: > > raise BadRequest('...') > > > > if tube.flag is False and container_flag is True: > > raise BadRequest('...') > > > > I would expect that row is None here, because the tube was removed from > its container slot, so the tube doesn't have a container > > However, row returns the container that the tube was previously in, and > since the container's flag is true, the validation fails. > > > > Some things I've already tried which didn't work are: > > > > Setting the container slot's tube field to None, instead of setting > tube_id to None > > Setting the tube's container_slot field to None, instead of setting the > container slot's tube or tube_id to None > > Using db.session.add instead of db.session.bulk_save_objects > > > > > > I'm using SQLAlchemy 1.1.18 and Flask-SQLAlchemy 2.0.6 > > > > Thank you! > > > > This e-mail is private and confidential and is for the addressee only. > If misdirected, please notify us by telephone, confirming that it has been > deleted from your system and any hard copies destroyed. You are strictly > prohibited from using, printing, distributing or disseminating it or any > information contained in it save to the intended recipient. > > > > -- > > 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 [email protected] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- This e-mail is private and confidential and is for the addressee only. If misdirected, please notify us by telephone, confirming that it has been deleted from your system and any hard copies destroyed. You are strictly prohibited from using, printing, distributing or disseminating it or any information contained in it save to the intended recipient. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
