如何将API JSON对象转换为SQL Server单行并处理缺失键？

一、解决键缺失错误 #

直接用[]索引嵌套JSON的键，一旦某个层级的键缺失就会抛出KeyError。改用字典的.get()方法可以避免这个问题，它允许你指定键不存在时的默认值，层级越深越要逐层处理：

示例代码 #

# 逐层获取agent_star_rating的各个字段，键不存在时返回None
agent_rating = response.get('answers', {}).get('agent_star_rating', {})
question_id = agent_rating.get('question_id')
question_text = agent_rating.get('question_text')
comment = agent_rating.get('comment')

# 处理selected_options的动态键（比如示例中的1072）
selected_options = agent_rating.get('selected_options', {})
# 取第一个选项的integer_value，无选项时返回None
star_rating = next(iter(selected_options.values()), {}).get('integer_value')

# 将字段整理成适合写入SQL的字典
processed_data = {
    'agent_question_id': question_id,
    'agent_question_text': question_text,
    'agent_comment': comment,
    'agent_star_rating': star_rating
}

二、更优实现：用pandas扁平化JSON #

对于嵌套JSON转表格格式，pandas.json_normalize是更高效的工具，无需手动逐个映射键，还能自动处理缺失字段（填充NaN）。

步骤1：预处理动态键 #

由于selected_options的键是动态数字（比如1072），先提取第一个选项的核心值：

import pandas as pd

# 提取agent_star_rating数据
agent_rating_data = response.get('answers', {}).get('agent_star_rating', {})

# 提取第一个选项的integer_value，替换原嵌套的selected_options字段
selected_opt = next(iter(agent_rating_data.get('selected_options', {}).values()), {})
agent_rating_data['star_rating'] = selected_opt.get('integer_value')
del agent_rating_data['selected_options']  # 删除冗余的嵌套字段

步骤2：转成单行DataFrame #

# 将字典转为单行DataFrame，自动匹配列名
df = pd.DataFrame([agent_rating_data])

此时df的列就是question_id、question_text、comment、star_rating，缺失字段会自动填充NaN，完全符合SQL Server的单行多列存储需求。

步骤3：写入SQL Server #

用pandas.to_sql直接写入数据库，需要提前安装sqlalchemy和pyodbc（SQL Server官方驱动）：

from sqlalchemy import create_engine

# 替换为你的数据库连接信息
conn_str = 'mssql+pyodbc://用户名:密码@服务器名/数据库名?driver=ODBC+Driver+17+for+SQL+Server'
engine = create_engine(conn_str)

# 写入数据库，if_exists='append'表示追加数据，index=False不写入索引列
df.to_sql('survey_responses', engine, if_exists='append', index=False)

三、两种方式对比 #

• 手动映射：灵活性高，但代码冗余，容易遗漏键缺失处理，适合单条数据的简单场景。
• pandas方法：代码简洁，自动处理缺失值，支持批量处理多份调查数据，后续维护成本更低，适合长期使用。

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