In memory SQLite3 shared database python

Question

I am trying to make the best out of an aws server and had the idea to use an in memory database across multiple threads(using SQLite 3 in python) I found this command online:

    conn = sqlite3.connect('file::memory:?cache=shared')

but then I get this vague error:

    sqlite3.OperationalError: unable to open database file

Is it even possible to do this anymore?

I saw somewhere that shared memory databases are not supported anymore — Emanuel Amit
– Emanuel Amit, Commented May 20, 2017 at 20:11
Try the code in my answer. If it works, the problem is just in your code. You're probably releasing your DB somewhere? — Aravind Voggu
– Aravind Voggu, Commented May 20, 2017 at 20:19

Aravind Voggu · Accepted Answer · 2019-03-13 06:07:05Z

1

It is still possible. I just verified against Python 3.6.0 and Python 2.7.13 on MacOS.

sqlite3.connect("file::memory:?cache=shared") is indeed the correct way to connect to DB.

import sqlite3
p = sqlite3.connect("file::memory:?cache=shared")
p.execute('CREATE TABLE foo (bar, baz)')
p.execute("INSERT INTO foo VALUES ('apple', 'orange')")
p.commit()

and in another python shell

import sqlite3
q = sqlite3.connect("file::memory:?cache=shared")
list(q.execute('SELECT * FROM foo'))

my output is [(u'apple', u'orange')]

To you answer your question "Is it even possible to do this anymore?", the answer is yes. So the problem lies in your system, as you confirmed it works on aws (in the comments below).

edited Mar 13, 2019 at 6:07

answered May 20, 2017 at 20:18

Aravind Voggu

1,53112 silver badges17 bronze badges

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

8 Comments

Emanuel Amit Over a year ago

Thank you. I'll try it oout

Emanuel Amit Over a year ago

so I tried this and it works when I try to read the database from the same shell I created it in but when I open a new shell it says "table not found foo"

Aravind Voggu Over a year ago

@EmanuelAmit how about another shell when this shell is still running?

Emanuel Amit Over a year ago

I just tried it in pycharm too through two seperate programs and it did not work

Emanuel Amit Over a year ago

So I just tried it again and it worked on aws. Before I was just running it on my machine (macOS). why could it be that it works on aws but not on my local machine? Thank You

|

vinod · Accepted Answer · 2020-05-16 03:59:07Z

1

In Python 3.4+ and SQLite 3.7.13+, you can use this approach:

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

answered May 16, 2020 at 3:59

vinod

2,36819 silver badges26 bronze badges

Collectives™ on Stack Overflow

In memory SQLite3 shared database python

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related