I am using MySQLdb with python. I need to do some calculation in python using
variable values in an existing table, and then return the output back to the a
MySQL table to continue data manipulation in MySQL. Say, the data table
my_table contains fields A, B, C, x and y, I wrote a python function called
fixit(lstX, lstY) calculate and returns a list of Z values. Ideally I would
like to "paste" the values back to the my_table as an additional column, so to
proceed with next step where x, y, z fields are needed.
Right now I use cursor.executemany to insert Z into a new empty table, and
since row number is not an default variable in MySQL (right?) I generate an
auto increasing field (which serves as row number) in both the original
my_table and the new table, and then join the two table using the auto
increasing field. Is there an nicer way to do this? Thanks !
UPDATE my_table SET z=%s WHERE x=%s and y=%s
with your execute_many.
Thank you for the response. I thought about that at first, but concerned that
using WHERE x=%s and y=%s will be slow when the table gets big.
add an index on the columns if necessary?