0

I have the following code:

idDiff = rupleID[0] - 19
cursor.execute("SELECT price FROM table WHERE id BETWEEN '%s' AND '%s';", (idDiff,rupleID[0]))
allPrice = cursor.fetchall()
rupleAprice = [float(i[0]) for i in allPrice]
movAvg = sum(rupleAprice)/20
movlAvg = str(movAvg)
cursor.execute ("UPDATE trading SET movingavg = '%s' ORDER BY id DESC LIMIT 1;", (movlAvg))
conn.commit()
print ("Successful Input Of: " + movlAvg)

What is happening:

Whenever the movingavg column is updated - I receive %s in the row, not whatever number is printed below with "Successful Input Of: (someNumber)

The '%s' works fine in the other 4 functions being called - this is the only row and column that is displaying %s in the table "trading".

Here are some print out's from the python 3 script and of the SQL table:

Successful Input Of: 183.68449999999999
Successful Input Of: 183.68449999999999
Successful Input Of: 183.68449999999999
Successful Input Of: 183.68449999999999
Successful Input Of: 183.685
Successful Input Of: 183.68449999999999
^C-- Goodbye! --

  ID |   SomeData  |   movingAvg

| 51 | 182.50000000 | %s        | NULL | NULL  | NULL   | 1516176541 |
+----+--------------+-----------+------+-------+--------+------------+
51 rows in set (0.00 sec)

Anyone shed some light on the issue here? Any pointers would be appreciated.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
paxton91michael
  • 77
  • 10
  • Think you need to use `'?'` instead of `'%s'`, the latter is for string formatting. Which you're not doing :) – The Pjot Jan 17 '18 at 08:23
  • 2 things: *don't add quotes around placeholders* (the driver does that for you), and you have to pass your arguments in a sequence. `(movlAvg)` is `movlAvg` with extra brackets. It is the comma that makes a tuple tuple. And is the column `movingAvg` a string type in your DB? If so, why? – Ilja Everilä Jan 17 '18 at 08:24
  • Will give it a shot ... haven't ran into that solution, anywhere. Thank you for the input, will update. – paxton91michael Jan 17 '18 at 08:24
  • @Pjot Unfortunately mysql-connector uses `%s` as placeholders, which is a source of endless confusion: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html – Ilja Everilä Jan 17 '18 at 08:24
  • Ahh yes, that is confusing indeed :) – The Pjot Jan 17 '18 at 08:29
  • @IljaEverilä Do you recommend removing the quotes for %s? ... I have tried that as well and I receive a syntax error from SQL. I have also tried (%s) and also received a syntax error My WebSocket uses the same '%s' and is the data you see printed in column two – paxton91michael Jan 17 '18 at 08:30
  • It's not so much as a recommendation, but [how you're supposed to do it](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html). Getting syntax errors would hint that you're doing something else wrong as well. Perhaps you're actually formatting the strings manually somewhere, instead of passing the arguments to `execute()`. – Ilja Everilä Jan 17 '18 at 08:32
  • @Pjot ... that is correct on the %s for the placeholder, as I mentioned without the quotes you will receive a syntax error (below) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right to use near '%s) ORDER BY id DESC LIMIT 1' at line 1 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 '%s ORDER BY id DESC LIMIT 1' at line 1 – paxton91michael Jan 17 '18 at 08:33
  • Ah you get it there. That's caused by the fact that you're passing a scalar value as "arguments", not a sequence. So add the missing comma in `(movlAvg)`. And remove the quotes. – Ilja Everilä Jan 17 '18 at 08:40
  • A freaking comma, lol. That did it, needed a tuple value as the article mentions- thank you for your help... oh and I'll remove my quotes – paxton91michael Jan 17 '18 at 15:14

0 Answers0