I'm working with an existing mssql database, where I'm not able to make any changes. Trying to make an API using Flask and Marshmallow. I have some issues deserializing the following query returning all people working on a project.
query = (
sa.session.query(Employee, sa.func.sum(JobEntry.number_registered).label("total"))
.join(JobEntry, Employee.employee_hashkey==JobEntry.employee_hashkey)
.filter(JobEntry.project_number==f'{project_number}')
.group_by(Employee)
).limit(3).all()
The query returns
print(query)
[(<Employee 188ED6A858997A48FDA53A404779A16F>, 229.0), (<Employee 1D40AB9C2A973C2BD33B1EF6108D2A70>, 2.0), (<Employee 38584E42E883131DC35151E4922A8094>, 176.75)]
The Employee contains the name, id, etc. How would I create a marshmallow schema returning, the following example.
[
{"name": "somename a", "total" 229.0, "id": 11},
{"name": "somename b", "total" 2.0, "id": 22},
{"name": "somename c", "total" 176.75, "id": 33}
]
Being a noob, I have experimented a bit... The following code returns almost what I want. But I get "Employee." in my keys ...
class ProjectUsersSchema(Schema):
class Meta:
model = Employee
fields = (
"Employee.name",
"Employee.id"
"total"
)
# returns "[{"Employee.name": "somename a", "total" 229.0, "Employee.id": 11}, ..."
Made a temporary f#ckly fix using nested schemas by including .split(".")[-1] into my snake_case to PascalCase schema
def pascalcase(s):
part_lst = s.split(".")[-1].split("_") # todo fix split(".")[-1]
if len(s) <= 2:
return "".join(i.upper() for i in part_lst)
else:
return "".join(i.title() for i in part_lst)
class PascalCaseSchema(ma.SQLAlchemyAutoSchema):
def on_bind_field(self, field_name, field_obj):
field_obj.data_key = pascalcase(field_obj.data_key or field_name)`