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

Question

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.

univerio · Accepted Answer · 2017-07-17 18:33:03Z

3

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")

answered Jul 17, 2017 at 18:33

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HippoMan Over a year ago

OK, I see. Apparently there is no sqlalchemy-specific way to use a dynamic column name, and so I just use the pythonic getattr() function to perform introspection. Thank you.

univerio Over a year ago

@HippoMan The SQLAlchemy way is to use table and column.

HippoMan Over a year ago

Yes, I understand that part. I'm talking about passing t.c to getattr(). That is pythonic introspection.

univerio Over a year ago

@HippoMan You could just do .where(column(col_name1) == "foo") but that's less flexible.

HippoMan Over a year ago

Understood. Thank you again.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related