Python - How to connect SQLAlchemy to existing database in memory

Question

I'm creating my DB from an existing shema and it's stored in :memory:.

db = Database(filename=':memory:', schema='schema.sql')
db.recreate()

I now want to "link" this to SQL Alchemy. Followed different methods but could not get it right.

My current attempt stands as follow:

engine = create_engine('sqlite:///:memory:')

Base = automap_base()
Base.prepare(engine, reflect=True)
User = Base.classes.user

session = Session(engine)

Much like the other stuff I tried this will throw AttributeError: user.

How can I have this work together?

Answer 1

The relevant part of the documentation is here: https://sqlite.org/inmemorydb.html .

If you use :memory: then every connection will have its own memory database. The trick is to use a named in memory database with the URI format, like the following

import random
import string
import sqlite3

# creating a random name for the temporary memory DB
sqlite_shared_name = "test_db_{}".format(
            random.sample(string.ascii_letters, k=4)
        )

create_engine(
   "sqlite:///file:{}?mode=memory&cache=shared&uri=true".format(
        sqlite_shared_name))

1. the format is a URI as stated by the query string parameter uri=true (see SQLAlchemy documentation)
2. it is a memory DB with mode=memory
3. it can be shared among various connection with cache=shared

If you have another connection, then you can use more or less the same connection string. For instance, for getting the connection to that same DB in memory using python's sqlite module, you can drop the uri=true from the query string (and the dialect part sqlite:///) and pass it as argument:

dest = sqlite3.connect(
            "file:{}?mode=memory&cache=shared".format(sqlite_shared_name), 
            uri=True)