如何修复Python requests.post上传CSV文件时的400错误

我使用Python的CSV和Requests库生成CSV文件并上传至API。手动打开生成的文件，将内容复制粘贴到Postman中上传时一切正常，但执行requests.post请求时，API返回400错误，错误提示为：

文件存在未知列，请确保列名定义正确、分隔符设置正确且文件编码正确

相关代码 #

# -*- coding: utf-8 -*-
import pyodbc
import requests
import csv
from datetime import datetime
import time

url = '&csvCodePage=65001&csvColumnDelimiter=%2C&csvTextQualifier=%22&SourceActionTemplate=MassClientAdd' \
      '&editActionTemplate=MassClientEdit'
filename = 'C:\\storeapi\\data.csv'
headers = {
    'Authorization': 'secretKey=""',
    'Accept': 'text/csv',
    'Content-Type': 'text/csv;charset=UTF-8'
}
now = datetime.now().strftime("%Y-%m-%d %H:%M")
connection_string = 'DRIVER={SQL Server};SERVER=XXX;DATABASE=XXX;UID=XXXXX;PWD=XXXXX;'
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
cursor.execute(
    "SELECT TOP(1) P.clients_email_address AS Email, "
    "CASE WHEN p.clients_phone like '' THEN '' "
    "ELSE CONCAT('1', REPLACE(p.clients_phone, ' ', '')) "
    "END AS mobilePhone, "
    "P.last_name AS LastName, "
    "P.first_name AS FirstName, "
    "P.middle_name AS MiddleName, "
    "CAST(CASE WHEN p.sex = '1' THEN 'Male' "
    "ELSE 'Female' END As varchar) AS Sex, "
    "format(P.birthday, 'dd.MM.yyyy') AS BirthDate, "
    "IsSubscribedByEmail = 'TRUE', "
    "IsSubscribedBySms = 'TRUE', "
    "H.name AS CustomFieldClientsStore, "
    "SourceDateTimeUtc = '' "
    "FROM tor.CLIENT P "
    "INNER JOIN tor.Store H ON P.store_id = H.id "
    "INNER JOIN tor.Request R ON P.id = R.client_id "
    "WHERE H.name LIKE '%KIDS%' AND P.last_name <> 'АКТ' AND p.clients_email_address like '%@%'"
    "AND (CAST(r.registration_date AS DATE) between DATEADD(day, -14, convert(date, GETDATE())) "
    "AND DATEADD(day, 0, convert(date, GETDATE())));"
)

rows = cursor.fetchall()
datacol = [row for row in rows]

def create_csv(datacol):
    with open(filename, 'w', encoding='utf-8', newline='') as file:
        writer = csv.writer(file, dialect='excel', delimiter=',', quoting=csv.QUOTE_MINIMAL)
        writer.writerow(
            ["Email", "mobilePhone", "LastName", "FirstName", "MiddleName", "Sex", "BirthDate", "IsSubscribedByEmail",
             "IsSubscribedBySMS", "CustomFieldClientsStore", "SourceDateTimeUtc"]
        )
        writer.writerows(datacol)

# time.sleep(500)
# create_csv(datacol)
# with open(filename, encoding='utf-8', ) as file:
#     content = file.read()
files = {'file': (filename, open(filename, 'rb'), 'text/csv')}
response = requests.post(url, headers=headers, files=files, verify=False)
print(headers)
print(url)
print(response)
print(response.text)
connection.close()

错误响应 #

<Response [400]>
{"status":"ProtocolError","errorMessage":" Unknown column exist in file -- ff7497df26c25afb4810c3ac8578680e. Make sure you define column name correctly, defined deliminator correctly, and you have correct file coding."}

修复方案 #

1. 执行CSV生成代码 #

代码中create_csv(datacol)被注释，导致上传的是旧文件（或空文件），取消注释该行，确保每次上传前生成最新的CSV：

create_csv(datacol)  # 取消注释这行

2. 移除错误的请求头 #

使用files参数上传文件时，requests会自动生成multipart/form-data类型的请求头，手动设置Content-Type: text/csv会导致API无法解析表单数据，修改headers：

headers = {
    'Authorization': 'secretKey=""',
    'Accept': 'text/csv'
    # 删除原有的Content-Type行
}

3. 统一列名大小写 #

CSV表头中是IsSubscribedBySMS（大写SMS），但SQL查询中是IsSubscribedBySms（小写sms），如果API对列名大小写敏感，需要统一两者：

• 要么修改CSV表头为IsSubscribedBySms
• 要么修改SQL查询为IsSubscribedBySMS = 'TRUE'

4. 验证文件编码一致性 #

确保上传的文件编码与API要求的UTF-8一致，可尝试以文本模式读取文件后上传：

with open(filename, 'r', encoding='utf-8') as file:
    files = {'file': (filename, file.read(), 'text/csv;charset=UTF-8')}

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