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

py执行my在 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) 
on duplicate key update `updatetime`= %(updatetime)s,nav= %(nav)s,accnav= %(accnav)s ''',
result)

登录后复制

报错内容：

traceback (most recent call last):
  file "f:pythoncodelearn9pymysqlmain.py", line 35, in <module>
    cursor.executemany('''insert into myfund(fcode,fname,nav,accnav,updatetime)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlcursors.py", line 173, in executemany
    return self._do_execute_many(
  file "f:pythoncodeenvlbqlibsite-packagespymysqlcursors.py", line 211, in _do_execute_many
    rows += self.execute(sql + postfix)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlcursors.py", line 148, in execute
    result = self._query(query)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlcursors.py", line 310, in _query
    conn.query(q)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlconnections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlconnections.py", line 775, in _read_query_result
    result.read()
  file "f:pythoncodeenvlbqlibsite-packagespymysqlconnections.py", line 1156, in read
    first_packet = self._read_packet()
  file "f:pythoncodeenvlbqlibsite-packagespymysqlconnections.py", line 725, in _read_packet
    packet.raise_for_error()
  file "f:pythoncodeenvlbqlibsite-packagespymysqlprotocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  file "f:pythoncodeenvlbqlibsite-packagespymysqlerr.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

以上就是py执行MySQL语句`on duplicate key update`报错，如何解决？的详细内容，更多请关注米云网其它相关文章！