SQLAlchemy: delete rows from associate table from many-to-many relationship

Question

I noticed that when deleting one entry, the corresponding rows from the secondary table are not deleted. here my models:

cashflows_tags_table = Table(
    "cashflows_tags",
    Base.metadata,
    Column("cashflow_id", ForeignKey("cashflows.id"),primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)

class Tag(Base):
    __tablename__ = "tags"

    id = Column(Integer, primary_key=True)
    tag = Column(String, nullable=False, unique=True)

class Cashflow(Base):
    __tablename__ = "cashflows"

    id = Column(Integer, primary_key=True)

    date = Column(DateTime, nullable=False)
    amount = Column(Float, nullable=False)
    description = Column(String, nullable=False)

    ### Many to Many ###
    tags = relationship("Tag", secondary=cashflows_tags_table)
    #######

What I understand from the documentation (https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#deleting-rows-from-the-many-to-many-table) is that the deletion should be propagated automatically. Additional cascade/delete/delete-orphan would cause the deletion of the elements on the other "many" side, which is not what I want.

I am using sqlite, and the following ORM syntax for the deletion:

with Session(db_engine) as session:
            ddd = delete(Cashflow).where(Cashflow.id.in_(id_list))
            session.execute(ddd)
            session.commit()

EDIT

in the end I solved it manually selecting the entry and emptying the tag collection before the deletion (as suggested in other threads). I will still leave the question open, since it is not clear to me if this is the expected behaviour

Answer 1

If you don't want to set a cascade, session.delete will remove both the CashFlow and the associated many-to-many records.

cfs = s.scalars(sa.select(Cashflow).where(Cashflow.id.in_(id_list)))
for cs in cfs:
    session.delete(cf)

It will emit a DELETE statement for the many-to-many records first, then the CashFlows.

Alternatively, you can enable foreign keys in SQLite and set "CASCADE" as the ondelete behaviour for the foreign key in the association table - only rows associated with the CashFlow to be deleted will be deleted when using sqlachemy.delete.

from sqlalchemy.engine import Engine

# Enable foreign keys (from the SQLAlchemy docs)

@sa.event.listens_for(Engine, 'connect')
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute('PRAGMA foreign_keys=ON')
    cursor.close()

cashflows_tags_table = sa.Table(
    'cashflows_tags',
    Base.metadata,
    sa.Column('cashflow_id', sa.ForeignKey('cashflows.id', ondelete='CASCADE'), primary_key=True),
    sa.Column('tag_id', sa.ForeignKey('tags.id'), primary_key=True),
)