I have a flask-sqlalchemy table composed of a relationship with 4 other tables as follows:
office_cardgroups = db.Table('office_cardgroups',
db.Column('officedata_id', db.Integer, db.ForeignKey('officedata.id')),
db.Column('cardgroups_id', db.Integer, db.ForeignKey('cardgroups.id'))
)
class OfficeData(BaseModel):
__tablename__ = 'officedata'
id = db.Column(db.Integer, primary_key=True)
department = db.relationship('Department', lazy=True)
cardgroups = db.relationship('Cardgroup', secondary=office_cardgroups, lazy=True)
doorgroup = db.relationship('Doorgroup', lazy=True)
office = db.relationship('Office', lazy=True)
office_id = db.Column(db.Integer, db.ForeignKey('offices.id'))
department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))
doorgroup_id = db.Column(db.Integer, db.ForeignKey('doorgroups.id'))
I have a Schema
for this model as follows:
from app.globals import marshmallow
from marshmallow_sqlalchemy import fields
from app.models.office_data import OfficeData
from app.schemas.cardgroup import CardgroupSchema
from app.schemas.doorgroup import DoorgroupSchema
from app.schemas.office import OfficeSchema
from app.schemas.department import DepartmentSchema
class OfficeDataSchema(marshmallow.SQLAlchemyAutoSchema):
class Meta:
model = OfficeData
cardgroups = fields.Nested(CardgroupSchema)
doorgroup = fields.Nested(DoorgroupSchema)
office = fields.Nested(OfficeSchema)
department = fields.Nested(DepartmentSchema)
include_relationships = True
load_instance = True
However, when I dump
the model, I only get the IDs of the nested tables not the actual data inside these tables.
Output:
office_data_schema = OfficeDataSchema(many=True)
user_office_data = office_data_schema.dump(user_office_data)
app.logger.info(user_office_data)
[{'id': 68, 'office': 1, 'department': 32, 'cardgroups': [2], 'doorgroup': None}, {'id': 69, 'office': 1, 'department': 33, 'cardgroups': [2], 'doorgroup': None}, {'id': 70, 'office': 1, 'department': 34, 'cardgroups': [2], 'doorgroup': None}, {'id': 71, 'office': 1, 'department': 35, 'cardgroups': [2], 'doorgroup': None}]
An example for one of the relationship tables and how they should look like is Table Office:
class Office(BaseModel):
__tablename__ = 'offices'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, nullable=False)
shortname = db.Column(db.String, nullable=False)
In Flask-Admin
, the the OfficeData
model is actually displayed in the right manner not IDs so I think its a problem with the Schema definition.
I just ran into this too thinking that include_relationships = True
would automatically populate the nested fields but also had to define the relationships in the Schema (as you've done with OfficeDataSchema
) but I think the nested fields need to be part of the OfficeDataSchema itself rather than Meta (one indentation level back), e.g.
class OfficeDataSchema(marshmallow.SQLAlchemyAutoSchema):
class Meta:
model = OfficeData
include_relationships = True
load_instance = True
cardgroups = fields.Nested(CardgroupSchema)
doorgroup = fields.Nested(DoorgroupSchema)
office = fields.Nested(OfficeSchema)
department = fields.Nested(DepartmentSchema)