AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

SqlAlchemy 2.0 中文文档（三十二）（4）

2024-07-08 20
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： SqlAlchemy 2.0 中文文档（三十二）

SqlAlchemy 2.0 中文文档（三十二）（3）https://developer.aliyun.com/article/1562551


与关系一起工作

创建与基于列的数据相反的与相关对象一起工作的混合类型时，没有本质区别。对于不同的表达式的需求往往更大。我们将说明的两种变体是“join-dependent”混合类型和“correlated subquery”混合类型。

Join-Dependent Relationship Hybrid

考虑以下将User与SavingsAccount相关联的声明性映射：

from __future__ import annotations
from decimal import Decimal
from typing import cast
from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy import SQLColumnExpression
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
    pass
class SavingsAccount(Base):
    __tablename__ = 'account'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('user.id'))
    balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
    owner: Mapped[User] = relationship(back_populates="accounts")
class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    accounts: Mapped[List[SavingsAccount]] = relationship(
        back_populates="owner", lazy="selectin"
    )
    @hybrid_property
    def balance(self) -> Optional[Decimal]:
        if self.accounts:
            return self.accounts[0].balance
        else:
            return None
    @balance.inplace.setter
    def _balance_setter(self, value: Optional[Decimal]) -> None:
        assert value is not None
        if not self.accounts:
            account = SavingsAccount(owner=self)
        else:
            account = self.accounts[0]
        account.balance = value
    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Optional[Decimal]]:
        return cast("SQLColumnExpression[Optional[Decimal]]", SavingsAccount.balance)

上述混合属性balance与此用户的账户列表中的第一个SavingsAccount条目配合使用。在 Python 中，getter/setter 方法可以将accounts视为self上可用的 Python 列表。

提示

上述示例中的User.balance getter 访问self.acccounts集合，通常会通过配置在User.balance relationship()上的selectinload()加载策略加载。当未在relationship()上另行说明时，默认加载策略是lazyload()，它会按需发出 SQL。在使用 asyncio 时，不支持按需加载程序，因此在使用 asyncio 时，应确保self.accounts集合对此混合访问器可访问。

在表达式级别，预期User类将在适当的上下文中使用，以便存在适当的连接到SavingsAccount：

>>> from sqlalchemy import select
>>> print(select(User, User.balance).
...       join(User.accounts).filter(User.balance > 5000))
SELECT  "user".id  AS  user_id,  "user".name  AS  user_name,
account.balance  AS  account_balance
FROM  "user"  JOIN  account  ON  "user".id  =  account.user_id
WHERE  account.balance  >  :balance_1

但请注意，尽管实例级访问器需要担心self.accounts是否存在，但在 SQL 表达式级别，这个问题表现得不同，基本上我们会使用外连接：

>>> from sqlalchemy import select
>>> from sqlalchemy import or_
>>> print (select(User, User.balance).outerjoin(User.accounts).
...         filter(or_(User.balance < 5000, User.balance == None)))
SELECT  "user".id  AS  user_id,  "user".name  AS  user_name,
account.balance  AS  account_balance
FROM  "user"  LEFT  OUTER  JOIN  account  ON  "user".id  =  account.user_id
WHERE  account.balance  <  :balance_1  OR  account.balance  IS  NULL

相关子查询关系混合

当然，我们可以放弃依赖于连接的查询使用，转而使用相关子查询，这可以方便地打包到单个列表达式中。相关子查询更具可移植性，但在 SQL 级别通常性能较差。使用与使用 column_property 中所示技术相同的技术，我们可以调整我们的SavingsAccount示例以聚合所有账户的余额，并使用相关子查询作为列表达式：

from __future__ import annotations
from decimal import Decimal
from typing import List
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Numeric
from sqlalchemy import select
from sqlalchemy import SQLColumnExpression
from sqlalchemy import String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
    pass
class SavingsAccount(Base):
    __tablename__ = 'account'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('user.id'))
    balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
    owner: Mapped[User] = relationship(back_populates="accounts")
class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    accounts: Mapped[List[SavingsAccount]] = relationship(
        back_populates="owner", lazy="selectin"
    )
    @hybrid_property
    def balance(self) -> Decimal:
        return sum((acc.balance for acc in self.accounts), start=Decimal("0"))
    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
        return (
            select(func.sum(SavingsAccount.balance))
            .where(SavingsAccount.user_id == cls.id)
            .label("total_balance")
        )

上述配方将为我们提供呈现相关 SELECT 的balance列：

>>> from sqlalchemy import select
>>> print(select(User).filter(User.balance > 400))
SELECT  "user".id,  "user".name
FROM  "user"
WHERE  (
  SELECT  sum(account.balance)  AS  sum_1  FROM  account
  WHERE  account.user_id  =  "user".id
)  >  :param_1

连接依赖关系混合

考虑以下声明性映射，将User与SavingsAccount相关联：

from __future__ import annotations
from decimal import Decimal
from typing import cast
from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy import SQLColumnExpression
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
    pass
class SavingsAccount(Base):
    __tablename__ = 'account'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('user.id'))
    balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
    owner: Mapped[User] = relationship(back_populates="accounts")
class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    accounts: Mapped[List[SavingsAccount]] = relationship(
        back_populates="owner", lazy="selectin"
    )
    @hybrid_property
    def balance(self) -> Optional[Decimal]:
        if self.accounts:
            return self.accounts[0].balance
        else:
            return None
    @balance.inplace.setter
    def _balance_setter(self, value: Optional[Decimal]) -> None:
        assert value is not None
        if not self.accounts:
            account = SavingsAccount(owner=self)
        else:
            account = self.accounts[0]
        account.balance = value
    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Optional[Decimal]]:
        return cast("SQLColumnExpression[Optional[Decimal]]", SavingsAccount.balance)

