Convert SQL into json in Python

Question

I need to pass an object that I can convert using $.parseJSON. The query looks like this:

cursor.execute("SELECT earnings, date FROM table")

What do I need to do from here in order to pass an HttpResponse object that can be converted into json?

score 14 · Accepted Answer · answered May 31 '12 at 19:00

14

Well, if you simply do:

json_string = json.dumps(cursor.fetchall())

you'll get an array of arrays...

[["earning1", "date1"], ["earning2", "date2"], ...]

Another way would be to use:

json_string = json.dumps(dict(cursor.fetchall()))

That will give you a json object with earnings as indexes...

{"earning1": "date1", "earning2": "date2", ...}

If that's not what you want, then you need to specify how you want your result to look...

answered May 31 '12 at 19:00

mata

67,110
10
163
162

Using, the dict() function gave an error – Nayan Jun 02 '23 at 13:08
@Nayan Not sure how that relates to the original question, but I guess you're not passing a list of pair-like items to it? – mata Jun 26 '23 at 18:43

score 0 · Answer 2 · answered May 31 '12 at 19:00

Have you investigated the json library?

The following will serialize your data base output into json. I'm not sure what you data looks like, or what you need the json to look like, but if you just keep in mind that python lists -> arrays, and python dicts -> objects, I think you'll be alright.

import json
json.dumps(data)

Convert SQL into json in Python

2 Answers2

Linked