Python如何迭代嵌套json中的所有键和值以放入csv文件中

Question

大家好，我有这个巨大的嵌套 json 响应

{
  "success": true,
  "Result": {
    "IsAggregate": false,
    "Count": 37,
    "Columns": [
      ...
      ...
      ...
      ...
    ],
    "FullCount": 37,
    "Results": [
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "adam",
            "IsForeignKey": true
          }
        ],
        "Row": {
          "PrincipalType": "User",
          "_NumDenyAdd": "None",
          "objecttype": "Row",
          "objectname": "Row|oath|41",
          "EventType": "Cloud.Core.Access.Rights.Change",
          "level": "Info",
          "RequestHostName": "6.1.7.3",
          "Principal": "a-1-4a-ad-4",
          "NumGrantAdd": "GenericAll",
          "NormalizedUser": "adam",
          "_IPaddress": "1.1.10.10",
          "WhenOccurred": "/Date(1577124009000)/",
          "_NumDenyRemove": "None",
          "_NumGrantRemove": "None",
          "_Principalname": null
        }
      },
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "eve",
            "IsForeignKey": true
          }
        ],
        "Row": {
          "PrincipalType": "User",
          "_NumDenyAdd": "None",
          "objecttype": "Row",
          "objectname": "Row|pvcheckout|",
          "EventType": "Cloud.Core.Access.Rights.Change",
          "level": "Info",
          "RequestHostName": "10.100.10.10",
          "Principal": "a1",
          "NumGrantAdd": "GenericAll",
          "NormalizedUser": "eve",
          "_IPaddress": "10.20.20.40.50",
          "WhenOccurred": "/Date(1576771533608)/",
          "_NumDenyRemove": "None",
          "_NumGrantRemove": "None",
          "_Principalname": null
        }
      },
      {
        "Entities": [
          {
            "Type": "User",
            "Key": "SYSTEM$",
            "IsForeignKey": true
          }
        ],
        "Row": {
          "PrincipalType": "User",
          "_NumDenyAdd": "None",
          "objecttype": "File",
          "objectname": "File|/Traces/Cps",
          "EventType": "Cloud.Core.Access.Rights.Change",
          "level": "Info",
          "RequestHostName": "130.100.500.204",
          "Principal": "a1",
          "NumGrantAdd": "Read",
          "NormalizedUser": "SYSTEM$",
          "_IPaddress": "10.81.700.20",
          "WhenOccurred": "/Date(1576771134144)/",
          "_NumDenyRemove": "None",
          "_NumGrantRemove": "None",
          "_Principalname": null
        }
      },
                {
        "Entities": [
          {
            "Type": "User",
            "Key": "john",
            "IsForeignKey": true
          }
        ],
        "Row": {
          "PrincipalType": "User",
          "_NumDenyAdd": "None",
          "objecttype": "Row",
          "objectname": "Row|pvcheckout|e069f223-cb58-4843-ba29-55a00ee1f247",
          "EventType": "Cloud.Core.Access.Rights.Change",
          "level": "Info",
          "RequestHostName": "08.6.3.9",
          "Principal": "a1",
          "NumGrantAdd": "GenericAll",
          "NormalizedUser": "john",
          "_IPaddress": "8.6.3.9",
          "WhenOccurred": "/Date(1575048797174)/",
          "_NumDenyRemove": "None",
          "_NumGrantRemove": "None",
          "_Principalname": null
        }
      }
    ],
    "ReturnID": ""
  },
  "Message": null,
  "MessageID": null,
  "Exception": null,
  "ErrorID": null,
  "ErrorCode": null,
  "IsSoftError": false,
  "InnerExceptions": null
}

我想获取所有出现的实体键和值以及行键和值，并将它们放入 csv 文件中。我做了什么

responseObject = r.json() # r is the get request, I store my response into a json
res_data = responseObject['Result']['Results'] # accessing result to reach results where the data i want resides

with open('test_data.csv', 'w') as file1:
    csv.writer = csv.DictWriter(file1,delimiter='|') # error occurs here no fieldname parameter
    for result in res_data:
        csv.writer.writerow(result['Entities']) 
        csv.writer.writerow(result['Row'])

这是我遇到错误和困惑的地方。我收到的第一个错误是没有字段名称参数需要“实体”和“行”中的键，但我确信还有另一种方法可以解决此问题。

第二个错误是csv.writer.writerow()，如果我将必填字段写入csv，它们将相互覆盖。有什么建议或想法来解决这个问题吗？我知道我错过了一些明显的东西

Answer 1

试试这个，代码会自行解释:

res_data = data['Result']['Results']
fields = [
    'Type',
    'Key',
    'IsForeignKey',
    "PrincipalType",
    "_NumDenyAdd",
    "objecttype",
    "objectname",
    "EventType",
    "level",
    "RequestHostName",
    "Principal",
    "NumGrantAdd",
    "NormalizedUser",
    "_IPaddress",
    "WhenOccurred",
    "_NumDenyRemove",
    "_NumGrantRemove",
    "_Principalname"
]
with open('test_data.csv', 'w', newline='') as file1:
    csv_writer = csv.writer(file1)
    csv_writer.writerow(fields)
    for user in res_data:
        dataToAppend = []
        for each in fields:
            if len(dataToAppend) < 3:
                dataToAppend.append(str(user['Entities'][0][each]))
            else:
                dataToAppend.append(user['Row'][each])
        print(dataToAppend)
        csv_writer.writerow(dataToAppend)