3

How can I execute an SQL query where the schema and table name are passed in a function? Something like below?

def get(engine, schema: str, table: str):
    query = text("select * from :schema.:table")

    result = engine.connect().execute(query, schema=schema, table=table)
    return result
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
RAbraham
  • 5,956
  • 8
  • 45
  • 80

1 Answers1

0

Two things going on here:

  1. Avoiding SQL injection
  2. Dynamically setting a schema with (presumably) PostgreSQL

The first question has a very broad scope, you might want to look at older questions about SQLAlchemy and SQL Injection like this one SQLAlchemy + SQL Injection

Your second question can be addressed in a number of ways, though I would recommend the following approach from SQLAlchemy's documentation: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

PostgreSQL supports a "search path" command which sets the schema for all operations in the transaction.

So your query code might look like:

qry_str = f"SET search_path TO {schema}";

Alternatively, if you use an SQLAlchemy declarative approach, you can use a MetaData object like in this question/answer SQLAlchemy support of Postgres Schemas

henrycjc
  • 663
  • 7
  • 20