PYODBC merge sql not working: Number of parameter markers not equal to parameters supplied. error code "HY000"

Question

Using pyodbc, I am trying to perform a SQL merge statement using insert_values, a list of 120 tuples with four elements each, insert or update (upsert) values in a Azure SQL DB table:

[('2023', 'M12', 'December', '541.442'), 
 ('2023', 'M11', 'November', '486.639'), 
 ('2023', 'M10', 'October', '468.226'), 
 ('2023', 'M09', 'September', '478.802'), 
 ('2023', 'M08', 'August', '475.411'), 
 ('2023', 'M07', 'July', '471.109')]

I keep getting an error that the number of parameter markers and parameters do not match. I don't know how to even format this to start with, so some guidance there would be greater. I am able to insert into my database, and select data from it, but I want to only insert new information, and update existing based if the price column is different when joining columns are matched.

('The SQL contains 480 parameter markers, but 120 parameters were supplied', 'HY000')

import pyodbc
import json
import textwrap
   
connection_string = """credentials that work"""

# connection object
obj_pyodbc_connection = pyodbc.connect(connection_string)

# create cursor boject
obj_pyodbc_cursor = obj_pyodbc_connection.cursor()

# Define record set
with open('BLS_source_ppi_data.json') as f:
    json_data = json.load(f)
    list_BLSIndex_records = []
    for var_series in json_data['Results']['series']:
        for output in var_series['data']:
            filtered_output = {k: v for k, v in output.items() if k in ('year', 'period', 'periodName', 'value')}
            list_BLSIndex_records.append(list(filtered_output.values()))

insert_values = list_BLSIndex_records

# Define Data Types
obj_pyodbc_cursor.setinputsizes(
    [
        (pyodbc.SQL_WVARCHAR, 255, 0),
        (pyodbc.SQL_WVARCHAR, 255, 0),
        (pyodbc.SQL_WVARCHAR, 255, 0),
        (pyodbc.SQL_WVARCHAR, 255, 0)
    ]
)

# Define table name
table_name = 'z_test_BLS_API'

# Define key columns
col_transaction_year = 'transaction_year'
col_month_num = 'month_num'

# Define other columns
col_month_name = 'month_name'

# Define update columns
col_price_nt = 'price_nt'

# Define values of insert statement
vals = ','.join(["(?, ?, ?, ?)" for _ in insert_values])
# vals = ','.join(["?" for _ in insert_values])


merge_sql = textwrap.dedent("""
MERGE INTO {table_name} AS trgt
USING (
    SELECT *
    FROM (VALUES {vals}) AS s (transaction_year, month_num, month_name, price_nt)
) AS Source
ON trgt.{trans_year} = Source.{trans_year} AND trgt.{month_num} = Source.{month_num}
WHEN NOT MATCHED THEN
    INSERT (transaction_year, month_num, month_name, price_nt)
    VALUES (Source.transaction_year, Source.month_num, Source.month_name, Source.price_nt)
WHEN MATCHED AND trgt.price_nt <> Source.price_nt THEN
    UPDATE SET {update_column} = Source.{update_column};
""").format(table_name=table_name, trans_year=col_transaction_year, update_column=col_price_nt, month_num=col_month_num, vals=vals)


# Execute Upsert
try:
    # obj_pyodbc_cursor.execute(merge_sql, insert_values)
    obj_pyodbc_cursor.execute(merge_sql, *insert_values)

# Rollback insert if there are errors
except Exception as e:
    obj_pyodbc_cursor.rollback()
    print(e)
    print('transaction rolled back')

# if there are no errors, commit the insert, print 'records inserted successfully', then close connection with sql
else:
    print('records inserted successfully')
    obj_pyodbc_cursor.commit()
    obj_pyodbc_connection.close()

I have tried implementing this solution, but nothing I try works. Please help me understand what pyodbc needs to be passed, list the format of inserting data, in order to function. I haven't found any documentation on the exact format data must be in, just tutorials which either use list or lists or list of tuples to pass values into pyodbc cursor.

