SQLAlchemy ORDER BY DESCENDING?

Question

How can I use ORDER BY descending in a SQLAlchemy query like the following?

This query works, but returns them in ascending order:

query = (model.Session.query(model.Entry)
        .join(model.ClassificationItem)
        .join(model.EnumerationValue)
        .filter_by(id=c.row.id)
        .order_by(model.Entry.amount) # This row :)
        )

If I try:

.order_by(desc(model.Entry.amount))

then I get: NameError: global name 'desc' is not defined.

What do you mean using `model.Entry.amount`? U can just use `.order_by(Model.field)` — fedotsoldier, Feb 15 '22 at 18:23

score 1013 · Answer 1 · edited May 27 '21 at 19:53

1013

Just as an FYI, you can also specify those things as column attributes. For instance, I might have done:

.order_by(model.Entry.amount.desc())

This is handy since it avoids an import, and you can use it on other places such as in a relation definition, etc.

For more information, you can refer this SQLAlchemy 1.4 Documentation

edited May 27 '21 at 19:53

Deepam Gupta

2,374
1
30
33

answered Nov 15 '10 at 17:46

Rick

15,484
5
25
29

2

Is there an API reference for this, would be cool to know what else is available? – John Mike Nov 15 '17 at 23:21
1

Thanks for the this. It seems it is missing from official documentation. – user3341078 Nov 25 '17 at 23:08
2

The best response (at the moment). – RodriKing May 29 '18 at 08:25
7

@JohnMike http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc – dzervas May 31 '18 at 23:36
16

The sqlalchemy orm doc is a mystery for me, i can never find anything by myself in it. Thank you @dzervas – Alex Aug 12 '21 at 15:20

score 442 · Accepted Answer · edited Jul 10 '17 at 20:56

442

from sqlalchemy import desc
someselect.order_by(desc(table1.mycol))

Usage from @jpmc26

edited Jul 10 '17 at 20:56

m-ric

5,621
7
38
51

answered Nov 15 '10 at 15:49

AP257

89,519
86
202
261

4

Docs have a [new url](https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.desc) – hughes Feb 14 '19 at 18:00

score 98 · Answer 3 · edited Apr 28 '23 at 06:40

98

One other thing you might do is:

from sqlalchemy.sql import text
...
.order_by(text("name desc")

This will result in: ORDER BY name desc. The disadvantage here is the explicit column name used in order by.

edited Apr 28 '23 at 06:40

Abramodj

5,709
9
49
75

answered Apr 01 '12 at 14:03

Radu

1,045
7
2

33

This is a potentially brittle workaround where a workaround is not needed. – BlueBomber Jun 25 '13 at 14:41
18

This is nice if you have the sort column in a string for any reason, though (like sanitized input in a web API). – Jim Stewart Oct 18 '13 at 20:29
22

As a followup, you can also do `getattr(MyModelClass, column_string).desc()` if you've got a string. – Jim Stewart Oct 18 '13 at 21:39
12

@JimStewart and for passing in direction I ended up using: `qry.order_by(getattr(getattr(Song, sort_by), sort_dir)())` – Tjorriemorrie Sep 11 '15 at 11:44
1

You can also use this if you have labeled your output and are sorting by a computed column instead of a model column – boatcoder Dec 04 '17 at 19:35
@boatcoder .. 3 yrs later... do you have an example handy of how to do that ascending? – sur.la.route Jul 14 '20 at 16:29
1

Answering my comment.. ```from sqlalchemy import asc, desc```, then ```.order_by(asc("label"))``` – sur.la.route Jul 14 '20 at 16:32

Anand Tripathi · Answer 4 · 2019-11-23T04:43:06.553

You can use `.desc()` function in your query just like this

query = (model.Session.query(model.Entry)
        .join(model.ClassificationItem)
        .join(model.EnumerationValue)
        .filter_by(id=c.row.id)
        .order_by(model.Entry.amount.desc())
        )

This will order by amount in descending order or

query = session.query(
    model.Entry
).join(
    model.ClassificationItem
).join(
    model.EnumerationValue
).filter_by(
    id=c.row.id
).order_by(
    model.Entry.amount.desc()
)
)

Use of desc function of SQLAlchemy

from sqlalchemy import desc
query = session.query(
    model.Entry
).join(
    model.ClassificationItem
).join(
    model.EnumerationValue
).filter_by(
    id=c.row.id
).order_by(
    desc(model.Entry.amount)
)
)

For official docs please use the link or check below snippet

sqlalchemy.sql.expression.desc(column) Produce a descending ORDER BY clause element.

e.g.:
from sqlalchemy import desc

stmt = select([users_table]).order_by(desc(users_table.c.name))
will produce SQL as:
SELECT id, name FROM user ORDER BY name DESC
The desc() function is a standalone version of the ColumnElement.desc() method available on all SQL expressions, e.g.:
stmt = select([users_table]).order_by(users_table.c.name.desc())
Parameters column – A ColumnElement (e.g. scalar SQL expression) with which to apply the desc() operation.

See also

asc()

nullsfirst()

nullslast()

Select.order_by()

how is this not a duplicate of https://stackoverflow.com/a/4187279/2718295 — cowbert, Jun 28 '18 at 20:57

score 17 · Answer 5 · edited Sep 03 '19 at 11:32

17

You can try: .order_by(ClientTotal.id.desc())

session = Session()
auth_client_name = 'client3' 
result_by_auth_client = session.query(ClientTotal).filter(ClientTotal.client ==
auth_client_name).order_by(ClientTotal.id.desc()).all()

for rbac in result_by_auth_client:
    print(rbac.id) 
session.close()

edited Sep 03 '19 at 11:32

SuperShoot

9,880
2
38
55

answered May 15 '19 at 19:07

Artem Baranov

863
11
11

score 2 · Answer 6 · answered Aug 28 '18 at 13:01

2

Complementary at @Radu answer, As in SQL, you can add the table name in the parameter if you have many table with the same attribute.

.order_by("TableName.name desc")

answered Aug 28 '18 at 13:01

Michael

1,063
14
32

1

Doesn’t this require wrapping the string by text()? – Glutexo May 21 '19 at 07:53
2

Take in care it cause an error in SQLAlchemy >1.3, a warning in lower versions – Checo R May 21 '19 at 22:02

SQLAlchemy ORDER BY DESCENDING?

6 Answers6

You can use `.desc()` function in your query just like this

Use of desc function of SQLAlchemy

For official docs please use the link or check below snippet

Linked

Related

SQLAlchemy ORDER BY DESCENDING?

6 Answers6

You can use .desc() function in your query just like this

Use of desc function of SQLAlchemy

For official docs please use the link or check below snippet

Linked

Related

You can use `.desc()` function in your query just like this