Yesterday I spent a couple of hours trying to find the best way of updating multiple records in MySQL db using Python. Unfortunately could not find any really elegant way, as a result I ended up with using
'INSERT ON DUPLICATE KEY UPDATE'.
Sharing my code here, hope it will save you time. If you know any better way of updating multiple rows in mysql db with python, let me know in the comments.
So, let’s say I have a
students table wich looks like this:
And let’s say I want to capitalize each word in the
name column (in reality I needed to apply more complex transformation, but will be talking about capitalizing for simplicity).
Here is the code that is doing a bulk update:
Basically what I am doing here is:
- getting data from the DB table using
- storing it into dataframe (to do so need to import
- applying the transformation I need (in this case I am capitalizing each word in the
- converting the transformed dataframe to tuples (so that the data will look like
(1, 'Cate Blanchett'), (2, 'Sia Furler'), (3, 'Priyanka Chopra'))
- updating the table using ‘INSERT INTO’ … ‘ON DUPLICATE KEY UPDATE’ statement
This is how the database table looks like after I ran my script:
Easy. But I still feel there should be a better way…