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.
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 <[email protected]
> <javascript:>> 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 <[email protected]>
> >> 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 [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.
> >
> > --
> > 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.
>
--
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.
