Flask-SQLAlchemy - 会话如何处理多个数据库? [英] Flask-SQLAlchemy - how do sessions work with multiple databases?

问题描述

我正在做一个 Flask 项目，我正在使用 Flask-SQLAlchemy.
我需要使用多个现有的数据库.
我创建了应用程序"对象和 SQLAlchemy 之一:

I'm working on a Flask project and I am using Flask-SQLAlchemy.
I need to work with multiple already existing databases.
I created the "app" object and the SQLAlchemy one:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
db = SQLAlchemy(app)

在配置中我设置了默认连接和附加绑定:

In the configuration I set the default connection and the additional binds:

SQLALCHEMY_DATABASE_URI = 'postgresql://pg_user:pg_pwd@pg_server/pg_db'
SQLALCHEMY_BINDS = {
    'oracle_bind': 'oracle://oracle_user:oracle_pwd@oracle_server/oracle_schema',
    'mssql_bind': 'mssql+pyodbc://msssql_user:mssql_pwd@mssql_server/mssql_schema?driver=FreeTDS'
}

然后我使用声明式系统创建了表模型，并在需要时设置了__bind_key__ 参数指示表所在的数据库.
例如:

Then I created the table models using the declarative system and, where needed, I set the __bind_key__ parameter to indicate in which database the table is located.
For example:

class MyTable(db.Model):
    __bind_key__ = 'mssql_bind'
    __tablename__ = 'my_table'
    
    id = db.Column(db.Integer, nullable=False, primary_key=True)
    val = db.Column(db.String(50), nullable=False)

这样一切正常，当我进行查询时，它是在正确的数据库上进行的.

阅读 SQLAlchemy 文档和 Flask-SQLALchemy 文档我明白这些事情(我把它们写下来以检查我是否理解正确):

in this way everything works correctly, when I do a query it is made on the right database.

Reading the SQLAlchemy documentation and the Flask-SQLALchemy documentation I understand these things (i write them down to check I understand correctly):

• 您可以通过会话处理事务.
• 在 SQLAlchemy 中，您可以将会话与特定引擎绑定.
• Flask-SQLAlchemy 在请求开始时自动创建会话 (scoped_session)，并在请求结束时将其销毁
  

所以我可以这样做:

so I can do:

record = MyTable(1, 'some text')
db.session.add(record)
db.session.commit()

我无法理解当我们在 Flask-SqlAlchemy 中使用多个数据库时会发生什么，关于会话.

I can not understand what happens when we use multiple databases, regarding the session, in Flask-SqlAlchemy.

我验证了系统能够通过 __bind_key__ 参数在正确的数据库中正确绑定表，因此，我可以通过 db.session 在不同的数据库中插入数据，并在提交时保存所有内容.

但是，我无法理解 Flask-SQLAlchemy 是创建多个会话(每个引擎一个)还是以不同的方式管理事物.
在这两种情况下，如何引用特定数据库的会话/事务?
如果我使用 db.session.commit()，系统会对所有涉及的数据库进行提交，但是如果我只想提交单个数据库，我该怎么做?
我会做类似的事情:

I verified that the system is able to bind the table correctly at the right database through the __bind_key__ parameter, I can, therefore, insert data on different databases through db.session and, at the commit, everything is saved.

I can't, however, understand if Flask-SQLAlchemy create multiple sessions (one for each engine) or if manages the thing in a different way.
In both cases, how is it possible refer to the session/transaction of a specific database?
If I use db.session.commit() the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?
I would do something like:

db.session('mssql_bind').commit()

但我不知道该怎么做.

我还看到了一个 Flask-SQLAlchemy 实现，它应该可以简化这些情况的管理:

问题:https://github.com/mitsuhiko/flask-sqlalchemy/issues/107
实现:https://github.com/mitsuhiko/flask-sqlalchemy/pull/249

但我不知道如何使用它.

