On Wed, May 2, 2018 at 12:22 PM, Diego Quintana <daquinta...@gmail.com> wrote:
> Hello, thanks again for your help. I'm not sure I understand what you said
> totally, and I believe this is the most simple MCVE I can provide.
>
> My local tests use postgresql, but I'm setting an in-memory sqlite3 engine
> here. I'm not fond of the differences between two backends, but the tests
> run without problems.
So this is great, and shows the problem. but what you are trying to
do here is deeply complicated. I was going to just type out
everything I did to figure this out but this was way too long a
process.
at the core is that when you remove a child from the parent in the
_remove_pets event, you want to prevent the _remove_children() event
from actually happening, I think.
If I remove a pet from a parent, then we remove the child from the
parent, and *only* that pet. we dont remove other pets that might be
associated with that child.
if I remove a child from the parent, then we remove *all* pets
associated with the child from that parent.
This seems like it's a contradiction. I have parent p1, not referring
to child c1, but it refers to pet p1 which *does* refer to child c1,
and that is valid. There's basically two flavors of "remove child
from parent", is that right?
I tried to work on an implementation here which would also have to be
extremely clever but I realized I don't actually understand what this
is supposed to do. if "remove child from parent" has two different
flavors then there needs to be all kinds of trickery to protect the
events from each other.
> import sqlalchemy as sa
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> # many to many relationship between parents and children
> parents_children_relationship = sa.Table('parents_children_relationship',
> Base.metadata,
> sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
> sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id')),
> sa.UniqueConstraint('parent_id', 'child_id'))
>
> # many to many relationship between User and Pet
> parents_pets_relationship = sa.Table('parents_pets_relationship',
> Base.metadata,
> sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
> sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id')),
> sa.UniqueConstraint('parent_id', 'pet_id'))
>
> class Parent(Base):
> __tablename__ = 'parents'
>
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
>
> # many to many relationship between parent and children
> # my case allows for a children to have many parents. Don't ask.
> children = sa.orm.relationship('Child',
> secondary=parents_children_relationship,
> backref=sa.orm.backref('parents',
> lazy='dynamic'),
> lazy='dynamic')
>
> # many to many relationship between parents and pets
> pets = sa.orm.relationship('Pet',
> secondary=parents_pets_relationship,
> backref=sa.orm.backref('parents',
> lazy='dynamic'), #
> lazy='dynamic')
>
>
> def __repr__(self):
> return '<Parent (name=%r)>' % (self.name)
>
> class Child(Base):
> __tablename__ = 'children'
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
> # parents = <backref relationship with User model>
>
> # one to many relationship with pets
> pets = sa.orm.relationship('Pet', backref='child', lazy='dynamic')
>
> def __repr__(self):
> return '<Child (name=%r)>' % (self.name)
>
> class Pet(Base):
> __tablename__ = 'pets'
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
> # child = backref relationship with cities
> child_id = sa.Column(sa.Integer, sa.ForeignKey('children.id'),
> nullable=True)
> # parents = <relationship backref from User>
>
> def __repr__(self):
> return '<Pet (name=%r)>' % (self.name)
>
>
>
> @sa.event.listens_for(Parent.children, 'append')
> def _append_children(parent, child, initiator):
> """
> If a new child is appended to the parent, this listener
> will also add the pets bound to the child being bound to the parent.
> """
> # appends also the pets bound to the child that the
> # parent is being appended to
> parent.pets.extend(child.pets.all())
>
> @sa.event.listens_for(Parent.children, 'remove')
> def _remove_children(parent, child, initiator, *args, **kwargs):
> """
> If a child is removed from the parent, this listener
> will also remove only remove_single_pet --> <Pet>
> """
>
> remove_single_pet = kwargs.get('remove_single_pet', None)
>
> if remove_single_pet is not None:
> parent.pets.remove(remove_single_pet)
> else: # removes every pet
> for pet in child.pets:
> parent.pets.remove(pet)
>
> @sa.event.listens_for(Parent.pets, 'remove')
> def _remove_pets(parent, pet, initiator, *args, **kwargs):
> """
> If a pet is removed from the parent, and the parent also is related
> to the child that has access to that pet, then
>
> * removes relationship with the child, and
> * keeps relationship with the remaining pets, except the one that was
> removed
> """
>
> if pet.child in parent.children.all():
> remove_single_pet = pet
> _remove_children(parent, pet.child, initiator, remove_single_pet)
>
>
> #### test ###
>
> import unittest
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> class BasicTestModelCase(unittest.TestCase):
>
> def setUp(self):
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> Session = sessionmaker(bind=e)
> self.session = Session()
>
>
> def tearDown(self):
> # Base.metadata.drop_all()
> pass
>
> def test_child_pet_relationship_on_parents_combined(self):
> """
> Test that a parent can be hold children and pets that don't
> belong necessary to the child, given the behaviour tested in the
> previous test.
> """
>
> # create new parent
> test_parent = Parent(name='test_parent')
>
> child1 = Child(id=1,
> name='FakeChild1')
>
> child2 = Child(id=2,
> name='FakeChild2')
>
> pet1 = Pet(id=1,
> name='FakePet1',
> child_id=1)
>
> pet2 = Pet(id=2,
> name='FakePet2',
> child_id=2)
>
> pet3 = Pet(id=3,
> name='FakePet3',
> child_id=1)
>
> self.session.add(test_parent)
> self.session.add(child1)
> self.session.add(child2)
> self.session.add(pet1)
> self.session.add(pet2)
> self.session.add(pet3)
> self.session.commit()
>
> # add parent to the child
> child1.parents.append(test_parent)
> self.session.add(child1)
> self.session.commit()
>
> # add parent to the child
> pet2.parents.append(test_parent)
>
> # persist changes in the db
> self.session.add(pet2)
> self.session.commit()
>
> print(test_parent.pets.all())
> print(child2.pets.all())
>
> # check that previous relationships are intact
> self.assertTrue(child1.pets.all() == [pet1, pet3])
> self.assertTrue(child2.pets.all() == [pet2])
>
> # resultant elements should be only child1, its pets and the single
> Pet
> self.assertTrue(test_parent.children.all() == [child1])
> self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3])
>
> # remove child from parent
> pet3.parents.remove(test_parent) ## ERROR here
>
> # resultant elements should be remaining pets, and no child
> self.assertTrue(test_parent.children.all() == [])
> self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2 was
> not touched,
> # but pet1
> should remain since only
> # pet3 was
> removed
> # child1
> should be also removed since
> #
> relationship is unbalanced, i.e.
> # user can't
> have access to a child if it
> # does not
> have access to all of the child's pets
>
> if __name__ == '__main__':
> # run tests
> unittest.main()
>
> Am Mittwoch, 2. Mai 2018 11:40:14 UTC-3 schrieb Mike Bayer:
>>
>> On Wed, May 2, 2018 at 10:14 AM, Diego Quintana <daqui...@gmail.com>
>> wrote:
>> > This worked.
>> >
>> > I'm trying to achieve some rather tricky behaviour, where
>> >
>> > Adding a children to some parent will also add the child's pets to the
>> > parent
>> > Removing a children from some parent will also remove every current
>> > relationship that the Parent has with such pet
>> > If upon removal of a pet from a Parent, there is a Pet.child that is
>> > also in
>> > Parent.children,
>> >
>> > remove that Child from Parent, but keep existing relationships in
>> > Parent.pets except the pet that is being removed
>> > else only remove the pet from the parent
>> >
>> >
>> > Some code I'm using for this is
>> >
>> > @db.event.listens_for(Parent.children, 'append')
>> > def _append_children(parent, child, initiator):
>> > """
>> > If a new child is appended to the parent, this listener
>> > will also add the pets bound to the child being bound to the parent.
>> > """
>> > # appends also the pets bound to the child that the
>> > # parent is being appended to
>> > parent.pets.extend(child.pets.all())
>> >
>> > @db.event.listens_for(Parent.children, 'remove')
>> > def _remove_children(parent, child, initiator, *args, **kwargs):
>> > """
>> > If a child is removed from the parent, this listener
>> > will also remove only remove_single_pet --> <Pet>
>> > """
>> >
>> > remove_single_pet = kwargs.get('remove_single_pet', None)
>> >
>> > if remove_single_pet is not None:
>> > parent.pets.remove(remove_single_pet)
>> > else: # removes every pet
>> > for pet in child.pets:
>> > parent.pets.remove(pet)
>> >
>> >
>> > @db.event.listens_for(Parent.pets, 'remove')
>> > def _remove_pets(parent, pet, initiator, *args, **kwargs):
>> > """
>> > If a pet is removed from the parent, and the parent also is related
>> > to the child that has access to that pet, then
>> >
>> > * removes relationship with the child, and
>> > * keeps relationship with the remaining pets, except the one that
>> > was
>> > removed
>> > """
>> >
>> > if pet.child in parent.children.all():
>> > remove_single_pet = pet
>> > _remove_children(parent, pet.child, initiator,
>> > remove_single_pet)
>> >
>> >
>> > #### test.py
>> >
>> > def test_child_pet_relationship_on_parents(self):
>> >
>> >
>> > # create new parent
>> > test_parent = Parent(name='test_parent')
>> >
>> > # commit parent to the database
>> > db.session.add(test_parent)
>> > db.session.commit()
>> >
>> > child1 = Child(id=1,
>> > name='FakeChild1')
>> >
>> > child2 = Child(id=2,
>> > name='FakeChild2')
>> >
>> > pet1 = Pet(id=1,
>> > name='FakePet1',
>> > child_id=1)
>> >
>> > pet2 = Pet(id=2,
>> > name='FakePet2',
>> > child_id=2)
>> >
>> > pet3 = Pet(id=3,
>> > name='FakePet3',
>> > child_id=1)
>> >
>> > db.session.add(child1)
>> > db.session.add(child2)
>> > db.session.add(pet1)
>> > db.session.add(pet2)
>> > db.session.add(pet3)
>> >
>> > db.session.commit()
>> >
>> > # add parent to the child
>> > child1.parents.append(test_parent)
>> > # add parent to the child
>> > pet2.parents.append(test_parent)
>> >
>> > # persist changes in the db
>> > db.session.add(child1)
>> > db.session.add(pet2)
>> > db.session.commit()
>> >
>> > # check that previous relationships are intact
>> > self.assertTrue(child1.pets.all() == [pet1, pet3])
>> > self.assertTrue(child2.pets.all() == [pet2])
>> >
>> > # resultant elements should be only child1, its pets and the
>> > single
>> > Pet
>> > self.assertTrue(test_parent.children.all() == [child1])
>> > self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3])
>> >
>> > # remove child from parent
>> > pet3.parents.remove(test_parent)
>> >
>> > # resultant elements should be remaining pets, and no child
>> > self.assertTrue(test_parent.children.all() == [])
>> > self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2
>> > was
>> > not touched,
>> > # but
>> > pet1
>> > should remain since only
>> > # pet3
>> > was
>> > removed
>> > # child1
>> > should be also removed since
>> > #
>> > relationship is unbalanced, i.e.
>> > # user
>> > can't
>> > have access to a child if it
>> > # does
>> > not
>> > have access to all of the child's pets
>> >
>> >
>> >
>> > I'm having errors
>> >
>> > sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
>> > 'parent_pets_relationship' expected to delete 1 row(s); Only 0 were
>> > matched.
>>
>> it looks like you have an explicit association mapping on the
>> secondary table as described in the green box in the section
>>
>> https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object,
>> but I dont' have your complete mappings so I can't say for sure, you'd
>> need to provide a complete MCVE.
>>
>>
>>
>>
>> >
>> > and the logger says nothing much. I suspect I'm falling into some weird
>> > recursion, calling the listener from another listener, where the second
>> > call
>> > can't find something that was already deleted.
>> >
>> > This might be a long shot, but I'm hoping this pattern might be solved
>> > already.
>> >
>> >
>> > Am Donnerstag, 26. April 2018 13:00:45 UTC-3 schrieb Mike Bayer:
>> >>
>> >> On Thu, Apr 26, 2018 at 11:04 AM, Diego Quintana <daqui...@gmail.com>
>> >> wrote:
>> >> > Hello.
>> >> >
>> >> > Say I have three tables in a declarative fashion, `Parent`, `Child`,
>> >> > and
>> >> > `Pet`, in such way that
>> >> >
>> >> > * `Parent` has a many-to-many relationship with both `Child` and
>> >> > `Pet`,
>> >> > meaning that a Parent can own a Child and its pets, and also a Pet
>> >> > without
>> >> > its Child.
>> >> > * `Child` has a one-to-many relationship with `Pet`
>> >> >
>> >> > The code for them is (using Flask-SQLAlchemy, although I believe the
>> >> > solution lives in the realm of SQLAlchemy rather than in Flask).
>> >> >
>> >> > class Parent(db.Model):
>> >> > __tablename__ = 'parents'
>> >> >
>> >> > id = db.Column(db.Integer, primary_key=True)
>> >> > name = db.Column(db.String(64))
>> >> >
>> >> > # many to many relationship between parent and children
>> >> > # my case allows for a children to have many parents. Don't
>> >> > ask.
>> >> > children = db.relationship('Child',
>> >> >
>> >> > secondary=parents_children_relationship,
>> >> > backref=db.backref('parents',
>> >> > lazy='dynamic'),
>> >> > lazy='dynamic')
>> >> >
>> >> > # many to many relationship between parents and pets
>> >> > pets = db.relationship('Pet',
>> >> > secondary=users_pets_relationship,
>> >> > backref=db.backref('parents',
>> >> > lazy='dynamic'), #
>> >> > lazy='dynamic')
>> >> >
>> >> > # many to many relationship between parents and children
>> >> > parents_children_relationship =
>> >> > db.Table('parents_children_relationship',
>> >> > db.Column('parent_id', db.Integer,
>> >> > db.ForeignKey('parents.id')),
>> >> > db.Column('child_id', db.Integer,
>> >> > db.ForeignKey('children.id')),
>> >> > UniqueConstraint('parent_id', 'child_id'))
>> >> >
>> >> > # many to many relationship between User and Pet
>> >> > users_pets_relationship = db.Table('users_pets_relationship',
>> >> > db.Column('parent_id', db.Integer,
>> >> > db.ForeignKey('parents.id')),
>> >> > db.Column('pet_id', db.Integer, db.ForeignKey('pets.id')),
>> >> > UniqueConstraint('parent_id', 'pet_id'))
>> >> >
>> >> > class Child(db.Model):
>> >> > __tablename__ = 'children'
>> >> > id = db.Column(db.Integer, primary_key=True)
>> >> > name = db.Column(db.String(64))
>> >> > # parents = <backref relationship with User model>
>> >> >
>> >> > # one to many relationship with pets
>> >> > pets = db.relationship('Pet', backref='child',
>> >> > lazy='dynamic')
>> >> >
>> >> >
>> >> > class Pet(db.Model):
>> >> > __tablename__ = 'pets'
>> >> > id = db.Column(db.Integer, primary_key=True)
>> >> > name = db.Column(db.String(64))
>> >> > # child = backref relationship with cities
>> >> > child_id = db.Column(db.Integer,
>> >> > db.ForeignKey('children.id'),
>> >> > nullable=True)
>> >> > # parents = <relationship backref from User>
>> >> >
>> >> >
>> >> >
>> >> > I would like to do something like this
>> >> >
>> >> > parent_a = Parent()
>> >> > child_a = Child()
>> >> > pet_a = Pet()
>> >> >
>> >> >
>> >> >
>> >> > I can then do this
>> >> >
>> >> > parent_a.children.append(child_a)
>> >> > # commit/persist data
>> >> > parent_a.children.all() # [child_a]
>> >> >
>> >> >
>> >> >
>> >> > I would like to achieve something like this
>> >> >
>> >> > child_a.pets.append(pet_a)
>> >> > parent_a.children.append(child_a)
>> >> > # commit/persist data
>> >> > parent_a.children.all() # [child_a]
>> >> > parent_a.pets.all() # [pet_a], because pet_a gets
>> >> > # automatically added to parent using some
>> >> > sorcery
>> >> > # like for child in parent_a.children.all():
>> >> > # parent.pets.append(child.pets.all())
>> >> > # or something like that.
>> >> >
>> >> > I can achieve this with a method in the Parent object like
>> >> > add_child_and_its_pets(), but I would like to override the way
>> >> > relationship
>> >> > works, so I don't need to override other modules that may benefit
>> >> > from
>> >> > this
>> >> > behaviour, like Flask-Admin for instance.
>> >> >
>> >> >
>> >> > Basically how should I override the backref.append method or the
>> >> > relationship.append method to also append other objects from other
>> >> > relationships at call time i.e. on the python side? How should I
>> >> > override
>> >> > the remove methods as well?
>> >>
>> >> this seems like coordination of two separate relationships, so use
>> >> attribute events for that,
>> >>
>> >>
>> >> http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=attributeevent#sqlalchemy.orm.events.AttributeEvents
>> >> :
>> >>
>> >> @event.listens_for(Parent.children, "append")
>> >> def _append_pets(parent, child, initiator):
>> >> parent.pets.extend(child.pets) # or whatever it is you need
>> >>
>> >> you would need to look at the "append" and "remove" events.
>> >>
>> >>
>> >>
>> >> >
>> >> >
>> >> > I have also posted this question in Stack Overflow, in case it means
>> >> > something.
>> >> >
>> >> >
>> >> > Best!
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > 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+...@googlegroups.com.
>> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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.
--
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.
