Recent site activity

Page authors

  • James King
    June 21, 2012
Recipes‎ > ‎

Loading a SQLite DB into Memory

Note: This applies only to SQLite databases being used with SQLObject.

Overview

Suppose you are working exclusively with SQLite databases for your particular project. If your databases happen to be small (or you happen to have a lot of RAM), you may want to load it into memory. However, this may not always be the most straightforward task to accomplish.

Solution 1: Dump the SQL

Additional Requirements: apsw
One solution (unfortunately, probably far from optimal for larger databases) is to dump the SQL from a file-db into a memory-db. Whenever you're done working in memory, you can re-dump the SQL back into the file DB.

If you want to do this, you'll probably want to use the following code:

Code (Python 2.7.1, SQLObject 1.0.0)

# by James King (2012)
import os;
import apsw;
from cStringIO import StringIO;
from sqlobject import *

def getFileDump(file_):
    sqldump = StringIO();
    shell = apsw.Shell(stdout = sqldump, args=(file_,));
    shell.process_command(".dump");
    shell.db.close(True);
    return sqldump.getvalue();

def getMemoryDump(conn):
    dump = "";
    trueConnection = conn.getConnection();
    for line in trueConnection.iterdump():
        dump+= line;
    return dump;

def load(conn, file_):
    if os.path.exists(file_):
        dump = getFileDump(file_ = file_);
        conn.getConnection().executescript(dump);
    else:
        raise Exception();

def save(conn, file_):
    dump = getMemoryDump(conn);
    fileconn=apsw.Connection(file_);
    cursor = fileconn.cursor();
    cursor.execute(dump);
    fileconn.close(True);

connstr = 'sqlite:/:memory:';
conn = connectionForURI(connstr);
sqlhub.processConnection = conn;

load(conn, "mydb.db");
print "Loaded database from file...";
save(conn, "mydb.db");
print "Saved database to file...";


Comments