Dynamic/multiple schema connection and data fetch in Python Fastapi

412 views Asked by At

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 CUSTOMER
B 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

1

There are 1 answers

0
Saif Baig On

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