Dynamic/multiple schema connection and data fetch in Python Fastapi

Question

I am working on fastapi app. I have a API which fetches data from multiple schemas and consolidates the output and sends as response.

Here is my code for changing schema dynamically:

def fetch_all_db_status(session: Session, schema_names):
    db_status = []
    try:
        for schema_name in schema_names:
            models.DBStatus.__table__.schema = schema_name
            result = session.query(models.DBStatus).all()
            db_status.append(result)
    except Exception as ee:
        logger.error("Error while fetching data from DB_STATUS for schema: " + str(schema_name))
        logger.error(str(ee))
    return db_status

But the schema does not change everytime, its pointing to the first schema.
For example i have 2 schemas: ["CUSTOMERA", "CUSTOMERB"]
On the first iteration the data is fetched from CUSTOMERA and on the second iteration the schema is set to CUSTOMERB but the query is still pointing to CUSTOMERA.

Can someone please explain how can i connect to schema dynamically and fetch data?
Do I have to create the engine with new schema name each time? WHat is the right approach?
Im using sqlalchemy

Answer 1

Using schema_translate_map help me achieve this:

def fetch_all_db_status(session: Session, schema_name):
db_status = []
try:
    session.connection(
        execution_options={"schema_translate_map": {"per_user": schema_name}}
    )
    db_status = session.query(models.DBStatus).all()
except Exception as ee:
    logger.error("Error while fetching data from DB_STATUS for schema: " + str(schema_name))
    logger.error(str(ee))
finally:
    session.close()
return db_status

Reference: https://docs.sqlalchemy.org/en/20/changelog/migration_11.html#multi-tenancy-schema-translation-for-table-objects