Update existing many-to-many record

Question

I am wondering what is the right way to update a many to many relationship using fastAPI and sqlalchemy.
Setting up the relationships is well documented and there are a lot of examples.

Thanks to that I was able to figuring out how to get nested data from the models and schemas.
But the tricky part to me is the updating process of an existing entry.

The Models:

class Customer(Base):
    __tablename__ = "customers"

    id = Column(Integer, primary_key=True, index=True)
    customer_number = Column(String(50),nullable="False",unique="True", index=True)

    children: Mapped[List["CustomerAuditStandard"]] = relationship()

class auditStandard(Base):
    __tablename__ = "standards"

    id = Column(Integer, primary_key=True, index=True)
    description = Column(String(400),nullable=False)


class CustomerAuditStandard(Base):
    __tablename__ = "customer_standards"

    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer,ForeignKey("customers.id"),nullable=False)
    standard_id = Column(Integer,ForeignKey("standards.id"),nullable=False)

    child: Mapped["auditStandard"] = relationship()

For example when I want to use an endpoint that syncs the existing records with the new ones coming from the frontend (multi-select-dropdown). How to add new entries but also delete old one if they doesnt show up?

Frontend payload example:

{
  "id": 1,
  "customer_number": "1374131",
  "standards": [
    {
      "id": 1,
      "description": "Standard 1"
    },
    {
      "id": 3,
      "description": "Standard 2"
    },
    {
      "id": 2,
      "description": "Standard 3"
    },
    {
      "id": 4,
      "description": "Standard 4"
    }
  ],
}

I could not find an "out-of-the box" function to compare the records

Answer 1

I'm not sure I understand your task correctly. But maybe my code will help you.

models.py

from typing import List

from sqlalchemy import Table, ForeignKey, Column, Integer, String
from sqlalchemy.orm import Mapped, mapped_column, relationship

from database import Base


customers_to_standards = Table(
    "customers_to_standards",
    Base.metadata,
    Column("customer_id", ForeignKey("customers.id"), primary_key=True),
    Column("standard_id", ForeignKey("standards.id"), primary_key=True),
)


class Customer(Base):
    __tablename__ = "customers"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    customer_number: Mapped[str] = mapped_column(
        String(50),nullable="False", unique="True", index=True
    )
    standards: Mapped[List["AuditStandard"]] = relationship(
        secondary=customers_to_standards
    )


class AuditStandard(Base):
    __tablename__ = "standards"

    id: Mapped[int] = mapped_column(primary_key=True, index=True)
    description: Mapped[str] = mapped_column(String(400), nullable=False)

in path operation:

    standard_ids = [1, 3, 2, 4] # List of IDs

    st = select(models.AuditStandard).where(models.AuditStandard.id.in_(standard_ids))
    standards = (await session.scalars(st)).all()

    customer = await session.get(
        models.Customer, customer_id, options=[selectinload(models.Customer.standards)]
    )

    customer.standards.clear()
    customer.standards.extend(standards)
    await session.commit()
    await session.refresh(customer)
    return customer
    

Here I'm working only with standard IDs. If you need to update their descriptions or create them, you have to do it before this code.

If it's not a correct answer for your question, could you clarify the task? What do you expect to be done when you send this request?