PyODBC插入SQL Server后无法提交却返回自增ID问题

• 需向带IDENTITY列的SQL Server表插入单条记录，并安全获取自增ID（因并发问题，不考虑MAX()方式）
• 表结构：

CREATE TABLE dbo.TableNameHere
(
  Column1 INT IDENTITY(1,1) PRIMARY KEY, 
  Column2 INT
)

• 执行的SQL语句：

SET NOCOUNT ON;

INSERT INTO dbo.TableNameHere
(Column2) 
VALUES(100);

SELECT  @@IDENTITY As TheIDINeed;

• 异常现象：通过PyODBC执行时，能返回自增ID且ID值递增，但目标表无插入记录；移除SELECT @@IDENTITY语句则插入正常
• 已尝试无效操作：SQL中包裹BEGIN TRAN/COMMIT、连接字符串设置auto_commit=true、更换SQL Native Client Driver

方法1：使用OUTPUT子句直接返回自增ID（推荐） #

修改SQL语句，利用OUTPUT子句在插入操作完成后直接返回目标IDENTITY列的值，整个操作原子性，无需额外SELECT语句，避免事务或结果集处理问题：

SET NOCOUNT ON;
INSERT INTO dbo.TableNameHere(Column2)
OUTPUT inserted.Column1
VALUES(100);

对应的PyODBC执行代码：

import pyodbc

# 替换为你的连接字符串
conn_str = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=用户名;PWD=密码;AUTOCOMMIT=yes"
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

try:
    cursor.execute("""
    SET NOCOUNT ON;
    INSERT INTO dbo.TableNameHere(Column2)
    OUTPUT inserted.Column1
    VALUES(100);
    """)
    # 获取返回的自增ID
    new_id = cursor.fetchone()[0]
    print(f"新插入记录的ID：{new_id}")
finally:
    cursor.close()
    conn.close()

方法2：使用SCOPE_IDENTITY()并确保事务正确提交 #

@@IDENTITY可能受触发器等跨作用域操作影响，改用SCOPE_IDENTITY()更安全；同时显式控制事务提交，避免隐式回滚：

import pyodbc

conn_str = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=用户名;PWD=密码"
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

try:
    cursor.execute("""
    SET NOCOUNT ON;
    INSERT INTO dbo.TableNameHere(Column2) VALUES(100);
    SELECT SCOPE_IDENTITY() AS TheIDINeed;
    """)
    new_id = cursor.fetchone()[0]
    # 显式提交事务
    conn.commit()
    print(f"新插入记录的ID：{new_id}")
except Exception as e:
    # 异常时回滚
    conn.rollback()
    print(f"执行出错：{str(e)}")
finally:
    cursor.close()
    conn.close()

方法3：检查结果集处理（针对多语句执行场景） #

若未设置SET NOCOUNT ON，INSERT操作会返回受影响行数的结果集，此时需跳过该结果集再获取SELECT的结果：

import pyodbc

conn = pyodbc.connect("你的连接字符串")
cursor = conn.cursor()

try:
    cursor.execute("""
    INSERT INTO dbo.TableNameHere(Column2) VALUES(100);
    SELECT SCOPE_IDENTITY() AS TheIDINeed;
    """)
    # 跳过INSERT返回的结果集
    cursor.nextset()
    new_id = cursor.fetchone()[0]
    conn.commit()
    print(f"新插入记录的ID：{new_id}")
finally:
    cursor.close()
    conn.close()

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