Switch to new query API in SQLAlchemy 2.0

[robintw]

🐞 Overview

#830 is the issue for updating to support SQLAlchemy 1.4 and prepare for 2.0, and this involves making various changes as the SQLAlchemy API has had significant changes. One of these changes is a new recommended style for doing database queries. This is not required for v2.0, but it is recommended, and the old way will be deprecated and not shown in the docs etc.

The docs say:

The biggest visible change in SQLAlchemy 2.0 is the use of Session.execute() in conjunction with select() to run ORM queries, instead of using Session.query(). As mentioned elsewhere, there is no plan to actually remove the Session.query() API itself, as it is now implemented by using the new API internally it will remain as a legacy API, and both APIs can be used freely.

There are examples in the docs, but in our case code often looks like this:

session.query(User).all()

and it would need to be replaced with:

session.execute(
    select(User)
).scalars().all()

(I must admit that I don't like this style as much)

So, we should probably change this at some point, but it's not urgent as it isn't being removed from SQLAlchemy 2.0