Re: Convert readonly file to :memory: database

Gerhard Häring schrieb:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Thomas Heller wrote:
>> I have a sqlite database in a readonly file.
>> Is it possible to create a :memory: database and quickly initialize it
>> from the file?
> 
> That's possible using a combination of ATTACH/DETACH commands and
> schema introspection. I've attached a draft.

Thanks, Gerhard.  Very useful.  I have attached the slightly improved script,
which handles indices and views and does not try to create internal tables.

----snip----
from pysqlite2 import dbapi2 as sqlite

def copy_database(src_db_name, dest_con):
    """
    Copies contents from one database to another.

    src_db_name: path to source database
    dest_con:    destination pysqlite connection
    """
    try:
        src_con = sqlite.connect(src_db_name)
        dest_con.execute("attach '%s' as srcdb" % src_db_name)
        for row in src_con.execute("select name, type, sql from sqlite_master"):
            if not row[0].startswith("sqlite_"):
                if row[1] == "table":
                    dest_con.execute("CREATE TABLE %s AS SELECT * FROM srcdb.%s" % (row[0], row[0]))
                elif row[1] in ("view", "index"):
                    dest_con.execute(row[2])
                    
        dest_con.execute("detach srcdb")
    finally:
        src_con.close()

if __name__ == "__main__":
    import os, time
    dest = sqlite.connect(":memory:")
    start = time.clock()
    copy_database(r"mydb.db3", dest)
    stop = time.clock()
    print "Copying took %.2f seconds" % (stop - start)
    # print dest.execute("select count(*) from ticket").fetchone()[0]
----snip----


Thomas