Why does "c.execute(...)" break the loop?

Question

I am trying to change some data in an sqlite3 file and I my non-existing knowledge in python and google-fu made me end up with this code:

#!/usr/bin/python
# Filename : hello.py

from sqlite3 import *

conn = connect('database')

c = conn.cursor()

c.execute('select * from table limit 2')

for row in c:
    newname = row[1]
    newname = newname[:-3]+"hello"
    newdata = "UPDATE table SET name = '" + newname + "', originalPath = '' WHERE id = '" + str(row[0]) + "'"
    print row
    c.execute(newdata)
    conn.commit()
c.close()

It works like a charm on the first row but for some reason it only runs the loop one time (only the first row in the table gets modified). When I remove "c.execute(newdata)" it loops through the first two rows in the table, as it should. How do I make it work?

Answer 1

It's doing that because as soon as you do c.execute(newdata) the cursor is no longer pointing at the original result set anymore. I would do it this way:

#!/usr/bin/python
# Filename : hello.py

from sqlite3 import *

conn = connect('database')

c = conn.cursor()

c.execute('select * from table limit 2')
result = c.fetchall()

for row in result:
    newname = row[1]
    newname = newname[:-3]+"hello"
    newdata = "UPDATE table SET name = '" + newname + "', originalPath = '' WHERE id = '" + str(row[0]) + "'"
    print row
    c.execute(newdata)
conn.commit()    
c.close()
conn.close()

Answer 2

When you call c.execute(newdata), it changes the cursor c so that for row in c: exits immediately.

Try:

c = conn.cursor()
c2 = conn.cursor()

c.execute('select * from table limit 2')

for row in c:
    newname = row[1]
    newname = newname[:-3]+"hello"
    newdata = "UPDATE table SET name = '" + newname + "', originalPath = '' WHERE id = '" + str(row[0]) + "'"
    print row
    c2.execute(newdata)
    conn.commit()
c2.close()
c.close()