MySQLdb UPDATE does nothing

[John Moore]

Hi,

I normally work with Java but I'm interested in using Python as well,
particularly for little tasks like doing some massaging of data in a
MySQL database. Below is my first attempt. I'm sure it's inelegantly
written, but my main concern is that the UPDATE sql doesn't actually
work, and I can't understand why. No error is returned, it's just that
the update does not take place. The SQL itself is fine, though - if I
instead write the SQL to a file I can use it from the mysql command line
and it does all the updates just fine. What have I missed?

John

=============== =============== =====

#!/usr/bin/python

# import MySQL module
import MySQLdb

# connect
db = MySQLdb.connect (host="localhos t", user="john",
passwd="xxx",db ="test_db")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute( "SELECT DISTINCT product_id FROM product_attribu te")

# get the resultset as a tuple
result = cursor.fetchall ()

# iterate through resultset
for record in result:
sql="SELECT id FROM product_attribu te WHERE product_id =
"+str(recor d[0])
print " "+sql
cursor.execute( sql)
result2=cursor. fetchall()
index=0
for record2 in result2:
sql="UPDATE product_attribu te SET index_column = "+str(index )+"
WHERE id = "+str(recor d2[0])
print " "+sql
cursor.execute( sql)
index+=1

cursor.close()

[David Wilson]

>> sql="UPDATE product_attribu te SET index_column = "+str(index )+" WHERE id = "+str(recor d2[0])

..
cursor.execute( sql)

To allow the DB-API adaptor to correctly take care of value conversion
and SQL escaping for you, this should be written as:

cursor.execute( "UPDATE product_attribu te SET col1 = %s WHERE id = %s",
(index, record2[0]))
As for why the UPDATE has no effect, which version of MySQL are you
using?
David.