SqlAlchemy Python multiple databases

Question

I'm using SqlAlchemy to access multiple databases (on the same server). My current connection string is the following

connect_string = "mssql+pyodbc://{0}:{1}@{2}/{3}".format(USERNAME_R, PASSWORD_R, SERVER_R, DATABASE_R)
engine = create_engine(connect_string)
session = sessionmaker(bind=engine)()
Record = declarative_base(engine)

How do I modify this declaration to be able to connect to multiple databases on the same server (e.g. DATABASE1 & DATABASE2). It seems like this is pointing in the right direction, but it's not very clear and I'm not sure if it is specific to Flask:

https://pythonhosted.org/Flask-SQLAlchemy/binds.html

divyanayan awasthi · Accepted Answer · 2019-01-17 13:10:09Z

Hi you can achieve this using follwing .

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

i have two databases in sever and two tables.After that you can using Routing class to route for specific database connection while making a query :

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

now you can fire queries accordingly for eg first you need to make a session using like this :

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

this was you can use multiple database in sqlalchemy

Sers · Accepted Answer · 2021-07-23 15:50:19Z

9

Adding a bit more of insight, the official SQLAlchemy documentation now has a couple more strategies than the ones presented on the rest of the answers.

Just take a look at the following links:

edited Jul 23, 2021 at 15:50

Sers

12.3k2 gold badges14 silver badges33 bronze badges

answered Aug 10, 2020 at 10:26

Alejandro de Haro

1,2051 gold badge11 silver badges15 bronze badges

Comments

9000 · Accepted Answer · 2015-02-02 03:28:17Z

3

(A hand-wavy answer because of lack of time, sorry.)

Do not bind an engine to declarative_base. Inherit form just declarative_base().

Instead, pass a specific engine to sqlalchemy.orm.sessionmaker when creating a session. Create different transaction factories (that sessionmaker returns) for different engines. In your queries, use .with_session to bind to a specific session, and thus a specific engine.

answered Feb 2, 2015 at 3:28

9000

41k9 gold badges69 silver badges108 bronze badges

Collectives™ on Stack Overflow

SqlAlchemy Python multiple databases

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related