pymssql executemany insert valueerror

Question

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?

Can you post results of `print(len(data))` and `print(set(map(len, data)))`? — Nigel Tufnel, Jul 28 '14 at 14:39
len(data)==602 set(map(len,data)==set([11,12,13,15,16,17,18,19,20,21]) — Synal, Jul 28 '14 at 15:31

Nigel Tufnel · Accepted Answer · 2014-07-28T15:48:25.603

1

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.

edited Jul 28 '14 at 15:48

answered Jul 28 '14 at 14:46

Nigel Tufnel

11,146
4
35
31

pymssql executemany insert valueerror

1 Answers1