Flask-marshmallow dump of flask-sqlalchemy outer join returns empty

Question

I have two tables, PackMatData and ColorData:

class PackMatData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    unique_name = db.Column(db.String(20), index=True, unique=True)
    se_name = db.Column(db.String(20), index=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    labflex_name = db.Column(db.String(20))
    country = db.Column(db.String(20), index=True)
    color_measurements = db.relationship('ColorData', backref='entry', cascade="all, delete-orphan", lazy='dynamic')


class ColorData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(20), index=True)
    patch = db.Column(db.String(10), index=True)
    L = db.Column(db.String(10), index=True)
    a = db.Column(db.String(10), index=True)
    b = db.Column(db.String(10), index=True)
    pack_mat_data_id = db.Column(db.Integer, db.ForeignKey('pack_mat_data.id'))

I would like to join these tables, and then serialize the returned value. I'm using flask-sqlalchemy left outer join to perform a join on the id of PackMatData table:

@app.route('/api/entries')
def entries():

    pmcd = db.session.query(PackMatData, ColorData).outerjoin(ColorData, PackMatData.id == ColorData.pack_mat_data_id).all()
    data = [{"packmatdata": x[0], "colordata": x[1]} for x in pmcd]
    output = NestedSchema(many=True).dump(data)
    return jsonify(output)

From the join, I get the data I'm expecting in the form of a list of tuples:

[...,(<PackMatData 138>, <ColorData 7272>), (<PackMatData 138>, <ColorData 7285>),(<PackMatData 138>, <ColorData 7286>), (<PackMatData 138>, <ColorData 7287>), (<PackMatData 138>, <ColorData 7284>), (<PackMatData 138>, <ColorData 7283>), (<PackMatData 139>, <ColorData 7321>), (<PackMatData 139>, <ColorData 7322>), (<PackMatData 139>, <ColorData 7323>), (<PackMatData 139>, <ColorData 7320>), (<PackMatData 139>, <ColorData 7319>), (<PackMatData 139>, <ColorData 7311>), ...]

I read in stackoverflow flask-marshmallow two db objects in one schema that marshmallow expects a list of dicts, which is why I'm making that transformation before passing it to dump.

Here are the schemas I defined for serialization:

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class NestedSchema(ma.ModelSchema):  
    colordata = ma.Nested(ColorDataSchema)
    packmatdata = ma.Nested(PackMatDataSchema, many=True)

The issue is that the dump function is always returning the following:

[...,{}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {},...]

I also tried with this schema (with the same results):

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    packmatdata = ma.Nested(PackMatDataSchema, many=True)
    class Meta:
        model = ColorData

Can you please lead me in the right direction? Sorry for the long post, but I wanted to provide you as much of the details as I could.

