Converting JSON string into Python dictionary

Question

I don't have much experience in Python and I've ran into problem converting sql query data which is technically a list containing a JSON string into a Python dictionary. I'm querying the sqlite3 database which is returning a piece of data like this:

def get_answer(self, id):
    self.__cur.execute('select answer from some_table where id= %s;' % id)
    res_data = self.__cur.fetchall()
    return res_data

The data is a single JSON format element which its simplified version looks like this:

[
 {"ind": [ {"v": 101}, {"v": 102}, {"v": 65 }]},
 {"ind": [ {"v": 33}, {"v": 102}, {"v": 65}]}
]

But when I try to convert the res_data to JSON, with code like this:

temp_json = simplejson.dumps(get_answer(trace_id))

it returns a string and when I get the len(temp_json) it returns the number of characters in res_data instead of the number of objects in res_data. However, if I use Visual Studio's JSON visualizer on what get_answer(trace_id) returns, it properly shows each of the objects res_data.

I also tried to convert the res_data to a dictionary with code like this:

dict_data = ast.literal_eval(Json_data)

or

dict_data = ast.literal_eval(Json_data[0])

and in both cases it throws a "malformed string" exception. I tried to write it to a file and read it back as a JSON but it didn't worked.

Before doing that I had the copy pasted the res_data manually and used:

with open(file_name) as json_file:
    Json_data = simplejson.load(json_file)

and it worked like a charm. I've been experimenting different ways stated in SO and elsewhere but although the problem seems very straight forward, I still haven't found a solution so your help is highly appreciated.

Alir Kahi · Accepted Answer · 2015-12-14 13:47:57Z

8

OK, I finally found the solution:

states = json.loads(temp_json[0][0])

one confusing issue was that states = json.loads(temp_json[0]) was throwing the "Expected string or buffer" exception and temp_json was a list containing only one element, so I didn't think I will get anything from temp_json[0][0].

I hope it helps others too!

answered Dec 14, 2015 at 13:47

Alir Kahi

1,3222 gold badges15 silver badges29 bronze badges

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

1 Comment

lucid_dreamer Over a year ago

What were the two ways?

Falk Schuetzenmeister · Accepted Answer · 2015-12-11 22:15:19Z

5

I think you are confusing the data formats. What you supposedly get back from your database wrapper seems to be a list of dictionaries (it is not SQL either - your question title is misleading). But I actually think that sqlite3 would give you a list of tuples.

JSON is a text format or more precisely the serialization of an object to a string. That's why json.dumps (= dump to string) results in a string and json.loads(= load string) would result in a python object, a dictionary or a list of dictionaries).

json.dumps does NOT mean "dump JSON to string". It is rather the .dumps method of the json module which takes a Python object (dict, list) and serializes it to JSON.

I will extend the answer if I understand what exactly you want to achieve but you get JSON with json.dumps(), JSON is a string format. In contrast simplejson.load() gives you a Python list or dict.

Did you try json.loads() just in case the return from your database is actually a string (which you could easily test).

edited Dec 11, 2015 at 22:15

answered Dec 11, 2015 at 18:59

Falk Schuetzenmeister

1,6072 gold badges18 silver badges37 bronze badges

5 Comments

Alir Kahi Over a year ago

simplejson,load() throws an exception "list has no attribute read". json.loads() throws an "expected string or buffer". When I use type(res_data) it says it's a list. But the length of the list is 1 and the length of that single element is the number of characters in that list.

Alir Kahi Over a year ago

I want to convert that res_data to a dictionary.

Alir Kahi Over a year ago

A list of tuples is sent back when querying multiple columns. In this case I'm just receiving a single cell in one column in one row which is a list that has one string element. So there is only a res_data[0] which is itself a string.

Alir Kahi Over a year ago

I might have described the issue imprecisely so please ask more questions if needed. I basically want the above described res_data in Python format so that I can go through it, find the elements I want and process the data. I still haven't tried the ORM but the data has already been stored and I have to process it so I can't do anything about the stored data.

Alir Kahi Over a year ago

The data is saved in the model in a Django web app with this statement: answer = models.TextField()

Collectives™ on Stack Overflow

Converting JSON string into Python dictionary

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related