CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
AME
AME

Reputation: 2609

How to use SQLAlchemy to seamlessly access multiple databases?

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: enter image description here

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

Upvotes: 21

Views: 16555

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121406

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.

Upvotes: 19

Related Questions