SQLAlchemy 2.0 version of User.query.get(1) in Flask-SQLAlchemy?

Question

The Problem

The Query.get() method is deprecated in SQLAlchemy 2.0. Accordingly, the Flask-SQLAlchemy query interface is considered legacy. Thus, running User.query.get(1) in my Flask-SQLAlchemy project gives the legacy warning shown below:

>>> User.query.get(1)
<stdin>:1: LegacyAPIWarning: The Query.get() method 
is considered legacy as of the 1.x series of SQLAlchemy 
and becomes a legacy construct in 2.0. The method is 
now available as Session.get() (deprecated since: 2.0) 
(Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
<User spongebob>

My Question

What is the new, SQLAlchemy 2.0-compatible version of User.query.get(1) in Flask-SQLAlchemy? More specifically, why does the Flask-SQLAlchemy documentation recommend Approach #2 below, even though Approach #1 appears to be the new version based on my reading of the SQLAlchemy 2.0 migration guide?

Approach #1: db.session.get(User, 1)

This first approach comes from the SQLAlchemy docs, specifically the SQLAlchemy 2.0 Migration - ORM Usage guide. Translating the "2.0 style" example in that guide to my Flask-SQLAlchemy project yields the following code, which works fine:

>>> db.session.get(User, 1)
<User spongebob>

This approach with session.get() isn't mentioned in the Flask-SQLAlchemy 3.0.x documentation as far as I can tell, except briefly in the API reference section on get_or_404.

Approach #2: db.session.execute(db.select(User).filter_by(id=1)).scalar()

This approach comes from the Flask-SQLAlchemy documentation, which suggests using session.execute(select(...)) as a replacement for the legacy Model.query and session.query. This works fine, too:

>>> db.session.execute(db.select(User).filter_by(id=1)).scalar()
<User spongebob>

Approach #1 vs. Approach #2 vs. Legacy Approach

Approach #1 (db.session.get(User, 1)) appears to be most similar to the Legacy Approach (User.query.get(1)) because it caches the result in the session the first time it runs and won't emit additional calls to the database unnecessarily. This can be seen in the REPL with the echo turned on, i.e. db.engine.echo = True. In contrast, Approach #2 (session.execute(select(...))) goes to the database each time, as expected.

My Set Up / Environment

1. Versions: Flask 2.2.2, Flask-SQLAlchemy 3.0.3, and SQLAlchemy 2.0.1 in a virtual environment with Python 3.11.

2. I'm using the project structure defined in the Flask Mega-Tutorial, specifically Part IV Database.

Answer 1

According to https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#orm-query-get-method-moves-to-session

The Query.get() method remains for legacy purposes, but the primary interface is now the Session.get() method:

# legacy usage
user_obj = session.query(User).get(5)

Migration to 2.0

In 1.4 / 2.0, the Session object adds a new Session.get() method:

# 1.4 / 2.0 cross-compatible use
user_obj = session.get(User, 5)

In which compatible with flask_sqlalchemy, this should work well

user_obj = db.session.get(User, 5)

Answer 2

I found an approach that avoids using the deprecated method while still letting you call a method on the model rather than having to access db.session.

First I noted that you can access the session from the model:

>>> MyModel.query.session
<sqlalchemy.orm.session.Session object at 0x7f1d9fe86c10>

So you could use the following verbose code:

>>> MyModel.query.session.get(MyModel, value)
<MyModel 1>

But that's not particularly elegant. Instead, in my case, I already made all my models inherit from a custom class I called IDMixin:

from flask_sqlalchemy import SQLAlchemy


db = SqlAlchemy()


class IDMixin:
    id = db.Column(db.Integer, primary_key=True)


class MyModel(IDMixin, db.Model):
    field = db.column(db.String(50))

So I added a classmethod to that mixin:

class IDMixin:
    id = db.Column(db.Integer, primary_key=True)

    @classmethod
    def get_by_id(cls, id_):
        return cls.query.session.get(cls, id_)

Now I can retrieve individual objects as follows:

>>> MyModel.get_by_id(1)
<MyModel 1>

It took a bit of custom code but I'm happy with the result.