How to handle nested relationships in marshmallow-sqlalchemy

Question

I have a project where I'm trying to load data from JSON into a Sqlalchemy database using the marshmallow-sqlalchemy package. The model contains a one-to-many relationship with a child model.

Using the classic example of an author with many books:

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True)
    title = Column(String(50))

    author_id = Column(Integer, ForeignKey("author.id"), nullable=False)
    author = relationship("Author", backref=backref("books"))


class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, primary_key=True)
    name = Column(String(250))

    books = relationship("Author", back_populates="author")


class BookSchema(ModelSchema):
    class Meta:
        model = Book
        sqla_session = Session


class AuthorSchema(ModelSchema):
    class Meta:
        model = Author
        sqla_session = Session

    books = fields.Nested(BookSchema, many=True)

And the input JSON is

{
    "name": "Author A",
    "books": [
        {"title": "Book 1"},
        {"title": "Book 2"}
    ]
 }

When I try to load that JSON, using the following:

json_repr = {...}
author_schema = AuthorSchema()
obj_repr = author_schema.load(json_repr)

It is raising an exception when it trys to deserialize the document saying that Book is an unhashable type.

What is happening, I believe is that the deserializer is trying to create the objects using something like

obj = model(**data)

which won't work with a one-to-many relationship, since the instances of the list need to be append()'ed to the Author.books property.

I've been unable to find any examples of this working anywhere on the web, Every example I've seen seems to be nesting a single instance, not a list. Is there a recommended way of accomplishing this using marshmallow-sqlalchemy, or should I revert back to using the straight marshmallow package, and manually appending the relationship objects using @post_load methods.

score 12 · Accepted Answer · answered Apr 08 '19 at 09:38

I was having similar issues to this old post. Managed to fix this post under a slightly different framework Flask + SQLAlchemy + Marshmallow-SQLAlchemy (version 2). Posted code in case helpful.

Most of change are to models.py

Change of line books = relationship("Book", back_populates="author")
Used back_populates instead of backref as I was getting error sqlalchemy.exc.ArgumentError: Error creating backref 'books' on relationship 'Book.author': property of that name exists on mapper 'mapped class Author->author

models.py

class Book(db.Model):
    __tablename__ = "book"
    id = Column(db.Integer, primary_key=True)
    title = Column(db.String(50))

    author_id = Column(db.Integer, db.ForeignKey("author.id"), nullable=False)
    author = relationship("Author", back_populates="books")


class Author(db.Model):
    __tablename__ = "author"
    id = Column(db.Integer, primary_key=True)
    name = Column(db.String(250))

    books = relationship("Book", back_populates="author")

schemas.py - mostly same

class BookSchema(ModelSchema):
    class Meta:
        model = Book
        sqla_session = db.session


class AuthorSchema(ModelSchema):
    class Meta:
        model = Author
        sqla_session = db.session

    books = fields.Nested(BookSchema, many=True)

views.py

@api.route('/author/', methods=['POST'])
def new_author():
    schema = AuthorSchema()
    author = schema.load(request.get_json())
    db.session.add(author.data) # version 2 marshmallow
    db.session.commit()
    return jsonify({"success": True})

score 3 · Answer 2 · answered Aug 08 '18 at 19:15

It appears, that one way of handling this is to add an initializer on the sqlalchemy model, which explicitly appends to the collection

class Author(Model):
    __tablename__ = "author"

    def __init__(self, books=None, *args, **kwargs):
        super(Author, self).__init__(*args, **kwargs)
        books = books or []
        for book in books:
            self.books.append(book)

Still curious though if there is a better solution out there.

Did you find any other ways of doing it? – Rotkiv Dec 17 '19 at 04:17 — Rotkiv, Dec 17 '19 at 04:17

How to handle nested relationships in marshmallow-sqlalchemy

2 Answers2