sqlite in memory

[Richard Gerd Kuesters]

hi!

curiosity [that may help me, lol]: is there a way to create a sqlite database in memory, then save it to disk after initial inserts are done? let's say I have around 2 gb of data to insert and ... it takes like forever. i still have not convinced my boss to buy a ssd, so ... :D


cheers,
richard.

[Petr Viktorin]

You can tell SQLite to disable syncing and journalling, it should get
very fast then:

if session.connection().dialect.name == 'sqlite':
session.connection().execute("PRAGMA synchronous=OFF")
session.connection().execute("PRAGMA journal_mode=OFF")

Of course, your data will be corrupted if the load doesn't exit
cleanly, but for the initial inserts it's not that important -- you
can just delete the DB and start again.

SQLite pragma docs are here: http://www.sqlite.org/pragma.html

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Richard Gerd Kuesters]

Hmmm, never knew of that. All my "constraints" are already predefined and inserts are in order so I won't get a broken FK. I'll try that too see if it increases the speed (decreasing my headaches per build, lol).

Thanks a lot, Petr!

[Richard Gerd Kuesters]

Petr, just found something intesting that may work for anyone:

http://blog.marcus-brinkmann.de/2010/08/27/how-to-use-sqlites-backup-in-python/

I was able do create a database totally in memory and them just "dump" it to a file. The process now takes about 5 minutes, against one hour+ that it took earlier.

Cheers :)
Richard.