Re: DBI and sqlite concurrency

[RichardK]

The whole scheme seems risky to me. What if your program crashes after you've deleted everything from the database but before you've written to your spreadsheet? Everything will be lost. Better to copy the data then delete only the rows you've successfully written.

Without knowing what sort of data you're working with it's difficult to suggest any other options. But you could move your rows into a temporary table, or use an extra column as a flag (set the flag for all rows, then select * where flag == t, delete * where flag == t).

Also it may be worth reading up on sqlite transaction handling.

[MorayJ]

Thanks - I think I didn't make it clear in the post that the moving is happening in the loop before the delete. I'm just using print in this example.

It still stands that if it crashed during that, I wouldn't know which records had made it and which hadn't.

I'm going to look into updating an extra column, as you suggest, and using that.

This will also mean that I can compare the number of records marked in the database as transferred and the number in the spreadsheet so will be a good check

Thanks for your help