Convert sql query result into JSON array in python

Question

The result I got from SQLite in Python looks like this:

{"John", "Alice"}, {"John", "Bob"}, {"Jogn", "Cook"} ......

I want to convert the result into JSON format like this:

{
    "Teacher": "John",
    "Students": ["Alice", "Bob", "Cook" .....]
}

I used GROUP_CONCAT to concat all the students' name and the following code:

row_headers = [x[0] for x in cursor.description] #this will extract row headers
result = []
for res in cursor.fetchall():
    result.append(dict(zip(row_headers, res)))

I was able to get this result:

{
    "Teacher": "John",
    "Students": "Alice, Bob, Cook" 
}

How can I make the students into array format?

Shawn · Accepted Answer · 2020-05-07 00:54:30Z

1

If your version of sqlite has the JSON1 extension enabled, it's easy to do in pure SQL:

SELECT json_object('Teacher', teacher,
                   'Students', json_group_array(student)) AS result
FROM ex
GROUP BY teacher;

DB Fiddle example

answered May 7, 2020 at 0:54

Shawn

53.8k3 gold badges29 silver badges74 bronze badges

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

Comments

puppydog · Accepted Answer · 2020-05-06 23:38:23Z

0

You could just do result["Students"] = result["Students"].split(", ").

answered May 6, 2020 at 23:38

puppydog

3854 silver badges14 bronze badges

1 Comment

EzyHoo Over a year ago

Nice! But what if the student's name contains ',' in it?

Collectives™ on Stack Overflow

Convert sql query result into JSON array in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related