如何使用SQLAlchemy无缝访问多个数据库？ [英] How to use SQLAlchemy to seamlessly access multiple databases?

问题描述

可以说我为公司的不同部门创建了一个产品数据库系统。出于各种原因，每个部门都有自己的PostgreSQL-databse-instance。数据库的模式相同，但是其中的数据不同。对于这些系统中的每个系统，都存在执行某些业务逻辑（无关）的Python应用程序。每个Python应用程序都通过SQLAlchemy访问其唯一的数据库。

Lets say I created a product database system for different departments of my company. Each department has its own PostgreSQL-databse-instance for various reasons. The schemata of the databases are the same, however the data in them is not. For each of these systems a Python application exists that does some business logic (irrelevant). Each Python app accesses its and only its databases through SQLAlchemy.

我想创建一个Supervisior-System，该系统可以访问所有这些数据库中的所有数据（通读功能）。

I want to create a Supervisior-System that can access all data in all of these databases (readthrough functionality).

以下是我所考虑的示例：

Here is an example of what I think about:

我可以使用SQLAlchemy吗？如果是这样，那么解决此类问题的最佳方法是什么？

Can I do that with SQLAlchemy? If so, what is the best approach for that kind of problem?

推荐答案

当然可以使用SQLAlchemy做到这一点。

Sure you can do that with SQLAlchemy.

您需要做的就是创建不同的连接引擎，每个引擎都有自己的会话创建者。没有任何SQLAlchemy限制您一次只使用一个数据库。

All you need to do is create different connection engines, each with their own session maker. Nothing in SQLAlchemy limits you to only one database at a time.

engines = []
sessions = []
for dbconninfo in databases:
    engine = create_engine(dbconninfo)
    engines.append(engine)
    sessions.append(sessionmaker(bind=engine)())

您可以使用每个会话来运行查询，结果对象将附加到生成它们的会话上，以便更改流回正确的数据库。请详细研究会话文档，以了解如果您是例如，将一个会话中的对象合并到另一个会话中。

You can use each session to run queries, result objects are attached to the session that produced them, so that changes flow back to the correct database. Do study the session documentation in detail, to see what happens if you were to merge an object from one session into another, for example.

这篇关于如何使用SQLAlchemy无缝访问多个数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！