How to override append and remove methods used in orm.relationships

[Diego Quintana]

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?

I have also posted this question in Stack Overflow, in case it means something.


Best!

[Mike Bayer]

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.

[Diego Quintana]

This worked.

I'm trying to achieve some rather tricky behaviour, where

1. Adding a children to some parent will also add the child's pets to the parent
2. Removing a children from some parent will also remove every current relationship that the Parent has with such pet
   
3. 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.

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.

[Mike Bayer]

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.

[Diego Quintana]

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()

[Mike Bayer]

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.

[Diego Quintana]

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?

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/

> email to sqlalchemy+unsubscribe@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+unsubscribe@googlegroups.com.

[Diego Quintana]

Just wanted to note something,

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.

In this case, remove pet from parent has two flavours, namely case A and B. Case A should trigger a different behaviour in the _remove_children listener, and work normally in every other case.

Best,

Diego

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/

[Mike Bayer]

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.

>> 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

[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,

[Diego Quintana]

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?

[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

[Diego Quintana]

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)

were equivalent. I have improved to test this but it is working for only one side:

import logging
import sys

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)

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)

        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):

        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!

[Mike Bayer]

On Fri, May 18, 2018 at 9:06 PM, Diego Quintana <daqui...@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.

[Diego Quintana]

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.

Thanks again!

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/

> email to sqlalchemy+unsubscribe@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+unsubscribe@googlegroups.com.

[Mike Bayer]

On Mon, May 21, 2018 at 4:12 PM, Diego Quintana <daqui...@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

[Mike Bayer]

the issue is because the addition of test_parent to child1.parents
triggers two individual "dirty" events, which each resolve to the same
INSERT operation into the parents_children_relationship table.
Normally, these two dirty events are resolved together during the
flush process. However, within your "insert" event handler, calling
upon the "child.pets" collection, since it is a dynamic relationship,
triggers autoflush. So, the first dirty event for parent being
appended to child is handled, the row is inserted within autoflush.
then your event completes. then the backref handler goes off and
appends child to parent, triggering the second dirty event. the
session.commit() then tries to flush the same thing a second time
which fails.

the solution is to not flush within the event handlers. the original
approach you had was also doing this and it was impossible to figure
out how to fix it, as the original way you were doing it sort relied
upon that flush.

sess = object_session(parent)
with sess.no_autoflush:
sess.execute(

"INSERT INTO parents_pets_relationship VALUES "
"(:parent_id, :pet_id)",
[
{"parent_id": parent.id, "pet_id": pet.id}
for pet in child.pets
]
)

I ran this with a deep breath because I never did really understand
the first case why this problematic nested flush seemed to be needed.
Fortunately, both tests passed here and it got away with not having
to flush inside the event handler.

[Diego Quintana]

This works! I can't thank you enough! I dived into the source code to understand what was happening but I did not get much. I really hope I can be of more help in the future.

I have one last question, if I may:

I ran this with a deep breath because I never did really understand
the first case why this problematic nested flush seemed to be needed.
 Fortunately, both tests passed here and it got away with not having
to flush inside the event handler.

I don't understand from that statement in bold if you refer to the way I am trying to achieve this feature (using dynamic relationships) or the feature itself (appending elements from other relationships). Perhaps you were thinking of using another loading method? I use `'dynamic'` in all of my models mostly because I want to control when I execute the query, but is not related to the feature at all, and I'm not sure if I understand the drawbacks of it, if any. What I'm trying to say is that this *problematic nested flush* is not needed per se, is just the way I found to get things done. Is there a different way you would have developed this feature?

Thanks again!

Diego

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/

>>> > email to sqlalchemy+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.

[Mike Bayer]

On Tue, May 22, 2018 at 2:47 PM, Diego Quintana <daqui...@gmail.com> wrote:
> This works! I can't thank you enough! I dived into the source code to
> understand what was happening but I did not get much. I really hope I can be
> of more help in the future.
>
> I have one last question, if I may:
>
>> I ran this with a deep breath because I never did really understand
>> the first case why this problematic nested flush seemed to be needed.
>> Fortunately, both tests passed here and it got away with not having
>> to flush inside the event handler.
>
>
> I don't understand from that statement in bold if you refer to the way I am
> trying to achieve this feature (using dynamic relationships) or the feature
> itself (appending elements from other relationships). Perhaps you were
> thinking of using another loading method? I use `'dynamic'` in all of my
> models mostly because I want to control when I execute the query, but is not
> related to the feature at all, and I'm not sure if I understand the
> drawbacks of it, if any. What I'm trying to say is that this *problematic
> nested flush* is not needed per se, is just the way I found to get things
> done. Is there a different way you would have developed this feature?

When I worked with the original code and tried to understand the
nature of the failure, one of the problems was that within the event,
while an object was being deleted from a collection, there was an
access of a dynamic collection. This access incurred a call to
autoflush, meaning, there were pending objects within the scope of the
event that needed to be flushed. From that, some problems happened
involving the flush within the event handler, probably not too
different from what we found here. However, when I tried removing
that flush from occurring inside the event handler, the tests we had I
believe falied *earlier*, meaning, that flush had to be there - most
likely so that the dynamic collection could return the correct
results.

The issue with dynamic collections is that mutations to them are not
apparent when you iterate the collection unless you flush first,
because iterations always read the database.

hope this makes sense.