Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In python, how can I load a sqlite db completely to memory before connecting to it? [duplicate]

I have a 100 mega bytes sqlite db file that I would like to load to memory before performing sql queries. Is it possible to do that in python?

Thanks

like image 315
relima Avatar asked Sep 29 '10 23:09

relima


People also ask

Does SQLite load database into memory?

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory. The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:".

How fetch all data from SQLite database in Python?

SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.


4 Answers

apsw is an alternate wrapper for sqlite, which enables you to backup an on-disk database to memory before doing operations.

From the 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 on-disk db.

like image 65
Ryan Ginstrom Avatar answered Oct 02 '22 19:10

Ryan Ginstrom


  1. Get an in-memory database running (standard stuff)
  2. Attach the disk database (file).
  3. Recreate tables / indexes and copy over contents.
  4. Detach the disk database (file)

Here's an example (taken from here) in Tcl (could be useful for getting the general idea along):

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}
    }
}
like image 32
ChristopheD Avatar answered Oct 02 '22 20:10

ChristopheD


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

It's very easy to use it:

  1. mount tmpfs to a directory.
  2. copy sqlite db file to the directory.
  3. open it as normal sqlite db file.

Remember, anything in tmpfs will be lost after reboot. So, you may copy db file back to disk if it changed.

like image 26
animalize Avatar answered Oct 02 '22 18:10

animalize


Note that you may not need to explicitly load the database into SQLite's memory at all. Simply prime your operating system disk cache by copying it to null.

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

This has the advantage of the operating system taking care of memory management, especially discarding it if something more important comes along.

like image 25
Roger Binns Avatar answered Oct 02 '22 19:10

Roger Binns