71

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?

Antti Haapala -- Слава Україні
  • 129,958
  • 22
  • 279
  • 321
Gerald Thibault
  • 1,043
  • 2
  • 11
  • 17

2 Answers2

101

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))
Mathieu Rodic
  • 6,637
  • 2
  • 43
  • 49
SingleNegationElimination
  • 151,563
  • 33
  • 264
  • 304
  • 20
    Wicked, thanks for that. And just in case anyone finds this question, naming the literal column is done like this: `literal_column("0").label(col_name)` – Gerald Thibault Sep 26 '11 at 18:09
  • +1 for `bindparam`, its very useful when dealing with string constants. – Answeror Aug 12 '13 at 01:53
  • 4
    I've found that using [`sqlalchemy.sql.expression.literal`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.literal) is a bit more succinct and readable than `bindparam` for string constants. – Dologan Apr 20 '16 at 14:34
  • 20
    Remember, when selecting a literal string, put the value in single quotes. For example, `literal_column("'my_string'")` (at least for Postgres) – zashu Jun 08 '16 at 22:12
  • 3
    `sqlalchemy.sql.expression.literal` appears to work for other types as well, e.g. an `int`. I noticed it's also available as `sqlalchemy.sql.literal` or `sqlalchemy.literal`, or at least on version 1.1.0b2 it is. – Tim Diels Aug 16 '16 at 16:37
  • 2
    from sqlalchemy import literal_column – Matthew Moisen Mar 15 '17 at 01:30
  • For anyone who is willing to use string variables for literal_columns value: literal_column(f"'{location}'").label('location')) – Azzonith Jul 14 '22 at 11:58
27

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.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127