Python调用Freshdesk Contact API写入PostgreSQL遇列不存在错误求助

问题根源 #

你遇到的问题核心是Pandas的to_sql方法默认不会自动修改已存在表的结构：

• 如果freshdesk_contacts表已提前创建（或之前运行脚本时生成），但当时的DataFrame不包含other_companies列，后续拉取的数据新增该列时，Pandas不会自动为表添加这个字段，直接插入就会触发列不存在的错误。
• 另外，other_companies是Freshdesk返回的嵌套JSON数组，Pandas无法自动识别并映射到PostgreSQL的JSONB类型，即使新建表也可能出现类型不匹配的隐性问题。

解决方案 #

根据你的同步需求，分两种场景处理：

场景1：允许清空表并重建（适合全量同步/测试）

直接用if_exists='replace'参数让Pandas删除旧表，基于当前DataFrame的结构重建新表：

import sqlalchemy

# 修改to_sql调用参数
df.to_sql(
    name='freshdesk_contacts',
    con=engine,
    if_exists='replace',  # 替换旧表，会清空历史数据
    index=False,
    dtype={
        'other_companies': sqlalchemy.types.JSONB  # 指定嵌套列的SQL存储类型
    }
)

场景2：保留历史数据并新增列（适合增量同步）

如果需要保留已有数据，先检查并手动添加缺失列，再执行插入：

from sqlalchemy import text

# 检查列是否存在，不存在则新增
with engine.connect() as conn:
    result = conn.execute(text("SELECT column_name FROM information_schema.columns WHERE table_name = 'freshdesk_contacts'"))
    existing_columns = [row[0] for row in result]
    if 'other_companies' not in existing_columns:
        conn.execute(text("ALTER TABLE freshdesk_contacts ADD COLUMN other_companies JSONB"))
        conn.commit()

# 插入新数据（增量追加）
df.to_sql(
    name='freshdesk_contacts',
    con=engine,
    if_exists='append',
    index=False,
    dtype={
        'other_companies': sqlalchemy.types.JSONB
    }
)

关键细节：处理嵌套JSON列

Freshdesk返回的other_companies是数组结构，必须指定dtype为sqlalchemy.types.JSONB，否则Pandas会将其转成字符串存储，后续查询和解析会非常麻烦。

额外建议 #

• 首次运行脚本时，确保目标表不存在，或用if_exists='replace'生成符合API返回结构的表。
• 增量同步场景下，建议动态对比API返回字段与表的列名，自动添加缺失列，避免硬编码字段名带来的维护问题。

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