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

Answer 1

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:

• Partitioning Strategies (e.g. multiple database backends per Session)
• Session API - Session and sessionmaker() - 'binds' parameter

Answer 2

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