如何在python中将json文件解析为csv文件时获得水平输出

Question

我想用python将json文件转换成csv文件。我为此写了一个脚本，但是脚本不能正常工作。我的json模板看起来像

{
 "-K6v8Ht6nXCjaO_ApNGr" : {
"key1" : 0,
"key2" : false,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
},......

我的脚本（V3.x）：

^{pr2}$

在这个脚本中，首先for循环应该像

-K6v8Ht6nXCjaO_ApNGr
-K6v8Ht6nXCjaO_ApNGr
-K6v8Ht6nXCjaO_ApNGr

它工作得很好，但不幸的是，我的第二个for循环将这个输出输出到我的csv文件

-KjR-JvTm3BKVkYWoyTV
-Kj5ZdfgX8lLLi-htqPU
-KkC4t0XQoJDViNqt18n

False

"{u'seconds': 18, u'year': 117, u'month': 4, u'hours': 8, u'time': 1494223818226L, u'date': 8, u'minutes': 10, u'day': 1, u'timezoneOffset': -120}"

0
08/05/2017 08:10
"{u'OPENSL': -1, u'USB_7': -1, u'SAPA': -1, u'USB': -1}"
08/05/2017 08:10
-KjafVrmb_wuwb36xNq0
0
23
0
0

我想要一个水平的输出，这也是我的第三个循环嵌套对象，但当我试图运行我的脚本为最后一个循环，我得到了这个错误：

AttributeError: 'bool' object has no attribute 'values'.

很快我的问题是我需要这样的水平输出

K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr

有谁能帮我理解和解决这些问题吗。谢谢你

Answer 1

我假设您的json会是这样的，所以我基于以下内容编写了脚本：

data = {
 "-K6v8Ht6nXCjaO_ApNGr" : {
"key1" : 0,
"key2" : False,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
}}, "-K6v8Ht6nXCjaO_ApNGr1" : {
"key1" : 0,
"key2" : False,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
}}}

代码：

^{pr2}$

输出：

-K6v8Ht6nXCjaO_ApNGr,False,0,0,01/01/2016 04:04
-K6v8Ht6nXCjaO_ApNGr,False,0,0,01/01/2016 04:04

Answer 2

除了csv字段的顺序不同外，此代码似乎可以执行您想要的操作。正常情况下这没关系。我还试图让代码解释它在做什么。在

json是您在问题中提供的三个副本，稍作修改以正确编译，同时更改了主键。在

json_txt = '''\
{
"-K6v8Ht6nXCjaO_ApNGr" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    },
"-K6v8Ht6nXCjaO_2" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    },
"-K6v8Ht6nXCjaO_3" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    }
}'''

import json
import csv

data = json.loads(json_txt)
print ('data keys  >', data.keys())
print ('one of these keys (arbitrary)  >', list(data.keys())[0])
print ('keys for one object in data  >', list(data[list(data.keys())[0]].keys()))
key9_fields = data[list(data.keys())[0]]['key9'].keys()
print ('keys in key9  >', key9_fields)

print ('Now writing csv ...')

the_fieldnames = list(data[list(data.keys())[0]].keys())
# remove triedConnector and key9
the_fieldnames.remove('triedConnector')
the_fieldnames.remove('key9')
# add items from key9
the_fieldnames.extend(key9_fields)
print (the_fieldnames)

with open('tpbafk_csv.csv', 'w', newline='') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=the_fieldnames)
    writer.writeheader()
    for key in data.keys():
        row = {}
        for field_key in the_fieldnames:
            if field_key in key9_fields:
                row[field_key] = data[key]['key9'][field_key]
            else:
                row[field_key] = data[key][field_key]
        writer.writerow(row)

打印在标准输出上的结果：

^{pr2}$

csv文件：

key6,key8,key2,key7,key4,key,key5,key3,key1,timezoneOffset,month,year,hours,time,day,seconds,date,minutes
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4

Answer 3

正如csv.writer documentation中所说：

If csvfile is a file object, it should be opened with newline=''

把这条线修好：

outputFile = open(fileOutput, 'wb+', newline='') #load csv file