Re: [pysqlite] Save memory database to file/load memory database from file

Jason R. Coombs wrote:
> Gerhard wrote:
>> Malcolm Greene wrote:
>>> Is there a generic, best practice way to save an arbitrary memory
>>> database to file and then later restore that file image to memory?
>> I've always wondered what the use case for that is. Can you tell us why
>> you want to do this?
> 
> I would like to do this for similar reasons.  I have a web application where 
> the application data will be organized in a sqlite database unique to each 
> user.  The user will upload the database when he begins a session and 
> download it when he is done.  I want to manage the data entirely in memory 
> because [...]
> 
> So, to be specific, I don't want to load/dump the in-memory database to/from 
> a file, but instead to/from a stream.
> It appears the API doesn't support what I'm trying to do.  Do you have any 
> suggestions on how I might go about saving an in-memory database or 
> initializing from a previously-saved database without touching the file 
> system?
> 
> I understand I could use the aforementioned file-based technique to 
> accomplish what I need, but that's a clumsy way to get at the raw data, when 
> it presumably already exists in memory. [...]

There's no way to serialize/deserialize the structures of the in-memory 
SQLite VFS.

One approach is to dump/restore the database:

gerhard@baal:~$ python3.0
Python 3.0b2 (r30b2:65080, Jul 24 2008, 10:45:02)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> import sqlite3
 >>> con = sqlite3.connect(":memory:")
 >>> con.execute("create table foo(x, y)")
<sqlite3.Cursor object at 0x8235140>
 >>> con.execute("create index idx_foo on foo(x)")
<sqlite3.Cursor object at 0x82351a0>
 >>> con.execute("insert into foo(x,y) values (3,4)")
<sqlite3.Cursor object at 0x8235140>
 >>> con.execute("insert into foo(x,y) values (4,5)")
<sqlite3.Cursor object at 0x82351a0>
 >>> for row in con.iterdump(): print(row)
...
BEGIN TRANSACTION;
CREATE TABLE foo(x, y);
INSERT INTO "foo" VALUES(3,4);
INSERT INTO "foo" VALUES(4,5);
CREATE INDEX idx_foo on foo(x);
COMMIT;
 >>> s = "\n".join(con.iterdump())
 >>> s
'BEGIN TRANSACTION;\nCREATE TABLE foo(x, y);\nINSERT INTO "foo" 
VALUES(3,4);\nINSERT INTO "foo" VALUES(4,5);\nCREATE INDEX idx_foo on 
foo(x);\nCOMMIT;'
 >>> con2 = sqlite3.connect(":memory:")
 >>> con2.executescript(s)
<sqlite3.Cursor object at 0x8235350>
 >>> con2.execute("select * from foo")
<sqlite3.Cursor object at 0x8235230>
 >>> con2.execute("select * from foo").fetchall()
[(3, 4), (4, 5)]
 >>>

The iterdump() method is currently only available in Python 2.6 and 3.0 
development versions. I haven't backported it to pysqlite, yet.

Here's the 2.6 version:

http://svn.python.org/view/*checkout*/python/trunk/Lib/sqlite3/dump.py?rev=64257

-- Gerhard