How can I load only a foreign key into Nested fields using Marshmallow / SQLAlchemy

Question

I'm very new to Flask-Marshmallow / Flask-SQLAlchemy and I'm trying to set up my own REST API with flask and mysql.

Here is the payload I'm trying to post. I want to be able to only send the ID and exclude all other fields:

{
    "code": "FG034",
    "product_name": "test test2",
    "description": "Description",
    "init_date": "2021-01-10",
    "init_by": {
        "id": "27bb9e1acad247618fb2c3e016ae841c"
    }
}

This is the error I get when I send that payload:

ERROR in utils: (pymysql.err.IntegrityError) (1048, "Column 'full_name' cannot be null")
[SQL: INSERT INTO user (id, full_name, initials, hashed_password, is_active) VALUES (%(id)s, %(full_name)s, %(initials)s, %(hashed_password)s, %(is_active)s)]
[parameters: {'id': '27bb9e1acad247618fb2c3e016ae841c', 'full_name': None, 'initials': None, 'hashed_password': None, 'is_active': 1}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Which is weird because I'm not trying to insert anything into the user table...

When I put all fields in it adds as expected:

{
    "code": "FG034",
    "product_name": "test test2",
    "description": "Description",
    "init_date": "2021-01-10",
    "init_by": {
        "id": "27bb9e1acad247618fb2c3e016ae841c",
        "initials": "EE",
        "email": "example@gmail.com",
        "full_name": "Example Exampleton",
        "is_active": true
    }
}

This is the GET for the result of that added FinishedGood (which is what I want):

{
    "code": "FG034",
    "init_date": "2021-01-10",
    "description": "Description",
    "id": 3,
    "is_active": true,
    "init_by": {
        "email": "example@gmail.com",
        "id": "27bb9e1acad247618fb2c3e016ae841c",
        "is_active": true,
        "full_name": "Example Exampleton",
        "initials": "EE"
    },
    "product_name": "test test2"
}

Maybe I'm using this completely wrong but can anyone tell me:

1. Why is it trying to insert into the user table? All I want is the primary key to be loaded into init_by_id
2. Is there a way to post a payload with just the ID like I showed at the beginning?

Here are my classes and schemas:

class User(db.Model):
    id = db.Column(db.CHAR(32), primary_key=True, default=uuid.uuid4().hex)
    email = db.Column(db.String(255), primary_key=True)
    full_name = db.Column(db.String(255), nullable=False)
    initials = db.Column(db.String(3), unique=True, nullable=False)
    hashed_password = db.Column(db.Text, nullable=False)
    is_active = db.Column(db.Boolean, default=True)

class UserSchema(ma.SQLAlchemySchema):
    class Meta:
        model = User
        load_instance = True
        sqla_session = db.session

    id = ma.auto_field()
    email = ma.auto_field(required=False)
    full_name = ma.auto_field(required=False)
    initials = ma.auto_field(required=False)
    is_active = ma.auto_field(required=False)

class FinishedGood(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(255), unique=True, nullable=False)
    product_name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text)
    init_date = db.Column(db.Date, nullable=False)
    init_by_id = db.Column(db.CHAR(32), db.ForeignKey(User.id), nullable=False)
    init_by = db.relationship(User)
    is_active = db.Column(db.Boolean, default=True)

class FinishedGoodSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = FinishedGood
        load_instance = True
        sqla_session = db.session

    init_by = ma.Nested(UserSchema)

And finally my post method:

def post_finished_good():
    data = request.get_json(force=True)
    fg = FinishedGoodSchema().load(data)
    db.session.add(fg)
    db.session.commit()
    return FinishedGoodSchema().jsonify(fg), 201

Answer 1

I'm also new to Marshmallow so this probably isn't an optimal solution, but I managed to find a workaround using the @pre_load decorator:

@pre_load
def pre_load_process(self, data, **kwargs):
  id = data.get('id')
  if id is None:
    return data

  user = User.query.filter(User.id == id).first()
  if user is None:
    return data

  merged_data = UserSchema().dump(user)
  merged_data.update(data)

  return merged_data

Essentially, adding this method to your UserSchema class enables it to deserialize from id only as in your example payload. If other fields are included as well as id, they will overwrite the values pulled from the database.

If you need to prevent the User from being updated via the FinishedGood endpoint (the equivalent is true of my project) then FinishedGoodSchema will also need a @pre_load decorated method to strip everything but id from the nested init_by.

This doesn't seem like a particularly exotic use-case so I'm surprised how difficult it has been to find relevant examples online - all I found was your three-month old question with no answers... Like I said, I'm also a beginner at using this library and would appreciate some context from a Marshmallow expert. When something this ostensibly simple turns out to be a huge headache to implement, it makes me concerned that my whole approach is misconceived.

Answer 2

TLDR: Change the init_by field to init_by = autofield(). Then load the init_by member as: "init_by": "27bb9e1acad247618fb2c3e016ae841c". Create a separate schema for serialization with init_by = Nested(UserSchema) to generate the desired output.

class User(db.Model):
    # columns omitted for brevity

class UserSchema(ma.SQLAlchemySchema):
    # fields omitted for brevity

class FinishedGood(db.Model):
    init_by = db.relationship(User)
    # columns omitted for brevity

class FinishedGoodSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = FinishedGood
        load_instance = True
        sqla_session = db.session

        # default is False
        load_relationships = True

    # or
    init_by = autofield()

    # remove this: nested fields seem to apply only for serialization and for loading a new instance 
    # init_by = ma.Nested(UserSchema)

Payload:

{
    "code": "FG034",
    "product_name": "test test2",
    "description": "Description",
    "init_date": "2021-01-10",
    "init_by": "27bb9e1acad247618fb2c3e016ae841c"
}

I have not tested this but feel free to comment for clarification.