python sqlalchemy label usage

Question

I know I can use the label method for alias, but I can't figure out how to use the labeled element later in the query - something like the following:

session.query(Foo.bar.label("foobar")).filter(foobar > 10).all()

Of course, this doesn't work since there is no variable called foobar. How could this be accomplished?

(The over simplified example was just for easy comprehension...)

score 59 · Accepted Answer · answered Mar 22 '13 at 05:57

59

Offhand, I believe you can use the labeled column itself as an expression:

foobar = Foo.bar.label("foobar")
session.query(foobar).filter(foobar > 10).all()

answered Mar 22 '13 at 05:57

Eevee

47,412
11
95
127

alecxe · Answer 2 · 2013-03-22T05:49:22.627

31

Just put foobar in quotes. It'll work for order_by like this:

session.query(Foo.bar.label("foobar")).order_by('foobar').all()

For filter you can use raw sql conditions:

session.query(Foo.bar.label("foobar")).filter("foobar > 10").all()

edited Mar 22 '13 at 05:49

answered Mar 21 '13 at 20:47

alecxe

462,703
120
1,088
1,195

1

How about putting filter conditions into raw sql? I've updated the answer. Hope that helps. – alecxe Mar 21 '13 at 21:02
1

Although this might work (I haven't tried it), my predicate includes dates and those doesn't work well with strings in some DBs – Ofir Mar 22 '13 at 20:41
3

This worked for me when using `func.sum` whereas @Eevee's answer didn't. – bcoughlan Nov 22 '13 at 16:15
2

Any idea how to change the sort order based on a labelled field? I get an error when trying `query = query.order_by("changepct".asc())` – raven May 02 '22 at 00:54
2

@raven you can use like this 'order_by(desc("quantity"))' – Karthikeyan Balusamy May 25 '22 at 08:46

score 2 · Answer 3 · answered Apr 04 '22 at 16:17

2

# When you use the SQLAlchemy Core interface you may apply text()
from sqlalchemy import text
stmt = select(Foo.c.bar.label("foobar"))
stmt = stmt.where(text("foobar > 10"))

answered Apr 04 '22 at 16:17

Михаил Смирнов

21
2

score 0 · Answer 4 · answered Jul 14 '23 at 18:31

Wanted to add on to @Eevee's answer:

This can also work similarly for func.coalesce() calls. You can assign the result of func.coalesce() to a variable, then reference that variable in a filter. For example:

x = func.coalesce(Order.x, Order.y)
y = query(<model>).filter(x = 10)

python sqlalchemy label usage

4 Answers4

Linked