How to create a json tree structure from SQLalchemy model

Question

I'm trying to create a webservice with Flask, my webservice needs to return a json encoded tree structure of all activities belonging to a profile.

My models:

class Activity(db.Model):
    __tablename__ = 'activity'
    id = db.Column(db.Integer, primary_key=True)
    profile_id = db.Column(db.String, db.ForeignKey('profile.id'), nullable=False, index=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('activity.id'), index=True)
    name = db.Column(db.String)

    parent = db.relationship('Activiteit', remote_side=[id], backref='children')

class Profile(db.Model):
    __tablename__ = 'profile'
    id = db.Column(db.Integer, primary_key=True)
    profile_name = db.Column(db.String(64))
    active = db.Column(db.Boolean, nullable=False)

    activities = db.relationship('Activity', backref="profile", lazy='dynamic')
    employees = db.relationship('Employee', backref="profile", lazy='dynamic')

I need the following structure:

Main activity 1
     Sub activity 1
     Sub activity 2
         Subsub activity 1
Main activity 2
etc. etc.

I tried creating nested dicts and lists of dicts, but everytime I get stuck. Level 3 elements that end up as Level 1 elements or a tree that only returns 2 levels.

After some searching I found that I need to create a Queue with Nodes that carry a name and a list of children and then add the Nodes to the tree.

I created the Queue with the following function:

def get(self, profile_id):
    profile = models.Profile.query.get(profile_id)
    queue = {}
    for activity in profile.activities:
        queue[activity.name]= [c.name for c in activity.children]

Now I have no clue how to proceed or if i'm even on the right way. Any help will be much appreciated

Edit

Based on Codegeek's answer I create the following:

def get(self, profile_id):
    activities = models.Activity.query.filter_by(profile_id=profile_id).all()
    tree = {}
    for level1 in activities:
        if level1.parent_id is None:
            tree[level1.name] = {'id': level1.id}
            for level2 in level1.children:
                tree[level1.name][level2.name] = {'id': level2.id}
                for level3 in level2.children:
                    tree[level1.name][level2.name][level3.name] = {'id': level3.id}
    return tree

Well i can't the structure right, so haven't tried the conversion to JSON yet, but i believe the flask-restful module has a builtin .jsonify() function — Jeffrey Slort
– Jeffrey Slort, Commented Mar 20, 2014 at 15:29

codegeek · Accepted Answer · 2014-03-20 15:36:31Z

1

Have you tried using flask.jsonify() ?

jsonify() takes dictionary as argument. You can try creating a nested dictionary. In your case, it is 3 levels and could look like:

d= { 'Activity': { 'Subactivity': { 'Subsubactivity': 'value }}}

You have not shared your code where you create the dictionary but you can do something like below (I am using generic names of course)

d = {}
for activity in Activities:
    for subactivity in activity:
        for subsubactivity in subactivity:
            d['Activity']['Subactivity']['subsubactivity'] = value

Then you can reuturn the JSON as:

return flask.jsonify(**d)

answered Mar 20, 2014 at 15:36

codegeek

33.5k12 gold badges65 silver badges65 bronze badges

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

3 Comments

Jeffrey Slort Over a year ago

Thanks for your input, but my problem is I don't know how to create the dictionary. I have a list with activity objects and want to structure that to a nested dictionary.

codegeek Over a year ago

ok post more code then and may be someone can take a look at how your list with activity objects is created ?

user956424 Over a year ago

@codegeek your solution will work for only 3 levels. Can you suggest the solution to traverse a dynamic tree structure where the number of levels is not same in any branch?

Collectives™ on Stack Overflow

How to create a json tree structure from SQLalchemy model

Edit

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related