Py+SQLite or other (big output) ?

[Gerhard Häring]

Anonymous Coward DurumDara wrote:
> [...]
> So I want to use one database file - but I want to protect it.
> How to do it with SQLite ?
> I see that solutions:
> - 1. I use transactions.
> - 2. I create full copy of database after every bigger transation.
> - 3. Shadow file ???
> - 4. Mirror database (this is problematic to synch.).
>  
> The transactions are very good things, but does not protect the database 
> from injuring.

Actually, SQLite transactions do protect you from "bad things 
happening". If a transaction suceeds, it also does an fsync() call so 
that the data gets written to disk and does not remain in the database's 
or operating system's write cache.

And of course, the main feature is that if you group your SQL statements 
right, then the database is always in a consistent state, even after a 
crash.

> The copy operation is better, but very decrease the processing speed, 
> because the result db grow fast, and copy of 1/2,2/3 GBs is slow, and 
> not too good.

and doesn't help the real problem at all. If your application or 
computer crashes during a copy, from where do you recover from?

> Have SQLite any solution to this problem ?

Transactions :-)

And don't set PRAGMA synchronous = OFF; to increase the speed of your 
application, because then your database *will* be corrupted during a 
crash. (*)

-- Gerhard

(*) Full details at http://www.sqlite.org/pragma.html for PRAGMA 
synchronous.