Search code examples
pythonsqlsql-serverpymssqlexecutemany

pymssql executemany insert valueerror

I get an error:

File "pymssql.pyx", line 443, in pymssql.Cursor.executemany (pymssql.c:6616) File "pymssql.pyx", line 417, in pymssql.Cursor.execute (pymssql.c:6057) File "_mssql.pyx", line 943, in _mssql.MSSQLConnection.execute_query (_mssql.c:9858) File "_mssql.pyx", line 974, in _mssql.MSSQLConnection.execute_query (_mssql.c:9734) File "_mssql.pyx", line 1091, in _mssql.MSSQLConnection.format_and_run_query (_mssql.c:10814) File "_mssql.pyx", line 1113, in _mssql.MSSQLConnection.format_sql_command (_mssql.c:11042) File "_mssql.pyx", line 1797, in _mssql._substitute_params (_mssql.c:18646) ValueError: more placeholders in sql than params available

while executing bulk insert into mssql table (executemany). Inserting the same data in one execute statemend as well as in mssql management studio works just fine.

I tried executemany (data) with dictionary and a tuple - same result.

The code:

cursor.executemany("insert table Values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (data))

Any ideas?

Solution

  • All sublists/subtuples in data should have length 21 (the number of %s in your SQL query).

    But. Your data have also items with length 11, 12, 13, 15, 16, 17, 18, 19, and 20. And you get the error more placeholders (meaning: %s) in sql than params (meaning: sublists in data) available

    To fix: check how you're generating data and mercilessly twenty-one every data item.