How to Serialize SQL data after a Join using Marshmallow? (flask extension)

Question

I have 2 tables in SQL:

class Zoo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nome = db.Column(db.String(80), unique=True, nullable=False)
    idade = db.Column(db.Integer, unique=False, nullable=False)
    peso = db.Column(db.Float, unique=False, nullable=False)
    cuidador = db.Column(db.Integer, db.ForeignKey('cuidador.id'))

class Cuidador(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nome = db.Column(db.String(80), unique=True, nullable=False)
    animais = db.relationship('Zoo', backref='zoo', lazy=True)

And the Schemas defined:

class WorkerSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Cuidador

    id = ma.auto_field()
    nome = ma.auto_field()

class ZooSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Zoo

    id = ma.auto_field()
    nome = ma.auto_field()
    idade = ma.auto_field()
    peso = ma.auto_field()
    cuidador = ma.Nested(WorkerSchema)

In a visualization route, I defined the following function to visualize the data present in the Zoo table:

def see_all_animals(self):
    result_join = db.session.query(Zoo,Cuidador).join(Zoo).all()
    zoo_schema = ZooSchema()
    result = zoo_schema.dump(result_join,many=True)
    return result

Unfortunately the function returns completely empty data. I would like something to appear along these lines:

{
...."id": 3,
...."idade": 5,
...."nome": "Cabra",
...."peso": 12.0,
...."cuidador": {"id":1,"nome":"Juan"}
}

Detlef · Accepted Answer · 2022-05-30T20:16:44.500

The back reference of the database relationship used in your example adds a zoo attribute to objects of the Zoo type, under which the referenced Cuidador object can be queried.
For the currently used database modeling, I recommend the following marshmallow schemas. This involves renaming the back reference defined by the database relationship from "zoo" to "cuidador".

class WorkerSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Cuidador

class ZooSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Zoo
    cuidador = ma.Nested(WorkerSchema, attribute='zoo')

It is not necessary to actively use a join statement to achieve the desired output. Due to the defined relationship and the nested schema, the query and formatting of the referenced data takes place automatically.

def index():
    zoos = Zoo.query.all()
    zoos_schema = ZooSchema(many=True)
    zoos_data = zoos_schema.dump(zoos)
    return jsonify(data=zoos_data)

The output obtained here is now the following.

{
  "data": [
    {
      "cuidador": {
        "id": 1, 
        "nome": "Juan"
      }, 
      "id": 1, 
      "idade": 5, 
      "nome": "Cabra", 
      "peso": 12.0
    }
  ]
}

I advise you to pay more attention to naming when modeling to avoid unnecessary renaming and to deepen your knowledge of database relationships. The correct representation of relationships and the avoidance of duplicates in naming will help you with further and larger projects, in which I wish you a lot of fun.

How to Serialize SQL data after a Join using Marshmallow? (flask extension)

1 Answers1

Linked