Python：如何在csv_file.writerow中忽略或替换KeyError？

我编写了一款Python脚本，从API拉取数据并转换为JSON格式，随后将JSON中每个设备的数据逐行写入CSV文件作为小型数据库。脚本处理10至100条数据时运行正常，但遇到缺失字段时会抛出KeyError。我希望要么用“N/A”“NaN”或“无数据”等内容替换缺失字段（保留该行其余数据），要么跳过缺失字段（仍保留该行其余数据）。

示例JSON数据 #

{"numFound": 5, "devices": [
{"general": {"mac": "M1033770383"}, "network": {"gateway": 542, "downRssi": -110, "upRssi": -124}, "reading": {"connectivity": "ONLINE"}}, 
{"general": {"mac": "A1007634388"}, "network": {"downRssi": -110, "upRssi": -124}, "reading": {"connectivity": "OFFLINE"}},
{"general": {"mac": "C1031474373"}, "network": {"gateway": 533, "downRssi": -110, "upRssi": -124}}
]}

原代码 #

import csv
import json

with open("CSV Builder/JSON_Temp.json") as file:
    data = json.load(file)

fname = "CSV Builder/DeviceReport.csv"
with open(fname, "w", newline="") as file:
   csv_file = csv.writer(file)
   csv_file.writerow(['MAC',
                      'Gateway',
                      'Connectivity',
                      'Down_RSSI',
                      'Up_RSSI',
                      ])

   for item in data["devices"]:
     csv_file.writerow([item['general']['mac'], 
                        item['network']['gateway'],
                        item['reading']['connectivity'],
                        item['network']['downRssi'],
                        item['network']['upRssi'],
                    ])

     print('done')

方法1：使用字典get()方法替换缺失值 #

字典的get(key, default)方法可以在key不存在时返回指定默认值，直接解决KeyError问题，修改后的代码如下：

import csv
import json

with open("CSV Builder/JSON_Temp.json") as file:
    data = json.load(file)

fname = "CSV Builder/DeviceReport.csv"
with open(fname, "w", newline="") as file:
   csv_file = csv.writer(file)
   csv_file.writerow(['MAC',
                      'Gateway',
                      'Connectivity',
                      'Down_RSSI',
                      'Up_RSSI',
                      ])

   for item in data["devices"]:
     csv_file.writerow([
         item['general']['mac'],  # 若MAC也可能缺失，可改为item['general'].get('mac', 'N/A')
         item['network'].get('gateway', 'N/A'),
         item['reading'].get('connectivity', 'N/A'),
         item['network'].get('downRssi', 'N/A'),
         item['network'].get('upRssi', 'N/A'),
     ])

print('done')

方法2：封装嵌套字段取值函数（适合复杂多层嵌套场景） #

如果JSON结构层级多、嵌套复杂，封装一个通用取值函数可以让代码更简洁易维护：

import csv
import json

def get_nested_value(data, keys, default='N/A'):
    """获取多层嵌套字典的值，不存在则返回默认值"""
    for key in keys:
        if isinstance(data, dict) and key in data:
            data = data[key]
        else:
            return default
    return data

with open("CSV Builder/JSON_Temp.json") as file:
    data = json.load(file)

fname = "CSV Builder/DeviceReport.csv"
with open(fname, "w", newline="") as file:
   csv_file = csv.writer(file)
   csv_file.writerow(['MAC',
                      'Gateway',
                      'Connectivity',
                      'Down_RSSI',
                      'Up_RSSI',
                      ])

   for item in data["devices"]:
     csv_file.writerow([
         get_nested_value(item, ['general', 'mac']),
         get_nested_value(item, ['network', 'gateway']),
         get_nested_value(item, ['reading', 'connectivity']),
         get_nested_value(item, ['network', 'downRssi']),
         get_nested_value(item, ['network', 'upRssi']),
     ])

print('done')

关于“跳过缺失字段”的说明 #

CSV是固定列格式，跳过缺失字段会导致列错位（某行少一个值会让后续列全部偏移），破坏CSV的结构化特性，因此更推荐用默认值替换的方案。

内容的提问来源于stack exchange，提问作者CantCode2SaveHerself