MySQL bulk update statement in Python

I wonder how I can do a bulk update using MySQL and Python. My requirement is like

for x in range(0,100):
NNN = some calculation
ABC = some calculation
query = update XXX set value = NNN, name = ABC where id = x
con.execute(query)

The problem here is it is executing 100 DB queries and makes the update process slow. Is it possible to have one DB call? I know bulk insert syntax on MySQL but it s开发者_开发百科eems to be non-working with the update statement.

According to the MySQL update manual, you can do

UPDATE tableX SET NNN = calculation, 
   ABC = (SELECT whatever FROM whatevertable WHERE condition) 
 WHERE x >=0 AND x <=100

I agree we can do the calculation on the query. But it is not flexible for change and maintenance. Going for a MYSQL Stored Procedure is a nice option. But I have solved it in python scripts using the following steps. 1. Created a temp table to hold columns A, B and id. Python & MySQL support Bulk insert (see petefreitag.com/item/379.cfm). 2. issue a simple update statement to update the fields using id in where condition. Thats fixed my problem