上述混合属性balance与此用户的账户列表中的第一个SavingsAccount条目配合使用。在 Python 中，getter/setter 方法可以将accounts视为self上可用的 Python 列表。

提示

上面示例中的User.balance getter 访问了self.acccounts集合，这通常会通过在User.balance relationship()上配置的selectinload()加载器策略进行加载。当没有在其他地方声明relationship()时，默认的加载器策略是lazyload()，它按需发出 SQL。当使用 asyncio 时，不支持按需加载器，如lazyload()，因此在使用 asyncio 时应注意确保self.accounts集合对此混合访问器是可访问的。

在表达式级别，预计User类将在适当的上下文中使用，以便存在适当的连接到SavingsAccount：

>>> from sqlalchemy import select
>>> print(select(User, User.balance).
...       join(User.accounts).filter(User.balance > 5000))
SELECT  "user".id  AS  user_id,  "user".name  AS  user_name,
account.balance  AS  account_balance
FROM  "user"  JOIN  account  ON  "user".id  =  account.user_id
WHERE  account.balance  >  :balance_1

但请注意，尽管实例级别的访问器需要担心self.accounts是否存在，但这个问题在 SQL 表达式级别上表现得不同，我们基本上会使用外连接：

>>> from sqlalchemy import select
>>> from sqlalchemy import or_
>>> print (select(User, User.balance).outerjoin(User.accounts).
...         filter(or_(User.balance < 5000, User.balance == None)))
SELECT  "user".id  AS  user_id,  "user".name  AS  user_name,
account.balance  AS  account_balance
FROM  "user"  LEFT  OUTER  JOIN  account  ON  "user".id  =  account.user_id
WHERE  account.balance  <  :balance_1  OR  account.balance  IS  NULL

关联子查询关系混合

当然，我们可以放弃依赖包含查询中的连接，而倾向于关联子查询，这可以被封装成一个单列表达式。关联子查询更具可移植性，但在 SQL 级别上通常性能较差。使用在使用 column_property 中说明的相同技术，我们可以调整我们的SavingsAccount示例来聚合所有账户的余额，并为列表达式使用关联子查询：

from __future__ import annotations
from decimal import Decimal
from typing import List
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Numeric
from sqlalchemy import select
from sqlalchemy import SQLColumnExpression
from sqlalchemy import String
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
class Base(DeclarativeBase):
    pass
class SavingsAccount(Base):
    __tablename__ = 'account'
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey('user.id'))
    balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
    owner: Mapped[User] = relationship(back_populates="accounts")
class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    accounts: Mapped[List[SavingsAccount]] = relationship(
        back_populates="owner", lazy="selectin"
    )
    @hybrid_property
    def balance(self) -> Decimal:
        return sum((acc.balance for acc in self.accounts), start=Decimal("0"))
    @balance.inplace.expression
    @classmethod
    def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
        return (
            select(func.sum(SavingsAccount.balance))
            .where(SavingsAccount.user_id == cls.id)
            .label("total_balance")
        )

上面的配方将为我们提供一个渲染关联 SELECT 的balance列：

>>> from sqlalchemy import select
>>> print(select(User).filter(User.balance > 400))
SELECT  "user".id,  "user".name
FROM  "user"
WHERE  (
  SELECT  sum(account.balance)  AS  sum_1  FROM  account
  WHERE  account.user_id  =  "user".id
)  >  :param_1


SqlAlchemy 2.0 中文文档（三十二）（5）https://developer.aliyun.com/article/1562558

文章标签：
Python
SQL
布客飞龙
目录
相关文章
布客飞龙
|
3月前
|
SQL 存储 关系型数据库
SqlAlchemy 2.0 中文文档（二十九）（1）
SqlAlchemy 2.0 中文文档（二十九）
布客飞龙
41 4
布客飞龙
|
3月前
|
SQL API 数据安全/隐私保护
SqlAlchemy 2.0 中文文档（三十二）（3）
SqlAlchemy 2.0 中文文档（三十二）
布客飞龙
22 1
布客飞龙
|
3月前
|
SQL 关系型数据库 数据库
SqlAlchemy 2.0 中文文档（三十四）（5）
SqlAlchemy 2.0 中文文档（三十四）
布客飞龙
35 0
布客飞龙
|
3月前
|
SQL 存储 关系型数据库
SqlAlchemy 2.0 中文文档（三十四）（4）
SqlAlchemy 2.0 中文文档（三十四）
布客飞龙
37 1
布客飞龙
|
3月前
|
存储 SQL 测试技术
SqlAlchemy 2.0 中文文档（三十一）（1）
SqlAlchemy 2.0 中文文档（三十一）
布客飞龙
29 1
布客飞龙
|
3月前
|
SQL 测试技术 数据库
SqlAlchemy 2.0 中文文档（三十一）（2）
SqlAlchemy 2.0 中文文档（三十一）
布客飞龙
24 1
布客飞龙
|
3月前
|
SQL 数据库 Python
SqlAlchemy 2.0 中文文档（三十一）（3）
SqlAlchemy 2.0 中文文档（三十一）
布客飞龙
23 1
布客飞龙
|
3月前
|
JSON 测试技术 数据格式
SqlAlchemy 2.0 中文文档（三十一）（4）
SqlAlchemy 2.0 中文文档（三十一）
布客飞龙
31 1
布客飞龙
|
3月前
|
SQL API 数据安全/隐私保护
SqlAlchemy 2.0 中文文档（三十二）（5）
SqlAlchemy 2.0 中文文档（三十二）
布客飞龙
25 0
布客飞龙
|
3月前
|
SQL Python
SqlAlchemy 2.0 中文文档（三十二）（2）
SqlAlchemy 2.0 中文文档（三十二）
布客飞龙
18 0