I want to iterate through a bunch of mySQL rows in Python and, after I process each row, alter it:
cursor.execute("SELECT someNonUniqueColumn,hasBeenProccessed
FROM myTable WHERE hasBeenProcessed = 0")
for row in cursor:
# use someNonUniqueColumn for something...
...
# set "hasBeenProccessed" to 1
I'd like to avoid the ugliness of: - having to execute another SQL query each time I process a record, or - fill an array with the ID's of rows as I process them (Both of which would require that I add a unique key to the table)
So, is there a way to "check these off as I go"?
No, there's no way to change a row in-pace. There's a number of reasons for this, one of them is that values returned by a query may be not updatable: think counts, sums and other aggregate functions.
If you do just a very small amount of database interaction, saving IDs and issuing a compound update
statement seems best.
If you issue more than 1-2 queries in your program, do yourself a favor and learn SQLAlchemy or Django ORM. Neither is completely transparent, but either will save you massive amounts of time and debugging pain.