API返回缺失子集时，如何在Python中返回NULL值避免KeyError？

我在存储API返回的数据时，不同国家的响应里部分数据子集存在缺失。尝试访问这些缺失子集时，即便用了.get()方法还是触发了KeyError，希望能忽略这些缺失变量，在API未提供对应数据时返回NULL值。

JSON响应示例 #

businessreport_ES.json #

"salesAndTrafficByAsin": [{
    "parentAsin": "AX0003",
    "childAsin": "AXC0001",
    "sku": "AXC1",
    "salesByAsin": {
        "unitsOrdered": 1,
        "orderedProductSales": {
            "amount": 31.06,
            "currencyCode": "EUR"
        },
        "totalOrderItems": 1
    },
    "trafficByAsin": {
        "browserSessions": 0,
        "mobileAppSessions": 1,
        "sessions": 1,
        "browserSessionPercentage": 0.0,
        "mobileAppSessionPercentage": 50.0,
        "sessionPercentage": 14.29,
        "browserPageViews": 0,
        "mobileAppPageViews": 9,
        "pageViews": 9,
        "browserPageViewsPercentage": 0.0,
        "mobileAppPageViewsPercentage": 90.0,
        "pageViewsPercentage": 56.25,
        "buyBoxPercentage": 100.0,
        "unitSessionPercentage": 100.0
    }]

businessreport_UK.json #

"salesAndTrafficByAsin": [{
    "parentAsin": "AX0003",
    "childAsin": "AXC0001",
    "sku": "AXC1",
    "salesByAsin": {
        "unitsOrdered": 0,
        "unitsOrderedB2B": 0,
        "orderedProductSales": {
            "amount": 0.0,
            "currencyCode": "GBP"
        },
        "orderedProductSalesB2B": {
            "amount": 0.0,
            "currencyCode": "GBP"
        },
        "totalOrderItems": 0,
        "totalOrderItemsB2B": 0
    },
    "trafficByAsin": {
        "browserSessions": 3,
        "browserSessionsB2B": 0,
        "mobileAppSessions": 12,
        "mobileAppSessionsB2B": 0,
        "sessions": 15,
        "sessionsB2B": 0,
        "browserSessionPercentage": 0.16,
        "browserSessionPercentageB2B": 0.0,
        "mobileAppSessionPercentage": 0.47,
        "mobileAppSessionPercentageB2B": 0.0,
        "sessionPercentage": 0.34,
        "sessionPercentageB2B": 0.0,
        "browserPageViews": 3,
        "browserPageViewsB2B": 0,
        "mobileAppPageViews": 15,
        "mobileAppPageViewsB2B": 0,
        "pageViews": 18,
        "pageViewsB2B": 0,
        "browserPageViewsPercentage": 0.12,
        "browserPageViewsPercentageB2B": 0.0,
        "mobileAppPageViewsPercentage": 0.46,
        "mobileAppPageViewsPercentageB2B": 0.0,
        "pageViewsPercentage": 0.31,
        "pageViewsPercentageB2B": 0.0,
        "buyBoxPercentage": 0.0,
        "buyBoxPercentageB2B": 0.0,
        "unitSessionPercentage": 0.0,
        "unitSessionPercentageB2B": 0.0
    }
}]

当前Python代码 #

f = open(f'./responses/businessreport_{marketplace}.json')
jsondata = json.load(f)
salesAndTrafficByAsin = []

for item in jsondata['salesAndTrafficByAsin']:
    salesAndTrafficByAsin.append({
        "date": pd.to_datetime(datef),
        "parentAsin": item.get('parentAsin'),
        "childAsin": item.get('childAsin'),
        'unitsOrdered': item["salesByAsin"].get('unitsOrdered'),
        'unitsOrderedB2B': item["salesByAsin"].get('unitsOrderedB2B'),
        'orderedProductSales': item["salesByAsin"]['orderedProductSales'].get('amount'),
        'currencyCode': item["salesByAsin"]['orderedProductSales'].get('currencyCode'),
        'orderedProductSales_B2B': item["salesByAsin"]['orderedProductSalesB2B'].get('amount'),
        'currencyCode_B2B': item["salesByAsin"]['orderedProductSalesB2B'].get('currencyCode'),
        'browserSessions': item["trafficByAsin"].get('browserSessions'),
        "browserSessionsB2B": item["trafficByAsin"].get('browserSessionsB2B'),
        "mobileAppSessions": item["trafficByAsin"].get('mobileAppSessions'),
        "mobileAppSessionsB2B": item["trafficByAsin"].get('mobileAppSessionsB2B'),
        "sessions": item["trafficByAsin"].get('sessions'),
        "sessionsB2B": item["trafficByAsin"].get('sessionsB2B'),
        "browserSessionPercentage": item["trafficByAsin"].get('browserSessionPercentage'),
        "browserSessionPercentageB2B": item["trafficByAsin"].get('browserSessionPercentageB2B'),
        "mobileAppSessionPercentage": item["trafficByAsin"].get('mobileAppSessionPercentage'),
        "mobileAppSessionPercentageB2B": item["trafficByAsin"].get('mobileAppSessionPercentageB2B'),
        "sessionPercentage": item["trafficByAsin"].get('sessionPercentage'),
        "sessionPercentageB2B": item["trafficByAsin"].get('sessionPercentageB2B'),
        "browserPageViews": item["trafficByAsin"].get('browserPageViews'),
        "browserPageViewsB2B": item["trafficByAsin"].get('browserPageViewsB2B'),
        "mobileAppPageViews": item["trafficByAsin"].get('mobileAppPageViews'),
        "mobileAppPageViewsB2B": item["trafficByAsin"].get('mobileAppPageViewsB2B'),
        "pageViews": item["trafficByAsin"].get('pageViews'),
        "pageViewsB2B": item["trafficByAsin"].get('pageViewsB2B'),
        "browserPageViewsPercentage": item["trafficByAsin"].get('browserPageViewsPercentage'),
        "browserPageViewsPercentageB2B": item["trafficByAsin"].get('browserPageViewsPercentageB2B'),
        "mobileAppPageViewsPercentage": item["trafficByAsin"].get('mobileAppPageViewsPercentage'),
        "mobileAppPageViewsPercentageB2B": item["trafficByAsin"].get('mobileAppPageViewsPercentageB2B'),
        "pageViewsPercentage": item["trafficByAsin"].get('pageViewsPercentage'),
        "pageViewsPercentageB2B": item["trafficByAsin"].get('pageViewsPercentageB2B'),
        "buyBoxPercentage": item["trafficByAsin"].get('buyBoxPercentage'),
        "buyBoxPercentageB2B": item["trafficByAsin"].get('buyBoxPercentageB2B'),
    })

错误信息 #

46 salesAndTrafficByDate = []
     47 for item in jsondata['salesAndTrafficByDate']:
     48     salesAndTrafficByDate.append({
     49         "date": pd.to_datetime(item.get('date')),
     50         "orderedProductSales": item["salesByDate"]['orderedProductSales'].get('amount'),
     51         "CurrencyCode": item["salesByDate"]['orderedProductSales'].get('currencyCode'),
---> 52         "orderedProductSales_B2B": item["salesByDate"]['orderedProductSalesB2B'].get('amount'),
     53         "CurrencyCode_B2B": item["salesByDate"]['orderedProductSalesB2B'].get('currencyCode'),
     54         "unitsOrdered": item["salesByDate"].get("unitsOrdered"),
     55         "unitsOrdered_B2B": item["salesByDate"].get("unitsOrderedB2B"),
     56         "totalOrderItems": item["salesByDate"].get("totalOrderItems"),
     57         "totalOrderItems_B2B": item["salesByDate"].get("totalOrderItemsB2B"),
     58         "averageSalesPerOrderItem": item["salesByDate"]["averageSalesPerOrderItem"].get("amount"),
     59         "averageSalesPerOrderItem_B2B": item["salesByDate"]["averageSalesPerOrderItemB2B"].get("amount"),
     60         "averageUnitsPerOrderItem": item["salesByDate"].get("averageUnitsPerOrderItem"),
     61         "averageUnitsPerOrderItem_B2B": item["salesByDate"].get("averageUnitsPerOrderItem"),
     62         "averageSellingPrice": item["salesByDate"]["averageSellingPrice"].get("amount"),
     63         "averageSellingPrice_B2B": item["salesByDate"]["averageSellingPriceB2B"].get("amount"),
     64         "unitsRefunded": item["salesByDate"].get("unitsRefunded"),
     65         "refundRate": item["salesByDate"].get("refundRate"),
     66         "claimsGranted": item["salesByDate"].get("claimsGranted"),
     67         "claimsAmount": item["salesByDate"]["claimsAmount"].get("amount"),
...
     71     })
     72 salesAndTrafficByAsin = []
     74 for item in jsondata['salesAndTrafficByAsin']:

KeyError: 'orderedProductSalesB2B'

解决方案 #

问题出在直接用[]访问可能不存在的嵌套字段，比如item["salesByDate"]['orderedProductSalesB2B']，当这个键不存在时就会触发KeyError。正确的做法是用.get()方法获取嵌套对象，同时指定默认值为空字典，再链式调用.get()获取子字段，这样缺失时会返回None（对应需要的NULL值）。

修改后的核心代码片段如下：

# 替换原来的B2B销售相关字段获取逻辑
'orderedProductSales_B2B': item["salesByDate"].get('orderedProductSalesB2B', {}).get('amount'),
'CurrencyCode_B2B': item["salesByDate"].get('orderedProductSalesB2B', {}).get('currencyCode'),
# 同理处理其他可能缺失的嵌套字段
'averageSalesPerOrderItem_B2B': item["salesByDate"].get('averageSalesPerOrderItemB2B', {}).get('amount'),
'averageSellingPrice_B2B': item["salesByDate"].get('averageSellingPriceB2B', {}).get('amount'),
'claimsAmount': item["salesByDate"].get('claimsAmount', {}).get('amount'),

对应到salesAndTrafficByAsin循环里的B2B字段，同样修改：

'orderedProductSales_B2B': item["salesByAsin"].get('orderedProductSalesB2B', {}).get('amount'),
'currencyCode_B2B': item["salesByAsin"].get('orderedProductSalesB2B', {}).get('currencyCode'),

这样修改后，当某个子集不存在时，.get()会返回空字典，再调用.get('amount')或.get('currencyCode')就会返回None，不会触发KeyError，实现缺失数据返回NULL的需求。

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