[SOLVED] How to select a value from each sql row to use in a function and then update each row

How to select a value from each sql row to use in a function and then update each row

I need to update each row in a table with a value returned by a function. This function uses a separate columns value of the same row. I understand how to select a columns value from a row and how to update one but not how to do both for every row in a table.

For example if I had a table with employees information like their birthdate, how would I use python to take that birthdate, run it through a function to determine their age and then update that specific row's age column with that determined age. I know how to do this for one row, but not for every row in the table that does not yet have a value for their age.

cursor = connection.cursor()


for row in cursor.execute('SELECT * FROM Table' WHERE Birthdate != ''):
   sql_update_query = """Update name-of-table set age = %s where 
   age = '' """
   age = CurrentYear - yearBorn
   input = (age)
   cursor.execute(sql_update_query, input)

Sorry for the sloppy code but here is an example of what I have tried to do ^

Solution

You need to make the update specific to the current row of the loop. Use the primary key of the table for this -- I've assumed it's called id in my code below.

sql_update_query = """Update employees set age = %s where id = %s"""

for id, birthdate in cursor.execute('SELECT id, BirthDate FROM employees' WHERE Birthdate != ''):
   age = calculate_age(birthdate)
   cursor.execute(sql_update_query, (age, id))