marshmallow-code / marshmallow-code/marshmallow-sqlalchemy

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

Open
#455 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
580
Forks
101
Avg merge
7h 6m
Merged PRs (30d)
3

Description

I have the following SQLAlchemy Models:

```py
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:

```py
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:
```py
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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with UserSchema, ToDoSchema, and ToDoTaskSchema, then reproduce the nested UserSchema().load(data) followed by session add and commit. Determine how existing ToDoTask.task values should be resolved without creating duplicate rows, and verify that the resulting User, ToDo, and foreign-key relationships match the example.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlalchemy
Domain
database
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.