从snowflake到python获取json数据

Question

我能得到数据，但不是我想要的格式。

当在python中运行时:python中的查询我得到:python中的输出

我怎样才能把它变成这种格式?:(这是我在snowflake中运行查询时得到的格式):雪花1中的输出雪花2中的输出我最终希望将格式化的结果写入.json文件中。目前，当我写入json文件时，我得到相同的错误格式。

任何指导都非常感谢。

Answer 1

输出是正确的，"被解释为换行符:'

>>> result = '''[('{n "extras": [n "ext warranty", n "paint protection" n ], n "make": "Honda", n "model": "Civic", n "price": "20275", n "year": "2017"n}',), ('{n "extras" : [n "ext warranty", n "rust proofing", n "fabric protection" n ], n "make": "Toyota", n "model": "Camry", n "price": "23500", n "year": "2017" n}',)]'''
>>> result
'[('{n "extras": [n "ext warranty", n "paint protection" n ], n "make": "Honda", n "model": "Civic", n "price": "20275", n "year": "2017"n}',), ('{n "extras" : [n "ext warranty", n "rust proofing", n "fabric protection" n ], n "make": "Toyota", n "model": "Camry", n "price": "23500", n "year": "2017" n}',)]'
>>> print(result)
[('{
"extras": [
"ext warranty", 
"paint protection" 
], 
"make": "Honda", 
"model": "Civic", 
"price": "20275", 
"year": "2017"
}',), ('{
"extras" : [
"ext warranty", 
"rust proofing", 
"fabric protection" 
], 
"make": "Toyota", 
"model": "Camry", 
"price": "23500", 
"year": "2017" 
}',)]

你可能想要格式化字符串，因为json对象不应该用单引号括起来，查询的行结果不应该用圆括号括起来:

>>> parsed_result = result.replace("'","").replace("(","").replace(",)","")
>>> print(parsed_result)
[{
"extras": [
"ext warranty", 
"paint protection" 
], 
"make": "Honda", 
"model": "Civic", 
"price": "20275", 
"year": "2017"
}, {
"extras" : [
"ext warranty", 
"rust proofing", 
"fabric protection" 
], 
"make": "Toyota", 
"model": "Camry", 
"price": "23500", 
"year": "2017" 
}]

你也可以通过使用python内置json库将其转换为字典来检查这是一个有效的json字符串:'

>>> import json
>>> dict_result = json.loads(parsed_result)
>>> dict_result
[{'extras': ['ext warranty', 'paint protection'], 'make': 'Honda', 'model': 'Civic', 'price': '20275', 'year': '2017'}, {'extras': ['ext warranty', 'rust proofing', 'fabric protection'], 'make': 'Toyota', 'model': 'Camry', 'price': '23500', 'year': '2017'}]