How do I select literal values in an sqlalchemy query?

I have a query which looks like this:

query = session.query(Item) 
    .filter(Item.company_id == company_id) 
    .order_by(Item.id)

It’s a pretty basic query. In addition to pulling out the values for the Item, I want to append an additional value into the mix, and have it returned to me. In raw SQL, I would do this:

SELECT *, 0 as subscribed
FROM items
WHERE company_id = 34
ORDER BY id

How can I manually add that value via sqlalchemy?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You’ll need to use a literal_column, which looks a bit like this:

sqlalchemy.orm.Query(Item, sqlalchemy.sql.expression.literal_column("0"))

Beware that the text argument is inserted into the query without any transformation; this may expose you to a SQL Injection vulnerability if you accept values for the text parameter from outside your application. If that’s something you need, you’ll want to use bindparam, which is about as easy to use; but you will have to invent a name:

sqlalchemy.orm.Query(Item, sqlalchemy.sql.expression.bindparam("zero", 0))

Method 2

As mentioned in the comments of the accepted answer there’s a “shorthand” for bindparam() that alleviates the need to come up with a name for the literal bindparam, literal():

Return a literal clause, bound to a bind parameter.

So one does not have to write

session.query(Item, bindparam("zero", 0).label("subscribed"))

but just

session.query(Item, literal(0).label("subscribed"))

without having to worry about quoting etc., if passing strings or such.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0