0

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

Saif Baig
  • 56
  • 6
  • 1
    Do you mean `schema` as in different databases, or as in table schema? In the first case, the actual schema is usually provided in the connection string and the underlying associated schema with the table is probably only for informative purposes? – MatsLindh May 26 '23 at 10:27

1 Answers1

0

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

Saif Baig
  • 56
  • 6