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 zuojiankuohaophpcnmoduleyoujiankuohaophpcn
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中执行则正常
mysqlyoujiankuohaophpcn insert into myfund(fcode,fname,NAV,ACCNAV,updatetime)
-youjiankuohaophpcn values('002649','华银能源革新灵活配比混合','1.3110','1.2840','2022-12-25 22:22:33')
-youjiankuohaophpcn 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
mysqlyoujiankuohaophpcn 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
