python - 执行命令时出现pymysql.err.ProgrammingError 1064

Question

我只是看不出此错误的原因。我在 phpMyAdmin 中尝试了相同的 SQL，它工作得很好，但在 Python 中尝试时失败了。

带有 SQL 查询的 Python 代码:

cursor.execute("UPDATE marketPricesAvg SET avg%sh=(SELECT AVG(price) FROM marketPrices WHERE itemName = %s AND ((NOW() - marketPrices.datetime) < %s) WHERE itemName = %s)", (time, itemName, time_sec, itemName))

错误消息:

Traceback (most recent call last):
  File "/root/marketprices/insert.py", line 42, in <module>
    calculate_avg(itemName, time)
  File "/root/marketprices/insert.py", line 29, in calculate_avg
    cursor.execute("UPDATE marketPricesAvg SET avg%sh=(SELECT AVG(price) FROM marketPrices WHERE itemName = %s AND ((NOW() - marketPrices.datetime) < %s) WHERE itemName = %s)", (time, itemName, time_sec, itemName))
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, u"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 'WHERE itemName = 'Aluminium')' at line 1")

感谢您的任何想法。

Answer 1

您的完全限定查询看起来像这样

UPDATE marketPricesAvg 
SET avg1234 = (
    SELECT AVG(price) FROM marketPrices 
    WHERE itemName = 'Aluminium' AND 
    ((NOW() - marketPrices.datetime) < '100') WHERE itemName = 'Aluminium'
);

编辑:应该是

(NOW() - marketPrices.datetime) < %s) WHERE itemName = %s

最后一个括号放错了位置