executemany

having syntax error trying to use on duplicate

sql_insert_query = "insert into TABLE1 (building, course, description, course_type, course_type_desc, dual_credit) 
VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE building = VALUES(building), course = VALUES(course), description = VALUES(description), course_type = VALUES(course_type), course_type_desc = VALUES(course_type_desc), dual_credit = VALUES(dual_credit);"

cursor.executemany(sql_insert_query, listCourse)

pyodbc.ProgrammingError: ('42000', u"[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'ON'. (156) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)")

This one below only works, but adds duplicate when running again.

"insert into TABLE1 (building, course, description, course_type, course_type_desc, dual_credit) VALUES (?, ?, ?, ?, ?, ?)"
Solution

  • I have resolved it by tile with tuple

    listCourse = numpy.tile(courses, 2) listCourse = map(tuple,numpy.tile(courses, 2))