CSV转JSON调用API返回422错误（Reference缺失）求助

问题背景 #

编写Python代码将CSV文件转换为JSON后调用API，执行时出现422错误，返回信息显示Reference is missing。但硬编码符合要求的JSON内容时，代码可正常运行。需要实现终端用户通过表格填写信息后发送请求的功能。

错误原因 #

当前代码的CSV转JSON逻辑完全不符合API要求的结构：

• API要求的请求体是外层包含Reference字段，以及products数组（数组内是产品对象），如Postman示例所示
• 现有csv_to_json函数只是把CSV的每一行单独转成JSON字符串，结构和API要求的层级完全不匹配，导致API无法找到顶级的Reference字段

解决方案 #

1. 调整CSV结构（必填） #

确保CSV包含Reference字段，以及产品所需的active、skuDistributor等字段（对应Postman里products数组内的字段）。示例CSV结构如下：

2. 修改代码逻辑 #

重新编写CSV转换逻辑，生成符合API要求的JSON结构；同时优化请求发送方式，使用requests.post的json参数自动处理JSON序列化和Content-Type头。

修改后的完整代码

import csv
import json
import requests
import tkinter as tk
from tkinter import filedialog, simpledialog

url_auth = 'https://api-gateway.com/token'
payload_auth = {'grant_type': 'client'} 
username = ""  # 替换为实际用户名
password = ""  # 替换为实际密码  

# 获取认证token
response_auth = requests.post(url_auth, auth=(username, password), data=payload_auth)
data = response_auth.json()
access_token = data['access_token']

url_prd = 'https://api-gateway.com/api/products'
headers = {
    'Authorization': f'Bearer {access_token}',
}

root = tk.Tk()
root.withdraw()

def csv_to_api_payloads(csvFilePath):
    payloads = []
    with open(csvFilePath, encoding='utf-8') as csvf: 
        csvReader = csv.DictReader(csvf)
        for row in csvReader:
            # 提取Reference字段，剩余字段作为products数组内的对象
            reference = row.pop('Reference')
            # 转换active字段为布尔值（CSV里是字符串，API需要布尔值）
            row['active'] = row['active'].lower() == 'true'
            # 构建符合API要求的结构
            payload = {
                "Reference": reference,
                "products": [row]
            }
            payloads.append(payload)
    return payloads

# 选择CSV文件
csvFilePath = filedialog.askopenfilename()
payloads = csv_to_api_payloads(csvFilePath)

# 发送请求
success_count = 0
fail_count = 0
for idx, payload in enumerate(payloads):
    try:
        # 使用json参数自动序列化并设置Content-Type
        response = requests.post(url_prd, headers=headers, json=payload)
        if response.status_code == 200:
            print(f"第{idx+1}条请求成功")
            success_count +=1
        else:
            print(f"第{idx+1}条请求失败，状态码:{response.status_code}，错误信息:{response.text}")
            fail_count +=1
    except Exception as e:
        print(f"第{idx+1}条请求异常: {str(e)}")
        fail_count +=1

print(f"\n导入完成：成功{success_count}条，失败{fail_count}条")

关键修改点说明 #

• 结构匹配：生成的每个请求体严格遵循API要求的{"Reference": "...", "products": [{}]}结构
• 类型转换：将CSV中active字段的字符串值转换为布尔值（API要求布尔类型）
• 请求优化：使用requests.post的json参数，无需手动序列化JSON，自动设置正确的Content-Type头
• 错误反馈：增加了逐条请求的结果反馈，便于排查问题

终端用户表格填写功能扩展 #

如果需要让用户直接通过GUI表格填写信息而非上传CSV，可以使用tkinter.ttk.Treeview实现简单表格，示例核心逻辑：

import tkinter.ttk as ttk

def create_input_form():
    root = tk.Tk()
    root.title("产品信息填写")
    
    # 表格列定义
    columns = ("Reference", "active", "skuDistributor", "designation", "smallDescription", "bigDescription", "price", "publicprice")
    tree = ttk.Treeview(root, columns=columns, show="headings")
    for col in columns:
        tree.heading(col, text=col)
        tree.column(col, width=120)
    tree.pack(fill=tk.BOTH, expand=True)
    
    # 添加行按钮
    def add_row():
        tree.insert("", tk.END, values=("", "True", "", "", "", "", "", ""))
    
    # 提交按钮
    def submit():
        payloads = []
        for item in tree.get_children():
            values = tree.item(item)["values"]
            row = dict(zip(columns, values))
            reference = row.pop('Reference')
            row['active'] = row['active'].lower() == 'true'
            payload = {"Reference": reference, "products": [row]}
            payloads.append(payload)
        # 调用发送请求的逻辑
        send_requests(payloads)
    
    ttk.Button(root, text="添加行", command=add_row).pack(side=tk.LEFT)
    ttk.Button(root, text="提交", command=submit).pack(side=tk.RIGHT)
    root.mainloop()

def send_requests(payloads):
    # 复用之前的发送请求逻辑
    success_count = 0
    fail_count = 0
    for idx, payload in enumerate(payloads):
        try:
            response = requests.post(url_prd, headers=headers, json=payload)
            if response.status_code == 200:
                print(f"第{idx+1}条请求成功")
                success_count +=1
            else:
                print(f"第{idx+1}条请求失败，状态码:{response.status_code}，错误信息:{response.text}")
                fail_count +=1
        except Exception as e:
            print(f"第{idx+1}条请求异常: {str(e)}")
            fail_count +=1
    print(f"\n提交完成：成功{success_count}条，失败{fail_count}条")

# 启动表单
create_input_form()

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