CODEWITHSUNDEEP
pythondatabaseinsertpyodbc
NicolaiF
NicolaiF

Reputation: 1343

Pyodbc executemany only returns last element inserted

Using the following function:

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

With the following arguments:

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)

Result only returns the id and sha of the last entry in params. Everything is inserted correctly into the database. Any insights on why only the last insert gives an output would be very welcome.

Upvotes: 3

Views: 1529

Answers (1)

Magnus Berg Sletfjerding
Magnus Berg Sletfjerding

Reputation: 326

The reason is that cursor.executemany() executes the SQL statement for each element in params. As shown in the docs, unless you set cursor.fast_executemany = True, the INSERT statement will be called len(params) times.

With cursor.fast_executemany = True, the result will be a single insert as described here

As described:

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.

Your code could be modified to:

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

Upvotes: 1

Related Questions