Python使用session.execute插入MS SQL数据后如何获取新增行主键

报错原因说明 #

• SQL Server不支持RETURNING语法，该语法为PostgreSQL等其他数据库的特性，SQL Server对应的功能是OUTPUT子句
• 普通INSERT语句默认不会返回结果集，且你在调用session.commit()之后才尝试读取结果对象，事务提交后结果对象会被自动关闭，因此触发ResourceClosedError

可靠实现方案（原生SQL场景） #

方案1：使用SCOPE_IDENTITY()（单条插入场景首选） #

SCOPE_IDENTITY()是SQL Server提供的内置函数，会返回当前会话、当前作用域内最后生成的自增ID值，不会受到触发器、其他并行会话的插入操作影响，比@@IDENTITY可靠性更高。
代码示例：

with session_scope() as session:
    # 执行插入操作
    session.execute("""INSERT INTO reports (report_type, report_source, received_date) VALUES (3, 'Email', '2021-06-24 12:00')""")
    # 插入完成后、提交事务前查询自增ID
    inserted_r_id = session.execute("SELECT SCOPE_IDENTITY() AS r_id").scalar()
    session.commit()
    print("report id of inserted row is: ", inserted_r_id)

方案2：使用OUTPUT子句（支持多字段返回、批量插入场景） #

SQL Server支持在INSERT语句中加入OUTPUT子句直接返回插入行的指定字段值，无需额外执行查询语句，适合需要返回多个插入字段的场景。
代码示例：

with session_scope() as session:
    result = session.execute("""
        INSERT INTO reports (report_type, report_source, received_date) 
        OUTPUT INSERTED.r_id
        VALUES (3, 'Email', '2021-06-24 12:00')
    """)
    inserted_r_id = result.scalar()
    session.commit()
    print("report id of inserted row is: ", inserted_r_id)

如果需要返回多个字段，只需修改OUTPUT部分即可，比如OUTPUT INSERTED.r_id, INSERTED.received_date就可以同时拿到插入的ID和接收时间。

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