PYODBC error ('The SQL contains -26272 parameter markers, but 235872 parameters were supplied', 'HY000')

Question

I am trying to pass values from a JSON file through a SQL merge statement using PYODBC python package. here is a sample of the JSON file which contains exchange rate data for USD to 162 countries for the year of 2023:

{"2023-01-01": {"USD": 1, "AED": 3.6725, "AFN": 88.74103815},
 "2023-01-02": {"USD": 1, "AED": 3.6725, "AFN": 89.02144276}}

I extracted it into the format (list of lists) I have been using for my Python to SQL merge jobs with this code:

with open('ExchangeRates.json', 'r') as f:
    data = json.load(f)
result = []
for date, conversion_rates in data.items():
    for currency, rate in conversion_rates.items():
        result.append(['USD', currency, rate, parser.parse(date)])

A sample of the result:

[['USD', 'XOF', 593.76331894, '2023-12-30'], ['USD', 'XPF', 108.01769686, '2023-12-30'],
 ['USD', 'YER', 247.14186482, '2023-12-30'], ['USD', 'ZAR', 18.35621464, '2023-12-30'],
 ['USD', 'ZMW', 25.69324612, '2023-12-30'], ['USD', 'ZWL', 6047.08996546, '2023-12-30']]

The sql and params arguments I am passing into pyodbc cursor.execute():

sql = """
MERGE INTO database.dbo.table_name AS Target
USING (
    VALUES {}
) AS Source (currency_from, currency_to, factor, timestamp)
ON Target.currency_from = Source.currency_from 
AND Target.currency_to = Source.currency_to
AND CAST(Target.timestamp as date) = CAST(Source.timestamp as date)
WHEN NOT MATCHED THEN
    INSERT (currency_from, currency_to, factor, timestamp) VALUES (Source.currency_from, Source.currency_to, Source.factor, Source.timestamp);
""".format(','.join(['(?,?,?,?)' for _ in range(len(data))]))

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

cnxn = pyodbc.connect(conn_string)
crsr = cnxn.cursor()

try:
    crsr.execute(sql, params)
except Exception as e:
    crsr.rollback()
    print(e)
    print('Transaction rollback')
else:
    cnxn.commit()
    crsr.close()
    cnxn.close()

This code has worked in the past, and everything seems correct. I checked each part separately by printing their output, and I found that the format join operation on the SQL code string inserts (?,?,?,?) 58,968 times (162 countries * 364 days), and the params variable separates out each of the column values into one long list of 235,872 elements (58,968 rows * 4 columns), which all seem correct. I checked my working queries, and they pass the same thing into cursor.execute(sql, params), just far less data.

The daily python job I made that connects to a website to get that day's exchange rate for USD to 162 other countries works perfectly. The only thing I can think of is that I am passing too much data into the pyodbc cursor execute function. Please help me figure out why pyodbc / SQL thinks I am providing "-26272 parameter markers" and how to correct my coding mistake.

Answer 1

As stated by AlwaysLearning, VALUES only accepts 1000 rows at a time. Meaning I was getting the error because I fed VALUES 58x the max number of rows.

The solution I used was to merge in batches of 250 rows (because misread the comment and thought you could only insert 1000 elements, not 1000 rows). This code replaced the old SQL MERGE section:

batch_size = 250
batches = [data[i:i + batch_size] for i in range(0, len(data), batch_size)]

# Prepare SQL template
sql_template = """
MERGE INTO database.dbo.table_name AS Target
USING (
    VALUES {}
) AS Source (currency_from, currency_to, factor, timestamp)
ON Target.currency_from = Source.currency_from 
AND Target.currency_to = Source.currency_to
AND CAST(Target.timestamp as date) = CAST(Source.timestamp as date)
WHEN NOT MATCHED THEN
    INSERT (currency_from, currency_to, factor, timestamp) VALUES (Source.currency_from, Source.currency_to, Source.factor, Source.timestamp);
"""

# Execute batches
cnxn = pyodbc.connect(conn_string)
crsr = cnxn.cursor()

try:
    for batch in batches:
        # Create a comma-separated list of placeholders for each batch
        placeholders = ','.join(['(?,?,?,?)'] * len(batch))
        sql = sql_template.format(placeholders)
        params = [item for sublist in batch for item in sublist]
        crsr.execute(sql, params)
        cnxn.commit()
except Exception as e:
    crsr.rollback()
    print(e)
    print('Transaction rollback')
finally:
    crsr.close()
    cnxn.close()