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 <phil.nac...@freenome.com> 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 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.
