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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

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.