SqlAlchemy 2.0 中文文档（十八）（2）

>>> class Book(Base):
...     __tablename__ = "book"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     owner_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
...     title: Mapped[str]
...     summary: Mapped[str] = mapped_column(Text, deferred=True, deferred_raiseload=True)
...     cover_photo: Mapped[bytes] = mapped_column(
...         LargeBinary, deferred=True, deferred_raiseload=True
...     )
...
...     def __repr__(self) -> str:
...         return f"Book(id={self.id!r}, title={self.title!r})"

>>> book = session.scalar(select(Book).where(Book.id == 2))
SELECT  book.id,  book.owner_id,  book.title
FROM  book
WHERE  book.id  =  ?
[...]  (2,)
>>> book.summary
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: 'Book.summary' is not available due to raiseload=True

>>> book = session.scalar(
...     select(Book)
...     .where(Book.id == 2)
...     .options(undefer("*"))
...     .execution_options(populate_existing=True)
... )
SELECT  book.id,  book.owner_id,  book.title,  book.summary,  book.cover_photo
FROM  book
WHERE  book.id  =  ?
[...]  (2,)
>>> book.summary
'another long summary'
```## 将任意 SQL 表达式加载到对象上
如选择 ORM 实体和属性及其他地方所讨论的，可以使用 `select()` 结构在结果集中加载任意 SQL 表达式。比如，如果我们想要发出一个查询，加载 `User` 对象，但也包括每个 `User` 拥有多少书籍的计数，我们可以使用 `func.count(Book.id)` 将“计数”列添加到一个查询中，该查询包括与 `Book` 的 JOIN 以及按所有者 id 进行的 GROUP BY。这将产生 `Row` 对象，每个对象包含两个条目，一个是 `User`，一个是 `func.count(Book.id)`：
```py
>>> from sqlalchemy import func
>>> stmt = select(User, func.count(Book.id)).join_from(User, Book).group_by(Book.owner_id)
>>> for user, book_count in session.execute(stmt):
...     print(f"Username: {user.name}  Number of books: {book_count}")
SELECT  user_account.id,  user_account.name,  user_account.fullname,
count(book.id)  AS  count_1
FROM  user_account  JOIN  book  ON  user_account.id  =  book.owner_id
GROUP  BY  book.owner_id
[...]  ()
Username: spongebob  Number of books: 3
Username: sandy  Number of books: 3

>>> from sqlalchemy.orm import query_expression
>>> class User(Base):
...     __tablename__ = "user_account"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     name: Mapped[str]
...     fullname: Mapped[Optional[str]]
...     book_count: Mapped[int] = query_expression()
...
...     def __repr__(self) -> str:
...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

>>> from sqlalchemy.orm import with_expression
>>> stmt = (
...     select(User)
...     .join_from(User, Book)
...     .group_by(Book.owner_id)
...     .options(with_expression(User.book_count, func.count(Book.id)))
... )
>>> for user in session.scalars(stmt):
...     print(f"Username: {user.name}  Number of books: {user.book_count}")
SELECT  count(book.id)  AS  count_1,  user_account.id,  user_account.name,
user_account.fullname
FROM  user_account  JOIN  book  ON  user_account.id  =  book.owner_id
GROUP  BY  book.owner_id
[...]  ()
Username: spongebob  Number of books: 3
Username: sandy  Number of books: 3

# load the first A
obj = session.scalars(select(A).order_by(A.id)).first()
# load the same A with an option; expression will **not** be applied
# to the already-loaded object
obj = session.scalars(select(A).options(with_expression(A.expr, some_expr))).first()

obj = session.scalars(
    select(A)
    .options(with_expression(A.expr, some_expr))
    .execution_options(populate_existing=True)
).first()

# can't refer to A.expr elsewhere in the query
stmt = (
    select(A)
    .options(with_expression(A.expr, A.x + A.y))
    .filter(A.expr > 5)
    .order_by(A.expr)
)

# assign desired expression up front, then refer to that in
# the query
a_expr = A.x + A.y
stmt = (
    select(A)
    .options(with_expression(A.expr, a_expr))
    .filter(a_expr > 5)
    .order_by(a_expr)
)

