SQLAlchemy批量插入MS SQL Server时用OUTPUT无法获取全部插入ID的问题

你当前的代码调用conn.execute(stmt, data)传入多组参数时，SQLAlchemy底层会调用ODBC的executemany接口，相当于分两次执行单条INSERT语句，每次插入一行数据。而SQL Server的ODBC驱动在默认executemany模式下，只会保留最后一次执行的结果集，因此你只能拿到最后一行插入数据的id。
你直接在数据库中执行的是单条INSERT携带两组VALUES的写法，仅执行一次，所以可以返回所有插入行的id，二者实际执行逻辑并不一致。

方案1：开启SQLAlchemy批量VALUES合并（推荐，代码改动最小） #

如果你使用的是SQLAlchemy 1.4及以上版本，只需要在创建数据库引擎时添加executemany_mode="values"参数，SQLAlchemy会自动把多组参数合并成单条INSERT的多VALUES语句，和你手写的原生SQL逻辑完全一致：

from sqlalchemy import create_engine
# 核心是添加 executemany_mode="values" 配置
engine = create_engine("mssql+pyodbc://你的数据库连接串", executemany_mode="values")

修改引擎配置后，你原有业务执行代码无需任何调整，就能正常获取所有插入行的id。

方案2：手动构造多VALUES的SQL语句 #

如果你的SQLAlchemy版本低于1.4，不支持executemany_mode参数，可以手动拼接占位符生成对应结构的SQL语句再执行：

try:
    data = [
         {'id': 1, 'fname':'Test1', 'lname':'rec1', 'dob':'05/05/2000'}
        ,{'id': 2, 'fname': 'Test2', 'lname': 'rec2', 'dob':'05/05/2001'}
    ]
    # 动态生成对应数量的VALUES占位符
    values_placeholders = ", ".join([f"(:id_{i}, :fname_{i}, :lname_{i}, :dob_{i})" for i in range(len(data))])
    sql = f"""
    INSERT INTO dbo.emp (id, fname, lname, dob)
    OUTPUT inserted.id
    VALUES {values_placeholders}
    """
    # 把批量参数打平成单组字典
    params = {}
    for idx, row in enumerate(data):
        params[f"id_{idx}"] = row["id"]
        params[f"fname_{idx}"] = row["fname"]
        params[f"lname_{idx}"] = row["lname"]
        params[f"dob_{idx}"] = row["dob"]

    stmt = text(sql)
    result = conn.execute(stmt, params)
    for id in result:
        print(id)

    conn.commit()
except exc.IntegrityError as e:
    print('Code3 - Integrity error raised',  e)
    conn.rollback()
except exc.SQLAlchemyError as e:
    print('Code3 - Something else went wrong', e)
    conn.rollback()

注意：SQL Server默认限制单条INSERT语句最多携带1000个VALUES项，如果你单次插入的行数超过1000，建议把数据拆成多批，每批不超过1000行分别执行。

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