如何将API返回的含total与list的字典数据正确写入CSV文件？

API返回的简化后JSON响应（字典类型）：

{'total': 99, 'list': [{'id': 'xxxxxxxx', 'name': 'foobar x', 'deleted': False, 'amount': 1234.5}, {'id': 'yyyyyyyy', 'name': 'foobar y', 'deleted': False, 'amount': 2345.6}, {'id': 'zzzzzzzz', 'name': 'foobar z', 'deleted': False, 'amount': 3456.7}]}

用户尝试的代码：

import csv

my_payload = client.request('GET', 'Opportunity')

field_names = ['id','name','deleted','amount']

print(type(my_payload))

with open('/tmp/test.csv', 'w') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames = field_names)
    writer.writeheader()
    for data in my_payload:
        writer.writerow(my_payload[1])

exit()

运行后报错：

<class 'dict'>
Traceback (most recent call last):
  File "/home/martin/bin/espo_get_opportunities.py", line 33, in <module>
    writer.writerow(my_payload)
  File "/usr/lib/python3.10/csv.py", line 154, in writerow
    return self.writer.writerow(self._dict_to_list(rowdict))
  File "/usr/lib/python3.10/csv.py", line 149, in _dict_to_list
    raise ValueError("dict contains fields not in fieldnames: "
ValueError: dict contains fields not in fieldnames: 'list', 'total

问题原因 #

1. 遍历逻辑错误：直接遍历字典my_payload，拿到的是字典的键（'total'、'list'），而非目标数据列表
2. 写入对象错误：代码错误地将整个my_payload字典传入writerow，但该字典包含不在field_names中的键；同时my_payload[1]是列表索引方式，不适用于字典，字典只能通过键访问数据

修正后的代码 #

核心是先提取my_payload['list']拿到目标数据列表，再遍历列表逐行写入：

import csv

my_payload = client.request('GET', 'Opportunity')

field_names = ['id','name','deleted','amount']

print(type(my_payload))

with open('/tmp/test.csv', 'w', newline='') as csvfile:  # 添加newline=''避免Windows下生成空行
    writer = csv.DictWriter(csvfile, fieldnames=field_names)
    writer.writeheader()
    # 提取list中的数据并遍历写入
    for item in my_payload['list']:
        writer.writerow(item)

说明 #

• my_payload['list']直接取出API响应中包含目标数据的列表
• 遍历列表时，每个item都是符合field_names的字典，直接传入writerow即可正确写入
• 添加newline=''是Python写入CSV的最佳实践，避免Windows系统下出现多余空行

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