CODEWITHSUNDEEP
pythoncachingmemorysqlite
smontanaro
smontanaro

Reputation: 1737

:memory: database with shared cache is not so temporary

The SQLite docs for in-memory databases (https://www.sqlite.org/inmemorydb.html) indicate that to share :memory: across connections, you should use URI naming and set cache=shared, like so (C API):

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

I tried this from Python 2.7.14, with these attributes:

>>> sqlite3.version
'2.6.0'
>>> sqlite3.apilevel
'2.0'
>>> sqlite3.sqlite_version
'3.20.1'

What I discovered was that the database isn't quite as transient as I would have expected. Here's a session.

First, create a database and insert a row...

% python
Python 2.7.14 |Anaconda, Inc.| (default, Dec  7 2017, 17:05:42) 
[GCC 7.2.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.Connection("file::memory:?cache=shared")
>>> curs = conn.cursor()
>>> curs.execute("select * from sqlite_master").fetchall()
[]
>>> curs.execute("create table XYZ (x int, y int, z varchar)")
<sqlite3.Cursor object at 0x7ff4b2a06110>
>>> curs.execute("insert into XYZ values (?, ?, ?)", (1, 2, '3'))
<sqlite3.Cursor object at 0x7ff4b2a06110>
>>> curs.execute("select * from sqlite_master").fetchall()
[(u'table', u'XYZ', u'XYZ', 2, u'CREATE TABLE XYZ (x int, y int, z varchar)')]
>>> curs.execute("select * from XYZ").fetchall()
[(1, 2, u'3')]
>>> conn.close()
>>>

Look here, there's a leftover file:

% ls -l file*
-rw-r--r-- 1 skip skip 8192 Jan 22 12:49 file::memory:?cache=shared

And it seems to have some bits left over from the session:

% strings -a file*
SQLite format 3
atableXYZXYZ
CREATE TABLE XYZ (x int, y int, z varchar)

Indeed, I can open it. Although it appears to be empty (I didn't explicitly commit() in the first session, so perhaps that's why), the database is still there and accessibe:

% python
Python 2.7.14 |Anaconda, Inc.| (default, Dec  7 2017, 17:05:42) 
[GCC 7.2.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.Connection("file::memory:?cache=shared")
>>> curs = conn.cursor()
>>> curs.execute("select * from XYZ").fetchall()
[]
>>> curs.execute("select * from sqlite_master").fetchall()
[(u'table', u'XYZ', u'XYZ', 2, u'CREATE TABLE XYZ (x int, y int, z varchar)')]

When I first saw mention of the form "file::memory?cache=shared", I thought it looked odd. Should it instead have been something more like "file://:memory:?cache=shared"? I tried that and received an error. Still, while the documented name works, it clearly writes a file to the filesystem. I discovered this quite by accident earlier when I noticed how slow it was compared to plain old ":memory:", and how large the resulting files were. Unfortunately, those files were written to an NFS-mounted filesystem, which might explain a lot of the slowness.

Is there a way to guarantee an in-memory database which can be accessed from multiple connections in the same process?

Upvotes: 0

Views: 2267

Answers (2)

David Laing
David Laing

Reputation: 7665

If connecting via SQLAlchemy, the uri=true flag needs to be set on the URI itself, eg:

sqlalchemy.create_engine('sqlite:///:memory:?cache=shared&uri=true')

See https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#uri-connections for details

Upvotes: 0

CL.
CL.

Reputation: 180020

Opening an in-memory database with shared-cache mode requires a URI filename. Your Python version does not support this; it always treats the entire string as the file name.

URI filenames are supported in Python 3.4 and later:

db = sqlite3.connect("file::memory:?cache=shared", uri=True)

Upvotes: 1

Related Questions