In python, how can I load sqlite db completely into memory before connecting to it?

Question

I have a 100 megabyte sqlite db file that I would like to load into memory before executing sql queries. Is it possible to do this in python?

thank

Answer 1

apsw is an alternative shell for sqlite, which allows you to backup the database to disk before the operation.

From docs :

### ### Backup to memory ### # We will copy the disk database into a memory database memcon=apsw.Connection(":memory:") # Copy into memory with memcon.backup("main", connection, "main") as backup: backup.step() # copy whole database in one go # There will be no disk accesses for this query for row in memcon.cursor().execute("select * from s"): pass 

connection above is your db on disk.

Answer 2

• Get the database in the database (standard material)
• Attach database (file) disk.
• Restore tables / indexes and copy contents.
• Detach the disk database (file)

Here's an example (taken from here ) in Tcl (may be useful for getting a general idea):

 proc loadDB {dbhandle filename} { if {$filename != ""} { #attach persistent DB to target DB $dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom" #copy each table to the target DB foreach {tablename} [$dbhandle eval "SELECT name FROM loadfrom.sqlite_master WHERE type = 'table'"] { $dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM loadfrom.'$tablename'" } #create indizes in loaded table foreach {sql_exp} [$dbhandle eval "SELECT sql FROM loadfrom.sqlite_master WHERE type = 'index'"] { $dbhandle eval $sql_exp } #detach the source DB $dbhandle eval {DETACH loadfrom} } } 

Answer 3

Please note that you may not need to explicitly load the database into SQLite memory. Just start the operating system cache by copying it to zero.

 Windows: copy file.db nul: Unix/Mac: cp file.db /dev/null 

This has the advantage of an operating system that takes care of memory management, especially discarding it if something more important happens.

Answer 4

If you are using Linux, you can try tmpfs , which is a memory based file system.

It is very easy to use:

• install tmpfs to the directory.
• copy the sqlite db file to the directory.
• open it as a regular sqlite db file.

Remember that anything in tmpfs will be lost after a reboot. This way you can copy the db file back to disk if it has changed.