Issue with "complex" many 2 many delete, FK violation
158 views
Skip to first unread message
maqui...@gmail.com
Apr 13, 2021, 10:03:59 AM4/13/21
to sqlalchemy
I need to delete the association table rows for many to many relationships when I delete one, but the default behavior (to remove those rows) does not seem to work in my case.
Cascades could be an issue, and I would rather handle removal of any "orphan" rows in the model tables via business logic than have the database cascade deletes and potentially remove rows that are associated with other objects.
import pytest
from sqlalchemy import (
Table,
Column,
Integer,
String,
ForeignKey,
create_engine,
)
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import relationship, Session
from sqlalchemy.util import OrderedSet
Base: DeclarativeMeta = declarative_base()
engine = create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
test_chain_const_region = Table(
"test_chain_const_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("const_region_id", Integer, ForeignKey("test_const_region.id")),
)
test_chain_var_region = Table(
"test_chain_var_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("var_region_id", Integer, ForeignKey("test_var_region.id")),
)
test_molecule_chain = Table(
"test_molecule_chain",
Base.metadata,
Column("molecule_id", Integer, ForeignKey("test_molecule.id")),
Column("chain_id", Integer, ForeignKey("test_chain.id")),
)
test_mol_sequence_feat_mol_sequence = Table(
"test_mol_sequence_feat_mol_sequence",
Base.metadata,
Column("mol_sequence_feat_id", Integer, ForeignKey("test_mol_sequence_feat.id")),
Column("mol_sequence_id", Integer, ForeignKey("test_mol_sequence.id")),
)
class TestMolecule(Base):
__tablename__ = "test_molecule"
id = Column(Integer, primary_key=True)
label = Column(String)
chains = relationship(
"TestChain",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="molecules",
)
class TestMolSequence(Base):
__tablename__ = "test_mol_sequence"
id = Column(Integer, primary_key=True)
content = Column(String, nullable=False, unique=True)
parent_features = relationship(
"TestMolSequenceFeat",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="feature_sequences",
single_parent=True,
)
chains = relationship(
"TestChain", back_populates="mol_sequence", collection_class=OrderedSet
)
class TestMolSequenceFeat(Base):
__tablename__ = "test_mol_sequence_feat"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
molecule_sequence = relationship("TestMolSequence",)
start = Column(Integer)
stop = Column(Integer)
feature_sequences = relationship(
"TestMolSequence",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="parent_features",
# single_parent=True,
)
class TestChain(Base):
__tablename__ = "test_chain"
id = Column(Integer, primary_key=True)
label = Column(String)
chain_type = Column(String)
mol_sequence_id = Column(Integer, ForeignKey("test_mol_sequence.id"))
mol_sequence = relationship("TestMolSequence", back_populates="chains")
molecules = relationship(
"TestMolecule",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="chains",
)
var_regions = relationship(
"TestVarRegion",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="chains",
)
const_regions = relationship(
"TestConstRegion",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="chains",
)
class TestVarRegion(Base):
__tablename__ = "test_var_region"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
description = Column(String)
additional_information = Column(String)
label = Column("label", String, nullable=True, unique=False)
molecule_sequence = relationship("TestMolSequence")
chains = relationship(
"TestChain",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="var_regions",
passive_deletes=True,
)
class TestConstRegion(Base):
__tablename__ = "test_const_region"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
description = Column(String)
additional_information = Column(String)
label = Column("label", String, nullable=True, unique=False)
molecule_sequence = relationship("TestMolSequence")
chains = relationship(
"TestChain",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="const_regions",
passive_deletes=True,
)
class TestManyToMany:
@pytest.fixture
def engine(self):
return create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
@pytest.fixture
def session(self):
"""Returns an sqlalchemy session, and after the test tears down everything properly."""
connection = engine.connect()
# begin the nested transaction
transaction = connection.begin()
# use the connection with the already started transaction
session = Session(bind=connection)
yield session
session.close()
# roll back the broader transaction
transaction.commit()
# put back the connection to the connection pool
connection.close()
@pytest.mark.create_m2m_models
def test_create_m2m_models(self, engine):
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
@pytest.mark.seed_m2m_data
def test_m2m_seeding_data(self, engine, session):
molecule1 = TestMolecule(label="molecule1")
molecule2 = TestMolecule(label="molecule2")
molecule3 = TestMolecule(label="molecule3")
molecule4 = TestMolecule(label="molecule4")
light_chain_1_sequence = TestMolSequence(content="taglconst1VAR1")
heavy_chain_1_sequence = TestMolSequence(content="tagheavyconstant1VAR2")
heavy_chain_2_sequence = TestMolSequence(content="tagheavyconstant2VAR2")
light_chain_2_sequence = TestMolSequence(content="taglconst1VAR3")
heavy_chain_1 = TestChain(
chain_type="heavy", mol_sequence=light_chain_1_sequence
)
light_chain_1 = TestChain(
chain_type="light", mol_sequence=heavy_chain_1_sequence
)
light_chain_2 = TestChain(
chain_type="light", mol_sequence=light_chain_2_sequence
)
heavy_chain_2 = TestChain(
chain_type="heavy", mol_sequence=heavy_chain_2_sequence
)
molecule1.chains.add(heavy_chain_1)
molecule1.chains.add(light_chain_1)
molecule2.chains.add(heavy_chain_2)
molecule2.chains.add(light_chain_2)
molecule3.chains.add(heavy_chain_1)
molecule3.chains.add(light_chain_2)
molecule4.chains.add(heavy_chain_2)
molecule4.chains.add(light_chain_1)
tag_sequence = TestMolSequence(content="tag")
light_constant_region_seq = TestMolSequence(content="lconst1")
heavy_constant_region_1_seq = TestMolSequence(content="heavyconstant1")
heavy_constant_region_2_seq = TestMolSequence(content="heavyconstant2")
vr1_seq = TestMolSequence(content="VAR1")
vr2_seq = TestMolSequence(content="VAR2")
vr3_seq = TestMolSequence(content="VAR3")
# lc2_const_region_seq = TestMolSequence(content="lconst")
lc1_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=light_chain_1_sequence
)
lc1_const_region_feature = TestMolSequenceFeat(
start=3, stop=10, molecule_sequence=light_chain_1_sequence
)
lc1_var_region_feature = TestMolSequenceFeat(
start=10, stop=14, molecule_sequence=light_chain_1_sequence
)
hc1_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=heavy_chain_1_sequence
)
hc1_const_region_feature = TestMolSequenceFeat(
start=3, stop=17, molecule_sequence=heavy_chain_1_sequence
)
hc1_var_region_feature = TestMolSequenceFeat(
start=17, stop=21, molecule_sequence=heavy_chain_1_sequence
)
hc2_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=heavy_chain_2_sequence
)
hc2_const_region_feature = TestMolSequenceFeat(
start=3, stop=17, molecule_sequence=heavy_chain_2_sequence
)
hc2_var_region_feature = TestMolSequenceFeat(
start=17, stop=21, molecule_sequence=heavy_chain_2_sequence
)
lc2_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=light_chain_2_sequence
)
lc2_const_region_feature = TestMolSequenceFeat(
start=3, stop=10, molecule_sequence=light_chain_2_sequence
)
lc2_var_region_feature = TestMolSequenceFeat(
start=10, stop=14, molecule_sequence=light_chain_2_sequence
)
var_region1 = TestVarRegion(molecule_sequence=vr1_seq)
var_region2 = TestVarRegion(molecule_sequence=vr2_seq)
var_region3 = TestVarRegion(molecule_sequence=vr3_seq)
const_region1 = TestConstRegion(molecule_sequence=light_constant_region_seq)
const_region2 = TestConstRegion(molecule_sequence=heavy_constant_region_1_seq)
const_region3 = TestConstRegion(molecule_sequence=heavy_constant_region_2_seq)
light_chain_1.var_regions.add(var_region1)
heavy_chain_1.var_regions.add(var_region2)
heavy_chain_2.var_regions.add(var_region2)
light_chain_2.var_regions.add(var_region3)
light_chain_1.const_regions.add(const_region1)
light_chain_2.const_regions.add(const_region1)
heavy_chain_1.const_regions.add(const_region2)
heavy_chain_2.const_regions.add(const_region3)
lc1_tag_feature.feature_sequences.add(tag_sequence)
lc1_var_region_feature.feature_sequences.add(vr1_seq)
lc1_const_region_feature.feature_sequences.add(light_constant_region_seq)
hc1_tag_feature.feature_sequences.add(tag_sequence)
hc1_var_region_feature.feature_sequences.add(vr2_seq)
hc1_const_region_feature.feature_sequences.add(heavy_constant_region_1_seq)
lc2_tag_feature.feature_sequences.add(tag_sequence)
lc2_var_region_feature.feature_sequences.add(vr3_seq)
lc2_const_region_feature.feature_sequences.add(light_constant_region_seq)
hc2_tag_feature.feature_sequences.add(tag_sequence)
hc2_var_region_feature.feature_sequences.add(vr2_seq)
hc2_const_region_feature.feature_sequences.add(heavy_constant_region_2_seq)
session.add_all(
[
heavy_chain_1,
light_chain_1,
light_chain_2,
heavy_chain_2,
molecule1,
molecule2,
molecule3,
molecule4,
# tag_sequence,
# lc1_tag_feature,
# lc1_const_region_feature,
# hc1_tag_feature,
# hc1_var_region_feature,
# lc1_tag_feature,
# lc2_const_region_feature,
]
)
session.commit()
@pytest.mark.delete_test_m2m_models
def test_create_m2m(self, session):
molecule = session.query(TestMolecule).filter_by(label="molecule1").one()
session.delete(molecule)
session.query(TestChain).filter(~TestChain.molecules.any()).delete(
synchronize_session="fetch"
)
session.expire_all()
orphan_chains = (
session.query(TestChain).filter(~TestChain.molecules.any()).all()
)
for chain in orphan_chains:
session.delete(chain)
orphan_vrs = (
session.query(TestVarRegion).filter(~TestVarRegion.chains.any()).all()
)
for orphan_vr in orphan_vrs:
session.delete(orphan_vr)
orphan_crs = (
session.query(TestConstRegion)
.filter(~TestConstRegion.chains.any())
.all()
)
for orphan_cr in orphan_crs:
session.delete(orphan_cr)
orphan_sequences = (
session.query(TestMolSequence).filter(~TestMolSequence.chains.any()).all()
)
orphan_sequence: TestMolSequence
for orphan_sequence in orphan_sequences:
session.delete(orphan_sequence)
session.commit()
I have multiple levels of many to many relationships, as you can see in the example I'll provide below and when I delete a "parent" afterwards I try to clean up any children left behind that have no other parents. However, these children are in many to many relationships with other children and that's when the ORM fails to attempt to remove those children from their related association tables (at least, in a way that I expect).
The issue is error is:
def do_executemany(self, cursor, statement, parameters, context=None):
if self.executemany_mode is EXECUTEMANY_DEFAULT:
> cursor.executemany(statement, parameters)
E sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "test_var_region" violates foreign key constraint "test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
E DETAIL: Key (id)=(1) is still referenced from table "test_chain_var_region".
E
E [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id = %(id)s]
E [parameters: ({'id': 5}, {'id': 6}, {'id': 7}, {'id': 8}, {'id': 9}, {'id': 10})]
E (Background on this error at: http://sqlalche.me/e/gkpj)
The desired effect, of course, is that the rows in test_chain_var_region that reference the deleted chains removed. I've tried several strategies to do this but with no change in this behavior.
Cascades could be an issue, and I would rather handle removal of any "orphan" rows in the model tables via business logic than have the database cascade deletes and potentially remove rows that are associated with other objects.
import pytest
from sqlalchemy import (
Table,
Column,
Integer,
String,
ForeignKey,
create_engine,
)
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import relationship, Session
from sqlalchemy.util import OrderedSet
Base: DeclarativeMeta = declarative_base()
engine = create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
test_chain_const_region = Table(
"test_chain_const_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("const_region_id", Integer, ForeignKey("test_const_region.id")),
)
test_chain_var_region = Table(
"test_chain_var_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("var_region_id", Integer, ForeignKey("test_var_region.id")),
)
test_molecule_chain = Table(
"test_molecule_chain",
Base.metadata,
Column("molecule_id", Integer, ForeignKey("test_molecule.id")),
Column("chain_id", Integer, ForeignKey("test_chain.id")),
)
test_mol_sequence_feat_mol_sequence = Table(
"test_mol_sequence_feat_mol_sequence",
Base.metadata,
Column("mol_sequence_feat_id", Integer, ForeignKey("test_mol_sequence_feat.id")),
Column("mol_sequence_id", Integer, ForeignKey("test_mol_sequence.id")),
)
class TestMolecule(Base):
__tablename__ = "test_molecule"
id = Column(Integer, primary_key=True)
label = Column(String)
chains = relationship(
"TestChain",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="molecules",
)
class TestMolSequence(Base):
__tablename__ = "test_mol_sequence"
id = Column(Integer, primary_key=True)
content = Column(String, nullable=False, unique=True)
parent_features = relationship(
"TestMolSequenceFeat",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="feature_sequences",
single_parent=True,
)
chains = relationship(
"TestChain", back_populates="mol_sequence", collection_class=OrderedSet
)
class TestMolSequenceFeat(Base):
__tablename__ = "test_mol_sequence_feat"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
molecule_sequence = relationship("TestMolSequence",)
start = Column(Integer)
stop = Column(Integer)
feature_sequences = relationship(
"TestMolSequence",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="parent_features",
# single_parent=True,
)
class TestChain(Base):
__tablename__ = "test_chain"
id = Column(Integer, primary_key=True)
label = Column(String)
chain_type = Column(String)
mol_sequence_id = Column(Integer, ForeignKey("test_mol_sequence.id"))
mol_sequence = relationship("TestMolSequence", back_populates="chains")
molecules = relationship(
"TestMolecule",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="chains",
)
var_regions = relationship(
"TestVarRegion",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="chains",
)
const_regions = relationship(
"TestConstRegion",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="chains",
)
class TestVarRegion(Base):
__tablename__ = "test_var_region"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
description = Column(String)
additional_information = Column(String)
label = Column("label", String, nullable=True, unique=False)
molecule_sequence = relationship("TestMolSequence")
chains = relationship(
"TestChain",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="var_regions",
passive_deletes=True,
)
class TestConstRegion(Base):
__tablename__ = "test_const_region"
id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
description = Column(String)
additional_information = Column(String)
label = Column("label", String, nullable=True, unique=False)
molecule_sequence = relationship("TestMolSequence")
chains = relationship(
"TestChain",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="const_regions",
passive_deletes=True,
)
class TestManyToMany:
@pytest.fixture
def engine(self):
return create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
@pytest.fixture
def session(self):
"""Returns an sqlalchemy session, and after the test tears down everything properly."""
connection = engine.connect()
# begin the nested transaction
transaction = connection.begin()
# use the connection with the already started transaction
session = Session(bind=connection)
yield session
session.close()
# roll back the broader transaction
transaction.commit()
# put back the connection to the connection pool
connection.close()
@pytest.mark.create_m2m_models
def test_create_m2m_models(self, engine):
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
@pytest.mark.seed_m2m_data
def test_m2m_seeding_data(self, engine, session):
molecule1 = TestMolecule(label="molecule1")
molecule2 = TestMolecule(label="molecule2")
molecule3 = TestMolecule(label="molecule3")
molecule4 = TestMolecule(label="molecule4")
light_chain_1_sequence = TestMolSequence(content="taglconst1VAR1")
heavy_chain_1_sequence = TestMolSequence(content="tagheavyconstant1VAR2")
heavy_chain_2_sequence = TestMolSequence(content="tagheavyconstant2VAR2")
light_chain_2_sequence = TestMolSequence(content="taglconst1VAR3")
heavy_chain_1 = TestChain(
chain_type="heavy", mol_sequence=light_chain_1_sequence
)
light_chain_1 = TestChain(
chain_type="light", mol_sequence=heavy_chain_1_sequence
)
light_chain_2 = TestChain(
chain_type="light", mol_sequence=light_chain_2_sequence
)
heavy_chain_2 = TestChain(
chain_type="heavy", mol_sequence=heavy_chain_2_sequence
)
molecule1.chains.add(heavy_chain_1)
molecule1.chains.add(light_chain_1)
molecule2.chains.add(heavy_chain_2)
molecule2.chains.add(light_chain_2)
molecule3.chains.add(heavy_chain_1)
molecule3.chains.add(light_chain_2)
molecule4.chains.add(heavy_chain_2)
molecule4.chains.add(light_chain_1)
tag_sequence = TestMolSequence(content="tag")
light_constant_region_seq = TestMolSequence(content="lconst1")
heavy_constant_region_1_seq = TestMolSequence(content="heavyconstant1")
heavy_constant_region_2_seq = TestMolSequence(content="heavyconstant2")
vr1_seq = TestMolSequence(content="VAR1")
vr2_seq = TestMolSequence(content="VAR2")
vr3_seq = TestMolSequence(content="VAR3")
# lc2_const_region_seq = TestMolSequence(content="lconst")
lc1_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=light_chain_1_sequence
)
lc1_const_region_feature = TestMolSequenceFeat(
start=3, stop=10, molecule_sequence=light_chain_1_sequence
)
lc1_var_region_feature = TestMolSequenceFeat(
start=10, stop=14, molecule_sequence=light_chain_1_sequence
)
hc1_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=heavy_chain_1_sequence
)
hc1_const_region_feature = TestMolSequenceFeat(
start=3, stop=17, molecule_sequence=heavy_chain_1_sequence
)
hc1_var_region_feature = TestMolSequenceFeat(
start=17, stop=21, molecule_sequence=heavy_chain_1_sequence
)
hc2_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=heavy_chain_2_sequence
)
hc2_const_region_feature = TestMolSequenceFeat(
start=3, stop=17, molecule_sequence=heavy_chain_2_sequence
)
hc2_var_region_feature = TestMolSequenceFeat(
start=17, stop=21, molecule_sequence=heavy_chain_2_sequence
)
lc2_tag_feature = TestMolSequenceFeat(
start=0, stop=3, molecule_sequence=light_chain_2_sequence
)
lc2_const_region_feature = TestMolSequenceFeat(
start=3, stop=10, molecule_sequence=light_chain_2_sequence
)
lc2_var_region_feature = TestMolSequenceFeat(
start=10, stop=14, molecule_sequence=light_chain_2_sequence
)
var_region1 = TestVarRegion(molecule_sequence=vr1_seq)
var_region2 = TestVarRegion(molecule_sequence=vr2_seq)
var_region3 = TestVarRegion(molecule_sequence=vr3_seq)
const_region1 = TestConstRegion(molecule_sequence=light_constant_region_seq)
const_region2 = TestConstRegion(molecule_sequence=heavy_constant_region_1_seq)
const_region3 = TestConstRegion(molecule_sequence=heavy_constant_region_2_seq)
light_chain_1.var_regions.add(var_region1)
heavy_chain_1.var_regions.add(var_region2)
heavy_chain_2.var_regions.add(var_region2)
light_chain_2.var_regions.add(var_region3)
light_chain_1.const_regions.add(const_region1)
light_chain_2.const_regions.add(const_region1)
heavy_chain_1.const_regions.add(const_region2)
heavy_chain_2.const_regions.add(const_region3)
lc1_tag_feature.feature_sequences.add(tag_sequence)
lc1_var_region_feature.feature_sequences.add(vr1_seq)
lc1_const_region_feature.feature_sequences.add(light_constant_region_seq)
hc1_tag_feature.feature_sequences.add(tag_sequence)
hc1_var_region_feature.feature_sequences.add(vr2_seq)
hc1_const_region_feature.feature_sequences.add(heavy_constant_region_1_seq)
lc2_tag_feature.feature_sequences.add(tag_sequence)
lc2_var_region_feature.feature_sequences.add(vr3_seq)
lc2_const_region_feature.feature_sequences.add(light_constant_region_seq)
hc2_tag_feature.feature_sequences.add(tag_sequence)
hc2_var_region_feature.feature_sequences.add(vr2_seq)
hc2_const_region_feature.feature_sequences.add(heavy_constant_region_2_seq)
session.add_all(
[
heavy_chain_1,
light_chain_1,
light_chain_2,
heavy_chain_2,
molecule1,
molecule2,
molecule3,
molecule4,
# tag_sequence,
# lc1_tag_feature,
# lc1_const_region_feature,
# hc1_tag_feature,
# hc1_var_region_feature,
# lc1_tag_feature,
# lc2_const_region_feature,
]
)
session.commit()
@pytest.mark.delete_test_m2m_models
def test_create_m2m(self, session):
molecule = session.query(TestMolecule).filter_by(label="molecule1").one()
session.delete(molecule)
session.query(TestChain).filter(~TestChain.molecules.any()).delete(
synchronize_session="fetch"
)
session.expire_all()
orphan_chains = (
session.query(TestChain).filter(~TestChain.molecules.any()).all()
)
for chain in orphan_chains:
session.delete(chain)
orphan_vrs = (
session.query(TestVarRegion).filter(~TestVarRegion.chains.any()).all()
)
for orphan_vr in orphan_vrs:
session.delete(orphan_vr)
orphan_crs = (
session.query(TestConstRegion)
.filter(~TestConstRegion.chains.any())
.all()
)
for orphan_cr in orphan_crs:
session.delete(orphan_cr)
orphan_sequences = (
session.query(TestMolSequence).filter(~TestMolSequence.chains.any()).all()
)
orphan_sequence: TestMolSequence
for orphan_sequence in orphan_sequences:
session.delete(orphan_sequence)
session.commit()
Mark Aquino
Apr 13, 2021, 3:12:37 PM4/13/21
to sqlal...@googlegroups.com
I just wanted to clarify, the desire would be for the "test_var_region" and "test_const_region" entities that are linked to other entities to remain untouched and only to have their associations removed from the deleted items. The output from the ORM indicates that the system is actually attempting to delete them in some sort of cascade event a.) although that is unspecified in any cascade option and b.) that is undesired in the first place.
--
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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7963c7d7-9053-436e-ae5b-f92519ddeb0en%40googlegroups.com.
Mike Bayer
Apr 13, 2021, 4:50:42 PM4/13/21
to noreply-spamdigest via sqlalchemy
Hi there -
I would ask that you try to make sure your formatting is maintained when posting examples especially such long ones as I had to re-indent it in order to run this.
The delete at the end is failing because of incomplete cascade rules. The DELETE against "test_mol_sequence" seeks to CASCADE as configured to the other three tables,, which then fail because there are non-cascading FKs in the association tables. We can see this via the message:
update or delete on table "test_var_region" violates foreign key constraint "test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
DETAIL: Key (id)=(1) is still referenced from table "test_chain_var_region".
it's DELETEing from test_mol_sequence but the error is against a totally different table. That's postgresql's cascade.
if you want these CASCADEs to remain in place then you'd need to ensure that those linked rows can be deleted without any rows being present in the association tables.
I can modify your test at the end to fully DELETE these rows without qualifying for those that have empty collections only and the script then passes, because now it's deleting those rows that would otherwise be dependent on by "test_var_region" and therefore "test_mol_sequence", so there's nothing unexpected going on. Easiest solution here would be to add CASCADE rules to the association tables also. If you want that to be prevented as you mention, and instead expect the script to explicitly delete those depending rows, then your script is already achieving that. the "business logic" so to speak in this case would be as below:
orphan_chains = (
session.query(TestChain).
# filter(~TestChain.molecules.any()).
all()
)
for chain in orphan_chains:
session.delete(chain)
orphan_vrs = (
session.query(TestVarRegion)
# .filter(~TestVarRegion.chains.any())
.all()
)
for orphan_vr in orphan_vrs:
session.delete(orphan_vr)
orphan_crs = (
session.query(TestConstRegion)
# .filter(~TestConstRegion.chains.any())
.all()
)
for orphan_cr in orphan_crs:
session.delete(orphan_cr)
orphan_sequences = (
session.query(TestMolSequence)
# .filter(~TestMolSequence.chains.any())
.all()
)
Mark Aquino
Apr 13, 2021, 5:30:09 PM4/13/21
to sqlal...@googlegroups.com
Hi Mike,
Sorry about the indentations.
I'm not sure I understand the changes you made to the script after delete as it removes all test_chains, test_var_regions, and test_const regions that are still referenced by the other test_molecules. The only way I've been able to get the delete to work properly is to manually delete test_var_regions and test_const_regions first and then delete the test_molecules, but the ideal outcome I'm trying to achieve is that when a test_molecule is deleted:
- that the system checks if the chains connected to it are removed if they are not linked to other test_molecules.
- if a test_chain is going to be deleted then
- the test_mol_sequence associated with it is deleted if it is no longer associated with any other test_chains
- any test_var_regions and test_const_regions are deleted if they are not associated with any other test_chains
- and finally if a test_mol_sequence is deleted that any test_mol_sequence_features are deleted if they are not associated with any other test_mol_sequences.
To make things a little easier to explain, if we just are dealing with molecule1 and molecule4 there are only 3 unique test_chains: heavy_chain_1, light_chain_1, and heavy_chain_2
if I deleted molecule1, I would want to retain heavy_chain_2 and light_chain_1 and delete heavy_chain_1 as it was no longer associated with any test_molecule. Ideally, then I would remove any test_const_regions and test_var_regions that are no longer associated with any test_chains.
Because heavy_chain_1 shares the same test_var_region as heavy_chain_2, that test_var_region would remain in the system but the test_const_region unique to heavy_chain_1 would be deleted along with the test_mol_sequence and test_mol_sequence_features associated with it.
molecule1.chains.add(heavy_chain_1)
molecule1.chains.add(light_chain_1)
#molecule2.chains.add(heavy_chain_2)
#molecule2.chains.add(light_chain_2)
#molecule3.chains.add(heavy_chain_1)
#molecule3.chains.add(light_chain_2)
molecule4.chains.add(heavy_chain_2)
molecule4.chains.add(light_chain_1)
light_chain_1_sequence = TestMolSequence(content="taglconst1VAR1")
heavy_chain_1_sequence = TestMolSequence(content="tagheavyconstant1VAR2")
heavy_chain_2_sequence = TestMolSequence(content="tagheavyconstant2VAR2")
light_chain_2_sequence = TestMolSequence(content="taglconst1VAR3")
Does that make sense?
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/56bfe88d-8b81-4541-9192-86d42ebcc27f%40www.fastmail.com.
Mike Bayer
Apr 14, 2021, 11:21:54 AM4/14/21
to noreply-spamdigest via sqlalchemy
hey there-
The general strategy, if you want to write business logic that checks things, takes other actions, etc. when changes occur in the session, is to use the before_flush event handler: https://docs.sqlalchemy.org/en/14/orm/events.html?highlight=before_flush#sqlalchemy.orm.SessionEvents.before_flush
in this event, you can review the linkages on the objects in question and emit additional statements if desired. Note this is because you said you didn't want to use CASCADE rules on your foreign keys; that would allow your script to pass without change.
The general form of using before_flush(), where I've paraphrased a few of your business rules below in the form of pseduocode, looks like:
from sqlalchemy import event
@event.listens_for(SomeSessionOrFactory, 'before_flush')
def receive_before_flush(session, flush_context, instances):
for obj in session.deleted:
if isinstance(obj, TestMolecule):
check_obj_not_linked_to_other_test_molecules(obj)
elif isinstance(obj, TestChain):
if should_delete_related_test_mol_sequence(obj):
session.delete(obj.related_test_mol_sequence)
# ... etc
Obviously you'd need to work out the specifics of your model here, but within before_flush() you can respond to all objects that have pending changes and/or deletions, and add additional custom rules and actions where you are free to further modify the state of the Session, which will take effect within this same flush operation.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAL6EnB4LLyBJiqLFaG9xO2wZAE08yeipo_Ap7M2h-Csk0JTamg%40mail.gmail.com.
Mark Aquino
Apr 14, 2021, 11:45:27 AM4/14/21
to sqlal...@googlegroups.com
Thanks. I’ll take a stab at this approach. To back up a little bit my main confusion is around why the association tables aren’t updating as expected. As I understand it, without cascades configured, the default behavior should be to remove associations from
those tables. If I configure cascades like on delete=cascade then the associated objects themselves are set to also be deleted (as I would expect). In the no cascades scenario, if I delete the test_chain then the orm should remove its rows from test_chain_var_region
and test_chain_const_region (this occurs after the test_molecule is already deleted so any corresponding test_molecule_chain rows are already removed) but the constraint error occurs because it leaves them there. It works as expected with deleting the molecule,
and test_molecule_chain rows are removed but not test_chains hence why I need to perform the additional logic to remove the stranded chains that are left behind but not linked to any test_molecules anymore. Am I doing something differently in my test_molecule
configuration that I’m just not seeing?
Mark Aquino
From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mik...@zzzcomputing.com>
Sent: Wednesday, April 14, 2021 11:21:28 AM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Issue with "complex" many 2 many delete, FK violation
Sent: Wednesday, April 14, 2021 11:21:28 AM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] Issue with "complex" many 2 many delete, FK violation
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/-6vjYYTMPb4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/13572dd1-155e-47e0-9e9c-976e0ec1c56b%40www.fastmail.com.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-6vjYYTMPb4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/13572dd1-155e-47e0-9e9c-976e0ec1c56b%40www.fastmail.com.
Mike Bayer
Apr 14, 2021, 1:19:45 PM4/14/21
to noreply-spamdigest via sqlalchemy
On Wed, Apr 14, 2021, at 11:45 AM, Mark Aquino wrote:
Thanks. I’ll take a stab at this approach. To back up a little bit my main confusion is around why the association tables aren’t updating as expected. As I understand it, without cascades configured, the default behavior should be to remove associations from those tables.
that's correct, assuming the objects on either side are being deleted or if the collection to which the association refers towards is being emptied.
If I configure cascades like on delete=cascade then the associated objects themselves are set to also be deleted (as I would expect). In the no cascades scenario, if I delete the test_chain then the orm should remove its rows from test_chain_var_region and test_chain_const_region (this occurs after the test_molecule is already deleted so any corresponding test_molecule_chain rows are already removed) but the constraint error occurs because it leaves them there.
Here is where we need to work with a more succinct example, as the example given is too long and verbose so it's hard for me to isolate where SQLAlchemy doing the wrong thing, as in the case earlier where it seemed to me the scope of the related delete statement needed to be expanded, but that wasn't what you were tring to do.
Below is a structure that paraphrases what I think is part of your model, more or less, which at the moment is able to delete the "Chain" object. If you can modify the below script to illustrate more specifically the structural pattern that's present and the originating, single delete() statement you expect to succeed, then I can give you a better answer what's going on.
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()
class Molecule(Base):
__tablename__ = "molecule"
id = Column(Integer, primary_key=True)
chain = relationship("Chain", back_populates="molecules")
chain_id = Column(ForeignKey("chain.id", ondelete="CASCADE"))
chain_to_related = Table(
"chain_to_related",
Base.metadata,
Column("chain_id", ForeignKey("chain.id"), primary_key=True),
Column("related_id", ForeignKey("related_to_chain.id"), primary_key=True),
)
class Chain(Base):
__tablename__ = "chain"
id = Column(Integer, primary_key=True)
related = relationship("RelatedToChain", secondary=chain_to_related)
molecules = relationship("Molecule", back_populates="chain")
class RelatedToChain(Base):
__tablename__ = "related_to_chain"
id = Column(Integer, primary_key=True)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
c1 = Chain()
m1 = Molecule(chain=c1)
r1 = RelatedToChain()
c1.related.append(r1)
s.add_all([c1, m1, r1])
s.commit()
s.delete(c1)
s.commit()
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/BL0PR16MB2515A95197043547A667732CF04E9%40BL0PR16MB2515.namprd16.prod.outlook.com.
Mark Aquino
Apr 14, 2021, 5:12:26 PM4/14/21
to sqlal...@googlegroups.com
It's still a little bit long because you need the many to many relationships off chain to at least variable_region, but I can send that if the below doesn't clear things up for you:
I finally got it to work by updating the "business logic" script that (at least from my current analysis and testing) but in order to do so the chain.variable_regions and chain.constant_regions collections/relationships must be manually emptied (along with a couple other minor tweaks and some errors i spotted in the original example but that don't have an impact on the problem) The question/"issue" I have is that I was expecting those associations to be removed automatically if I delete the chain object. Is that incorrect?
molecules = (
session.query(TestMolecule)
.filter(TestMolecule.label.in_(["molecule1", "molecule2", "molecule3"]))
.all()
)
for molecule in molecules:
session.delete(molecule)
orphan_chains = (
session.query(TestChain).filter(~TestChain.molecules.any()).all()
)
for orphan_chain in orphan_chains:
orphan_chain.var_regions.clear()
orphan_chain.const_regions.clear()
session.delete(orphan_chain).
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b2ae5c82-c1a0-4b9e-931f-b515e3e55bce%40www.fastmail.com.
Mike Bayer
Apr 14, 2021, 7:35:52 PM4/14/21
to noreply-spamdigest via sqlalchemy
Overall for SQLAlchemy to know that it has to remove a row from an association table, it has to know that an object on one side or the other is being deleted. if the linkages here are somehow going from that CASCADE you have to a row that then does not have a CASCADE, and SQLAlchemy doesn't have relationship paths it can follow with "cascade='all, delete'" set up so it knows these things are being deleted, then it wouldn't know to do that.
basically it's very unlikely that SQLAlchemy is not correctly doing something that it has all the information it needs otherwise, as this logic is over ten years old and has had hundreds if not thousands of scenarios and issues against it tested and fixed.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAL6EnB7D%3DC9ynmGHujWXWiYgN3cOVBYVRWs7x4dDpuOZwibnfw%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages