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:

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

Answers


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.


Need Your Help

Wall collision problems AS3

actionscript-3 flash collision-detection hittest flash-cs3

I'm making a game where a character walks around different rooms, but I want to keep it from going through the walls. I tried making a barrier symbol to stop it, but since the four walls enclose the

What are the reasons that protocols and multimethods in Clojure are less powerful for polymorphism than typeclasses in Haskell?

haskell clojure protocols typeclass multimethod

More broadly this question is about various approaches to the expression problem. The idea is that your program is a combination of a datatype and operations over it. We want to be able to add new...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.