Dumping all the sql statements as backup

[Jack]

On 07/25/2012 09:56 AM, andrea crotti wrote:
> I have some long running processes that do very long simulations which
> at the end need to write things on a database.
>
> At the moment sometimes there are network problems and we end up with
> half the data on the database.
>
> The half-data problem is probably solved easily with sessions and
> sqlalchemy (a db-transaction), but still we would like to be able to
> keep a backup SQL file in case something goes badly wrong and we want to
> re-run it manually..
>
> This might also be useful if we have to rollback the db for some reasons
> to a previous day and we don't want to re-run the simulations..
>
> Anyone did something similar?
> It would be nice to do something like:
>
> with CachedDatabase('backup.sql'):
>     # do all your things
>   
Since you know the content of what the sql code is, why not just build
the sql file(s) needed and store them so that in case of a burp you can
just execute the code file. If you don't know the exact sql code, dump
it to a file as the statements are constructed... The only problem you
would run into in this scenario is duplicate data, which is also easily
solvable by using transaction-level commits to the db.