Add columns to sqlalchemy query's SELECT clause

Question

I have a base query that I would like to customize with different select clauses depending on a condition. Essentially

engine = create_engine(…)
session = sessionmaker(bind=engine)()
    
base_query = session.query(Employees.id).\
                     filter(Employees.years_service>5)

if need_name:
    Add a select clause for the name field

if need_birthday:
    Add a select clause for birthday

…except the filter criteria are much more complicated so I’d rather not just create duplicate queries.

When I want to add additional filter criteria, I can do this kind of decoration by just saying something like:

if years_service_max_needed:
    base_query.filter(Employees.years_service<10)

but I can’t find a query object method that would allow me to add additional select statements.

Is there a way to do such a thing?

Answer 1

You can do this with Query.add_columns:

Add one or more column expressions to the list of result columns to be returned.

q = session.query(Employees.id)

q = q.add_columns(Employees.name, Employees.years_service)

for row in q:
    print(row)