How to use SQLAlchemy to seamlessly access multiple databases?

Question

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.

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:

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

Answer 1

Sure you can do that with 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.