CODEWITHSUNDEEP
sqlpostgresqldynamicsqlalchemy
HippoMan
HippoMan

Reputation: 2318

sqlalchemy/postgres: parameterizing column names in queries at run time?

I have a sqlalchemy/postgres query that is run like this (example code):

start = # a datetime value
end   = # a datetime value
engine = # the definition of an engine
table = Table('table_name', ... etc. ...)
query = select(['*'])
results = engine.connect().execute(query).where(between(table.c.some_column, start, end))

This is simple and straightforward. However, it turns out that I want to reuse this code with a different table name and a different column name, both of which are specified as strings in a config file.

So for example, suppose that table "xyz" contains a column called "the_timestamp", and suppose the table "abc" contains a column called "the_time". I associate "xyz" with "the_timestamp" in a config file and associate "abc" with "the_time" in the config file. Then, depending upon which table/column combination is selected from the config file, the where clause will use "the_timestamp" if the table is "xyz" and "the_time" if the table is "abc".

Obviously, the table part of this is trivial, because the Table() function takes a string as an argument. However, the column name in the where clause is not a string, but rather, it's a reference to an sqlalchemy object. I'd like to have the column name in the where clause be dynamically set to the column name string from the config file, instead of using a pre-defined object for the name of the column.

I haven't been able to figure out how to do this in sqlalchemy. Thank you for your suggestions.

Upvotes: 1

Views: 744

Answers (1)

univerio
univerio

Reputation: 20518

You'll need to dynamically define and access the table and columns. You probably want to use table and column instead of Table and Column so that you don't need to take care to remove these dynamically created objects from the MetaData. So:

t = table(table_name, column(col_name1), column(col_name2), ...)

To use these dynamically, use getattr:

select([t]).where(getattr(t.c, col_name1) == "foo")

Upvotes: 3

Related Questions