Python sqlite3 cached in-memory database - is it supported?

Question

Python 3.12, built-in sqlite3 package on MacOS Sonoma 14.5, using PyCharm community edition.

I'm attempting to create an in-memory sqlite3 database that can be closed and reopened for each query.

The reason for this is that I want to be able to override the sqlite specific functions and allow the same program to be used with a different sql library and a suitable connection function.

I see the python.org sqlite3 documentation describes the use of ':Memory:' for non-persistent connections (which works), and refers to sqlite documentation for persistent connections.

That documentation says to use con = sqlite3.connect('file::memory:?cache=shared') in order to set up a persistent connection that can be opened and closed within the same process.

When I do that, I find that the connect function has created a disk file with the 'file::memory:?cache=shared' filename. This is a file, not an in-memory database!

Does anyone here know whether Python's sqlite3 supports cached memory databases and what I might be doing wrong (or any recommended alternative approaches)?

I found an two similar question on this board but the described problem is different in those situations.

Thank you!

'That documentation says to use con = sqlite3.connect('file::memory:?cache=shared')" - Where does it say that? — no comment
– no comment, Commented Aug 29, 2024 at 19:43
answering "no comment"'s question - the python.org sqlite3 reference page has a link to this page: sqlite.org/inmemorydb.html — DonH
– DonH, Commented Aug 29, 2024 at 20:07

no comment · Accepted Answer · 2024-08-29 20:35:02Z

1

You need connect(..., uri=True) to enable URIs, as shown in the examples.

answered Aug 29, 2024 at 20:35

no comment

10.9k5 gold badges21 silver badges44 bronze badges

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

1 Comment

DonH Over a year ago

Thank you "no comment" - that pointed me in the right direction. In case anyone else is looking for the same thing, this small example works: db = 'file:mem1?mode=memory&cache=shared' con = sqlite3.connect(db, uri=True) <do stuff, including opening and closing other connections> `con.close() # closes original connection`` this works as I intended, allowing me to keep the first connection open for persistence and then opening and closing other connections from different functions.

Collectives™ on Stack Overflow

Python sqlite3 cached in-memory database - is it supported?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related