>>> from sqlalchemy import union_all
>>> s1 = (
...     select(User, func.count(Book.id).label("book_count"))
...     .join_from(User, Book)
...     .where(User.name == "spongebob")
... )
>>> s2 = (
...     select(User, func.count(Book.id).label("book_count"))
...     .join_from(User, Book)
...     .where(User.name == "sandy")
... )
>>> union_stmt = union_all(s1, s2)
>>> orm_stmt = (
...     select(User)
...     .from_statement(union_stmt)
...     .options(with_expression(User.book_count, union_stmt.selected_columns.book_count))
... )
>>> for user in session.scalars(orm_stmt):
...     print(f"Username: {user.name}  Number of books: {user.book_count}")
SELECT  user_account.id,  user_account.name,  user_account.fullname,  count(book.id)  AS  book_count
FROM  user_account  JOIN  book  ON  user_account.id  =  book.owner_id
WHERE  user_account.name  =  ?
UNION  ALL
SELECT  user_account.id,  user_account.name,  user_account.fullname,  count(book.id)  AS  book_count
FROM  user_account  JOIN  book  ON  user_account.id  =  book.owner_id
WHERE  user_account.name  =  ?
[...]  ('spongebob',  'sandy')
Username: spongebob  Number of books: 3
Username: sandy  Number of books: 3

function sqlalchemy.orm.defer(key: Literal['*'] | QueryableAttribute[Any], *addl_attrs: Literal['*'] | QueryableAttribute[Any], raiseload: bool = False) → _AbstractLoad

from sqlalchemy.orm import defer
session.query(MyClass).options(
 defer(MyClass.attribute_one),
 defer(MyClass.attribute_two)
)

session.query(MyClass).options(
 defaultload(MyClass.someattr).defer(RelatedClass.some_column)
)

select(MyClass).options(
 defaultload(MyClass.someattr).options(
 defer(RelatedClass.some_column),
 defer(RelatedClass.some_other_column),
 defer(RelatedClass.another_column)
 )
)

function sqlalchemy.orm.deferred(column: _ORMColumnExprArgument[_T], *additional_columns: _ORMColumnExprArgument[Any], group: str | None = None, raiseload: bool = False, comparator_factory: Type[PropComparator[_T]] | None = None, init: _NoArg | bool = _NoArg.NO_ARG, repr: _NoArg | bool = _NoArg.NO_ARG, default: Any | None = _NoArg.NO_ARG, default_factory: _NoArg | Callable[[], _T] = _NoArg.NO_ARG, compare: _NoArg | bool = _NoArg.NO_ARG, kw_only: _NoArg | bool = _NoArg.NO_ARG, active_history: bool = False, expire_on_flush: bool = True, info: _InfoType | None = None, doc: str | None = None) → MappedSQLExpression[_T]

function sqlalchemy.orm.query_expression(default_expr: _ORMColumnExprArgument[_T] = <sqlalchemy.sql.elements.Null object>, *, repr: Union[_NoArg, bool] = _NoArg.NO_ARG, compare: Union[_NoArg, bool] = _NoArg.NO_ARG, expire_on_flush: bool = True, info: Optional[_InfoType] = None, doc: Optional[str] = None) → MappedSQLExpression[_T]

function sqlalchemy.orm.load_only(*attrs: Literal['*'] | QueryableAttribute[Any], raiseload: bool = False) → _AbstractLoad

session.query(User).options(load_only(User.name, User.fullname))

session.query(User).options(
 subqueryload(User.addresses).load_only(Address.email_address)
)

stmt = (
 select(User, Address)
 .join(User.addresses)
 .options(
 Load(User).load_only(User.name, User.fullname),
 Load(Address).load_only(Address.email_address),
 )
)

function sqlalchemy.orm.undefer(key: Literal['*'] | QueryableAttribute[Any], *addl_attrs: Literal['*'] | QueryableAttribute[Any]) → _AbstractLoad

# undefer two columns
session.query(MyClass).options(
 undefer(MyClass.col1), undefer(MyClass.col2)
)
# undefer all columns specific to a single class using Load + *
session.query(MyClass, MyOtherClass).options(
 Load(MyClass).undefer("*")
)
# undefer a column on a related object
select(MyClass).options(
 defaultload(MyClass.items).undefer(MyClass.text)
)

function sqlalchemy.orm.undefer_group(name: str) → _AbstractLoad

session.query(MyClass).options(undefer_group("large_attrs"))

select(MyClass).options(
 defaultload("someattr").undefer_group("large_attrs")
)

function sqlalchemy.orm.with_expression(key: _AttrType, expression: _ColumnExpressionArgument[Any]) → _AbstractLoad

stmt = select(SomeClass).options(
 with_expression(SomeClass.x_y_expr, SomeClass.x + SomeClass.y)
)