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ä
– Ilja Everilä, Commented Jan 17, 2019 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
– divyanayan awasthi, Commented Jan 17, 2019 at 13:53
seems like a duplicate post stackoverflow.com/questions/44564369/… ..@IljaEverilä the solution you have posted in this link does it work for postgres as well. — divyanayan awasthi
– divyanayan awasthi, Commented Jan 17, 2019 at 14:02

divyanayan awasthi · Accepted Answer · 2019-01-21 10:01:56Z

0

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.

answered Jan 21, 2019 at 10:01

divyanayan awasthi

9701 gold badge11 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Aggregation function on multiple databases in sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related