Re: Transaction isolation

Pawel Lewicki wrote:
> Hi,
> I tried using
> 
> con = sqlite.connect(..., isolation_level=None)
> 
> but inserting is incredibly slow and making hard drive busy.

Sure. You're working in autocommit mode then. That means every DML 
(insert, update, delete, replace) works in its own transaction, and 
after each command, the database engine has to flush the cache to disk. 
That of course is slow.

Using pysqlite as it's meant to be used and calling the connection's 
commit() method whenever you have consistent data is recommended.

If you do mass inserts for importing data, you can make your code faster 
if you commit only every 1000 inserts or so.

If after all possible optimizations (not too many commits, using 
prepared statements, etc.) you still *really need* to improve 
performance of bulk imports, reading

http://www.sqlite.org/pragma.html

and turning on PRAGMA synchronous = OFF;

This implies the risk of data corruption should a power failure happen, 
but for initial imports this is IMO worth it.

-- Gerhard