CODEWITHSUNDEEP
pythonsqlsessionsqlalchemycascade
hadamard
hadamard

Reputation: 443

SQLALCHEMY deleting Association Object does not delete it from the parent and child

I am trying to delete an association object. It is deleted from the table but the parent object still has the child object in its collection. Here is my Code:

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

engine = sqlalchemy.create_engine('sqlite:///:memory:')
Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

    def __repr__(self) -> str:
        return f"Parent: {self.id}, {self.children}"


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

    def __repr__(self) -> str:
        return f"Child: {self.id}, {self.parents}"


class Association(Base):
    __tablename__ = 'association'
    parent_id = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    child_id = Column(Integer, ForeignKey('child.id'), primary_key=True)
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")

    def __repr__(self) -> str:
        return f"Association: ({self.parent}); ({self.child};)"


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=False)
session = Session()

p = Parent()
c = Child()

session.add(p)
session.add(c)
session.commit()

association = Association(parent=p, child=c)
session.add(association)
session.commit()

assert session.query(Association).all()
assert session.query(Parent).all()[0].children
assert session.query(Child).all()[0].parents


session.delete(association)
session.commit()

assert not session.query(Association).all()
assert not session.query(Parent).all()[0].children
assert not session.query(Child).all()[0].parents

Now when I delete an association object and query it from the table association it's gone. However if I query the parent or child object the association is still in their collection field. How can I configure it, so when I delete the association object the object is also removed completely from the Parent collection and the Child collection.

Upvotes: 1

Views: 672

Answers (1)

ricekab
ricekab

Reputation: 650

This is because you've set expire_on_commit=False. This means that any time you commit, existing instances are not expired.

If you set it to True (the default value), you'll get the behaviour you'd expect.

See the SQLAlchemy documentation for details.

If using expire_on_commit=True is not desired for some reason, you can manually expire instances using Session#expire or Session#expire_all.

Upvotes: 1

Related Questions