Aggregation function on multiple databases in sqlalchemy

Question

i have two databases in postgres server named as dispatch and drivers and have configured it like this :

engines = {    'drivers':create_engine('postgres://postgres:admin@localhost:5432/drivers'),    'dispatch':create_engine('postgres://postgres:admin@localhost:5432/dispatch')
}

and also i have routingSession class which based on the object passsed at runtime to the query ,excutes the query at particular database and extract the result

class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if mapper and issubclass(mapper.class_, drivers):
            return engines['drivers']
        elif mapper and issubclass(mapper.class_, dispatch):
            return engines['dispatch']

so now i can post query on database tables like this :

Session = sessionmaker(class_=RoutingSession)
session=Session()
res=session.query(drivers).all()

but the problem i am facing is that i need to do aggreagation of results between my two tables i.e drivers and dispatch ,which i am able to do when working on same database and multiple schemas :

result=session.query(drivers,dispatch).filter(drivers.id==dispatch.id).all()

but fails when i try to do it on different databases.please suggest how can i achieve this .

Though it feels like you should be using schema, as you've noted, try reading on foreign data wrappers or dblink, if you must have separate databases. — Ilja Everilä, Jan 17 '19 at 13:50
@IljaEverilä i am able to achieve this using schemas in same database ,wanted to test if we can achive the same having multiple databases.And for multiple databases also i am able to fire queries to both databases but not sure how to aggreagate the results together of two different queries. — divyanayan awasthi, Jan 17 '19 at 13:53
seems like a duplicate post https://stackoverflow.com/questions/44564369/join-tables-in-two-databases-using-sqlalchemy ..@IljaEverilä the solution you have posted in this link does it work for postgres as well. — divyanayan awasthi, Jan 17 '19 at 14:02
It's a MySQL specific solution, since in MySQL "database" and "schema" are synonymous. I'd still research FDWs, since the newer versions of Postgresql have pretty nifty features in relation to FDWs and aggregation: https://www.enterprisedb.com/blog/postgresql-aggregate-push-down-postgresfdw. — Ilja Everilä, Jan 17 '19 at 14:14
the names "dispatch" and "drivers" sounds related and wanting to aggregate data between them is an even stronger sign that they are. Unless you have a very strong reason for wanting to have those table in separate databases you will make your life way more difficult that way. — Björn Nilsson, Jan 17 '19 at 16:35

score 0 · Answer 1 · answered Jan 21 '19 at 10:01

Foreign Data wrappers is a nice way to do aggregation on multiple data bases or servers.

https://www.percona.com/blog/2018/08/21/foreign-data-wrappers-postgresql-postgres_fdw/

Or else you can use pandas as well ,by using pandas we can have our dataframes and then do any kind of aggregartion and manipulation on dataframes.Though i have not done any performance testing using pandas but i am assuming it will be slower compared to first approach.

Aggregation function on multiple databases in sqlalchemy

1 Answers1