Flask restful json formating

Question

I have a table is Database similar to below data. I am trying to create a REST API using Flask-Restful.

I am using flask-restful and flask-sqlalchemy with the below api.

/project/1

    @classmethod
    def find_by_project_no(cls,proj_no):
       cls.query.filter_by(project_no = proj_no).all()

Result is [<automap.bla.bla>,<>,<>...]

I need the result converted into.

{
    'project': [{
            'country': 'USA',
            'category': [{
                    'Beverages': [{
                            'product': 'Milk',
                            'count': 2
                        }, {
                            'product': 'Juice',
                            'count': 5
                        }
                    ]
                }, {
                    'Snacks': [{
                            'product': 'Potato Chips',
                            'count': 2
                        }
                    ]
                }, {
                    'Oils': [{
                            'product': 'Canola',
                            'count': 5
                        }, {
                            'product': 'Olive',
                            'count': 8
                        }
                    ]
                }
            ]

        }, {
            'country': 'CAN',
            'category': [{
                    'Beverages': [{
                            'product': 'Milk',
                            'count': 7
                        }, {
                            'product': 'Juice',
                            'count': 8
                        }
                    ]
                }, {
                    'Snacks': [{
                            'product': 'Potato Chips',
                            'count': 8
                        }
                    ]
                }, {
                    'Oils': [{
                            'product': 'Canola',
                            'count': 3
                        }, {
                            'product': 'Olive',
                            'count': 4
                        }
                    ]
                }
            ]
        }

    ]
}

How can I get the desired json format? Any help is appreciated.

@roganjosh someone might know what the return value is for sqlalchemy query. If you don't know that is fine. You expect me to upload the whole project and Database? — Keerikkattu Chellappan, Jan 21 '20 at 20:16
Nobody can know what the return value is for an SQLA query when they can't see the models. You've given a screenshot of a dataframe (which isn't what you're using and is missing fields). I didn't suggest the entire db, I suggested a [mcve] — roganjosh, Jan 21 '20 at 20:23

jlucier · Answer 1 · 2020-01-21T20:28:05.563

I take it you're asking how to turn your Flask-SQLAlchemy models into JSON to send as a response to a request. I'll elaborate on that:

I usually implement a class that all my models inherit from that supplies the necessary base functionality. Here's an example (note, there are a million ways you can do this, this is just something quick and flexible I use):

from flask.json import JSONEncoder
from sqlalchemy.orm.query import Query

DEFAULT_IGNORE_ATTRS = {'base_obj_members', 'metadata'}.union(dir(type('dummy', (object,), {})))


class CustomEncoder(JSONEncoder):
    def default(self, obj):
        if isinstance(obj, BaseModel):
            return obj.to_json()
        elif isinstance(obj, Query):
            return obj.all()
        return super(CustomEncoder, self).default(obj)

class BaseModel():
    def to_json(self, ignore_keys=list()):
        ignore_keys = DEFAULT_IGNORE_ATTRS.union(ignore_keys)
        obj = dict()
        for k in filter(lambda k: k not in ignore_keys and k[0] != '_', dir(self)):
            v = getattr(self, k)
            if not (isinstance(v, Query) or callable(v)):
                obj[k] = v

        return obj

app.json_encoder = CustomEncoder

And here's how to use it:

class SomeModel(db.Model, BaseModel):
    name = db.String()

    @property
    def some_attr_that_is_not_a_col(self):
        return 'heyooo'

What this BaseModel will do for you is provide all your models with an function to_json that will essentially just turn the object into a dictionary that you can then respond with from Flask. So in your example, you can call to_json on every item in that list to turn them all into a JSON object.

This is nice because it will get the values of each column of the table, also get any properties you define, ignore functions and "hidden" attributes (i.e. things that have a leading "_"), and allow you to add custom attributes to be ignored.

This approach is great for getting started, but is pretty inefficient and will slow you down if your API is servicing a lot of requests. So for anyone looking for faster ways of serializing model objects, I can't recommend marshmallow enough.

Flask restful json formating

1 Answers1