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

user_3185051 0
Viewed 4428

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?

syntax-error mysql python
1 Answers

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)
user_18632290
Related