Monday, January 26, 2009

Multi row transaction capabilities

The project in which I wrote a python script to process rows in an Excel spreadsheet interfacing with a MySQLdb and fronted on the web end using django is coming to a close. There is just one more requirement to go. They want rollback capabilities with the ability to completely rollback all actions on a particular excel file if one of the rows in the file buffs up or errors out. This is so that they can correct just that one row in that Excel file then feed the same Excel file to my Python process script without worry about the sanctity of the data of the rest of the rows.

This means that I have to somehow implement a multi-row rollback functionality or application level transaction. Python's MySQLdb library supports rollback but only on each transaction. I need a "all or nothing" like functionality. This is something new for me, so I scouted around on google to get some inspiration or to see if someone else had done the same thing. I found this small transaction class by Harald Hoyer at his site. Planning to try out his stuff to see if they can fulfill my requirements. Instinctively, I feel the final solution that I come up with will be a bit different.

The only other way I can see this happening is to maintain another database table keeping track of all actions that are performed at the row level in the input Excel Forms eg: row1 : insert, row2: update. Then if the script detects an error or Exception while processing the form it will perform the reciprocal or rollback to all of the actions that have been performed on the data on the Excel file. So far whatever I have is still being worked out in the noggin' coming close to being implemented.

Update:
Something that I seem to have overlooked is that since I am using django for the web front maybe I can also use it's ORM at the backend. It all depends on how much work it would be to ORM-ize my existing code base.
Post a Comment