FastAPI SQLAlchemy delete Many-to-Many relation causes StaleDataError-CodePudding

I have an error upon deleting a ManyToMany relation from table. I found that the problem caused by same items in bills_dishes table, but I need to add same items.

So, the code below works fine for items without duplicates like:

bill_id | dish_id
1       | 1
1       | 2

Models:

bills_dishes_association = Table(
    "bills_dishes",
    Base.metadata,
    Column("bill_id", Integer, ForeignKey("bills.id")),
    Column("dish_id", Integer, ForeignKey("dishes.id")),
)


class Waiter(Base):
    """Waiter model."""

    __tablename__ = "waiters"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    password = Column(String(6), nullable=False)
    bills = relationship("Bill")

    def __repr__(self):
        return f"Waiter(id={self.id}, username={self.username})"


class Bill(Base):
    """Bill model."""

    __tablename__ = "bills"

    id = Column(Integer, primary_key=True, autoincrement=True)
    waiter_id = Column(Integer, ForeignKey("waiters.id"), nullable=False)
    table_number = Column(Integer, nullable=False)
    amount = Column(Float, nullable=False)
    tip_percent = Column(Integer)
    tip_included = Column(Boolean, default=False, nullable=False)
    time = Column(DateTime(timezone=True), server_default=func.now())

    dishes = relationship(
        "Dish", secondary=bills_dishes_association, back_populates="ordered"
    )

    def __repr__(self):
        return f"Bill(id={self.id}, table_number={self.table_number}, amount={self.amount})"


class Dish(Base):
    """Dish model."""

    __tablename__ = "dishes"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    description = Column(String(1024), nullable=False)
    image_url = Column(String(500), nullable=False)
    cost = Column(Float)

    ordered = relationship(
        "Bill",
        secondary=bills_dishes_association,
        back_populates="dishes",
        cascade="all, delete",
        passive_deletes=True,
    )

crud.py:

def delete_bill(db: Session, bill_id: int):
    """Delete a bill by id."""
    db_bill = db.query(Bill).filter(Bill.id == bill_id).first()
    db.delete(db_bill)
    db.commit()
    return db_bill

But it doesn't work for this case:

bill_id | dish_id
1       | 2
1       | 2

sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'bills_dishes' expected to delete 1 row(s); Only 2 were matched.

How to handle this?

CodePudding user response：

I think you need to set passive_deletes=True on the dishes relationship in Bill and also add ondelete="CASCADE" to both foreign keys in the bills_dishes_association.

Although for a critical table you might be better off elevating bills_dishes to something more like bill_lines and promoting it to a full blown Model like BillLine such that Bill -- 1tomany -- BillLine -- manyto1 -- Dish and giving it its own PK id:

sqlalchemy.orm.relationship.params.passive_deletes