Syntax error with parameterized queries after switching from pyodbc to pymssql

Question

I have 13-15MB raw files to load its data into DB, and it takes around 50-55mins to insert around 30k-35k rows using executemany with pyodbc and it worked fine but its processing time is high.

For testing the performance I tried pymssql but it is showing syntax error here

x = [('a',1),('b',2).... ]  # inserting only 999 rows at a time
qry = "INSERT INTO ["+tablename+"] VALUES({}) ".format(placeholders)
cursor.executemany(qry,x)

print qry
 # INSERT INTO [my_T] VALUES(?,?,?,?,?,?,?,?,?,?,?,?...)

AT executemany() it is thowing error as

Error:

Error in loadData (102, "Incorrect syntax near '?'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n"

those are (?,?,....) are around 89 columns.. the same syntax worked with pyodbc — Shivkumar kondi, Jul 19 '17 at 13:19
For pymssql you will need to change the placeholders from `?` to `%s`. — Gord Thompson, Jul 19 '17 at 14:05
@GordThompson Great thanks it solved my problem,Thanks again — Shivkumar kondi, Jul 20 '17 at 06:13

score 3 · Accepted Answer · answered Jul 21 '17 at 23:03

Python's DB-API 2.0 specification defines several parameter styles that implementers can use.

pyodbc adopted the "qmark" style which is the most common style for ODBC

INSERT INTO tablename (col1, col2) VALUES (?, ?)

while pymssql opted for the "format" style

INSERT INTO tablename (col1, col2) VALUES (%s, %s)

Note that for historical reasons pymssql supports %d as well as %s but we don't need to use %s for strings and %d for numbers; %s works in all cases and is the preferred placeholder.

Thanks for the explaination – Shivkumar kondi Jul 24 '17 at 07:24 — Shivkumar kondi, Jul 24 '17 at 07:24

Syntax error with parameterized queries after switching from pyodbc to pymssql

1 Answers1