JSON 到 CSV 与 Python 错误，键不存在

Question

我想将JSON数据转换为csv文件，问题是不是所有的数据都是相同的，它们并不都有相同的键。我设法手动打印第一行键名，然后调用相应键的每个值。

我的代码:

import json
import csv
with open("./res.json") as file:
data = json.load(file)
file_name = "output.csv"
with open(file_name, "w") as file:
csv_file = csv.writer(file)
csv_file.writerow(["Account_Name__c", "License_Address__c", "Operating_Name__c", "Property_Location__c", "License_Address_for_Business_Directroy__c", "License_Status__c", "Account__c", "Building__c", "Longitude__c", "Latitude__c", "Id", "CurrencyIsoCode", "Property_Location__r"])
if "resultData" in data:
for item in data["resultData"]:
csv_file.writerow([item['Account_Name__c'], item['License_Address__c'], item['Operating_Name__c'], item['Property_Location__c'], item['License_Address_for_Business_Directroy__c'], item['License_Status__c'], item['Account__c'], item['Building__c'], item['Longitude__c'], item['Latitude__c'], item['Id'], item['CurrencyIsoCode'], item['Property_Location__r']])

数据样本:

{
"resultData": [
{
"Account_Name__c": "ZURI FLOWERS DMCC",
"License_Address__c": "Unit No: 2201<br>Tiffany Towers<br>Plot No: JLT-PH2-W2A<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"Operating_Name__c": "001b000000MV4EnAAL",
"Property_Location__c": "a1G10000000XLRNEA4",
"License_Address_for_Business_Directroy__c": "Unit No: 2201<br>Tiffany Towers<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"License_Status__c": "Active",
"Account__c": "001b000000MV4EnAAL",
"Building__c": "Tiffany Towers",
"Longitude__c": "55.14960835",
"Latitude__c": "25.07726334",
"Id": "a03b0000006h0Y7AAI",
"CurrencyIsoCode": "AED",
"Operating_Name__r": {
"Name": "ZURI FLOWERS DMCC",
"Id": "001b000000MV4EnAAL",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
},
"Property_Location__r": {
"Name": "PL-017352",
"Function_Type_Class__c": "Office",
"Id": "a1G10000000XLRNEA4",
"RecordTypeId": "01210000000YFA3AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Publishing_agreement_for_BD__c": "Publish all details in DMCC online/printed content",
"Name": "ZURI FLOWERS DMCC",
"Phone_BD__c": "[phone]",
"Company_Website_Address__c": "01:00",
"Company_Official_Email_Address__c": "[e-mail]",
"Saturday_From__c": "Closed",
"Saturday_To__c": "Closed",
"Operating_Time_from_regular__c": "09:00 AM",
"Operating_Time_to_regular__c": "05:00 PM",
"Monday_From__c": "09:00 AM",
"Monday_To__c": "05:00 PM",
"Tuesday_From__c": "09:00 AM",
"Tuesday_To__c": "05:00 PM",
"Wednesday_From__c": "09:00 AM",
"Wednesday_To__c": "05:00 PM",
"Thursday_From__c": "09:00 AM",
"Thursday_To__c": "04:00 PM",
"Friday_From__c": "Closed",
"Friday_To__c": "Closed",
"Id": "001b000000MV4EnAAL",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
},
{
"Account_Name__c": "ZYDUS WORLDWIDE DMCC",
"License_Address__c": "Unit No: 908<br>Armada 2<br>Plot No: JLT-PH2-P2A<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"Operating_Name__c": "0011000000jkjT1AAI",
"Property_Location__c": "a1G10000000XKCCEA4",
"License_Address_for_Business_Directroy__c": "Unit No: 908<br>Armada 2<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"License_Status__c": "Active",
"Account__c": "0011000000jkjT1AAI",
"Building__c": "Armada 2",
"Longitude__c": "55.14552528",
"Latitude__c": "25.07481256",
"Id": "a031000000M6HQ6AAN",
"CurrencyIsoCode": "AED",
"Operating_Name__r": {
"Name": "ZYDUS WORLDWIDE DMCC",
"Id": "0011000000jkjT1AAI",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
},
"Property_Location__r": {
"Name": "PL-012567",
"Function_Type_Class__c": "Office",
"Id": "a1G10000000XKCCEA4",
"RecordTypeId": "01210000000YFA3AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Publishing_agreement_for_BD__c": "Publish only name and address in DMCC online/printed content",
"Name": "ZYDUS WORLDWIDE DMCC",
"Phone_BD__c": "[phone]",
"Website": "www.zyduscadila.com",
"Company_Official_Email_Address__c": "[e-mail]",
"Saturday_From__c": "Closed",
"Saturday_To__c": "Closed",
"Operating_Time_from_regular__c": "09:00 AM",
"Operating_Time_to_regular__c": "06:00 PM",
"Monday_From__c": "09:00 AM",
"Monday_To__c": "06:00 PM",
"Tuesday_From__c": "09:00 AM",
"Tuesday_To__c": "06:00 PM",
"Wednesday_From__c": "09:00 AM",
"Wednesday_To__c": "06:00 PM",
"Thursday_From__c": "09:00 AM",
"Thursday_To__c": "06:00 PM",
"Friday_From__c": "Closed",
"Friday_To__c": "Closed",
"Id": "0011000000jkjT1AAI",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
},
{
"Account_Name__c": "ZYLIN TECHNOLOGIES DMCC",
"License_Address__c": "Unit No: 3O-01-2357<br>Jewellery &amp; Gemplex 3<br>Plot No: DMCC-PH2-J&amp;GPlexS<br>Jewellery &amp; Gemplex<br>Dubai<br>United Arab Emirates",
"Property_Location__c": "a1G10000001X1gHEAS",
"License_Status__c": "Active",
"Account__c": "0015F00001NOV3iQAH",
"Building__c": "55.13646334",
"Id": "a035F00001K4be6QAB",
"CurrencyIsoCode": "AED",
"Property_Location__r": {
"Name": "PL-128190",
"Function_Type_Class__c": "Flexi Desk",
"Id": "a1G10000001X1gHEAS",
"RecordTypeId": "01210000000YFA5AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Publishing_agreement_for_BD__c": "Publish all details in DMCC online/printed content",
"Name": "ZYLIN TECHNOLOGIES DMCC",
"Phone_BD__c": "[phone]",
"Company_Official_Email_Address__c": "[e-mail]",
"Saturday_From__c": "Closed",
"Saturday_To__c": "Closed",
"Operating_Time_from_regular__c": "09:00 AM",
"Operating_Time_to_regular__c": "05:00 PM",
"Monday_From__c": "09:00 AM",
"Monday_To__c": "05:00 PM",
"Tuesday_From__c": "09:00 AM",
"Tuesday_To__c": "05:00 PM",
"Wednesday_From__c": "09:00 AM",
"Wednesday_To__c": "05:00 PM",
"Thursday_From__c": "09:00 AM",
"Thursday_To__c": "05:00 PM",
"Friday_From__c": "Closed",
"Friday_To__c": "Closed",
"Id": "0015F00001NOV3iQAH",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
},
{
"Account_Name__c": "ZYLWAY DMCC",
"License_Address__c": "Unit No: 3O-01-3360<br>Jewellery &amp; Gemplex 3<br>Plot No: DMCC-PH2-J&amp;GPlexS<br>Jewellery &amp; Gemplex<br>Dubai<br>United Arab Emirates",
"Property_Location__c": "a1G5F000002KAF5UAO",
"License_Status__c": "Active",
"Account__c": "0015F00001TmYjTQAV",
"Building__c": "55.13646334",
"Id": "a035F00001TS4hiQAD",
"CurrencyIsoCode": "AED",
"Property_Location__r": {
"Name": "PL-357158",
"Function_Type_Class__c": "Flexi Desk",
"Id": "a1G5F000002KAF5UAO",
"RecordTypeId": "01210000000YFA5AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Name": "ZYLWAY DMCC",
"Phone_BD__c": "[phone]",
"Id": "0015F00001TmYjTQAV",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
},
{
"Account_Name__c": "ZYMENON UDSTYRE CONSULTING DMCC",
"License_Address__c": "Unit No: 2H-05-124<br>Jewellery &amp; Gemplex 2<br>Plot No: DMCC-PH2-J&amp;GPlexS<br>Jewellery &amp; Gemplex<br>DUBAI<br>United Arab Emirates",
"Property_Location__c": "a1G10000000XGmnEAG",
"License_Status__c": "Active",
"Account__c": "0011000000xig08AAA",
"Building__c": "55.13603472",
"Id": "a031000000YrBUGAA3",
"CurrencyIsoCode": "AED",
"Property_Location__r": {
"Name": "PL-001899",
"Function_Type_Class__c": "Flexi Desk",
"Id": "a1G10000000XGmnEAG",
"RecordTypeId": "01210000000YFA5AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Publishing_agreement_for_BD__c": "Publish only name and address in DMCC online/printed content",
"Name": "ZYMENON UDSTYRE CONSULTING DMCC",
"Phone_BD__c": "[phone]",
"Company_Official_Email_Address__c": "[e-mail]",
"Saturday_From__c": "Closed",
"Saturday_To__c": "Closed",
"Operating_Time_from_regular__c": "09:00 AM",
"Operating_Time_to_regular__c": "05:00 PM",
"Monday_From__c": "09:00 AM",
"Monday_To__c": "05:00 PM",
"Tuesday_From__c": "09:00 AM",
"Tuesday_To__c": "05:00 PM",
"Wednesday_From__c": "09:00 AM",
"Wednesday_To__c": "05:00 PM",
"Thursday_From__c": "09:00 AM",
"Thursday_To__c": "05:00 PM",
"Friday_From__c": "Closed",
"Friday_To__c": "Closed",
"Id": "0011000000xig08AAA",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
},
{
"Account_Name__c": "ZYWA PAYMENTS DMCC",
"License_Address__c": "Unit No: 3306-1<br>Mazaya Business Avenue BB1<br>Plot No: JLTE-PH2-BB1<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"Property_Location__c": "a1G10000001W6LgEAK",
"License_Address_for_Business_Directroy__c": "Unit No: 3306-1<br>Mazaya Business Avenue BB1<br>Jumeirah Lakes Towers<br>Dubai<br>UAE",
"License_Status__c": "Active",
"Account__c": "0015F00001VsGJFQA3",
"Building__c": "55.1450717",
"Longitude__c": "55.1450717",
"Latitude__c": "25.06828081",
"Id": "a035F00001ZVZS0QAP",
"CurrencyIsoCode": "AED",
"Property_Location__r": {
"Name": "PL-102042",
"Function_Type_Class__c": "BC Office 4",
"Id": "a1G10000001W6LgEAK",
"RecordTypeId": "01210000000YFA5AAO",
"CurrencyIsoCode": "AED"
},
"Account__r": {
"Name": "ZYWA PAYMENTS DMCC",
"Phone_BD__c": "[phone]",
"Id": "0015F00001VsGJFQA3",
"RecordTypeId": "01210000000a3e5AAA",
"CurrencyIsoCode": "AED"
}
}
]
}

pycharm会给我这个错误:

Traceback(最近一次调用):文件"./json_to_csv.py"，第14行csv_file。writerow([item['Account_Name__c']，项目['License_Address__c']，项目['Operating_Name__c']，项目['Property_Location__c']，项目['License_Address_for_Business_Directroy__c']，项目['License_Status__c']，项目[' account_c ']，项目['Building__c']，项目['Longitude__c']，项目['Latitude__c']，项目['Id']，项目['CurrencyIsoCode']，项目['Property_Location__r']))KeyError:"Operating_Name__c">

Answer 1

似乎没有Operating_name_c的键。Operating_name__r有一个键

Answer 2

最好的解决方案是首先扫描所有记录并收集所有可能的键:

all_keys = set()
for item in data["resultData"]:
all_keys |= item.keys()

之后，all_keys将包含所有可能的值，也就是说，您的CSV的所有列。

if "resultData" in data:
all_keys = set()
for item in data["resultData"]:
all_keys |= item.keys()
for item in data["resultData"]:
csv_file.writerow([item.get(k) for k in all_keys ])