CODEWITHSUNDEEP
python-2.7sql-server-2008pyodbcpymssqlexecutemany
Shivkumar kondi
Shivkumar kondi

Reputation: 6762

Syntax error with parameterized queries after switching from pyodbc to pymssql

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"

Upvotes: 0

Views: 1939

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

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.

Upvotes: 3

Related Questions