Subject: 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:

[email protected]:~$ 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




Programming list archiving by: Enterprise Git Hosting