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?

dfrankow · Accepted Answer · 2024-06-28 02:01:32Z

3

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

edited Jun 28, 2024 at 2:01

dfrankow

21.7k44 gold badges168 silver badges246 bronze badges

answered May 22, 2022 at 3:52

glisu

1,13710 silver badges21 bronze badges

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

1 Comment

Eric Jin Over a year ago

Although this code might be an answer to the question, it would be more helpful to future readers if an explanation was added.

Nick K9 · Accepted Answer · 2022-07-30 09:06:39Z

0

ATTACH is your friend.

You can attach an in-memory database to the current database session.

E.g.,

db.init('sqlite://')
db.execute("ATTACH DATABASE ':memory:' AS my_database")
db.create_all()

edited Jul 30, 2022 at 9:06

Nick K9

4,8081 gold badge39 silver badges83 bronze badges

answered Jun 11, 2020 at 20:54

Marco Milanesio

1008 bronze badges

3 Comments

walkman Over a year ago

I'm sorry but I can't figure out how to use your answer within my code

Marco Milanesio Over a year ago

In order to use :memory: you need to specify an empty url for your db. db = create_engine('sqlite://'). Your code: engine = create_engine('sqlite:///:memory:') is wrong. Look here

Pratyush Raizada Over a year ago

@MarcoMilanesio, the syntax is wrong, the correct syntax for ATTACH is ATTACH DATABASE ':memory:' AS my_database;

Collectives™ on Stack Overflow

Python SQLAlchemy in-memory database connect

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related