I've decided that I'm not fond of the automatic filing and renaming provided by Mendeley, but have found that it keeps track of everything in an sqlite database that I can easily read and modify from Python.
My question is, if I'm going to iterate over the rows of a table containing file paths and hashes that are serving as identifiers that are used elsewhere in the database, what is an appropriate pattern to use to update the paths as I move along, check if the file needs renaming, and then want to update the row in the db as I move the file.
I started putting together some Python to do this, but it seems to me that I probably need to do a query upfront to get all the rows I'm going to later iterate through, and then subsequently do REPLACE or UPDATE statements for files I've decided to move.
I'm not familiar with the internals of the sqlite3 module, but I presume it would be a bad idea to do REPLACE/UPDATEs while iterating on the cursor used for the original select.
Is there another way to do this that doesn't require going back with REPLACE/UPDATEs? I do need to do queries from other tables, using the file hash to get some other metadata in order to construct the rename.