Increment row MySql Python

Question

I am trying to increment row in MySQL database like this

rows = cursor.fetchall()

i = 0
for row in rows:
    cursor.execute("UPDATE Table SET order = %s WHERE name = 'JAMES'", (i,))
    db.commit()
    i += 1

But at the end order for all of the items is 19, and the length of rows is 20. How can I have it go form 0 to 19, I though if I commit() after each loop this would be solved?

Thanks

Without where clause, the UPDATE statement will update all records. — falsetru
– falsetru, Commented Dec 16, 2015 at 7:05
@falsetru its there ill add it to the question I just left it out by accident — iqueqiorio
– iqueqiorio, Commented Dec 16, 2015 at 7:05
Is it always 'JAMES'? Then, the same records will be updated multiple times. — falsetru
– falsetru, Commented Dec 16, 2015 at 7:06
@falsetru why does it matter what the WHERE clause is? No it is not always james, it is getting a different row every time — iqueqiorio
– iqueqiorio, Commented Dec 16, 2015 at 7:07
Do you mean something like this? cursor.execute("UPDATE Table SET order = %s WHERE name = %s", (i, row.name)). WHERE clause matters. — falsetru
– falsetru, Commented Dec 16, 2015 at 7:09

falsetru · Accepted Answer · 2015-12-16 07:17:22Z

2

Maybe you meant something like this (WHERE clause change for rows):

rows = cursor.fetchall()
for i, row in enumerate(rows):
    cursor.execute("UPDATE Table SET order = %s WHERE name = %s", (i, row.name))
    db.commit()

Otherwise, order fields or one record is updated multiple times.

answered Dec 16, 2015 at 7:17

falsetru

371k69 gold badges770 silver badges660 bronze badges

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

Collectives™ on Stack Overflow

Increment row MySql Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related