pymysql执行MySQL语句`on duplicate key update`报错，如何解决？

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，主机宝贝就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《pymysql执行MySQL语句`on duplicate key update`报错，如何解决？》，主要介绍了，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

pymysql执行mysql语句在 on duplicate key update这句报错不知如何解决

在学习python中练习数据入库时候这里一直提示报错：

# 插入抓取的数据到表中
cursor = cursor(connection)
cursor.executemany('''insert into myfund(fcode,fname,nav,accnav,updatetime)
values(%(fcode)s,%(fname)s,%(nav)s,%(accnav)s,%(updatetime)s) 
on duplicate key update `updatetime`= %(updatetime)s,nav= %(nav)s,accnav= %(accnav)s ''',
result)

报错内容：

traceback (most recent call last):
  file "f:\pythoncode\learn\9pymysql\main.py", line 35, in <module>
    cursor.executemany('''insert into myfund(fcode,fname,nav,accnav,updatetime)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\cursors.py", line 173, in executemany
    return self._do_execute_many(
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\cursors.py", line 211, in _do_execute_many
    rows += self.execute(sql + postfix)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\connections.py", line 1156, in read
    first_packet = self._read_packet()
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
    packet.raise_for_error()
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  file "f:\pythoncode\venv\lbq\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.programmingerror: (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 '%(updatetime)s,nav= %(nav)s,accnav= %(accnav)s' at line 3")

如果不写语句中的 on duplicate key update…则执行没问题

# 插入抓取的数据到表中
cursor = cursor(connection)
cursor.executemany('''insert into myfund(fcode,fname,nav,accnav,updatetime)
values(%(fcode)s,%(fname)s,%(nav)s,%(accnav)s,%(updatetime)s) ''',
result)

但是这个语句放到mysql中执行则正常

mysql> insert into myfund(fcode,fname,NAV,ACCNAV,updatetime)
    -> values('002649','华银能源革新灵活配比混合','1.3110','1.2840','2022-12-25 22:22:33')
    -> on duplicate key update `updatetime`= '2022-12-25 22:22:33',NAV= '1.3110',ACCNAV= '1.2840';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from myfund where fcode = '002649';
+---------+-----------------------------+------+-------+---------------------+
| fcode    | fname                       | NAV   | ACCNAV | updatetime          |
+---------+-----------------------------+------+-------+---------------------+
| 002649   | 华银能源革新灵活配比混合 | 1.311 | 1.284 | 2022-12-25 22:22:33 |
+---------+-----------------------------+------+-------+---------------------+
1 row in set (0.00 sec)

参数是一个dump

今天关于《pymysql执行MySQL语句`on duplicate key update`报错，如何解决？》的内容介绍就到此结束，如果有什么疑问或者建议，可以在主机宝贝公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！