SQLAlchemy ORDER BY DESCENDING?
PythonSqlalchemyPython Problem Overview
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.
Python Solutions
Solution 1 - Python
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
Solution 2 - Python
from sqlalchemy import desc
someselect.order_by(desc(table1.mycol))
Usage from @jpmc26
Solution 3 - Python
One other thing you might do is:
.order_by("name desc")
This will result in: ORDER BY name desc. The disadvantage here is the explicit column name used in order by.
Solution 4 - Python
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()
Solution 5 - Python
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()
Solution 6 - Python
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")