Flask-SQLAlchemy - 会话如何与多个数据库一起工作？ [英] Flask-SQLAlchemy - how do sessions work with multiple databases?

问题描述

我正在使用Flask项目，并使用Flask-SQLAlchemy。

我需要使用多个已经存在的数据库。

我创建了app对象和SQLAlchemy之一：

  from flask import Flask 
 from flask_sqlalchemy import SQLAlchemy 
 
 $ b $ app = Flask（__ name__）
 db = SQLAlchemy（app）
  

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


$ p $ sql code $ 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 __ 参数来指示ch数据库表位于。

例如：

  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文档我明白这些事情
（i写下来检查我是否正确理解）：

您可以通过会话处理事务。

在SQLAlchemy中，您可以将会话绑定到特定的引擎。

Flask-SQLAlchemy会在请求开始时自动创建会话（scoped_session），并在请求结束时将其销毁

所以我可以这样做：


<$ （$，$'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' code>

我不明白在Flask-SqlAlchemy中使用多个关于会话的数据库时会发生什么。

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



我可以但是，了解Flask-SQLAlchemy是否创建了多个会话（每个引擎一个）或者以不同的方式管理事件。

在这两种情况下，如何引用会话/事务如果我使用 db.session.commit（），系统会对所有涉及的数据库进行提交，但我该怎么办如果我只想提交一个数据库？

我会这样做：

  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中，我怎样才能专门为每个引擎管理会话？

解决方案

Flask-SQLAlchemy使用自定义会话处理绑定路由协议ing给予 __ bind_key __ 映射类中的属性。在引擎盖下，它实际上将该键添加到创建的表中。换句话说，Flask不会为每个绑定创建多个会话，而是根据绑定键为路由来更正可连接（引擎/连接）的单个会话。请注意，vanilla SQLAlchemy的类似的功能
$ b

在这两种情况下，如何引用特定数据库的会话/事务？
如果我使用 db.session.commit（），系统会在所有涉及的数据库上执行提交，但是如果我只想提交单一数据库？

使用会话拥有的连接，可能不是一个好主意， 。会议是一个整体和跟踪状态，当需要时刷新对数据库的更改等。这意味着由会话处理的事务不仅仅是数据库事务，还是会话自己的事务。所有应该提交和回滚为一个。

另一方面，您可以创建新的SQLAlchemy（或Flask-SQLAlchemy）会话，可能加入进行中交易在一个绑定：

  session = db.create_scoped_session（
 options = dict（bind = db.get_engine（app，'oracle_bind'），
 binds = {}））
  

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

注意 SignallingSession bind_key ，那么总是咨询 db.get_engine（）方法。这意味着示例会话无法在没有绑定键的情况下查询表，而您的oracle数据库中不存在这些表，但仍然可以使用 mssql_bind 键工作。

另一方面，您链接的问题列出了将SQL发送到特定绑定的方式：

  rows = db.session.execute（query，params，
 bind = db.get_engine（app，'oracle_bind'））
  

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

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'
}

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)

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):

• You can handle the transactions through the session.
• In SQLAlchemy you can bind a session with a specific engine.
• Flask-SQLAlchemy automatically creates the session (scoped_session) at the request start and it destroys it at the request end
  

so I can do:

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

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

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()

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 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.

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?

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.

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={}))

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.

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.

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屋！