31
votes

I want to do something like this:

select username, userid, 'user' as new_column  from  users_table.

The columns of the table can be selected using sqlalchemy as follows:

query = select([users_table.c.username, users_table.c.userid])

How do I do the select x as col_x to the query in sqlalchemy?

3
I'm not sure you can do aliases of column names without entering in raw SQL. Why would you want to do this? - Michael Mior
I want to do union of this query with another query which has a column with name 'new_column' - user401574
This question shows up as a top result for "Rename Column In Query SQLAlchemy" on Google but does not actually ask that question. The question seems to have more to with creating a literal column than renaming an existing column. The top voted answer, answers the Google query but not the best guess of the authors question. All that combined with none of the links to docs working anymore and changed APIs I think this is better archived / closed to prevent any more confusion. - Ken Colton

3 Answers

59
votes

Use the label(...) function: users_table.c.userid.label('NewColumn')

i.e.,

query = select([users_table.c.username, users_table.c.userid.label('NewColumn')])

evaluates to:

SELECT username, userid as NewColumn From MyTable;
7
votes

Maybe literal_column?

query = select([users_table.c.username, users_table.c.userid, literal_column("user", type_=Unicode).label('new_column')])

See https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.literal_column

Edit: actually I should have said "literal":

query = select([users_table.c.username, users_table.c.userid, literal("user", type_=Unicode).label('new_column')])