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?

snakecharmerb · Accepted Answer · 2020-08-22 09:33:29Z

7

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)

answered Aug 22, 2020 at 9:33

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

Collectives™ on Stack Overflow

Add columns to sqlalchemy query's SELECT clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related