I have two table user and film. These table have many to many relationship with extra field role. I am trying to update role if user and film matched. But this giving me error

user_film_table = Table(
  "user_film_association",
  Base.metadata,
  Column("id", Integer, primary_key=True, index=True),
  Column("user_id", ForeignKey("users.id"), index=True),
  Column("film_id", ForeignKey("film.id"), index=True),
  Column('role', ENUM(UserRoleEnum))  # Role can be 'writer', 'producer', or 'director' 
)

def update_user_role(self, user_id, film_id, new_role):
        user = self.db.query(User).filter(User.id == user_id).first()
        if not user:
            return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="User not found")

        film = self.db.query(Film).filter(Film.id == film_id).first()
        if not film:          
            return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Film not found")

        if film not in user.film:
            return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="User is not associated with the specified film")

        user_film = self.db.query(user_film_table).filter(user_film_table.c.user_id == user_id, user_film_table.c.film_id == film_id).first()
        print(user_film[0])
        user_film[3].role = new_role.value   #facing error while updating the role
        self.db.add(user_film)
        self.db.commit()
        return {"message": f"User role in film updated to {new_role} successfully", 'status_code': status.HTTP_202_ACCEPTED}

Not able to update the role in many to many field
getting error

raise exc.UnmappedInstanceError(instance) from err
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.engine.row.Row' is not mapped

>Solution :

The error you are encountering is because the user_film object you are trying to add using self.db.add(user_film) is not a mapped instance, but rather a row result.

In SQLAlchemy, only “mapped” instances (instances of classes that have been mapped to database tables) can be added to a session for ORM-style CRUD operations.

you can use the update statement provided by SQLAlchemy, which can be used to update specific fields in a row. The where method is used to specify which row(s) should be updated, and the values method is used to specify the new values for the fields.
Here’s how you can fix this:

from sqlalchemy import update

def update_user_role(self, user_id, film_id, new_role):
    user = self.db.query(User).filter(User.id == user_id).first()
    if not user:
        return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="User not found")

    film = self.db.query(Film).filter(Film.id == film_id).first()
    if not film:          
        return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Film not found")

    if film not in user.film:
        return HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="User is not associated with the specified film")

    # Use the update statement
    stmt = (
        update(user_film_table).
        where(user_film_table.c.user_id == user_id, user_film_table.c.film_id == film_id).
        values(role = new_role.value)
    )
    self.db.execute(stmt)
    self.db.commit()
    return {"message": f"User role in film updated to {new_role} successfully", 'status_code': status.HTTP_202_ACCEPTED}