如何为SQLAlchemy Insert对象指定列并使用from_select？

场景与需求 #

现有两张表结构如下：

create table master (id serial, name varchar);
create table mapping (id serial, new_name varchar, master_id integer);

-- master_id是指向master表id列的外键

已完成master表的数据填充，需要将master表的数据批量导入mapping表，对应的原生SQL语句为：

insert into mapping (master_id, new_name) select id, name from master;

问题描述 #

使用以下SQLAlchemy代码时，因列名逻辑不匹配，无法正确解析映射关系：

stmt = sa_mapping_table.insert().from_select(['name', 'id'], stmt)

解决方法 #

from_select方法支持明确指定目标表的列与查询结果列的对应关系，只需保证查询结果的列顺序与目标列顺序一致即可。

方法1：直接指定目标列与查询语句 #

from sqlalchemy import select

# 构建查询master表的语句，列顺序对应目标表的master_id、new_name
select_stmt = select(sa_master_table.c.id, sa_master_table.c.name)

# 构建insert语句，第一个参数传入目标表的列列表，第二个参数传入查询语句
insert_stmt = sa_mapping_table.insert().from_select(
    ['master_id', 'new_name'],
    select_stmt
)

方法2：通过列别名明确映射关系 #

如果希望更直观地展示列对应关系，可以在查询语句中给列设置别名，使其与目标表列名一致：

select_stmt = select(
    sa_master_table.c.id.label('master_id'),
    sa_master_table.c.name.label('new_name')
)

insert_stmt = sa_mapping_table.insert().from_select(
    ['master_id', 'new_name'],
    select_stmt
)

执行上述insert_stmt即可生成正确的批量插入SQL，完成数据导入。

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