从API获取嵌套JSON转CSV遇键属性错误，如何仅导出records数据？

从API获取到嵌套JSON数据，转换为CSV文件时出现键属性错误，需要仅将数据中的records部分写入CSV。

JSON示例数据 #

{"result":{"total_records":3471,"offset":0.0,"size":100.0,"records":[{"hr_case_number":"HRC0177303","requested_by":"Test Emp 1","employee_name":"Test Emp2","employee_id":"99991","artifact_type":"Competency Assessment","artifact_subtype":"Transfer Competency Assessment","artifact_date":"2022-10-30","status":"uploaded"},{"hr_case_number":"HRC0177302","requested_by":"test emp 3","employee_name":"Test Emp 4","employee_id":"192499","artifact_type":"Orientation","artifact_subtype":"Acknowledgement of Mandated Reporter Status","artifact_date":"2022-10-28","status":"uploaded"}]}}

当前Python代码 #

import requests, json
import csv
url = "https://some.com/api?offset=0&size=10000"
headers = {
  'Authorization': 'Basic c3ZjX2RhdGF',
  'Cookie': 'BIGipServerpool_sometest=a728; 
JSESSIONID=26D6FA5703B691409AA3E44E6825C816; glide_user_route=glide.a50e06d87c4640335db5b2b40400f955; glide_session_store=D9F021AC1BE6D1103FB41F87B04BCB49',
      'Content-Type':'application/json','Accept':'application/json'
    }
payload={}
response = requests.request("GET", url, headers=headers, data=payload)
with open('outputfile.json', 'wb') as outf:
     outf.write(response.content)
     outf.close()

#Open JSON load the data into the variable data
with open('C:\Python\outputfile.json', 'r') as json_file:
    data = json.load(json_file)
 
result = data['result']
 
# open a file for writing
data_file = open('C:\Python\outputfile.csv', 'w', newline='')
 
# create the csv writer
csv_writer = csv.writer(data_file)
 
# header

count = 0
 
for records in result:
    if count == 0:
 
        # Writing headers of CSV file
        header = records.keys()
        csv_writer.writerow(header)
        count += 1
 
    # Writing data of CSV file
    csv_writer.writerow(records.values())
 
data_file.close() 

错误原因及修正方案 #

错误原因 #

原代码中result = data['result']拿到的是包含total_records、offset、size、records的字典，直接遍历result会拿到这些键名（字符串类型），字符串没有keys()和values()方法，因此触发属性错误。

修正后的代码 #

直接定位到data['result']['records']数组，并用csv.DictWriter简化表头和数据的写入逻辑：

import requests
import csv

url = "https://some.com/api?offset=0&size=10000"
headers = {
    'Authorization': 'Basic c3ZjX2RhdGF',
    'Cookie': 'BIGipServerpool_sometest=a728; JSESSIONID=26D6FA5703B691409AA3E44E6825C816; glide_user_route=glide.a50e06d87c4640335db5b2b40400f955; glide_session_store=D9F021AC1BE6D1103FB41F87B04BCB49',
    'Content-Type': 'application/json',
    'Accept': 'application/json'
}

# 直接解析API返回的JSON，无需写入中间文件
response = requests.get(url, headers=headers)
data = response.json()

# 获取目标records数组
records = data['result']['records']

if not records:
    print("无可用数据可写入")
    exit()

# 写入CSV文件
with open('C:\\Python\\outputfile.csv', 'w', newline='', encoding='utf-8') as data_file:
    # 从第一条数据提取表头字段
    fieldnames = records[0].keys()
    csv_writer = csv.DictWriter(data_file, fieldnames=fieldnames)
    
    # 写入表头
    csv_writer.writeheader()
    # 批量写入所有数据行
    csv_writer.writerows(records)

优化说明 #

• 移除中间JSON文件写入步骤，直接解析API响应，提升处理效率
• 使用csv.DictWriter自动处理表头和数据映射，代码更简洁易维护
• 增加空数据判断，避免无数据时触发异常
• 文件路径用双反斜杠避免转义问题，指定utf-8编码防止中文乱码

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