How can you add nested arrays and objects within json responses using flask and jsonify

Question

I'm currently following this example to get an API up and running (my code below)

from flask import Flask, request, jsonify
from flask_restful import Resource, Api
from sqlalchemy import create_engine
from json import dumps


import mysql.connector

db_connect = create_engine('mysql+pymysql://root:pass@localhost/news')

app = Flask(__name__)
api = Api(app)

class Events(Resource):
    def get(self):

        conn = db_connect.connect() # connect to database
        query = conn.execute("select id, title, description, date, scheduled,favourite,count_audio,count_video,count_articles from events;")
        result = [dict(zip(tuple (query.keys()) ,i)) for i in query.cursor]
        return jsonify(result)

api.add_resource(Events, '/events') # Route_1


if __name__ == '__main__':
     app.run(port='5002')

And I want to end up with a nested array, tags, and a nested object, location, as below

[{
    "description": "TEST DESCRIPTION",
    "id": 1,
    "title": "TEST TITLE",
    "date": "2020-09-02",
    "scheduled":"true",
    "favourite":"true",
    "tags": ["celebration", "national holiday"],
    "location": {
        "state": {
            "name": "new zealand",
            "affiliation": ["United Nations"]
        },
        "province": "",
        "urbanisation": "Wellington"
        }
},
{
    "description": "LONG DESCRIPTION",
    "id": 2,
    "title": "SECOND ENTRY",
    "date": "2020-09-03",
    "scheduled":"false",
    "favourite":"false",
    "tags": ["election", "national holiday"],
    "location": {
        "state": {
            "name": "Brazil",
            "affiliation": [""]
        },
        "province": "",
        "urbanisation": ""
        }
}]

Is something like this even possible? Do I need to rethink my API to have endpoints covering each nested object?

api.add_resource(Events, '/events')
api.add_resource(tags, '/events/tag')
api.add_resource(location, '/events/location')

Or, do I nest dictionaries as in this answer.

NMO · Accepted Answer · 2020-09-08 12:17:57Z

0

Yes its possible and totally valid. But I wouldn't query the database directly. I would use an ORM like SQLAlchemy, for flask use can use flask-sqlalchemy extension. The serialization can be achieved using flask-marshmallow

Use SQLAlchemy to create your models:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

class EventModel(db.Model):
    __tablename__ = "event"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=True)


class LocationModel(db.Model):
    __tablename__ = "location"

    id = db.Column(db.Integer, primary_key=True)
    province = db.Column(db.String, nullable=True)

    event_id = db.Column(db.Integer, db.ForeignKey("event.id", ondelete="CASCADE"), nullable=False)

    event = db.relationship("EventModel", backref=db.backref("locations", lazy=True))

Then you need serializer classes (employing flask-marshmallow) to handle the serialization:

from marshmallow_sqlalchemy import SQLAlchemyAutoSchema, fields
from marshmallow_sqlalchemy import auto_field
class LocationSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = LocationModel
        load_instance = True
        include_fk = False

    id = auto_field(load_only=True)

class EventSchema(SQLAlchemyAutoSchema):
    class Meta:
        model = EventModel
        load_instance = True
        include_fk = False

    id = auto_field(load_only=True)
    locations = fields.Nested(LocationSchema, many=True)

And finally, you can serialize to json as follows:

db.drop_all()
db.create_all()
event = EventModel(title="Summer Show")
db.session.add(event)
db.session.commit()
location1 = LocationModel(province="Upper Austria", event_id=event.id)
db.session.add(location1)
location2 = LocationModel(province="Tirol", event_id=event.id)
db.session.add(location2)
db.session.commit()


schema = EventSchema()
result = db.session.query(EventModel).all()
print(schema.dump(result, many=True))

A complete tutorial can be found here.

edited Sep 8, 2020 at 12:17

answered Sep 8, 2020 at 11:15

NMO

7968 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

TheMightyLlama Over a year ago

That's great. I'll give this a quick go and report back. Thank you.

TheMightyLlama Over a year ago

if I'm reading this correctly, the location table would have the event id as a foreign key. What if Events and Locations are Many to One? Does the foreign key site with the Event both in the table and the model with the relationship being expressed the other way round?

NMO Over a year ago

I made the code runnable now. Just copy it into a script and try it out with python >= 3.6.

NMO Over a year ago

The events and locations are many to one already: One event can have multiple locations. Therefore the foreign key is in the Location model. Additionally you need a relationship field to connect two models. See here for more details: docs.sqlalchemy.org/en/13/orm/basic_relationships.html

TheMightyLlama Over a year ago

That's very clear documentation, I'm keeping that. As to my model any single event can have only one location, and any single location can have many events.. How do I build the relationship for that?

|

Collectives™ on Stack Overflow

How can you add nested arrays and objects within json responses using flask and jsonify

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related