提问人：Amit Gupta 提问时间：7/19/2023 最后编辑：ouroboros1Amit Gupta 更新时间：7/19/2023 访问量：30
如何从mysql thrugh pandas读取读取json数据？
How to read read json data from mysql thrugh pandas?
问：
我在mysql数据库列中有这种类型的json，我想使用python读取并在CSV中转换。
{"type":["11","27","26","6"],
"comment":["","","","ADVANCE"],
"remark":["","","","anything"],
"unit":["1.00","1.00","1.00","5000"],
"rate":["1300000","1409.37","100","1"],
"extra":["1.00","850","850","1"],
"amount":["1300000.00","1197964.50","85000.00","5000.00"]}
我需要这样的最终输出：
"type","comment","remark","unit","rate","extra","amount"
"11","","","1.00","1300000","1.00","1300000.00"
"27","","","1.00","1409.37","850","1197964.50"
"26","","","1.00","100","850","85000.00"
"6","ADVANCE","anything","5000","1","1","5000.00"
答：
0赞
Onyx
7/19/2023
#1
好的，所以通过使用 pandas 和 json 模块，我们也许有一个解决方案。这是从原始数据到csv文件的代码：
import pandas as pd
import json
data = {"type":["11","27","26","6"],
"comment":["","","","ADVANCE"],
"remark":["","","","anything"],
"unit":["1.00","1.00","1.00","5000"],
"rate":["1300000","1409.37","100","1"],
"extra":["1.00","850","850","1"],
"amount":["1300000.00","1197964.50","85000.00","5000.00"]}
json_object = json.dumps(data, indent=1)
results = pd.read_json(json_object)
results.to_csv(path_or_buf = 'location\\data_json.csv', index = False, encoding = 'UTF-8', sep = ',')
输出：
type,comment,remark,unit,rate,extra,amount
11,,,1,1300000.0,1,1300000.0
27,,,1,1409.37,850,1197964.5
26,,,1,100.0,850,85000.0
6,ADVANCE,anything,5000,1.0,1,5000.0
希望对您有所帮助。如果不是您想要的，请提供更多信息或背景信息。美好的一天。
评论
import csvpd.DataFrame(data).to_csv('test.csv', quoting=csv.QUOTE_ALL, index=False)