问题描述
限时送ChatGPT账号..所以我一直在尝试使用 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屋!
更多推荐
[db:关键词]
发布评论