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:pythoncodelearn9pymysqlmain.py", line 35, in <module> cursor.executemany('''insert into myfund(fcode,fname,nav,accnav,updatetime) file "f:pythoncodevenvlbqlibsite-packagespymysqlcursors.py", line 173, in executemany return self._do_execute_many( file "f:pythoncodevenvlbqlibsite-packagespymysqlcursors.py", line 211, in _do_execute_many rows += self.execute(sql + postfix) file "f:pythoncodevenvlbqlibsite-packagespymysqlcursors.py", line 148, in execute result = self._query(query) file "f:pythoncodevenvlbqlibsite-packagespymysqlcursors.py", line 310, in _query conn.query(q) file "f:pythoncodevenvlbqlibsite-packagespymysqlconnections.py", line 548, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) file "f:pythoncodevenvlbqlibsite-packagespymysqlconnections.py", line 775, in _read_query_result result.read() file "f:pythoncodevenvlbqlibsite-packagespymysqlconnections.py", line 1156, in read first_packet = self._read_packet() file "f:pythoncodevenvlbqlibsite-packagespymysqlconnections.py", line 725, in _read_packet packet.raise_for_error() file "f:pythoncodevenvlbqlibsite-packagespymysqlprotocol.py", line 221, in raise_for_error err.raise_mysql_exception(self._data) file "f:pythoncodevenvlbqlibsite-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
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
