how to create a json object from tree data structure in database?

Question

I'm using flask with the following model:

class NewsCategory(db.Model):
    __tablename__ = 'news_category'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64))
    parent_id = db.Column(db.Integer, db.ForeignKey('news_category.id'))
    children = db.relationship("NewsCategory")

And I want to create a json object from this model for use in navigation menu.

I would like to parse it recursively and build a hierarchical JSON object that looks something like this:

tree = [{"title": "Node 1", "id": "1"},
         {"title": "Folder 2", "id": "2", "folder": "true", "children": [
            {"title": "Node 2.1", "id": "3"},
            {"title": "Node 2.2", "id": "4"}
          ]}
        ]

Please include what you have tried. Does it work?

user1907906
– user1907906

2015-05-21 07:40:07 +00:00
Commented May 21, 2015 at 7:40 — user1907906
– user1907906, Commented May 21, 2015 at 7:40

Nick Woodhams · Accepted Answer · 2015-05-21 08:10:30Z

3

I use a library called Flask-Restless for querying the database and returning json. It's made to work with SQLAlchemy.

If you're not looking to integrate with something like this, you can subclass your SQLAlchemy model and just run to_json() method on it.

class NewsCategory(db.Model, JsonSerializer)

class JsonSerializer(object):
    """A mixin that can be used to mark a SQLAlchemy model class which
    implements a :func:`to_json` method. The :func:`to_json` method is used
    in conjuction with the custom :class:`JSONEncoder` class. By default this
    mixin will assume all properties of the SQLAlchemy model are to be visible
    in the JSON output. Extend this class to customize which properties are
    public, hidden or modified before being being passed to the JSON serializer.
    """

    __json_public__ = None
    __json_hidden__ = None
    __json_modifiers__ = None

    def get_field_names(self):
        for p in self.__mapper__.iterate_properties:
            yield p.key

    def to_json(self):
        field_names = self.get_field_names()

        public = self.__json_public__ or field_names
        hidden = self.__json_hidden__ or []
        modifiers = self.__json_modifiers__ or dict()

        rv = dict()
        for key in public:
            rv[key] = getattr(self, key)
        for key, modifier in modifiers.items():
            value = getattr(self, key)
            rv[key] = modifier(value, self)
        for key in hidden:
            rv.pop(key, None)
        return rv

Credit: Github Overholt project (author of Flask-Security)

answered May 21, 2015 at 8:10

Nick Woodhams

12.8k11 gold badges56 silver badges52 bronze badges

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

3 Comments

horcle_buzz Over a year ago

I have the same use case as above, so I tried this as NewsCategory().to_json(), but got an empty dictionary back, as in {'title': None, 'id': None, 'parent_id': None, 'children': []}... I have verified that data are in the underlying table too. I have a feeling my call to the to_json() method is not right.

Nick Woodhams Over a year ago

@horcle_buzz This method is meant to be used on an existing object-- are you attempting to use it on an empty object? news_cat = NewsCategory.query.filter_by(name='Technology').first() news_cat_json = news_cat.to_json() Hope this helps clarify.

horcle_buzz Over a year ago

Hi. I actually solved my problem using the nesting scheme available in the Marshmallow library. Very simple to implement. And yes, the object does exist as an SQLAlchemy object.

Collectives™ on Stack Overflow

how to create a json object from tree data structure in database?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related