I have also tried using (?, ?, ?, ?) for values:

merge_sql = textwrap.dedent("""
MERGE INTO {table_name} AS trgt
USING (
    SELECT *
    FROM (VALUES (?, ?, ?, ?)) AS s (transaction_year, month_num, month_name, price_nt)
) AS Source
ON trgt.{trans_year} = Source.{trans_year} AND trgt.{month_num} = Source.{month_num}
WHEN NOT MATCHED THEN
    INSERT (transaction_year, month_num, month_name, price_nt)
    VALUES (Source.transaction_year, Source.month_num, Source.month_name, Source.price_nt)
WHEN MATCHED AND trgt.price_nt <> Source.price_nt THEN
    UPDATE SET {update_column} = Source.{update_column};
""").format(table_name=table_name, trans_year=col_transaction_year, update_column=col_price_nt, month_num=col_month_num, vals=vals)

But I get the following error:

('The SQL contains 4 parameter markers, but 120 parameters were supplied', 'HY000')

Answer 1

PYODBC merge sql not working: Number of parameter markers not equal to parameters supplied. error code "HY000"

The error causes due to the construction of merge statement and passing the parameters to the execute method. The statement must properly formatted with the correct number of parameter markers ?. Since you're using a merge statement with multiple rows to insert, you should dynamically construct the VALUES part of the statement to match the number of rows you want to insert/update.

Below is the data which is already stored in the Azure database table.

[('2023', 'M12', 'December', '541.401'),
('2023', 'M11', 'November', '486.601'),
('2023', 'M10', 'October', '468.226'),
('2023', 'M09', 'September', '478.802'),
('2023', 'M08', 'August', '475.444'),
('2023', 'M07', 'July', '471.109')]

Below is the data which will be inserted only if the values are modified with new values.

[('2023', 'M12', 'December', '541.111'), 
('2023', 'M11', 'November', '486.601'), 
('2023', 'M10', 'October', '468.222'), 
('2023', 'M09', 'September', '478.802'), 
('2023', 'M08', 'August', '475.333'), 
('2023', 'M07', 'July', '471.109'),
('2023', 'M06', 'June', '385.100')]

Below is the code if a row already exists with the same transaction_year and month_num, but with different month_name or price_nt, it will update those values. If a row does not exist, it will insert it into the table using merge statement.

import pyodbc

connection_string = "*****"
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

data = [('2023', 'M12', 'December', '541.111'), 
        ('2023', 'M11', 'November', '486.601'), 
        ('2023', 'M10', 'October', '468.222'), 
        ('2023', 'M09', 'September', '478.802'), 
        ('2023', 'M08', 'August', '475.333'), 
        ('2023', 'M07', 'July', '471.109'),
        ('2023', 'M06', 'June', '385.100')]

merge_query = """
MERGE INTO z_test_BLS_API AS Target
USING (
    VALUES {}
) AS Source (transaction_year, month_num, month_name, price_nt)
ON Target.transaction_year = Source.transaction_year 
AND Target.month_num = Source.month_num
WHEN MATCHED AND (Target.month_name != Source.month_name OR Target.price_nt != Source.price_nt) THEN
    UPDATE SET Target.month_name = Source.month_name, Target.price_nt = Source.price_nt
WHEN NOT MATCHED THEN
    INSERT (transaction_year, month_num, month_name, price_nt) VALUES (Source.transaction_year, Source.month_num, Source.month_name, Source.price_nt);
""".format(','.join(['(?,?,?,?)' for _ in range(len(data))]))

params = [item for sublist in data for item in sublist]

cursor.execute(merge_query, params)

conn.commit()
cursor.close()
conn.close()

The record with the June month is the new and the value 541.111 is the new values which are inserted and modified respectively as shown in the output below.

Output: