Python SQLAlchemy in-memory database connect

Question

I'm creating a in-memory database with python and I want to use SQLAlchemy with it.

All my application is currently working directly with queries to the db.

I've seen multiple ways of connecting but none of it is working. My current attempt stands as:

# Creates an sqlite database in memory
db = Database(filename=':memory:', schema='schema.sql')
db.recreate()

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

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

session = Session(engine)

This gives AttributeError: user. How do I properly connect my database to the SQLAlchemy?

Answer 1

from sqlalchemy import create_engine   
from sqlalchemy.orm import registry, Session
 
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
session = Session(engine)
registry().metadata.create_all(engine)

:memory: is a special name used by sqlite for an in-memory database (see here).

sqlite+pysqlite is the database and driver. The format of the connection string is

dialect+driver://username:password@host:port/database