[Python] SQLAlchemy association table with soft delete

I'm trying to configure my SQLAlchemy models to use an association table for a relationship. The issue I have is that the association table has a column "removed". I can't find a way to tell SQLAlchemy not to return associated objects if the relationship has been removed (soft deleted).

Here is an example:

class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
b_list = relationship("B", secondary=lambda: association_table, backref="a_list")

class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)

association_table = Table(
"a_b_association",
metadata,
Column("a_id", Integer, ForeignKey(A.id), primary_key=True),
Column("b_id", Integer, ForeignKey(B.id), primary_key=True),
Column("removed", Integer)
)


The example above will always return the relationship. I need to change the relationship to only return when "removed==0".

Thanks for any help with this.

I know the SQL query I want SQLAlchemy to make is:

SELECT A.id, B.id
FROM A
INNER JOIN a_b_association ON A.id=a_b_association.a_id
INNER JOIN B ON B.id=a_b_association.b_id
WHERE a_b_association.removed=0;

Continue reading...