Error updating one to many relationship in Flask/ SQLAlchemy

[atindra]

Hello,

I have two models Parent and Child in my database, there's a one to many relationship between these two models i.e. one parent can have multiple children.

from flask import Flask, request
from flask_restful import Resource
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
from flask_restful import Api
from marshmallow import fields

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///relationships.db"
db = SQLAlchemy(app)
ma = Marshmallow(app)
api = Api(app, prefix="/api")


class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False, unique=True)
    children = db.relationship("Child", backref="parent")


class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False, unique=True)
    parent_id = db.Column(db.Integer, db.ForeignKey("parent.id"))


class ChildSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Child
        ordered = True
        include_fk = True
        dump_only = ("id",)
        load_instance = True


class ParentSchema(ma.SQLAlchemyAutoSchema):
    children = fields.Pluck(ChildSchema, "name", many=True)

    class Meta:
        model = Parent
        ordered = True
        include_relationships = True
        dump_only = ("id",)
        load_instance = True

class ParentResource(Resource):
    @classmethod
    def get(cls, _id: int):
        parent_schema = ParentSchema()
        return parent_schema.dump(parent.query.filter_by(id=_id).first()), 200

    @classmethod
    def put(cls, _id: int):
        parent_json = request.get_json()
        parent_schema = ParentSchema()

        parent_input_data = parent_schema.load(parent_json)

        parent = Parent.query.filter_by(id=_id).first()
        parent.name = parent_input_data.name

        child_names = [child.name.lower() for child in parent.children]

        # Check if child is not already in parent children list
        for child_input in parent_input_data.children:
            if child_input.name.lower() not in child_names:
                parent.children.append(child_input)

        db.session.add(parent)
        db.session.commit()

        return {"message": "Updated"}, 200


api.add_resource(ParentResource, "/parent/<int:_id>")

if __name__ == "__main__":
    db.create_all()
    app.logger.info("Starting app...")
    app.run("127.0.0.1", 3003)

When I try to update the parent by adding some new children, I get a unique key constraint error as it seems that SQLAlchemy is trying to run an insert query in the parent table rather than trying to update the parent table record. There is already a record with the same name 'ABCD' in the parent table i.e. I just tried to leave the parent name as is and just update the children.



This is the input I have given to the PUT request.

{
"name": "ABCD",
"children": [
"Tom",
"Spot"
]
}
Can some one please help me understand where I am going wrong ? When I try to update the parent without trying to update the children, the update seems to work as expected. The issue happens only when I try to update the child relationship.