Executemany insert on duplicate key update error: Not all parameters were used

Question

So I've been trying to execute this query using python 2.7.15 using mysql.connector. But for some reason it doesn't seem to work and always return the error: Not all parameters were used.

The table updates has one primary key which is 'ID'

This is the query I tried to run this SQL:

sql = "INSERT INTO updates (ID, insert_datetime, egroup, job_state) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE insert_datetime = VALUES(%s), egroup = VALUES(%s), job_state = VALUES(%s);"
mycursor.executemany(sql, jobUpdatesList)

This is how one array from the jobUpdatesList array looks like:

[u'17281725', datetime.datetime(2018, 9, 10, 16, 11, 45, 724000), u'language', u'R', datetime.datetime(2018, 9, 10, 16, 11, 45, 724000), u'language', u'R']

I did this because I tought it needed 7 parameters because I have 7 %s

I also tried to use just 4 parameters like this:

sql = "INSERT INTO updates (ID, insert_datetime, egroup, job_state) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE ID = VALUES(%s), insert_datetime = VALUES(%s), egroup = VALUES(%s), job_state = VALUES(%s);"
mycursor.executemany(sql, jobUpdatesList)

This is how one Array from the jobUpdatesList array looks like:

[u'17281725', datetime.datetime(2018, 9, 10, 16, 18, 9, 268000), u'language', u'R']

but then I get the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s), egroup = VALUES(%s), job_state = VALUES(%s)' at line 1

When I tried to fix this error by replacing VALUES(%s) with '%s' it worked. Except it replaced all fields with %s instead of the value. I also tried it with unquoted %s like: egroup = %s but I get the same error.

I tried lots of things but I can't seem to get it to work. What am I doing wrong?

Answer 1

It isn't possible to use positional parameters to represent actual column names; column names need to be hard coded in the statement for many reasons. But, I don't see any problem with doing that in your case:

INSERT INTO updates (ID, insert_datetime, egroup, job_state)
VALUES (%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE
    insert_datetime = VALUES(insert_datetime),
    egroup = VALUES(egroup),
    job_state = VALUES(job_state);

Or, as Python code:

sql = "INSERT INTO updates (ID, insert_datetime, egroup, job_state) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE insert_datetime = VALUES(insert_datetime), egroup = VALUES(egroup), job_state = VALUES(job_state);"
mycursor.executemany(sql, jobUpdatesList)