How to create multiple ":memory:" databases in sqlite3

Question

I am trying to create multiple in-memory databases with sqlite3 (if possible). For on-disk databases, I would do:

import sqlite3 

db1 = sqlite3.connect('/mnt/tmp/db1.db')
db2 = sqlite3.connect('/mnt/tmp/db2.db')
db3 = sqlite3.connect('/mnt/tmp/db3.db')

If I instead do:

db1 = sqlite3.connect("file::memory:?cache=shared")
db2 = sqlite3.connect("file::memory:?cache=shared")
db3 = sqlite3.connect("file::memory:?cache=shared")

Will this result in three separate in-memory databases? This seems to create three connections to a shared in-memory database, which is not what I want.

I then proceed to create cursors on the databases (on-disk or in-memory) with:

cur_db1 = db1.cursor()
cur_db2 = db2.cursor()
cur_db3 = db3.cursor()

If multiple in-memory databases are not possible, is my best choice to use RAM-disk databases (as in my first code block), but then to execute "PRAGMA journal_mode=MEMORY" on these connections?

What do you mean "seems to create"? What do you do to verify and/or test? — C Perkins
– C Perkins, Commented Oct 31, 2019 at 19:00
If I create the three db connections using "file::memory:?cache=shared", then upon trying to execute an insert on a table created in one of the databases, I get OperationalError('database is locked',) — pjw
– pjw, Commented Oct 31, 2019 at 19:02

C Perkins · Accepted Answer · 2021-02-07 19:19:51Z

11

As explained in online docs, specifying a shared cache using the URI format like you are using will cause all connections in the same process to use the same database. If you want three separate in-memory instances, then use a named in-memory database using file:<DB_Name>?mode=memory&cache=shared as the connection string.

From the webpage...

If two or more distinct but shareable in-memory databases are needed in a single process, then the mode=memory query parameter can be used with a URI filename to create a named in-memory database:

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

When an in-memory database is named in this way, it will only share its cache with another connection that uses exactly the same name.

edited Feb 7, 2021 at 19:19

answered Oct 31, 2019 at 19:35

C Perkins

3,8854 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create multiple ":memory:" databases in sqlite3

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related