Flask-SQLAlchemy-快速连接到多个数据库 [英] Flask-SQLAlchemy - on the fly connections to multiple databases

问题描述

我有一个flask webapp，用户可以在其中连接到自己的mysql数据库并查询自己的表

I have a flask webapp where users will be able to connect to their own mysql databases and query their own tables

使用flask-sqlalchemy创建(到不同数据库的)多个连接的最佳方法是什么.似乎需要使用scoped_session和sessionmaker完成操作，但似乎无法将其包裹住.

What's the best way to create multiple connections (to different databases) using flask-sqlalchemy. It seems like it needs to be done with scoped_session and sessionmaker but cant seem to wrap my head around it.

问题的第二部分，一旦我为其中一个用户创建了与mysql db的连接，我如何在请求之间保持连接?

Also the second part of the question, once I create a connection to a mysql db for one of the users, how do i persist the connection across requests ?

当前，我将每个用户的连接字符串放在flask会话变量上，并在每个新请求下，依次创建引擎和连接

Currently, i put the connection string for each user on the flask session variable and at each new request, i create the engine and connection as such

engine = create_engine(connection_string, convert_unicode=True)
conn = engine.connect()
db_session = Session(bind=conn) # Session - i create it globally on the __init__ like this Session = scoped_session(sessionmaker()) and import it in the view

## Query using the db_session

这似乎对于创建引擎和与每个请求的连接都是非常浪费的-能否在请求之间保留该连接?

This seems super wasteful to create the engine and the connection with every request - cant the connection be persisted across requests ?

推荐答案

一个数据库

引擎是允许您使用连接池的引擎.默认情况下，它将在请求之间保持连接.基本用法(没有像scoped_session或sessionmaker这样的花哨的东西)是这样的:

One Database

The engine is what allows you to use connection pooling. By default, it will persist connections across requests. The basic usage (without fancy things like scoped_session or sessionmaker) is like this:

engine = create_engine(...)

@app.route(...)
def foo():
    session = Session(bind=engine)
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

在此之上，您可以添加scoped_session和sessionmaker:

On top of this, you can add scoped_session and sessionmaker:

engine = create_engine(...)
Session = sessionmaker(bind=engine)
session = scoped_session(Session, scopefunc=...)

@app.route(...)
def foo():
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

flask-sqlalchemy通过提供所有这些功能使您的生活更轻松:

flask-sqlalchemy makes your life easier by providing all of this:

db = SQLAlchemy(app)

@app.route(...)
def foo():
    db.session.query(...)
    db.session.commit()
    return ""

多个数据库

您可以轻松地将此概念扩展到多个数据库:

Multiple Databases

You can easily extend this concept to multiple databases:

engine1 = create_engine(...)
engine2 = create_engine(...)

@app.route(...)
def foo():
    session = Session(bind=choose_engine_for_user())
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

添加scoped_session和sessionmaker时:

engine1 = create_engine(...)
engine2 = create_engine(...)
Session1 = sessionmaker(bind=engine1)
Session2 = sessionmaker(bind=engine2)
session1 = scoped_session(Session1, scopefunc=...)
session2 = scoped_session(Session2, scopefunc=...)

@app.route(...)
def foo():
    session = choose_session_for_user()
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

当您有许多数据库时，这会有点烦人，在这种情况下，您可能应该编写一个注册表类来跟踪所有引擎和会话:

This gets a little annoying when you have many databases, in which case you should probably write a registry class to keep track of all the engines and sessions:

class SessionRegistry(object):
    _registry = {}

    def get(self, url, **kwargs):
        if url not in self._registry:
            engine = create_engine(url, **kwargs)
            Session = session_maker(bind=engine)
            session = scoped_session(Session, scopefunc=...)
            self._registry[url] = session
        return self._registry[url]

registry = SessionRegistry()

@app.route(...)
def foo():
    session = registry.get(...)
    try:
        session.query(...)
        session.commit()
    finally:
        session.close()
    return ""

您需要在其之上添加某种LRU，以便不会无限创建引擎.

You'll need to add some kind of LRU on top of it so that there's no unbounded creation of engines.

flask-sqlalchemy支持有限形式的多个数据库，其中每个模型都连接到不同的数据库.如果您愿意，请访问文档此处.

flask-sqlalchemy has support for a limited form of multiple databases where each of your model connects to a different database. If this applies to you, the documentation is here.

这篇关于Flask-SQLAlchemy-快速连接到多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！