如何解决Azure数据库Python脚本中.rowcount返回-1的问题

使用SQLAlchemy结合pyodbc驱动向MSSQL批量插入数据时，result.rowcount始终返回-1，核心原因是pyodbc驱动默认批量执行逻辑未正确累加插入行数，且缺少关键配置参数导致无法返回真实插入总数。

1. 添加fast_executemany=True引擎配置 #

这是最关键的修复步骤，fast_executemany是pyodbc针对批量操作的优化参数，既能大幅提升插入性能，还能让驱动正确统计所有插入行的数量。

修改引擎创建代码：

engine = create_engine(
    f"{db_type}://{username}:{password}@{host},{port}/{database}?driver={driver}",
    fast_executemany=True  # 新增该参数
)

2. 移除冗余的连接调用 #

原代码中的engine.connect()属于多余操作，with engine.begin()会自动创建并管理连接与事务，无需手动提前连接。

3. 确认目标表结构有效性 #

确保test表已正确创建，例如：

CREATE TABLE test (
    val INT
);

修改后的完整脚本 #

db_type = "mssql+pyodbc"
username = "sa"
password = "abcABC123"
host = "localhost"
port = "1433"
database = "tempdb"
driver = "ODBC+Driver+17+for+SQL+Server"

query = """
    INSERT INTO test (val) VALUES (:val)
"""

params = [
    {'val': 1},
    {'val': 2},
    {'val': 3},
    {'val': 4},
]

from sqlalchemy import create_engine
from sqlalchemy import text

# 配置fast_executemany参数
engine = create_engine(
    f"{db_type}://{username}:{password}@{host},{port}/{database}?driver={driver}",
    fast_executemany=True
)

with engine.begin() as connection:
    result = connection.execute(text(query), params)
    print(result.rowcount)  # 此时会输出预期的4

可显式调用底层pyodbc的executemany方法，手动处理事务：

with engine.begin() as connection:
    cursor = connection.connection.cursor()
    # 编译SQL语句适配MSSQL语法
    compiled_query = text(query).compile(engine=engine)
    cursor.executemany(str(compiled_query), params)
    print(cursor.rowcount)
    connection.connection.commit()

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