MySQL is reporting an error when I use parameters in my INSERT query. I seem to be misunderstanding the documentation.
Code:
sqlCommand = "INSERT INTO role (role_id, name, notes) VALUES (%s,'%s','%s') ON DUPLICATE KEY UPDATE name='%s',notes='%s';"
sqlParams = []
for role in roleList:
sqlParams.append(
(role['role_id'],role['name'],role['notes'],role['name'],role['notes'])
# (role['role_id'],role['name'],role['notes'])
)
break
sqlConnect = mysql.connector.connect(host=mysqlHost,port=mysqlPort,user=mysqlUser,password=mysqlPass,database=mysqlDB)
sqlCursor = sqlConnect.cursor(prepared=True)
try:
sqlCursor.executemany(sqlCommand,sqlParams)
finally:
sqlCursor.close()
sqlConnect.close()
The error:
[(1, âdefaultâ, âPlaceholder role/category, feel free to editâ, âdefaultâ, âPlaceholder role/category, feel free to editâ)]
Traceback (most recent call last):
File âtest-stuff.pyâ, line 187, in
updateDBRoles(pfRoles)
File âtest-stuff.pyâ, line 178, in updateDBRoles
sqlCursor.executemany(sqlCommand,sqlParams)
File â/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.pyâ, line 991, in executemany
self.execute(operation, params)
File â/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.pyâ, line 972, in execute
msg="Incorrect number of arguments executing prepared "
mysql.connector.errors.ProgrammingError: 1210: Incorrect number of arguments executing prepared statement