在 Flask-SQLAlchemy 中，我如何专门为每个引擎管理会话?

but I can not figure out how to do this.

I also saw a Flask-SQLAlchemy implementation which should ease the management of these situations:

Issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/107
Implementation: https://github.com/mitsuhiko/flask-sqlalchemy/pull/249

but I can not figure out how to use it.

In Flask-SQLAlchemy how can I manage sessions specifically for each single engine?

推荐答案

Flask-SQLAlchemy 使用 自定义会话，根据给定的__bind_key__ 映射类中的属性.在幕后，它实际上将该键作为信息添加到创建的表中.换句话说，Flask 不会创建多个会话，每个绑定一个，而是根据绑定键路由到正确可连接(引擎/连接)的单个会话.请注意，vanilla SQLAlchemy 具有 类似的功能 开箱即用.

Flask-SQLAlchemy uses a customized session that handles bind routing according to given __bind_key__ attribute in mapped class. Under the hood it actually adds that key as info to the created table. In other words, Flask does not create multiple sessions, one for each bind, but a single session that routes to correct connectable (engine/connection) according to the bind key. Note that vanilla SQLAlchemy has similar functionality out of the box.

在这两种情况下，如何引用特定数据库的会话/事务?如果我使用 db.session.commit() 系统会提交所有涉及的数据库，但如果我只想提交单个数据库，我该怎么做?

In both cases, how is it possible refer to the session/transaction of a specific database? If I use db.session.commit() the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?

在会话中使用会话拥有的连接破坏和发布对特定数据库的提交可能不是一个好主意.会话是一个整体，跟踪state 用于对象实例，在需要时将更改刷新到数据库等.这意味着会话处理的事务不仅是数据库事务，还包括会话自己的事务.所有应该提交和回滚的内容.

It might not be a good idea to subvert and issue commits to specific databases mid session using the connections owned by the session. The session is a whole and keeps track of state for object instances, flushing changes to databases when needed etc. That means that the transaction handled by the session is not just the database transactions, but the session's own transaction as well. All that should commit and rollback as one.

另一方面，您可以创建新的 SQLAlchemy(或 Flask-SQLAlchemy)会话，这些会话可能 在绑定之一中加入正在进行的事务:

You could on the other hand create new SQLAlchemy (or Flask-SQLAlchemy) sessions that possibly join the ongoing transaction in one of the binds:

session = db.create_scoped_session(
    options=dict(bind=db.get_engine(app, 'oracle_bind'),
                 binds={}))

这就是拉取请求的内容.它允许使用现有的事务连接作为新 Flask-SQLAlchemy 会话的绑定.例如，这在测试中非常有用，从拉取请求的基本原理中可以看出.这样你就可以拥有一个主"事务，例如可以回滚在测试中完成的所有事情.

This is what the pull request is about. It allows using an existing transactional connection as the bind for a new Flask-SQLAlchemy session. This is very useful for example in testing, as can be seen in the rationale for that pull request. That way you can have a "master" transaction that can for example rollback everything done in testing.

请注意，如果 bind_key 存在，SignallingSession 始终会咨询 db.get_engine() 方法.这意味着示例会话无法查询没有绑定键且在您的 oracle 数据库中不存在的表，但仍可用于带有 mssql_bind 键的表.

Note that the SignallingSession always consults the db.get_engine() method if a bind_key is present. This means that the example session is unable to query tables without a bind key and which don't exist on your oracle DB, but would still work for tables with your mssql_bind key.

另一方面，您链接到的问题确实列出了向特定绑定发出 SQL 的方法:

The issue you linked to on the other hand does list ways to issue SQL to specific binds:

rows = db.session.execute(query, params,
                          bind=db.get_engine(app, 'oracle_bind'))

还列出了其他不太显式的方法，但显式优于隐式.

There were other less explicit methods listed as well, but explicit is better than implicit.

这篇关于Flask-SQLAlchemy - 会话如何处理多个数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！