Python - 如何将 SQLAlchemy 连接到内存中的现有数据库 [英] Python - How to connect SQLAlchemy to existing database in memory

问题描述

我正在从现有的 shema 创建我的数据库，它存储在 :memory: 中.

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

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

我现在想要链接"这对 SQL Alchemy.采取了不同的方法，但都做对了.

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

我目前的尝试如下:

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

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

session = Session(engine)

就像我尝试过的其他东西一样，这会抛出 AttributeError: user.

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

我怎样才能一起工作?

推荐答案

文档的相关部分在这里:https://sqlite.org/inmemorydb.html .

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

如果你使用 :memory: 那么每个连接都会有自己的内存数据库.诀窍是在内存数据库中使用 named 和 URI 格式，如下所示

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. 格式是查询字符串参数声明的 URI uri=true(请参阅 SQLAlchemy 文档)
2. 它是一个带有 mode=memory
的内存数据库
3. 它可以通过cache=shared
在各种连接之间共享

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

如果您有另一个连接，那么您可以或多或少地使用相同的连接字符串.例如，为了使用 python 的 sqlite 模块获取到内存中相同数据库的连接，您可以从查询字符串中删除 uri=true(以及方言部分 sqlite:///) 并将其作为参数传递:

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)

这篇关于Python - 如何将 SQLAlchemy 连接到内存中的现有数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！