How can I return a nested json using flask.jsonify by querying a database?

Question

Here is what my database table schema

Movie (id, movie_name, genre, time, channel)

I have a MySQL table and want to return a nested json to an API, grouping them by genre and channel.

For example:

[
    'comedy': {
        [{'id': 1, 'movie_name': 'm1', 'time': 18:00, 'channel': 'c1'},
         {'id': 1, 'movie_name': 'm2', 'time': 18:00, 'channel': 'c2'}]
    },
    'horror': {
        [{'id': 1, 'movie_name': 'm3', 'time': 18:00, 'channel': 'c1'},
         {'id': 1, 'movie_name': 'm4', 'time': 18:00, 'channel': 'c2'}]
    }
]

And similarly for each channels.

Update: I am not using SQL-Alchemy, all I am doing now is return jsonify(cursor.fetchall())

Moses N. Njenga · Accepted Answer · 2018-04-22 18:16:05Z

1

Assuming you are using SQLAlchemy ORM

comedies = []
horrors =[] #for lack of a better way to put it
all_movies = session.query(Movie)
for movie in all_movies:
   if movie.genre == 'comedy':
      comedy_entry ={'id':movie.id,
                      'movie_name':movie.movie_name,
                      'time':movie.time,
                      'channel': movie.channel }
      comedies.append(comedy_entry)
   if movie.genre == 'horror':
      horror_entry ={'id':movie.id,
                      'movie_name':movie.movie_name,
                      'time':movie.time,
                      'channel': movie.channel }
      horrors.append(horror_entry)

return jsonify({'comedy'= comedies, 'horror'= horrors})

I hope that guides you or at least gives you a starting point

edited Apr 22, 2018 at 18:16

answered Apr 22, 2018 at 17:48

Moses N. Njenga

7711 gold badge9 silver badges19 bronze badges

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

2 Comments

Sagun Shrestha Over a year ago

Thank you for your solution but can you suggest me an alternative solution without using SQL-Alchemy?

Moses N. Njenga Over a year ago

@SagunShrestha please see this answer on how you can connect to mysql database. Answer 3 looks best if you do not want to use SQLAlchemy. The results becomes what you iterate over (equivalent to all movie's above)

Joost · Accepted Answer · 2018-04-22 20:01:27Z

0

Here is another, more extendible approach:

columns_to_be_jsonified = ['id', 'movie_name', 'time']
genredict = {}

for movie in Movie.query.all():
    movietup = movie.genre, movie.channel
    moviedict = {}
    for column in columns_to_be_jsonified:
        moviedict[column] = getattr(movie, column)

    if movietup in genredict:
        genredict[movietup].append(moviedict)
    else:
        genredict[movietup] = [moviedict]

return jsonify(genredict)

It returns

{('horror', 'channel1'): [{'id': 1, 'movie_name': 'it', 'time': '18:00'}, {'id': 2, 'movie_name': 'the ring', 'time': '20:00'}],
 ('comedy', 'channel2'): [...]}

answered Apr 22, 2018 at 20:01

Joost

3,7492 gold badges15 silver badges30 bronze badges

1 Comment

Sagun Shrestha Over a year ago

Thank you for your solution but can you suggest me an alternative solution without using SQL-Alchemy?

Collectives™ on Stack Overflow

How can I return a nested json using flask.jsonify by querying a database?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest