pymysql.err.ProgrammingError 1064 when executing command

Question

I just can't see a reason for this error. I have tried the same SQL in phpMyAdmin and it works perfectly fine, but fails when trying from Python.

Python code with SQL query:

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))

Error message:

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")

Thank you for any ideas.

Answer 1

Your query fully qualified would look something like

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

Edit: Should be

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

The final parentheses is misplaced