CODEWITHSUNDEEP
sqlalchemyfastapipydantic
Ness
Ness

Reputation: 128

Fastapi delete only one record in many to many table

I am building a Fastapi application that uses SQLAlchemy, and I am trying to implement a many-to-many relationship. My problem is when I try to delete a record in intermediate table it deletes all records. Example of table 'device_protocol'

device_id protocol_id status_id
1 1 1
1 2 1
1 3 3

If I try to remove only device_id=1 with protocol_id=2 it actually removes all records with device_id=1

Models:

class DeviceProtocolAssociation(Base):
    __tablename__ = "device_protocol"
    device_id = Column(Integer, ForeignKey("device.id", ondelete="CASCADE"), primary_key=True)
    device = relationship("Device", back_populates="device_protocols")
    protocol_id = Column(Integer, ForeignKey("protocol.id"), primary_key=True)
    protocol = relationship("Protocol", back_populates="device_protocols")
    status_id = Column(Integer, ForeignKey("status.id"), nullable=True)
    status = relationship("Status", back_populates="device_protocols")

class Device(Base):
    __tablename__ = "device"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    status_id = Column(Integer, ForeignKey('status.id'))
    status = relationship("Status", back_populates="devices")
    device_protocols = relationship(DeviceProtocolAssociation, back_populates="device")
    protocols = association_proxy("device_protocols", "protocols")

class Protocol(Base):
    __tablename__ = "protocol"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    device_protocols = relationship(DeviceProtocolAssociation, back_populates="protocol")
    devices = association_proxy("device_protocols", "devices")

class Status(Base):
    __tablename__ = "status"
    id = Column(Integer, primary_key=True, unique=True, index=True)
    name = Column(String(255))
    description = Column(String(255), nullable=True)
    devices = relationship("Device", back_populates="status")
    device_protocols = relationship(DeviceProtocolAssociation, back_populates="status")

Router:

@router.delete('/{device_id}/{protocol_id}')
async def delete_status(device_id: int, protocol_id: int, db:Session=Depends(get_db)):
    relation_query = db.query(DeviceProtocolAssociation).filter(DeviceProtocolAssociation.device_id==device_id and DeviceProtocolAssociation.protocol== protocol_id)
    db_relation = relation_query.first()
    if not db_relation:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail=f'No relation with this id: {id} found')
    relation_query.delete(db_relation)
    db.commit()
    return {"relation": "deleted"}

How can I remove only a record?

Upvotes: 0

Views: 337

Answers (2)

Ness
Ness

Reputation: 128

Solution to my problem is:

relation_query = db.query(DeviceProtocolAssociation).filter(DeviceProtocolAssociation.device_id==device_id, DeviceProtocolAssociation.protocol_id==protocol_id)

Upvotes: 0

M.O.
M.O.

Reputation: 2911

You can't use python's and when filtering in SQLAlchemy. You can either pass several conditions to the filter

relation_query = db.query(DeviceProtocolAssociation).filter(
    DeviceProtocolAssociation.device_id == device_id,
    DeviceProtocolAssociation.protocol == protocol_id,
)

or use the binary and-operator &:

relation_query = db.query(DeviceProtocolAssociation).filter(
    (DeviceProtocolAssociation.device_id == device_id)
    & (DeviceProtocolAssociation.protocol == protocol_id)
)

Upvotes: 1

Related Questions