Did you ever find a solution to this? I have already killed a lot of time with the same problem :( — Greg Holst, Jun 09 '21 at 11:36
No, unfortunately I never found a solution. I ended up reworking it completely — AureliusEpictetus, Jun 15 '21 at 09:07
In the meanwhile I found out that it works if you avoid the Tuples (a result of joining tables), see my answer here: https://stackoverflow.com/questions/67213618/flask-sqlalchmey-marshmallow-nested-schema-fails-for-joins-with-filter-where/67937228#67937228 I am still not sure, if it is the best solution, because it seems not very fast (just my impression). How did you work around? — Greg Holst, Jun 16 '21 at 10:26

score 1 · Answer 1 · answered Sep 08 '21 at 20:44

Marshmallow dump(many=True) function handles a list of items. It can not handle a list of tuples of items. The item can be an object or a dict.

L1 = [item1, item2, item3]
schema.dump(L1, many=True)
# returns [data1, data2, data3]

Here you have an item linked to another in your database.

PackMatData table: | id | unique_name | | -- | ------| | 1 | name1 | | 2 | name2 |

ColorData table: | id | color | pack_mat_data_id | | -- | ------| ---------------- | | 1 | color1 | 1 | | 2 | color2 | 1 | | 3 | color3 | 2 |

You need to specify to Marshmallow what do with it.

#output type 1 : dumping PackMatData items
[
  { 
    id: 1,
    unique_name: name1, 
    color_measurements: [{id: 1, color: color1}, {id: 2, color: color2}],
  },
  {
    id: 2,
    unique_name: name2, 
    color_measurements: [{id: 3, color: color3}],
  }
]

#output type 2 : dumping ColorData items
[
  { 
    id: 1,
    color: color1, 
    entries: {id: 1, unique_name: name1},
  },
  {
    id: 2,
    color: color2, 
    entry: {id: 1, unique_name: name1},
  },
  { 
    id: 3,
    color: color3, 
    entry: {id: 2, unique_name: name2},
  },
]

#output type 3: dumping PackMatData and ColorData "side by side"
[
  { 
    packMetaData: {id:1, unique_name: name1},
    colorData: {id: 1, color: color1}
  },
  {
    packMetaData: {id:1, unique_name: name1},
    colorData: {id: 2, color: color2}
  },
  { 
    packMetaData: {id:2, unique_name: name2},
    colorData: {id: 3, color: color3}
  },
]

To get output type 1

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    fields = ('id', 'color')  

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData
    color_measurements = fields.Nested(ColorDataSchema) #the name is important

query = db.session.query(PackMatData).all()
data = PackMatDataSchema().dump(query, many=True)

To get output type 2

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    entry = fields.Nested(PackMatDataSchema) #the name is important 

query = db.session.query(ColorData).all()
data = ColorDataSchema().dump(query, many=True)

And to get output type 3

class PackMatDataSchema(ma.ModelSchema):  
    class Meta:
        model = PackMatData

class ColorDataSchema(ma.ModelSchema):
    class Meta:
        model = ColorData
    fields = ('id', 'color')

class SideBySideSchema(ma.ModelSchema):
    packMetaData = fields.Nested(PackMatDataSchema)
    colorData = fields.Nested(ColorDataSchema)

query = db.session.query(PackMatData, ColorData).join(ColorData).all()
list_of_dict = [{packMetaData: p, colorData: c} for p, c in query]
data = SideBySideSchema().dump(list_of_dict, many=True)

Zachary Chiodini · Answer 2 · 2021-07-15T17:24:24.790

This worked for me using flask-sqlalchemy, flask-marshmallow and marshmallow-sqlalchemy.

Perhaps, you need to include the table names into your flask SQL Alchemy database models.

class PackMatData(db.Model):
    __tablename__ = 'packmatdata'
    id = db.Column(db.Integer, primary_key=True)
    unique_name = db.Column(db.String(20), index=True, unique=True)
    se_name = db.Column(db.String(20), index=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    labflex_name = db.Column(db.String(20))
    country = db.Column(db.String(20), index=True)
    color_measurements = db.relationship('ColorData', backref='entry', cascade="all, delete-orphan", lazy='dynamic')


class ColorData(db.Model):
    __tablename__ = 'colordata'
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(20), index=True)
    patch = db.Column(db.String(10), index=True)
    L = db.Column(db.String(10), index=True)
    a = db.Column(db.String(10), index=True)
    b = db.Column(db.String(10), index=True)
    pack_mat_data_id = db.Column(db.Integer, db.ForeignKey('pack_mat_data.id'))

I included some extra stuff in the schema classes. This may not be necessary for you.

class PackMatDataSchema(ma.SQLAlchemyAutoSchema):  
    class Meta:
        model = PackMatData
        load_instance = True

class ColorDataSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = ColorData
        load_instance = True
        include_fk = True

To deserialize the SQL Alchemy query, I used a dynamic schema. (A dynamic schema was necessary for my case.)

@app.route('/api/entries')
def entries():
    # SQL Alchemy automatically joins on foreign keys.
    pmcd = db.session.query(PackMatData, ColorData)\
        .outerjoin(ColorData).all()
    data = [
        {PackMatData.__tablename__: x[0],
         ColorData.__tablename__: x[1]} 
        for x in pmcd
        ]
    DynamicSchema = ma.Schema.from_dict({
        PackMatData.__tablename__: 
            ma.Nested(PackMatDataSchema, dump_only=True),
        ColorData.__tablename__: 
            ma.Nested(ColorDataSchema, dump_only=True)
        })
    dynamic_schema = DynamicSchema(many=True)
    return jsonify(dynamic_schema.dump(data))

This should work. You can try creating the "NestedSchema" instead of using a dynamic schema. As previously stated, I need to use a dynamic schema for my own purposes. I do not think it's necessary for your purpose.

Flask-marshmallow dump of flask-sqlalchemy outer join returns empty

2 Answers2