On Mon, May 21, 2018 at 4:12 PM, Diego Quintana <daquinta...@gmail.com> wrote: > I understand it might be a lot to process, and I really appreciate your > help. To avoid polluting this mailing list with more code I've moved this > question to Stackoverflow at > https://stackoverflow.com/questions/50452535/symmetry-in-backref-relationships-not-working-as-expected-with-event-listeners, > and added more info about the issue.
not surprisingly, nobody has attended to this question because...it's a long one and pretty much I've yet to find anyone other than me who gets into these big ones ...this is a bug, not a feature :( looking now > > Thanks again! > > Diego Alexandro Quintana Valenzuela > Ingeniero Civil Electricista > Universidad de la Frontera - Chile > +56 9 7965 3455 > > IEEE PES & CIS Member > diego.quint...@ieee.org > LinkedIn > https://www.linkedin.com/in/diego-quintana-valenzuela/ > > 2018-05-18 23:44 GMT-04:00 Mike Bayer <mike...@zzzcomputing.com>: >> >> On Fri, May 18, 2018 at 9:06 PM, Diego Quintana <daquinta...@gmail.com> >> wrote: >> > So I watched the video, made a lot of stuff clear. What I'm not certain >> > about is the symmetry of relationships. >> > >> > >> > I used to think that, in many to many relationships (perhaps for all of >> > the >> > relationships available) >> > >> > parent.children.append(child) >> > and >> > >> > child.parents.append(parent) >> >> well in the simple case with backrefs + secondary, they are. with >> this specific thing you're doing with multiple association tables and >> events firing off additional mutations, I have no idea. this case is >> deeply complicated. >> >> > >> > >> > were equivalent. I have improved to test this but it is working for only >> > one >> > side: >> > >> > import logging >> > import sys >> >> I'll have to find time to step through this as these tests are very >> long. if you can narrow down the thing you want to illustrate to just >> a few lines that would make it easier. >> >> >> > >> > import sqlalchemy as sa >> > from sqlalchemy.ext.declarative import declarative_base >> > >> > # setup logger >> > stdout_handler = logging.StreamHandler(sys.stdout) >> > formatter = >> > >> > logging.Formatter('%(asctime)s:%(filename)s:%(lineno)d\t%(levelname)s\t%(message)s') >> > stdout_handler.setFormatter(formatter) >> > >> > logger = logging.getLogger('sqlalchemy.engine') >> > logger.setLevel(logging.DEBUG) >> > logger.addHandler(stdout_handler) >> > >> > 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) >> > >> > >> > from sqlalchemy.orm import object_session >> > >> > @sa.event.listens_for(Parent.children, 'append') >> > def _on_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 >> > >> > logger.debug(f'**********1. adding the pets of {child} to >> > {parent}***************') >> > >> > object_session(parent).execute( >> > "INSERT INTO parents_pets_relationship VALUES " >> > "(:parent_id, :pet_id)", >> > [ >> > {"parent_id": parent.id, "pet_id": pet.id} >> > for pet in child.pets >> > ] >> > ) >> > >> > logger.debug('**********1. done!***************') >> > >> > @sa.event.listens_for(Parent.children, 'remove') >> > def _on_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> >> > """ >> > >> > object_session(parent).execute( >> > "DELETE FROM parents_pets_relationship WHERE " >> > "parent_id=:parent_id AND pet_id=:pet_id", >> > [ >> > {"parent_id": parent.id, "pet_id": pet.id} >> > for pet in child.pets >> > ] >> > ) >> > >> > >> > @sa.event.listens_for(Parent.pets, 'remove') >> > def _on_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 >> > """ >> > >> > object_session(parent).execute( >> > "DELETE FROM parents_children_relationship WHERE " >> > "parent_id=:parent_id AND child_id=:child_id", >> > {"parent_id": parent.id, "child_id": pet.child.id} >> > ) >> > >> > #### test ### >> > >> > import unittest >> > from sqlalchemy import create_engine >> > from sqlalchemy.orm import sessionmaker >> > >> > class BasicTestModelCase(unittest.TestCase): >> > >> > def setUp(self): >> > self.engine = create_engine("sqlite://", echo=False) >> > Base.metadata.create_all(self.engine) >> > >> > Session = sessionmaker(bind=self.engine) >> > self.session = Session() >> > >> > >> > def tearDown(self): >> > Base.metadata.drop_all(bind=self.engine) >> > >> > 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 >> > logger.debug('************A - add test_parent to >> > child1***************') >> > child1.parents.append(test_parent) >> > self.session.add(child1) >> > self.session.commit() >> > logger.debug('**********A - done!***************') >> > >> > # add parent to the child >> > pet2.parents.append(test_parent) >> > logger.debug('************B - add test_parent to >> > child1***************') >> > >> > # persist changes in the db >> > self.session.add(pet2) >> > self.session.commit() >> > logger.debug('**********B - done!***************') >> > >> > 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 >> > logger.debug('***********C - remove test_parent from >> > pet3****************') >> > pet3.parents.remove(test_parent) ## ERROR here >> > logger.debug('**********C - done!***************') >> > >> > # 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 >> > >> > def test_child_pet_relationship_on_parents_combined_reversed(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() >> > >> > >> > logger.debug('************A` - add child1 to >> > test_parent***************') >> > # add parent to the child >> > test_parent.children.append(child1) >> > self.session.add(test_parent) >> > self.session.commit() >> > logger.debug('**********A` - done!***************') >> > >> > >> > logger.debug('************B` - add pet2 to >> > test_parent***************') >> > # add parent to the child >> > test_parent.pets.append(pet2) >> > >> > # persist changes in the db >> > self.session.add(test_parent) >> > self.session.commit() >> > logger.debug('**********B` - done!***************') >> > >> > # 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 >> > logger.debug('***********C` - remove pet3 from >> > test_parent****************') >> > test_parent.pets.remove(pet3) >> > logger.debug('**********C` - done!***************') >> > >> > # 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 >> > >> > >> > >> > import sys >> > >> > if __name__ == '__main__': >> > # # run tests >> > unittest.main() >> > >> > Checking the logs I see that in the second test >> > >> > 2018-05-18 20:54:58,686:pets2.py:284 DEBUG ************A` - add >> > child1 >> > to test_parent*************** >> > 2018-05-18 20:54:58,692:base.py:682 INFO BEGIN (implicit) >> > 2018-05-18 20:54:58,694:base.py:1151 INFO SELECT children.id AS >> > children_id, children.name AS children_name >> > FROM children >> > WHERE children.id = ? >> > 2018-05-18 20:54:58,695:base.py:1154 INFO (1,) >> > 2018-05-18 20:54:58,696:result.py:681 DEBUG Col ('children_id', >> > 'children_name') >> > 2018-05-18 20:54:58,696:result.py:1106 DEBUG Row (1, 'FakeChild1') >> > 2018-05-18 20:54:58,698:base.py:1151 INFO SELECT parents.id AS >> > parents_id, parents.name AS parents_name >> > FROM parents >> > WHERE parents.id = ? >> > 2018-05-18 20:54:58,698:base.py:1154 INFO (1,) >> > 2018-05-18 20:54:58,699:result.py:681 DEBUG Col ('parents_id', >> > 'parents_name') >> > 2018-05-18 20:54:58,699:result.py:1106 DEBUG Row (1, 'test_parent') >> > 2018-05-18 20:54:58,700:pets2.py:102 DEBUG **********1. adding the >> > pets >> > of <Child (name='FakeChild1')> to <Parent >> > (name='test_parent')>*************** >> > 2018-05-18 20:54:58,703:base.py:1151 INFO INSERT INTO >> > parents_children_relationship (parent_id, child_id) VALUES (?, ?) >> > 2018-05-18 20:54:58,703:base.py:1154 INFO (1, 1) >> > 2018-05-18 20:54:58,705:base.py:1151 INFO SELECT pets.id AS >> > pets_id, >> > pets.name AS pets_name, pets.child_id AS pets_child_id >> > FROM pets >> > WHERE ? = pets.child_id >> > 2018-05-18 20:54:58,705:base.py:1154 INFO (1,) >> > 2018-05-18 20:54:58,705:result.py:681 DEBUG Col ('pets_id', >> > 'pets_name', >> > 'pets_child_id') >> > 2018-05-18 20:54:58,706:result.py:1106 DEBUG Row (1, 'FakePet1', 1) >> > 2018-05-18 20:54:58,706:result.py:1106 DEBUG Row (3, 'FakePet3', 1) >> > 2018-05-18 20:54:58,707:base.py:1151 INFO INSERT INTO >> > parents_pets_relationship VALUES (?, ?) >> > 2018-05-18 20:54:58,707:base.py:1154 INFO ((1, 1), (1, 3)) >> > 2018-05-18 20:54:58,707:pets2.py:113 DEBUG **********1. >> > done!*************** >> > 2018-05-18 20:54:58,709:base.py:1151 INFO INSERT INTO >> > parents_children_relationship (parent_id, child_id) VALUES (?, ?) >> > 2018-05-18 20:54:58,709:base.py:1154 INFO (1, 1) >> > 2018-05-18 20:54:58,710:base.py:702 INFO ROLLBACK >> > >> > I really don't understand why both are different cases, and in >> > particular >> > why one fails and the other does not. >> > >> > Thanks again for your time! >> > >> > >> > Am Freitag, 18. Mai 2018 17:13:23 UTC-4 schrieb Mike Bayer: >> >> >> >> On Fri, May 18, 2018 at 4:44 PM, Diego Quintana <daqui...@gmail.com> >> >> wrote: >> >> > So I'm back to this, and I wonder about something you said: >> >> >> >> >> >> >> >> >> the main complication here is that those "dynamic" relationships >> >> >> require that a query runs for everything, which means everything has >> >> >> to be in the database, which means it flushes the session very >> >> >> aggressively (and also disabling the flush, another thing I tried, >> >> >> means it doesn't read the contents of the collections accurately), >> >> >> and >> >> >> all of that makes an already tricky operation nearly impossible >> >> >> without it barreling into itself. >> >> >> >> >> >> If you are committed to using the "dynamic" relationships, you can >> >> >> always rely on emitting SQL to read from those association tables, >> >> >> and >> >> >> there's a completely unorthodox way to do this which would be way >> >> >> more >> >> >> efficient in most cases, and is extremely simple, just emit the >> >> >> DELETE >> >> >> statements: >> >> > >> >> > >> >> > So, I don't know actually if I have to be committed to one approach >> >> > over >> >> > the >> >> > other. I was trying to leverage the orm layer for consistency, but if >> >> > there >> >> > is another approach to achieve the same thing I would like to know. >> >> > Also, >> >> > you mentioned a test you've added, do you mind to share it? >> >> > >> >> > BTW, the code bit you changed makes it work, thanks!. There are a lot >> >> > of >> >> > things I don't understand. But first, why does I need to use >> >> > `session_object`? to expose the `execute` method? What is that I am >> >> > "bypassing" by using this over the orm? >> >> >> >> object_session() gives you the Session that has loaded / persisted >> >> that object for you, assuming that Session is still around. This is >> >> important because when you deal with the object you are really dealing >> >> with a proxy for a row in the database, local to a specific >> >> transaction. Other transactions might see a totally different row. >> >> So you want to stick to that one transaction when you emit SQL. >> >> >> >> not sure if you watched my video on this, I try to go into it pretty >> >> deep: >> >> http://www.sqlalchemy.org/library.html#thesqlalchemysessionindepth >> >> >> >> >> >> > >> >> > >> >> > Am Freitag, 4. Mai 2018 11:40:34 UTC-3 schrieb Diego Quintana: >> >> >> >> >> >> At the moment I've moved to other features, but I should be back to >> >> >> this >> >> >> somewhere in the near future. I will let you know the results. >> >> >> >> >> >> I really appreciate your time, thanks again. >> >> >> >> >> >> Best, >> >> >> >> >> >> Am Donnerstag, 3. Mai 2018 10:10:47 UTC-3 schrieb Mike Bayer: >> >> >>> >> >> >>> On Thu, May 3, 2018 at 7:39 AM, Diego Quintana <daqui...@gmail.com> >> >> >>> wrote: >> >> >>> > Thanks again for your reply >> >> >>> > >> >> >>> >> 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. >> >> >>> > >> >> >>> > >> >> >>> > Yes, since it is a different usage case or flavour. I was trying >> >> >>> > to >> >> >>> > pass >> >> >>> > kwargs to the event listener directly, but it does not work. >> >> >>> > >> >> >>> >> 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. >> >> >>> > >> >> >>> > >> >> >>> > Correct, this would be case A and the idea behind it is that, if >> >> >>> > a >> >> >>> > User >> >> >>> > has >> >> >>> > *access* to a Child, it should also have access to all of the >> >> >>> > Child's >> >> >>> > pets. >> >> >>> > If for some reason Parent does not have access to all of them, >> >> >>> > means >> >> >>> > it >> >> >>> > does >> >> >>> > not have access to the Child either. >> >> >>> > >> >> >>> > Removing a pet that has a Child that is also present in >> >> >>> > user.children >> >> >>> > should >> >> >>> > trigger this, leaving the parent with, as you say, only pets >> >> >>> > minus >> >> >>> > the >> >> >>> > one >> >> >>> > that was removed. It would also remove the child from the >> >> >>> > parent/children >> >> >>> > relationship in the association table. >> >> >>> > >> >> >>> >> if I remove a child from the parent, then we remove *all* pets >> >> >>> >> associated with the child from that parent. >> >> >>> > >> >> >>> > >> >> >>> > Correct, and this would be case B >> >> >>> > >> >> >>> >> 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? >> >> >>> > >> >> >>> > >> >> >>> > Yes, both relationships -parent/pet and parent/child- are not >> >> >>> > seeing >> >> >>> > each >> >> >>> > other, and the only binding is >> >> >>> > their relationship between child and pets. >> >> >>> > >> >> >>> > Thus, the two flavors are depending on where is that listener >> >> >>> > being >> >> >>> > called >> >> >>> > from. For the case A, this is called inside the if statement in >> >> >>> > _remove_pets. Outside of this it should be always case B. >> >> >>> > >> >> >>> >> 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. >> >> >>> > >> >> >>> > >> >> >>> > I understand that it requires a lot of fiddling. I was trying to >> >> >>> > pass >> >> >>> > kwargs >> >> >>> > to the listener directly, and parse them inside the other >> >> >>> > listener, >> >> >>> > but >> >> >>> > the >> >> >>> > other listener is not receiving them. I was seeing this is on >> >> >>> > purpose >> >> >>> > here , >> >> >>> > since accepting kwargs would pollute the API. Perhaps a custom >> >> >>> > event >> >> >>> > implementation? >> >> >>> >> >> >>> I slowly realized it looked like you hoped that flag would pass >> >> >>> through but there's too many layers of indirection for it to work >> >> >>> that >> >> >>> way. the main complication here is that those "dynamic" >> >> >>> relationships >> >> >>> require that a query runs for everything, which means everything >> >> >>> has >> >> >>> to be in the database, which means it flushes the session very >> >> >>> aggressively (and also disabling the flush, another thing I tried, >> >> >>> means it doesn't read the contents of the collections accurately), >> >> >>> and >> >> >>> all of that makes an already tricky operation nearly impossible >> >> >>> without it barreling into itself. >> >> >>> >> >> >>> If you are committed to using the "dynamic" relationships, you can >> >> >>> always rely on emitting SQL to read from those association tables, >> >> >>> and >> >> >>> there's a completely unorthodox way to do this which would be way >> >> >>> more >> >> >>> efficient in most cases, and is extremely simple, just emit the >> >> >>> DELETE >> >> >>> statements: >> >> >>> >> >> >>> from sqlalchemy.orm import object_session >> >> >>> >> >> >>> @sa.event.listens_for(Parent.children, 'remove') >> >> >>> def _remove_children(parent, child, initiator, *args, **kwargs): >> >> >>> object_session(parent).execute( >> >> >>> "delete from parents_pets_relationship where " >> >> >>> "parent_id=:parent_id and pet_id=:pet_id", >> >> >>> [ >> >> >>> {"parent_id": parent.id, "pet_id": pet.id} >> >> >>> for pet in child.pets >> >> >>> ] >> >> >>> ) >> >> >>> >> >> >>> >> >> >>> @sa.event.listens_for(Parent.pets, 'remove') >> >> >>> def _remove_pets(parent, pet, initiator, *args, **kwargs): >> >> >>> object_session(parent).execute( >> >> >>> "delete from parents_children_relationship where " >> >> >>> "parent_id=:parent_id and child_id=:child_id", >> >> >>> {"parent_id": parent.id, "child_id": pet.child.id} >> >> >>> ) >> >> >>> >> >> >>> I added a second test for the other case and this works as far as >> >> >>> I've >> >> >>> gotten it. since we are only dealing with these standalone >> >> >>> association tables the above is pretty simple, and now you only >> >> >>> emit >> >> >>> one query (albeit a DML query, not a SELECT) rather than lots of >> >> >>> SELECT statements. The above is at least very simple to work >> >> >>> with since the ORM has no involvement past getting you those >> >> >>> events. >> >> >>> >> >> >>> if the above does what you need then it's probably worth doing that >> >> >>> way. >> >> >>> >> >> >>> >> >> >>> >> >> >>> > >> >> >>> > Thanks again for your time >> >> >>> > >> >> >>> > >> >> >>> > >> >> >>> > Diego Alexandro Quintana Valenzuela >> >> >>> > Ingeniero Civil Electricista >> >> >>> > Universidad de la Frontera - Chile >> >> >>> > +56 9 7965 3455 >> >> >>> > >> >> >>> > IEEE PES & CIS Member >> >> >>> > diego.q...@ieee.org >> >> >>> > LinkedIn >> >> >>> > https://www.linkedin.com/in/diego-quintana-valenzuela/ >> >> >>> > >> >> >>> > 2018-05-02 18:46 GMT-03:00 Mike Bayer <mik...@zzzcomputing.com>: >> >> >>> >> >> >> >>> >> On Wed, May 2, 2018 at 12:22 PM, Diego Quintana >> >> >>> >> <daqui...@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+...@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 a topic >> >> >>> >> in >> >> >>> >> the >> >> >>> >> Google Groups "sqlalchemy" group. >> >> >>> >> To unsubscribe from this topic, visit >> >> >>> >> >> >> >>> >> >> >> >>> >> https://groups.google.com/d/topic/sqlalchemy/jgKgv5zQT7E/unsubscribe. >> >> >>> >> To unsubscribe from this group and all its topics, 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+...@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 a topic in the >> Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/jgKgv5zQT7E/unsubscribe. >> To unsubscribe from this group and all its topics, 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. -- 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.
