Executemany 插入重复键更新错误:未使用所有参数 [英] Executemany insert on duplicate key update error: Not all parameters were used

问题描述

所以我一直在尝试使用 mysql.connector 使用 python 2.7.15 执行此查询.但是由于某种原因它似乎不起作用并且总是返回错误:未使用所有参数.

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.

表更新有一个主键，即ID"

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

这是我试图运行此 SQL 的查询:

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)

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']

我这样做是因为我认为它需要 7 个参数，因为我有 7 个 %s

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

我也尝试只使用 4 个这样的参数:

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)

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']

但随后出现以下错误:

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

当我尝试通过将 VALUES(%s) 替换为%s"来修复此错误时，它起作用了.除了它用 %s 而不是值替换了所有字段.我也用不带引号的 %s 尝试过，比如:egroup = %s 但我得到了同样的错误.

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?

推荐答案

无法使用位置参数来表示实际的列名；出于多种原因，需要在语句中对列名进行硬编码.但是，在您的情况下，我认为这样做没有任何问题:

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);

或者，作为 Python 代码:

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)

这篇关于Executemany 插入重复键更新错误:未使用所有参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！