SQL Merge Error using Python (import pyodbc)

Question

I have an error when I am trying to merge and update data into a SQLExpress table I have create:

Error: ('The SQL contains 10 parameter markers, but 5 parameters were supplied', 'HY000') Not sure what I am doing wrong, I can Merge and Update when I use SSMS. But can't get past this parametre error when using this Python code.

    for i, row in df.iterrows():
                cols = ', '.join(row.index)
                vals = ', '.join(['?'] * len(row))
                print(cols)
                print(vals)
                #query = f"INSERT INTO {table} ({cols}) VALUES ({vals});"
                query = f"MERGE INTO {table} AS target " \
                    f"USING (VALUES ({vals})) AS source ({cols}) " \
                    f"ON (target.id = source.id) " \
                    f"WHEN MATCHED THEN " \
                    f"    UPDATE SET {', '.join(f'target.{col} = source.{col}' for col in row.index)} " \
                    f"WHEN NOT MATCHED THEN " \
                    f"INSERT ({cols}) VALUES ({vals});"   
                print(query) 
                cursor.execute(query, tuple(row))
                conn.commit()
        
            print(f"Inserted/updated {cursor.rowcount} rows into {table}")

        except pyodbc.Error as e:
            print("Error:", e)

        finally:
            cursor.close()
            conn.close()

the print(cols), print(vals) and print (query) return this in terminal.

id, operationId, operation, name, externalId

?, ?, ?, ?, ?

MERGE INTO Activities AS target 
USING (VALUES (?, ?, ?, ?, ?)) 
   AS source (id, operationId, operation, name, externalId) 
ON (target.id = source.id)
WHEN MATCHED THEN     
UPDATE
   SET target.id = source.id,
       target.operationId = source.operationId, 
       target.operation = source.operation, 
       target.name = source.name,
       target.externalId = source.externalId
WHEN NOT MATCHED THEN 
INSERT (id, operationId, operation, name, externalId) 
VALUES (?, ?, ?, ?, ?);

here is the table and two rows of data couple of rows of data

WHat am I doing wrong? Cheers in advance Dave

I tried using the SQlalchemy library and also asked good old GPT but didn't get very far.

Answer 1

Simply format source.* columns to INSERT clause like you did in the UPDATE clause. This avoids sending two set of vals parameters into query. Also, consider avoiding backslash line breaks for parentheses to concatenate lines:

...

upd_cols = ', '.join(f'target.{col} = source.{col}' for col in row.index)
src_cols = ', '.join(f'source.{col}' for col in row.index)

query = (
    f"MERGE INTO {table} AS target "
    f"USING (VALUES ({vals})) AS source ({cols}) "
    "ON (target.id = source.id) "
    "WHEN MATCHED THEN "
    f"    UPDATE SET {upd_cols} "
    "WHEN NOT MATCHED THEN "
    f"INSERT ({cols}) VALUES ({src_cols});"
)

...