MySQL Python update rows

Question

I am trying to update the data in a row of MySQL which this line of code is throwing this error.

for k in range(city_count):
    cur.execute("UPDATE hqstock SET citylastprice = '%s' WHERE id = '%s'"%(CITYPRICE[k],   tID[k]))

The error that was returned:

File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 201, in execute
    self.errorhandler(self, exc, value)
File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
InterfaceError: (0, '')

This is my table structure.

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| id                 |int(11) unsigned  | NO   | PRI | NULL    |       |
| barcode            | char(15)         | YES  |     | NULL    |       |
| citycurstock       | smallint(6)      | YES  |     | NULL    |       |
| citylastprice      | demical(4,2)     | YES  |     | NULL    |       |
| city               | varchar(60)      | YES  |     | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+

Anybody knows what is wrong with my query statement? or why doesn't this work?

possible duplicate of mysqldb interfaceError and InterfaceError (0, '') — Martijn Pieters
– Martijn Pieters, Commented Oct 25, 2013 at 16:53
Note: don't use string interpolation; use SQL parameters instead: cur.execute("UPDATE hqstock SET citylastprice = %s WHERE id = %s", (CITYPRICE[k], tID[k])); this leaves quoting to the database adapter (safer and more convenient). — Martijn Pieters
– Martijn Pieters, Commented Oct 25, 2013 at 16:54

mrlouhibi · Accepted Answer · 2013-10-25 17:12:57Z

1

try this :

for k in range(city_count):
    cur.execute("UPDATE hqstock SET citylastprice = '%s' WHERE id = '%s'"% (CITYPRICE[k],   tID[k]))
    cur.commit()

edited Oct 25, 2013 at 17:12

answered Oct 25, 2013 at 17:07

mrlouhibi

1214 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2828757 Over a year ago

works like a charm. This helps the second problem i encountered. thank you

Collectives™ on Stack Overflow

MySQL Python update rows

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related