Re: [sqlite] Saving an in-memory database to file

[Keith Goodman]

On Sat, May 31, 2008 at 8:57 PM, Keith Goodman <[EMAIL PROTECTED]> wrote:
> On Sat, May 31, 2008 at 8:30 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
>> An example of how to do this with the shell would be helpful.
>>
>> Oddly enough I can do it with applescript; but I can't do it with some other
>> shell tools I'm trying to use.
>>
>> My problem has to do with how to pass multiple lines to a single command.
>> I'm sure it's quite simple but I keep poking around not getting anywhere.
>>
>> This is the applescript version:
>>
>> set this to "echo '
>> .read /a.sql
>> .o stdout
>> .dump
>> .q
>> '|sqlite3 "
>> set this to paragraphs of this
>> set applescript's text item delimiters to "\n"
>> do shell script (this as text)
>> -- result:
>> "BEGIN TRANSACTION;
>> CREATE TABLE Responses (GFUP_ID TEXT,FullQNum TEXT,ResponseNumber
>> TEXT,SurveyVersion TEXT,RecordID TEXT);
>> INSERT INTO \"Responses\" VALUES('36780001', '00.1.01', '1', '2000', '1');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.01', '1', '2000', '2');
>> INSERT INTO \"Responses\" VALUES('36780001', '02.1.02', '', '2000', '3');
>
> Are you trying to dump one database into another, new database? I
> played around at the command line and came up with this. Not sure it
> fits your needs. (I create a database test.db and then dump into a new
> database test2.db.)
>
> $ sqlite3 test.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> create table test (one integer, two integer);
> sqlite> insert into test values (1,2);
> sqlite> select * from test;
> 1|2
> $
> $ echo '.dump' | sqlite3 test.db | sqlite3 test2.db
> $ sqlite3 test2.db
> SQLite version 3.5.7
> Enter ".help" for instructions
> sqlite> select * from test;
> 1|2
> sqlite>

If you like python, here's a python script to dump from memory to
file. (pysqlite doesn't yet have access to sqlite's dump so it loops
through all the tables instead.)

>From http://oss.itsystementwicklung.de/trac/pysqlite/wiki/DumpToDisk

try:
    import sqlite3 as sqlite
except:
    from pysqlite2 import dbapi2 as sqlite

def dump_to_disk(con, filename):
    """
    Dumps the tables of an in-memory database into a file-based SQLite database.

    @param con:         Connection to in-memory database.
    @param filename:    Name of the file to write to.
    """
    cur = con.cursor()
    cur.execute("attach '%s' as __extdb" % filename)
    cur.execute("select name from sqlite_master where type='table'")
    table_names = cur.fetchall()
    for table_name, in table_names:
        cur.execute("create table __extdb.%s as select * from %s" %
(table_name, table_name))
    cur.execute("detach __extdb")

con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table t1(x)")
cur.execute("insert into t1(x) values (1)")
cur.execute("create table t2(x)")
cur.execute("insert into t2(x) values (2)")
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users