marshmallow-code / marshmallow-code/marshmallow-sqlalchemy
Deserializing a multiple nested dictionary while abiding unique constraints on foreign key tables
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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