python - Pyodbc executemany 只返回插入的最后一个元素

Question

使用以下函数:

import pyodbc

def execute_side_effect_stmt(sql_stmt: str, params: list):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.executemany(sql_stmt, params)
        columns = [column[0] for column in cursor.description]
        results = cursor.fetchall()
        response = []
        for row in results:
            response.append(dict(zip(columns, row)))
        conn.commit()
        if not response:
            return ''
        return response

使用以下参数:

sql = """INSERT INTO dbo.events
      (sha, duration)
      OUTPUT Inserted.id, Inserted.sha
      VALUES (?, ?)"""

params = [('123',1),('456', 2), ('789', 3)]

result = execute_side_effect_stmt(sql, params)

结果仅返回参数中最后一个条目的 id 和 sha。一切都正确地插入到数据库中。非常欢迎任何关于为什么只有最后一个插入给出输出的见解。

Answer 1

原因是cursor.executemany()对params中的每个元素执行SQL语句。如图docs ，除非您设置 cursor.fast_executemany = True，否则 INSERT 语句将被调用 len(params) 次。

使用 cursor.fast_executemany = True，结果将是单个插入，如 here 所述

如所述:

Here, all the parameters are sent to the database server in one bundle (along with the SQL statement), and the database executes the SQL against all the parameters as one database transaction. Hence, this form of executemany() should be much faster than the default executemany(). However, there are limitations to it, see fast_executemany for more details.

您的代码可以修改为:

import pyodbc

def execute_side_effect_stmt(sql_stmt: str, params: list):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.fast_executemany = True
        cursor.executemany(sql_stmt, params)
        columns = [column[0] for column in cursor.description]
        results = cursor.fetchall()
        response = []
        for row in results:
            response.append(dict(zip(columns, row)))
        conn.commit()
        if not response:
            return ''
        return response