About SQLAlchemy Scalars

I often use session.scalars() in in my SQLAlchemy code, however, I don't understand why this method is called this name until further reading the doc today.

Basically, this is something to do with the return value data structure defined by SQLAlchemy.

Data Structure

Let's check out the image below, which simply illustrating how SQLAlchemy handle returned value.

As we can see, the elements provided to select() statements is packaged in Row, and all satisfied rows are packaged into Result. In some degree, we could consider the Result a 2-dim array, and Row element a vector.

Scalars

However, in normal ORM usecases, we actually don't need the Row wrapper in lots of cases seems the only element in Row will be some ORM Mapped class instance, check the following SELECT example:

select(User).where(User.created_time > ...)

Based on the content above, we know the original returned data should looks like:

Result(
  Row(User(), ),
  Row(User(), ),
  Row(User(), ),
  ...
)

In this case, the Row as a wrapper is completely redundant since only one element is inside. This is when we use session.scalars(), which will directly give us a ScalarResult object:

ScalarResult(
  User(),
  User(),
  User(),
  ...
)

In addition, if we know that we only want the first elements in the collections, we could call ScalarResult.first() like the example below:

session.scalars(
    select(User).where(User.user_id == 1)
).first()

Which will directly returns the single User class instance.