Convert MySQL queries in Flask that return multiple rows into a list for a JSON return

Mook 0
Viewed 11

How can I return multiple rows of a MySQL query in a Flask application as JSON? I looked around at other questions for around an hour but couldn't get any of the proposed solutions for similar problems to work. I have confirmed it is returning the correct number of objects and do not have a problem returning single rows with jsonify().

Here is the table:

And the current attempt at getting the rows to display as a list in JSON:

request = requests.query.filter(requests.caregiverID == incoming['caregiverID']).all()
    
json_object = json.dumps(request)

return json_object, 200

The above code returns TypeError: Object of type requests is not JSON serializable

flask-sqlalchemy
1 Answers

So using this post: Dict for MySQL rows

I was able to implement this and it worked exactly the way I wanted it to.

request = []
    
    for row in requests.query.filter(requests.caregiveeID == incoming['caregiveeID']).all():
        dictRet = dict(row.__dict__)
        dictRet.pop('_sa_instance_state', None)
        request.append(dictRet)
    print(request)

    return jsonify({"requests": request}), 200
Mook0
Related