Get first AND last element with SQLAlchemy

Question

In my Python (Flask) code, I need to get the first element and the last one sorted by a given variable from a SQLAlchemy query.

I first wrote the following code :

first_valuation = Valuation.query.filter_by(..).order_by(sqlalchemy.desc(Valuation.date)).first()
# Do some things
last_valuation = Valuation.query.filter_by(..).order_by(sqlalchemy.asc(Valuation.date)).first()
# Do other things

As these queries can be heavy for the PostgreSQL database, and as I am duplicating my code, I think it will be better to use only one request, but I don't know SQLAlchemy enough to do it... (When queries are effectively triggered, for example ?)

What is the best solution to this problem ?

Answer 1

1) How to get First and Last record from a sql query? this is about how to get first and last records in one query.

2) Here are docs on sqlalchemy query. Specifically pay attention to union_all (to implement answers from above). It also has info on when queries are triggered (basically, queries are triggered when you use methods, that returns results, like first() or all(). That means, Valuation.query.filter_by(..).order_by(sqlalchemy.desc(Valuation.date)) will not emit query to database).

Also, if memory is not a problem, I'd say get all() objects from your first query and just get first and last result via python:

results = Valuation.query.filter_by(..).order_by(sqlalchemy.desc(Valuation.date)).all()
first_valuation = results[0]
last_valuation = results[-1]

It will be faster than performing two (even unified) queries, but will potentially eat a lot of memory, if your database is large enough.