将API响应转换为适用于SQL导入的Dataframe

我有如下REST API请求代码：

api_response = req.post('APIUrl', params=api_param,headers=api_header, json=api_body)

返回的JSON结构如下：

[
  {
    "productStatusMessage": "ACOPS ARE AVAILABLE FOR THIS CUSTOMER AND SKU",
    "ingramPartNumber": "123512",
    "vendorPartNumber": "LS1016A-CISCO",
    "customerPartNumber": "A5-8963TEST",
    "upc": "0718908728116",
    "partNumberType": "T",
    "vendorNumber": "1234",
    "vendorName": "INTERNAL",
    "description": "6FT PARALLEL PRINTER DB25M TO  SVCS CENT36M PRO SERIES 28AWG ROHS",
    "productClass": "P",
    "uom": "EA",
    "acceptBackOrder": true,
    "productAuthorized": true,
    "returnableProduct": true,
    "endUserInfoRequired": true,
    "availability": {
      "available": true,
      "totalAvailability": 240479,
      "availabilityByWarehouse": [
        {
          "location": "Fort Worth, TX",
          "warehouseId": "20",
          "quantityAvailable": 105415
        },
        {
          "location": "Carol Stream, IL",
          "warehouseId": "40",
          "quantityAvailable": 1049
        }
      ],
      "pricing": {
        "currencyCode": "USD",
        "retailPrice": 10,
        "mapPrice": 540.25,
        "customerPrice": 5.43
      }
    }
  }
]

我通过以下代码将其导入Dataframe并写入文本文件：

json_df = pd.read_json(api_response.text, orient='records')
with open (txt_file,'w') as me:
        me.write(json_df.to_string(header=True, index = True))

生成的文件内容中，嵌套列表availabilityByWarehouse无法被解析展开，我希望生成如下格式的Dataframe（每条仓库记录对应一行主数据）以便导入SQL表：

PartNumber vendorPartNumber           upc partNumberType vendorNumber vendorName                                                description productClass uom  acceptBackOrder  productAuthorized  returnableProduct  endUserInfoRequired                                                                 availabilityByWarehouse                                                                                    pricing
0           123512          LS1016A-CISCO  0718908728116              T         1234     6FT PARALLEL PRINTER DB25M TO  SVCS CENT36M PRO SERIES 28AWG ROHS            P  EA             True               True               True                False   {'location': 'Anywhere, IL', 'warehouseId': '15', 'quantityAvailable': 890, 'quantityBackordered': 54}  {'currencyCode': 'USD', 'retailPrice': 69.0, 'mapPrice': 69.0, 'customerPrice': 39.59}
1           123512          LS1016A-CISCO  0718908728116              T         1234     6FT PARALLEL PRINTER DB25M TO  SVCS CENT36M PRO SERIES 28AWG ROHS            P  EA             True               True               True                False   {'location': 'BFE, TX', 'warehouseId': '67', 'quantityAvailable': 3456, 'quantityBackordered': 122}  {'currencyCode': 'USD', 'retailPrice': 69.0, 'mapPrice': 69.0, 'customerPrice': 39.59}

请问我需要单独创建availability的Dataframe再合并，还是可以直接在当前Dataframe中处理？我是Python编程新手，若有基础知识点遗漏请指出。

不需要单独创建Dataframe再合并，直接在当前Dataframe里用explode方法就能处理嵌套列表，步骤如下：

1. 提取嵌套字段并展开 #

先把availability里的子字段提取到顶层，再对availabilityByWarehouse列表进行行展开：

import pandas as pd

# 读取API返回的JSON到Dataframe
json_df = pd.read_json(api_response.text, orient='records')

# 提取availability下的子字段到顶层
json_df = json_df.assign(
    availabilityByWarehouse=json_df['availability'].apply(lambda x: x['availabilityByWarehouse']),
    pricing=json_df['availability'].apply(lambda x: x['pricing']),
    # 按需提取availability的其他字段
    available=json_df['availability'].apply(lambda x: x['available']),
    totalAvailability=json_df['availability'].apply(lambda x: x['totalAvailability'])
).drop(columns=['availability'])  # 移除原嵌套列

# 展开availabilityByWarehouse列表，每个仓库条目生成一行
expanded_df = json_df.explode('availabilityByWarehouse', ignore_index=True)

2. 写入文件或导入SQL #

生成目标格式的Dataframe后，即可执行后续操作：

# 写入文本文件
with open(txt_file, 'w') as me:
    me.write(expanded_df.to_string(header=True, index=True))

# 导入SQL（需提前创建数据库连接）
# expanded_df.to_sql('your_table_name', con=your_db_connection, if_exists='append', index=False)

新手知识点补充 #

• explode方法：专门用于将Dataframe中包含列表的列展开，每个列表元素对应一行，其他列的值自动重复填充，是处理一对多嵌套结构的核心方法。
• 嵌套JSON处理：pandas默认不会自动解析嵌套的字典/列表，需手动提取或展开，否则会以字符串/对象形式保留在单元格中，无法直接用于SQL导入。
• SQL规范化建议：如果最终要导入SQL，建议把availabilityByWarehouse和pricing里的字段进一步扁平化（比如提取location、warehouseId为单独列），符合数据库表的规范化设计。可以用json_normalize实现：

from pandas import json_normalize

# 先展开availabilityByWarehouse
expanded_df = json_df.explode('availabilityByWarehouse', ignore_index=True)

# 扁平化嵌套字段
warehouse_df = json_normalize(expanded_df['availabilityByWarehouse'])
pricing_df = json_normalize(expanded_df['pricing'])

# 合并所有列
final_df = pd.concat([expanded_df.drop(columns=['availabilityByWarehouse', 'pricing']), warehouse_df, pricing_df], axis=1)

这样生成的Dataframe每个字段都是独立列，完全适配SQL表结构。

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