How to load a sqlite db into memory using pysqlite

Question

It is very important to me.

Thanks.

Answer 1

http://stackoverflow.com/questions/2182774/can-sqlite-load-10m-data-into-the-memory

Answer 2

Follow these steps:

1. sudo easy_install pysqlite
2. open python prompt
3. from pysqlite2 import dbapi2 as sqlite
4. connection = sqlite.connect('test.db')
5. cursor = connection.cursor()
6. cursor.execute('CREATE TABLE names (id INTEGER PRIMARY KEY, name VARCHAR(50), email VARCHAR(50))')

Thats it.

Answer 3

By "into memory", do you mean into the SQL world or into the pure-Python world ? If the latter, here's a snippet to read a sqlite db into a pure-Python dict of namedtuples (records, structs; these make code more readable, especially if you have 10 or 20 columns).

def sqlread( rows, db, tablename, namedtup=None ):
    """ read a sqlite table into a dict of namedtuples
        example:
        Person = namedtuple( "Person", "name city" )
        rows = {}
        db = sqlite3.connect( "my.db" )
        sqlread( rows, db, "Persontable", Person )
        now rows = e.g. { 1: Person( "Jim", "Tokyo" ), 2: ... }
        for key, row in rows.iteritems():
            if row.name == "Jim": ...

    """
    cur = db.cursor()
    cur.execute( "Select * from %s" % tablename )  # where ...
    nrow = 0
    for keyrow in cur.fetchall():
        key, row = keyrow[0], keyrow[1:] 
            # numbers, strings, plain tuples work too --
        row = namedtup._make( row ) if namedtup \
            else row if len(row) > 1 \
            else row[0]
        rows[key] = row
        nrow += 1
    return nrow