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?

Answer 1

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.