SQLAlchemy Core select插入非空列空值、字段超长截断问题

核心原因说明 #

insert().from_select() 会生成纯数据库侧执行的INSERT INTO ... SELECT ...语句，不会触发Column定义中Python侧的default逻辑：Python侧默认值仅在通过values()传参、且未显式指定对应字段时，才会在客户端生成值后插入；from_select会将SELECT返回的结果集原样写入目标表，所有值的转换逻辑都需要在SELECT查询阶段完成。以下方案全部使用SQLAlchemy封装的标准SQL函数实现，兼容DB2语法，无需编写原生SQL。

问题1：源表空值触发目标表非空约束报错 #

使用标准SQL通用、DB2原生支持的COALESCE函数处理即可，该函数会返回参数列表中第一个非NULL值，可直接将源表中的NULL值替换为你需要的默认值0。如果目标表其他非空字段（如Location）也存在源表空值情况，使用完全相同的逻辑处理即可。

问题2：源表字符串长度超过目标字段长度限制 #

使用DB2兼容的LEFT函数对字符串字段做截断，按目标字段定义的长度取前N位即可避免长度溢出错误。你的目标表name字段长度为255、Location字段长度为50，分别截断到对应长度即可。

修正后的可直接运行代码 #

from sqlalchemy import select, insert, func

# 重构SELECT语句，在查询阶段完成所有值转换
Select_stmt = select(
    # 学校名称截断到255字符，匹配目标表name字段长度
    func.left(chicago_schools_manual.columns['Name_of_School'], 255).label('name'),
    # 安全分数空值自动替换为0
    func.coalesce(chicago_schools_manual.columns['Safety_Score'], 0).label('Safety_Score'),
    # 地址截断到50字符，匹配目标表Location字段长度，存在空值可嵌套coalesce设置默认值
    func.left(chicago_schools_manual.columns['Location'], 50).label('Location'),
    # 补充Start_date字段值，使用数据库当前时间，替代Python侧不生效的default
    func.current_timestamp().label('Start_date')
)

# 生成插入语句，字段顺序与SELECT返回列严格对应
Insert_stmt = insert(NiceSchools).from_select(
    ['name', 'Safety_Score', 'Location', 'Start_date'],
    Select_stmt
)

# 执行语句即可完成批量插入
# with engine.begin() as conn:
#     conn.execute(Insert_stmt)

优化建议 #

如果后续需要在from_select场景下自动生效时间字段默认值，建议修改目标表定义，给Start_date添加数据库侧默认值配置，不需要每次在SELECT语句中手动补全：

# 表定义修正片段
Column('Start_date', DateTime, server_default=func.current_timestamp())

配置后即使from_select不指定该字段，数据库也会自动填充当前时间，不会触发非空约束报错。

内容的提问来源于stack exchange，提问作者FábioRB