Deserializing a multiple nested dictionary while abiding unique constraints on foreign key tables

[DanielJerrehian]

I have the following SQLAlchemy Models:

class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(30), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    todos = db.relationship("ToDo", uselist=True, cascade = "all, delete", order_by="desc(ToDo.id)", backref=backref("user", uselist=False), lazy=True)
 
   
class ToDo(db.Model):
    __tablename__ = "to_do"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    task_id = db.Column(db.Integer, db.ForeignKey('to_do_task.id'), nullable=False)
    task = db.relationship("ToDoTask", uselist=False, cascade = "all, delete", backref=backref("todos", uselist=False), lazy=True)


class ToDoTask(db.Model):
    __tablename__ = "to_do_task"
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.String(128), nullable=False, unique=True)

I went ahead and created the corresponding Marshmallow schemas:

class UserSchema(ma.SQLAlchemyAutoSchema):
    todos = ma.Nested("ToDoSchema", many=True)
    
    class Meta:
        model = User
        load_instance = True


class ToDoSchema(ma.SQLAlchemyAutoSchema):
    task = ma.Nested("ToDoTaskSchema", many=False)
    
    class Meta:
        model = ToDo
        load_instance = True
        include_relationships = True



class ToDoTaskSchema(ma.SQLAlchemyAutoSchema):
    to_dos = ma.Nested("ToDoSchema")

    class Meta:
        model = ToDoTask
        load_instance = True

I would ideally like to be able to add the following dictionary to the database, without adding a new task to the ToDoTask table if it already exists (hence the unique=True constraint on the model).

When trying to use the Marshmallow .load() method, I run into the error that the data cannot be added due to the unique constraint because dictionary already includes that value. I would like to only add the corresponding foreign key to the to ToDo table instead.

Given the following code:

data = {
    "email": "test_email_2@gmail.com",
    "password": "test",
    "todos": [
        {
            "task": {
                "task": "Gym"
            }
        }
    ]
}

user = UserSchema().load(data=data)
db.session.add(user) # Integrity error occurs here
db.session.commit()

I am getting an integrity error here because of the unique constraint on the ToDoTask.task column although I want Marshmallow to recognize the value already exists and only fetch it's ID and enter it in the ToDo table under task_id.

If the ToDoTask table contains 1 row with task equal to "Gym", I would like to populate the User table with the email="test_email_2@gmail.com", password="test" and then the ToDo table with user_id=2, task_id=1

Here is the link on SO if anyone is interested: https://stackoverflow.com/questions/73335484/how-to-deserialize-a-multiple-nested-dictionary-using-marshmallow-while-abiding

[deckar01]

load_instance only queries for existing objects using the primary key. Even if you overloaded ToDoTaskSchema.get_instance() to query on the name, the schema doesn't appear to handle copying the nested task.id to task_id, so you would probably have to add a @post_load hook to ToDoSchema to test for this situation and copy it.

I suspect this behavior would be generally useful:

• Try querying unique fields when the primary key is not provided
• Propagate nested primary keys to foreign keys when instances are found