所以我有以下几段Python代码:

id=10001
ip="10.0.0.1"
up=[80, 5555]
down=0

sql = "INSERT INTO ip(id, ip, open, closed) VALUES (%d, '%s', '%s', '%s') ON DUPLICATE KEY UPDATE open=VALUES('%s');" % (id, ip, up, down, up)


当我运行它时,出现以下错误:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[80, 5555]')' at line 1")


注意:当我删除:

ON DUPLICATE KEY UPDATE open=VALUES('%s');


它运行得很好

关于可能出什么问题的任何想法?

最佳答案

VALUES()中删除​​单引号。参数是列名:

INSERT INTO ip(id, ip, open, closed)
    VALUES (%d, '%s', '%s', '%s')
    ON DUPLICATE KEY UPDATE open = VALUES(%s);


但是,我希望正确的查询是:

INSERT INTO ip(id, ip, open, closed)
    VALUES (%d, '%s', '%s', '%s')
    ON DUPLICATE KEY UPDATE open = VALUES(open);


注意:open是关键字,尽管它不是保留字。我认为这对于列名而言是一个糟糕的选择。

关于python - python mysql DUPLICATE KEY UPDATE错误,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31644137/

10-16 16:48