json.loads() does not work with data retrieved from database but works if stored in variable-CodePudding

Im running this code and getting below error

df = pd.read_sql(f"select id, jsonresponse from ResponseDetails;", engine)
all_df = df[['id', 'jsonresponse']].values.tolist()

for x in all_df:
   jsn1 = x[1]
   print(jsn1)
   print(json.loads(jsn1))

Output:
>
{\"request_id\":\"2312\",\"task_id\":\"423432\",\"group_id\":\"43r23\",\"success\":true,\"response_code\":\"100\",\"response_message\":\"Valid Authentication\"}

---------------------------------------------------------------------------
JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)

To produce above error i have stored above json string into variable. But this time it works perfectly.

jsn2 = '{\"request_id\":\"2312\",\"task_id\":\"423432\",\"group_id\":\"43r23\",\"success\":true,\"response_code\":\"100\",\"response_message\":\"Valid Authentication\"}'

print(json.loads(jsn2))

Output:
>  
{'request_id': '2312',
 'task_id': '423432',
 'group_id': '43r23',
 'success': True,
 'response_code': '100',
 'response_message': 'Valid Authentication'}

How come jsn2 is different from jsn1. And how can I json.load() jsn1 variable.

EDIT: tried below code still

for x in all_df:
    jsn1 = x[1]
    dmp = json.dumps(jsn1)
    print(dmp)
    print(json.loads(dmp))

Output:

"{\\\"request_id\\\":\\\"7a4974bb-8b43-4ff0-bc7c-8a0923aef03d\\\",\\\"task_id\\\":\\\"ce57782d-a56e-4be7-a803-18dcd71588a2\\\",\\\"group_id\\\":\\\"268eba73-fe5a-4cd2-a80e-11fc2d06f127\\\",\\\"success\\\":true,\\\"response_code\\\":\\\"100\\\",\\\"response_message\\\":\\\"Valid Authentication\\\"}"

{\"request_id\":\"7a4974bb-8b43-4ff0-bc7c-8a0923aef03d\",\"task_id\":\"ce57782d-a56e-4be7-a803-18dcd71588a2\",\"group_id\":\"268eba73-fe5a-4cd2-a80e-11fc2d06f127\",\"success\":true,\"response_code\":\"100\",\"response_message\":\"Valid Authentication\"}

CodePudding user response：

Converting Python object into Json string use json.dumps() and to convert Json string into Python object use json.loads()

so your code will look like this

for x in all_df:
   jsn1 = json.dumps(x[1])
   print(jsn1)
   print(json.loads(jsn1))

Update

Your string is not escaped proprly so try like this

for x in all_df:
   jsn1 = json.dumps(x[1])
   jsn1 = bytes(jsn1, "utf-8").decode("unicode_escape")
   print(jsn1)
   print(json.loads(jsn1))

CodePudding user response：

As @craigb suggested. Below changes worked for me

for x in all_df:
    jsn1 = x[1].replace('\\"','"')
    print(json.loads(jsn1))