SQLAlchemy 2.0 统一教程 P2

SQLAlchemy 呈现为两个不同的 API，一个构建在另一个之上。这些 API 称为 Core 和 ORM。

SQLAlchemy Core 是 SQLAlchemy 作为“数据库工具包”的基础架构。该库提供了用于管理数据库连接、与数据库查询和结果交互以及 SQL 语句的编程构造的工具。

主要仅限核心的部分不会引用 ORM。这些部分中使用的 SQLAlchemy 构造将从 sqlalchemy 命名空间导入。作为主题分类的附加指标，它们还将在右侧包含深蓝色边框。使用 ORM 时，这些概念仍然有效，但在用户代码中不太明确。 ORM 用户应该阅读这些部分，但不要期望直接将这些 API 用于以 ORM 为中心的代码。

SQLAlchemy ORM 构建在 Core 之上，提供可选的对象关系映射功能。 ORM 提供了一个额外的配置层，允许将用户定义的 Python 类映射到数据库表和其他构造，以及称为会话的对象持久性机制。然后，它扩展了核心级 SQL 表达式语言，以允许根据用户定义的对象来组合和调用 SQL 查询。

主要仅涉及 ORM 的部分的标题应包含短语“ORM”，以便清楚地表明这是一个 ORM 相关主题。这些部分中使用的 SQLAlchemy 构造将从 sqlalchemy.orm 命名空间导入。最后，作为主题分类的附加指标，它们还将在左侧包含浅蓝色边框。仅核心用户可以跳过这些。

本教程中的大多数部分都讨论了 ORM 中显式使用的核心概念。 SQLAlchemy 2.0 尤其具有在 ORM 中使用核心 API 的更高级别的集成。

对于每个部分，都会有介绍性文本讨论 ORM 用户应该期望使用这些编程模式的程度。这些部分中的 SQLAlchemy 构造将从 sqlalchemy 命名空间导入，同时可能使用 sqlalchemy.orm 构造。作为主题分类的附加指标，这些部分还将包括左侧较细的浅色边框和右侧较粗的深色边框。核心用户和 ORM 用户应该同样熟悉这些部分中的概念。

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

from sqlalchemy import create_engine
engine = create_engine('sqlite:///blard.sqlite3', echo=True)

Working with DataBase MetaData 元数据

使用表对象设置元数据

from sqlalchemy import MetaData
metadata_obj = MetaData()

一旦我们有了一个 MetaData 对象，我们就可以声明一些 Table 对象。本教程将从经典的 SQLAlchemy 教程模型开始，该模型有一个名为 user_account 的表，用于存储网站用户等信息，以及一个相关的表 address ，用于存储电子邮件与 user_account 表中的行关联的地址。当根本不使用 ORM 声明性模型时，我们直接构造每个 Table 对象，通常将每个对象分配给一个变量，这将是我们在应用程序代码中引用表的方式：

from sqlalchemy import Table, Column, Integer, String
user_table = Table(
    "user_account",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(30)),
    Column("fullname", String),
)

user_table.c.name

user_table.c.keys()

Column('name', String(length=30), table=<user_account>)






['id', 'name', 'fullname']

user_table.primary_key

PrimaryKeyConstraint(Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False))

下面我们声明第二个表 address ，它将具有引用 user 表的外键约束：

from sqlalchemy import ForeignKey
address_table = Table(
    "address",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("user_id", ForeignKey("user_account.id"), nullable=False),
    Column("email_address", String, nullable=False),
)

向数据库发送 DDL ¶

metadata_obj.create_all(engine)

2024-01-17 16:28:32,231 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,232 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2024-01-17 16:28:32,233 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,235 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user_account")
2024-01-17 16:28:32,236 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,237 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2024-01-17 16:28:32,238 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,239 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("address")
2024-01-17 16:28:32,239 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,241 INFO sqlalchemy.engine.Engine 
CREATE TABLE user_account (
	id INTEGER NOT NULL, 
	name VARCHAR(30), 
	fullname VARCHAR, 
	PRIMARY KEY (id)
)


2024-01-17 16:28:32,241 INFO sqlalchemy.engine.Engine [no key 0.00071s] ()
2024-01-17 16:28:32,248 INFO sqlalchemy.engine.Engine 
CREATE TABLE address (
	id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	email_address VARCHAR NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES user_account (id)
)


2024-01-17 16:28:32,249 INFO sqlalchemy.engine.Engine [no key 0.00155s] ()
2024-01-17 16:28:32,254 INFO sqlalchemy.engine.Engine COMMIT

上面的 DDL 创建过程包括一些特定于 SQLite 的 PRAGMA 语句，这些语句在发出 CREATE 之前测试每个表是否存在。完整的一系列步骤也包含在 BEGIN/COMMIT 对中，以适应事务性 DDL。

MetaData 对象还具有 MetaData.drop_all() 方法，该方法将以相反的顺序发出 DROP 语句，因为它会发出 CREATE 来删除架构元素。

使用 ORM 声明式形式定义表元数据

本节将说明使用声明性表构建的与上一节相同的 Table 元数据。

前面的示例说明了 Table 对象的直接使用，它是 SQLAlchemy 在构造 SQL 表达式时最终如何引用数据库表的基础。如前所述，SQLAlchemy ORM 围绕 Table 声明过程（称为声明表）提供了一个外观。声明性表过程实现了与上一节中相同的目标，即构建 Table 对象，但也在该过程中为我们提供了其他称为 ORM 映射类的东西，或者简称为“映射类”。映射类是使用 ORM 时最常见的 SQL 基础单元，在现代 SQLAlchemy 中也可以非常有效地以 Core 为中心的使用。

建立声明性基础 ¶

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass

Base.metadata

# 此 MetaData 集合可通过 DeclarativeBase.metadata 类级属性访问。当我们创建新的映射类时，它们每个都将引用此 MetaData 集合中的 Table ：

MetaData()

Base.registry

# 声明性基础还引用了一个名为 registry 的集合，它是 SQLAlchemy ORM 中的中央“映射器配置”单元。虽然很少直接访问，但该对象是映射器配置过程的核心，因为一组 ORM 映射类将通过此注册表相互协调。与 MetaData 的情况一样，我们的声明性基础也为我们创建了一个 registry （同样带有传递我们自己的 registry 的选项），我们可以通过 DeclarativeBase.registry 类变量：

<sqlalchemy.orm.decl_api.registry at 0x23d581ecfd0>

声明映射类

建立 Base 类后，我们现在可以根据新类 User 和 user_account 和 address 表定义 ORM 映射类 Address 。我们在下面说明了最现代的声明式形式，它是使用特殊类型 Mapped 从 PEP 484 类型注释驱动的，该类型指示要映射为特定类型的属性：

Mapped

from typing import List
from typing import Optional
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class User(Base):
    __tablename__ = "user_account"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(back_populates="user")

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

# 我们上面的映射可以在没有类型注释的情况下编写为：
# class User(Base):
#     __tablename__ = "user_account"
#     id = mapped_column(Integer, primary_key=True)
#     name = mapped_column(String(30), nullable=False)
#     fullname = mapped_column(String)
#     addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = "address"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id = mapped_column(ForeignKey("user_account.id"))
    user: Mapped[User] = relationship(back_populates="addresses")
    
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

上面的两个类 User 和 Address 现在被称为 ORM 映射类，可用于 ORM 持久化和查询操作，稍后将对此进行描述。

# 如果我们不声明自己的方法，这些类会自动获得 __init__() 方法。此方法的默认形式接受所有属性名称作为可选关键字参数：
sandy = User(name="sandy", fullname="Sandy Cheeks")

SQLAlchemy 1.4 或更低版本的用户会注意到上面的映射使用了与以前显着不同的形式；它不仅在声明性映射中使用 mapped_column() 而不是 Column ，它还使用 Python 类型注释来派生列信息。

为了为“旧”方式的用户提供上下文，仍然可以像以前一样使用 Column 对象（以及使用 declarative_base() 函数创建基类）进行声明性映射，这些表格将继续受到支持，并且没有计划取消支持。这两个工具被新结构取代的原因首先是为了与 PEP 484 工具顺利集成，包括 VSCode 等 IDE 和 Mypy 和 Pyright 等类型检查器，而无需插件。其次，从类型注释派生声明是 SQLAlchemy 与 Python 数据类集成的一部分，现在可以从映射本地生成数据类。

对于喜欢“旧”方式但仍希望 IDE 不会错误地报告声明性映射的键入错误的用户来说， mapped_column() 构造是 Column 的直接替代品ORM 声明性映射（请注意， mapped_column() 仅适用于 ORM 声明性映射；它不能在 Table 构造中使用），并且类型注释是可选的。

从 ORM 映射向数据库发送 DDL

Base.metadata.create_all(engine)

2024-01-17 16:28:32,460 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,461 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2024-01-17 16:28:32,461 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,462 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2024-01-17 16:28:32,463 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-01-17 16:28:32,464 INFO sqlalchemy.engine.Engine COMMIT

Table Reflection 表反射

表反射是指通过读取数据库当前状态生成 Table 及相关对象的过程。在前面的部分中，我们已经在 Python 中声明了 Table 对象，然后我们可以选择向数据库发出 DDL 以生成这样的模式，而反射过程则相反地执行这两个步骤，从从现有数据库生成 Python 数据结构来表示该数据库中的模式。

作为反射的示例，我们将创建一个新的 Table 对象，它代表我们在本文档前面部分中手动创建的 some_table 对象。执行方式也有多种，但最基本的是构造一个 Table 对象，给定表的名称和它所属的 MetaData 集合，然后，不要指示单独的 Column 和 Constraint 对象，而是使用 Table.autoload_with 参数将其传递给目标 Engine ：

# some_table = Table("some_table", metadata_obj, autoload_with=engine)
# NoSuchTableError: some_table
# user_account2 = Table("user_account", metadata_obj, autoload_with=engine)

# # 在该过程结束时， some_table 对象现在包含有关表中存在的 Column 对象的信息，并且该对象的使用方式与 Table 我们明确声明：
# user_account2
# del user_account2

现在，我们已经准备好了一个 SQLite 数据库，其中包含两个表，以及 Core 和 ORM 面向表的构造，我们可以使用它们通过 Connection 和/或 ORM Session .在以下部分中，我们将说明如何使用这些结构创建、操作和选择数据。

Working with Data 处理数据

在本节中，我们将结合上述两个概念来创建、选择和操作关系数据库中的数据。我们与数据库的交互始终以事务的形式进行，即使我们已将数据库驱动程序设置为在幕后使用自动提交。

使用 INSERT 语句 ¶

当使用 Core 以及使用 ORM 进行批量操作时，会使用 insert() 函数直接生成 SQL INSERT 语句 - 该函数生成一个 Insert 的新实例，它代表一个 INSERT SQL 语句，将新数据添加到表中。

insert

from sqlalchemy import insert
stmt = insert(user_table).values(
    name="spongebob", fullname="Spongebob Squarepants")
# 上面的 stmt 变量是 Insert 的实例。大多数 SQL 表达式都可以就地进行字符串化，作为查看所生成内容的一般形式的一种方法：
print(stmt)

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

compiled = stmt.compile()
compiled.params
# 我们的 Insert 构造是“参数化”构造的一个示例，如之前在发送参数中所示；要查看 name 和 fullname 绑定参数，这些参数也可从 Compiled 构造中获取：

{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

with engine.connect() as conn:
    result = conn.execute(stmt)
    # 调用该语句，我们可以将一行插入到 user_table 中。 INSERT SQL 以及捆绑的参数可以在 SQL 日志记录中看到：
    conn.commit()
    # 数据库中的第一行通常会返回 1 作为第一个整数主键值，我们可以使用 CursorResult.inserted_primary_key 访问器获取该值：
    result.inserted_primary_key

2024-01-17 16:28:32,523 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,524 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)
2024-01-17 16:28:32,524 INFO sqlalchemy.engine.Engine [generated in 0.00136s] ('spongebob', 'Spongebob Squarepants')
2024-01-17 16:28:32,527 INFO sqlalchemy.engine.Engine COMMIT





(1,)

如果我们采用一个尚未调用 Insert.values() 的 Insert 构造并执行它，则该语句将根据我们传递给的参数编译为字符串 Connection.execute() 方法，并且仅包含与传递的参数相关的列。这实际上是使用 Insert 插入行而无需键入显式 VALUES 子句的常用方法。下面的示例说明了一次使用参数列表执行的两列 INSERT 语句：

with engine.connect() as conn:
    result = conn.execute(
        insert(user_table),
        [
            {"name": "sandy", "fullname": "Sandy Cheeks"}, 
            {"name": "patrick", "fullname": "Patrick Star"},
        ],
    )
    conn.commit()

2024-01-17 16:28:32,544 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,545 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)
2024-01-17 16:28:32,545 INFO sqlalchemy.engine.Engine [generated in 0.00157s] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')]
2024-01-17 16:28:32,547 INFO sqlalchemy.engine.Engine COMMIT

上面的执行采用“executemany”形式，首先在发送多个参数中进行说明，但与使用 text() 构造时不同，我们不必拼写任何 SQL。通过将字典或字典列表与 Insert 构造一起传递给 Connection.execute() 方法， Connection 确保传递的列名将被表达自动在 Insert 构造的 VALUES 子句中。

为了在 address_table 中包含一些有趣的数据，下面是一个更高级的示例，说明如何同时显式使用 Insert.values() 方法包括从参数生成的附加值。使用下一节中介绍的 select() 构造构造标量子查询，并使用显式绑定参数名称来设置子查询中使用的参数，该参数名称是使用 bindparam() 构建。这是一些更深层次的炼金术，以便我们可以添加相关行，而无需从 user_table 操作中将主键标识符获取到应用程序中。

from sqlalchemy import select, bindparam
scalar_subq = (
    select(user_table.c.id)
    .where(user_table.c.name == bindparam("username"))
    .scalar_subquery()
)

with engine.connect() as conn:
    result = conn.execute(
        insert(address_table).values(user_id=scalar_subq),
        [
            {
                "username": "spongebob",
                "email_address": "spongebob@sqlalchemy.org",
            },
            {"username": "sandy", "email_address": "sandy@sqlalchemy.org"},
            {"username": "sandy", "email_address": "sandy@squirrelpower.org"},
        ],
    )
    conn.commit()  

2024-01-17 16:28:32,564 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,566 INFO sqlalchemy.engine.Engine INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id 
FROM user_account 
WHERE user_account.name = ?), ?)
2024-01-17 16:28:32,567 INFO sqlalchemy.engine.Engine [generated in 0.00262s] [('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org')]
2024-01-17 16:28:32,568 INFO sqlalchemy.engine.Engine COMMIT

INSERT…RETURNING

insert_stmt = insert(address_table).returning(
    address_table.c.id, address_table.c.email_address
)
print(insert_stmt)

INSERT INTO address (id, user_id, email_address) VALUES (:id, :user_id, :email_address) RETURNING address.id, address.email_address

select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
insert_stmt = insert(address_table).from_select(
    ["user_id", "email_address"], select_stmt
)
print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 
FROM user_account RETURNING address.id, address.email_address

INSERT…FROM SELECT

select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
insert_stmt = insert(address_table).from_select(
    ["user_id", "email_address"], select_stmt
)
print(insert_stmt)

INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 
FROM user_account

使用 SELECT 语句

对于 Core 和 ORM， select() 函数生成一个用于所有 SELECT 查询的 Select 构造。传递给 Core 中的 Connection.execute() 和 ORM 中的 Session.execute() 等方法，在当前事务中发出 SELECT 语句，并通过返回的 Result 对象获取结果行。

select

select() 表达式结构

from sqlalchemy import select
stmt = select(user_table).where(user_table.c.name == "spongebob")
print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = :name_1

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(row)

2024-01-17 16:28:32,622 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,623 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:32,624 INFO sqlalchemy.engine.Engine [generated in 0.00181s] ('spongebob',)
(1, 'spongebob', 'Spongebob Squarepants')
2024-01-17 16:28:32,625 INFO sqlalchemy.engine.Engine ROLLBACK

当使用 ORM 时，特别是使用针对 ORM 实体组成的 select() 构造时，我们将希望使用 Session 上的 Session.execute() 方法来执行它；使用这种方法，我们继续从结果中获取 Row 对象，但是这些行现在能够包含完整的实体，例如 User 类的实例，作为每个中的单独元素排：

# from sqlalchemy import Session
from sqlalchemy.orm import Session

stmt = select(User).where(User.name == "spongebob")
with Session(engine) as session:
    for row in session.execute(stmt):
        print(row)

2024-01-17 16:28:32,643 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,645 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:32,646 INFO sqlalchemy.engine.Engine [generated in 0.00103s] ('spongebob',)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
2024-01-17 16:28:32,648 INFO sqlalchemy.engine.Engine ROLLBACK

虽然无论我们调用 select(user_table) 还是 select(User) ，这些示例中生成的 SQL 看起来都是相同的，但在更一般的情况下，它们不一定呈现相同的内容，因为 ORM 映射的类可能会呈现相同的内容。映射到除表格之外的其他类型的“可选择项”。针对 ORM 实体的 select() 还指示应在结果中返回 ORM 映射的实例，而从 Table 对象中进行 SELECT 时情况并非如此。

设置 COLUMNS 和 FROM 子句

print(select(user_table))
# 该子句是根据传递的列和类表表达式推断出来的：

print(select(user_table.c.name, user_table.c.fullname))
# 要使用核心方法从各个列中进行 SELECT，可以从 Table.c 访问器访问 Column 对象，并且可以直接发送； FROM 子句将被推断为由这些列表示的所有 Table 和其他 FromClause 对象的集合：

print(select(user_table.c["name", "fullname"]))
# 或者，当使用任何 FromClause （例如 Table ）的 FromClause.c 集合时，可以使用元组为 select() 指定多个列字符串名称：

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account
SELECT user_account.name, user_account.fullname 
FROM user_account
SELECT user_account.name, user_account.fullname 
FROM user_account

选择 ORM 实体和列 ¶

ORM 实体（例如我们的 User 类及其上的列映射属性（例如 User.name ））也参与表示表和列的 SQL 表达式语言系统。下面说明了从 User 实体进行 SELECT 的示例，它最终的呈现方式与我们直接使用 user_table 的方式相同。当使用 ORM Session.execute() 方法执行上述语句时，当我们从完整实体（例如 User ）中进行选择时，与 user_table ，实体本身作为每行中的单个元素返回。也就是说，当我们从上述语句中获取行时，由于要获取的内容列表中只有 User 实体，因此我们会返回只有一个元素的 Row 对象，其中包含 User 类的实例：

print(select(User))

row = session.execute(select(User)).first()
row

# 上面的 Row 只有一个元素，代表 User 实体：

row[0]

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:32,667 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,669 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:32,669 INFO sqlalchemy.engine.Engine [generated in 0.00080s] ()





(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)






User(id=1, name='spongebob', fullname='Spongebob Squarepants')

强烈推荐的实现与上述相同结果的便捷方法是使用 Session.scalars() 方法直接执行语句；此方法将返回一个 ScalarResult 对象，该对象一次提供每行的第一个“列”，在本例中为 User 类的实例：

user = session.scalars(select(User)).first()
user

2024-01-17 16:28:32,681 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:32,682 INFO sqlalchemy.engine.Engine [cached since 0.01335s ago] ()





User(id=1, name='spongebob', fullname='Spongebob Squarepants')

或者，我们可以使用类绑定属性选择 ORM 实体的各个列作为结果行中的不同元素；当它们传递给诸如 select() 之类的构造时，它们将被解析为 Column 或由每个属性表示的其他 SQL 表达式：

print(select(User.name, User.fullname))

row = session.execute(select(User.name, User.fullname)).first()
row

SELECT user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:32,694 INFO sqlalchemy.engine.Engine SELECT user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:32,695 INFO sqlalchemy.engine.Engine [generated in 0.00081s] ()





('spongebob', 'Spongebob Squarepants')

# 这些方法也可以混合使用，如下所示，我们选择 User 实体的 name 属性作为行的第一个元素，并将其与完整的 Address 第二个元素中的实体一起返回：
session.execute(
    select(User.name, Address).where(
        User.id == Address.user_id).order_by(Address.id)
).all()

2024-01-17 16:28:32,707 INFO sqlalchemy.engine.Engine SELECT user_account.name, address.id, address.email_address, address.user_id 
FROM user_account, address 
WHERE user_account.id = address.user_id ORDER BY address.id
2024-01-17 16:28:32,708 INFO sqlalchemy.engine.Engine [generated in 0.00094s] ()





[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')), ('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')), ('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

选择 ORM 实体和属性 - 在 ORM 查询指南

从带标签的 SQL 表达式中选择 ¶

from sqlalchemy import func, cast

stmt = select(
    ("Username: " + user_table.c.name).label("username"),
).order_by(user_table.c.name)

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(f"{row.username}")

2024-01-17 16:28:32,721 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,722 INFO sqlalchemy.engine.Engine SELECT ? || user_account.name AS username 
FROM user_account ORDER BY user_account.name
2024-01-17 16:28:32,723 INFO sqlalchemy.engine.Engine [generated in 0.00151s] ('Username: ',)
Username: patrick
Username: sandy
Username: spongebob
2024-01-17 16:28:32,724 INFO sqlalchemy.engine.Engine ROLLBACK

使用文本列表达式进行选择 ¶

有时也需要在语句中创建任意 SQL 块，例如常量字符串表达式，或者只是一些按字面编写速度更快的任意 SQL.

使用事务和 DBAPI 中引入的 text() 构造实际上可以直接嵌入到 Select 构造中，例如下面我们制造硬编码字符串文字 'some phrase' 并将其嵌入到 SELECT 语句中：

from sqlalchemy import text
stmt = select(text("'some phrase'"), user_table.c.name).order_by(
    user_table.c.name)
with engine.connect() as conn:
    print(conn.execute(stmt).all())

2024-01-17 16:28:32,735 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,736 INFO sqlalchemy.engine.Engine SELECT 'some phrase', user_account.name 
FROM user_account ORDER BY user_account.name
2024-01-17 16:28:32,737 INFO sqlalchemy.engine.Engine [generated in 0.00170s] ()
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]
2024-01-17 16:28:32,738 INFO sqlalchemy.engine.Engine ROLLBACK

虽然 text() 构造可以在大多数地方使用来注入文字 SQL 短语，但我们实际上经常处理的是每个表示单个列表达式的文本单元。在这种常见情况下，我们可以使用 literal_column() 构造从文本片段中获得更多功能。该对象与 text() 类似，不同之处在于它不表示任何形式的任意 SQL，而是显式表示单个“列”，然后可以在子查询和其他表达式中进行标记和引用：

from sqlalchemy import literal_column
stmt = select(literal_column("'some phrase'").label("p"), user_table.c.name).order_by(
    user_table.c.name
)
with engine.connect() as conn:
    # print(conn.execute(stmt).all())
    for row in conn.execute(stmt):
        print(f"{row.p}, {row.name}")

2024-01-17 16:28:32,748 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,749 INFO sqlalchemy.engine.Engine SELECT 'some phrase' AS p, user_account.name 
FROM user_account ORDER BY user_account.name
2024-01-17 16:28:32,750 INFO sqlalchemy.engine.Engine [generated in 0.00176s] ()
some phrase, patrick
some phrase, sandy
some phrase, spongebob
2024-01-17 16:28:32,751 INFO sqlalchemy.engine.Engine ROLLBACK

WHERE 子句

SQLAlchemy 允许我们将标准 Python 运算符与 Column 和类似对象结合使用来编写 SQL 表达式，例如 name = 'squidward' 或 user_id > 10 。对于布尔表达式，大多数 Python 运算符（例如 == 、 != 、 < 、 >= 等）会生成新的 SQL 表达式对象，而不是普通布尔 True / False 值：

我们可以使用这样的表达式通过将结果对象传递给 Select.where() 方法来生成 WHERE 子句：

print(select(user_table).where(user_table.c.name == "squidward"))

print('-- 要生成由 AND 连接的多个表达式，可以调用 Select.where() 方法任意多次：')
print(
    select(address_table.c.email_address)
    .where(user_table.c.name == "squidward")
    .where(address_table.c.user_id == user_table.c.id)
)

print('-- 对 Select.where() 的单次调用也接受具有相同效果的多个表达式：')
print(
    select(address_table.c.email_address).where(
        user_table.c.name == "squidward",
        address_table.c.user_id == user_table.c.id,
    )
)

print('-- “AND”和“OR”连词都可以直接使用 and_() 和 or_() 函数使用，如下图的 ORM 实体所示：')
from sqlalchemy import and_, or_
print(
    select(Address.email_address).where(
        and_(
            or_(User.name == "squidward", User.name == "sandy"),
            Address.user_id == User.id,
        )
    )
)

print('''-- 对于与单个实体进行简单的“相等”比较，还有一种名为 Select.filter_by() 的流行方法，
      -- 它接受与列键或 ORM 属性名称匹配的关键字参数。
      -- 它将根据最左边的 FROM 子句或最后加入的实体进行过滤：''')
print(select(User).filter_by(name="spongebob", fullname="Spongebob Squarepants"))

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = :name_1
-- 要生成由 AND 连接的多个表达式，可以调用 Select.where() 方法任意多次：
SELECT address.email_address 
FROM address, user_account 
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
-- 对 Select.where() 的单次调用也接受具有相同效果的多个表达式：
SELECT address.email_address 
FROM address, user_account 
WHERE user_account.name = :name_1 AND address.user_id = user_account.id
-- “AND”和“OR”连词都可以直接使用 and_() 和 or_() 函数使用，如下图的 ORM 实体所示：
SELECT address.email_address 
FROM address, user_account 
WHERE (user_account.name = :name_1 OR user_account.name = :name_2) AND address.user_id = user_account.id
-- 对于与单个实体进行简单的“相等”比较，还有一种名为 Select.filter_by() 的流行方法，
      -- 它接受与列键或 ORM 属性名称匹配的关键字参数。
      -- 它将根据最左边的 FROM 子句或最后加入的实体进行过滤：
SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

# 执行测试
with engine.connect() as conn:
    result = conn.execute(select(User).filter_by(
        name="spongebob", fullname="Spongebob Squarepants"))
    for row in result:
        print(row)

2024-01-17 16:28:32,774 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,775 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ? AND user_account.fullname = ?
2024-01-17 16:28:32,776 INFO sqlalchemy.engine.Engine [generated in 0.00158s] ('spongebob', 'Spongebob Squarepants')
(1, 'spongebob', 'Spongebob Squarepants')
2024-01-17 16:28:32,777 INFO sqlalchemy.engine.Engine ROLLBACK

显式 FROM 子句和 JOIN

为了将这两个表连接在一起，我们通常在 Select 上使用两种方法之一。第一个是 Select.join_from() 方法，它允许我们显式指示 JOIN 的左侧和右侧：

另一种是 Select.join() 方法，它只指示JOIN的右侧，推断左侧：

这两个表之所以能自动生成 ON 子句，是因为 user_table 和 address_table Table 对象包含用于形成此 ON 子句的单个 ForeignKeyConstraint 定义。

如果连接的左右目标没有这样的约束，或者有多个约束，我们需要直接指定ON子句。 Select.join() 和 Select.join_from() 都接受 ON 子句的附加参数，该参数使用与我们在 WHERE 子句中看到的相同的 SQL 表达式机制来声明：

print(
    select(user_table.c.name, address_table.c.email_address).join_from(
        user_table, address_table
    )
)

print(select(user_table.c.name, address_table.c.email_address).join(address_table))

# 直接指定连接关系的 onclause(通过JOIN函数的第二个参数)
print(
    select(address_table.c.email_address)
    .select_from(user_table)
    .join(address_table, user_table.c.id == address_table.c.user_id)
)

SELECT user_account.name, address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id
SELECT user_account.name, address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id
SELECT address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id

外部连接和完整连接

Select.join() 和 Select.join_from() 方法都接受关键字参数 Select.join.isouter 和 Select.join.full ，它们将分别呈现 LEFT OUTER JOIN 和 FULL OUTER JOIN：

还有一个方法 Select.outerjoin() 相当于使用 .join(..., isouter=True) 。

SQL 也有“RIGHT OUTER JOIN”。 SQLAlchemy 不会直接渲染它；相反，颠倒表的顺序并使用“LEFT OUTER JOIN”。

print(select(user_table).join(address_table, isouter=True))
print(select(user_table).join(address_table, full=True))

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

ORDER BY 排序依据

ORDER BY 子句是根据通常基于 Column 或类似对象的 SQL 表达式构造来构造的。 Select.order_by() 方法按位置接受一个或多个以下表达式：

升序/降序可通过 ColumnElement.asc() 和 ColumnElement.desc() 修饰符使用，这些修饰符也存在于 ORM 绑定属性中：

print(select(user_table).order_by(user_table.c.name))

# 降序
print(select(User).order_by(User.fullname.desc()))

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account ORDER BY user_account.name
SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account ORDER BY user_account.fullname DESC

使用 GROUP BY / HAVING 聚合函数

SQLAlchemy 使用名为 func 的命名空间以开放式方式提供 SQL 函数。这是一个特殊的构造函数对象，当给定特定 SQL 函数的名称时，它将创建 Function 的新实例，该函数可以具有任何名称，以及传递给该函数的零个或多个参数，这些参数是与所有其他情况一样，SQL 表达式构造。例如，要针对 user_account.id 列呈现 SQL COUNT() 函数，我们调用 count() 名称：

FUNCTION

SQLAlchemy 使用 Select.group_by() 和 Select.having() 方法提供这两个子句。下面我们将说明如何为拥有多个地址的用户选择用户名字段以及地址计数：

from sqlalchemy import func
count_fn = func.count(user_table.c.id)
print(count_fn)

count(user_account.id)

with engine.connect() as conn:
    result = conn.execute(
        select(User.name, func.count(Address.id).label("count"))
        .join(Address)
        .group_by(User.name)
        .having(func.count(Address.id) > 1)
    )
    print(result.all())

2024-01-17 16:28:32,828 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,829 INFO sqlalchemy.engine.Engine SELECT user_account.name, count(address.id) AS count 
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name 
HAVING count(address.id) > ?
2024-01-17 16:28:32,829 INFO sqlalchemy.engine.Engine [generated in 0.00137s] (1,)
[('sandy', 2)]
2024-01-17 16:28:32,831 INFO sqlalchemy.engine.Engine ROLLBACK

Ordering or Grouping by a Label 按标签排序或分组

一项重要的技术（特别是在某些数据库后端上）是能够对 columns 子句中已声明的表达式进行 ORDER BY 或 GROUP BY，而无需在 ORDER BY 或 GROUP BY 子句中重新声明表达式，而是使用列COLUMNS 子句中的名称或标记名称。通过将名称的字符串文本传递给 Select.order_by() 或 Select.group_by() 方法，可以使用此形式。传递的文本并不直接渲染；相反，为 columns 子句中的表达式指定的名称，并在上下文中呈现为该表达式名称，如果未找到匹配项，则会引发错误。一元修饰符 asc() 和 desc() 也可以这种形式使用：

from sqlalchemy import func, desc
stmt = (
    select(Address.user_id, func.count(Address.id).label("num_addresses"))
    .group_by("user_id")
    .order_by("user_id", desc("num_addresses"))
)
print(stmt)

SELECT address.user_id, count(address.id) AS num_addresses 
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

Using Aliases 使用别名

在 SQLAlchemy 表达式语言中，这些“名称”由称为 Alias 构造的 FromClause 对象表示，该对象是使用 FromClause.alias() 方法在 Core 中构造的。 Alias 构造就像 Table 构造一样，它也具有 Alias.c 集合中的 Column 对象的命名空间。例如，下面的 SELECT 语句返回所有唯一的用户名对：

user_alias_1 = user_table.alias()
user_alias_2 = user_table.alias()
print(
    select(user_alias_1.c.name, user_alias_2.c.name).join_from(
        user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id
    )
)

SELECT user_account_1.name, user_account_2.name AS name_1 
FROM user_account AS user_account_1 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

ORM 实体别名

FromClause.alias() 方法的 ORM 等效项是 ORM aliased() 函数，它可以应用于诸如 User 和 Address 之类的实体。这会在内部生成一个与原始映射的 Table 对象相对应的 Alias 对象，同时保持 ORM 功能。下面的 SELECT 从 User 实体中选择包含两个特定电子邮件地址的所有对象：

from sqlalchemy.orm import aliased
address_alias_1 = aliased(Address)
address_alias_2 = aliased(Address)
stmt =  (select(User)
        .join_from(User, address_alias_1)
        .where(address_alias_1.email_address == "sandy@squirrelpower.org")
        .join_from(User, address_alias_2)
        .where(address_alias_2.email_address == "sandy@sqlalchemy.org")
)
print(stmt)


with engine.connect() as connection:
    result = connection.execute(stmt)
    print(result.all())

SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id 
WHERE address_1.email_address = :email_address_1 AND address_2.email_address = :email_address_2
2024-01-17 16:28:32,865 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,866 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account JOIN address AS address_1 ON user_account.id = address_1.user_id JOIN address AS address_2 ON user_account.id = address_2.user_id 
WHERE address_1.email_address = ? AND address_2.email_address = ?
2024-01-17 16:28:32,867 INFO sqlalchemy.engine.Engine [generated in 0.00183s] ('sandy@squirrelpower.org', 'sandy@sqlalchemy.org')
[(2, 'sandy', 'Sandy Cheeks')]
2024-01-17 16:28:32,868 INFO sqlalchemy.engine.Engine ROLLBACK

子查询和CTE subquery() ste()

SQLAlchemy 使用 Subquery 对象表示子查询，使用 CTE 表示 Common Table Expression，通常从 Select.subquery() 和 Select.cte() 方法获取，分别。任一对象都可以用作较大 select() 构造内的 FROM 元素。

Subquery 对象的行为与任何其他 FROM 对象（例如 Table ）类似，值得注意的是它包含它选择的列的 Subquery.c 命名空间。我们可以使用此命名空间来引用 user_id 列以及我们的自定义标记 count 表达式：

SQLAlchemy 中 CTE 构造的用法实际上与 Subquery 构造的使用方式相同。通过更改 Select.subquery() 方法的调用以使用 Select.cte() 代替，我们可以以相同的方式将结果对象用作 FROM 元素，但呈现的 SQL 是非常不同的公用表表达式语法：

CTE 构造还具有以“递归”样式使用的能力，并且在更复杂的情况下可以由 INSERT、UPDATE 或 DELETE 语句的 RETURNING 子句组成。 CTE 的文档字符串包含有关这些附加模式的详细信息。

Select.subquery() - 有关子查询的更多详细信息

Select.cte() - CTE 示例，包括如何使用 RECURSIVE 以及面向 DML 的 CTE

subq = (
    select(func.count(address_table.c.id).label(
        "count"), address_table.c.user_id)
    .group_by(address_table.c.user_id)
    .subquery()
)

print(subq)

print(select(subq.c.user_id, subq.c.count))

# 通过选择 subq 对象中包含的行，我们可以将该对象应用于更大的 Select ，该 Select 会将数据连接到 user_account 表：
stmt = select(user_table.c.name, user_table.c.fullname, subq.c.count).join_from(
    user_table, subq
)

print(stmt)

SELECT count(address.id) AS count, address.user_id 
FROM address GROUP BY address.user_id
SELECT anon_1.user_id, anon_1.count 
FROM (SELECT count(address.id) AS count, address.user_id AS user_id 
FROM address GROUP BY address.user_id) AS anon_1
SELECT user_account.name, user_account.fullname, anon_1.count 
FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id 
FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id

cteq = (
    select(func.count(address_table.c.id).label(
        "count"), address_table.c.user_id)
    .group_by(address_table.c.user_id)
    .cte()
)

stmt = select(user_table.c.name, user_table.c.fullname, cteq.c.count).join_from(
    user_table, cteq
)

print(stmt)

WITH anon_1 AS 
(SELECT count(address.id) AS count, address.user_id AS user_id 
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count 
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

ORM 实体子查询/CTE aliased

aliased 生成给定元素的别名

在这里，我们说明 aliased() 对 Subquery 以及针对 Select 构造生成的 CTE 执行相同的操作，最终派生来自同一个映射的 Table 。

# 下面是将 aliased() 应用于 Subquery 构造的示例，以便可以从其行中提取 ORM 实体。结果显示一系列 User 和 Address 对象，
# 其中每个 Address 对象的数据最终来自针对 address 的子查询表而不是直接该表：
# ~ 表示 否, like 变成 not like
subq = select(Address).where(
    ~Address.email_address.like("%@aol.com")).subquery()
address_subq = aliased(Address, subq)
stmt = (
    select(User, address_subq)
    .join_from(User, address_subq)
    .order_by(User.id, address_subq.id)
)
with Session(engine) as session:
    for user, address in session.execute(stmt):
        print(f"{user} {address}")

2024-01-17 16:28:32,902 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,905 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.email_address, anon_1.user_id 
FROM user_account JOIN (SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id 
FROM address 
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id
2024-01-17 16:28:32,906 INFO sqlalchemy.engine.Engine [generated in 0.00077s] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
2024-01-17 16:28:32,908 INFO sqlalchemy.engine.Engine ROLLBACK

# 下面是另一个示例，除了使用 CTE 构造之外，它完全相同：
cte_obj = select(Address).where(~Address.email_address.like("%@aol.com")).cte()
address_cte = aliased(Address, cte_obj)
stmt = (
    select(User, address_cte)
    .join_from(User, address_cte)
    .order_by(User.id, address_cte.id)
)
with Session(engine) as session:
    for user, address in session.execute(stmt):
        print(f"{user} {address}")

2024-01-17 16:28:32,919 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,922 INFO sqlalchemy.engine.Engine WITH anon_1 AS 
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id 
FROM address 
WHERE address.email_address NOT LIKE ?)
 SELECT user_account.id, user_account.name, user_account.fullname, anon_1.id AS id_1, anon_1.email_address, anon_1.user_id 
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.id
2024-01-17 16:28:32,923 INFO sqlalchemy.engine.Engine [generated in 0.00090s] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
2024-01-17 16:28:32,925 INFO sqlalchemy.engine.Engine ROLLBACK

标量和相关子查询 scalar_subquery

标量子查询是精确返回零或一行和一列的子查询。然后，该子查询将在封闭的 SELECT 语句的 COLUMNS 或 WHERE 子句中使用，并且与常规子查询不同，它不在 FROM 子句中使用。相关子查询是引用封闭 SELECT 语句中的表的标量子查询。

SQLAlchemy 使用 ScalarSelect 构造表示标量子查询，该构造是 ColumnElement 表达式层次结构的一部分，与由 Subquery 构造表示的常规子查询形成对比，位于 FromClause 层次结构中。

标量子查询通常（但不一定）与聚合函数一起使用，之前在使用 GROUP BY / HAVING 的聚合函数中介绍过。标量子查询通过使用 Select.scalar_subquery() 方法显式指示，如下所示。当它本身进行字符串化时，它是默认的字符串形式，呈现为从两个表中进行选择的普通 SELECT 语句：

subq = (
    select(func.count(address_table.c.id))
    .where(user_table.c.id == address_table.c.user_id)
    .scalar_subquery()
)
print(subq)

# subq 对象现在属于 ColumnElement SQL 表达式层次结构，因为它可以像任何其他列表达式一样使用：
print(subq == 5)

# 虽然标量子查询本身在其 FROM 子句中进行字符串化时会呈现 user_account 和 address ，
# 但当将其嵌入到处理以下内容的封闭 select() 构造中时， user_account 表、 user_account 表自动关联，这意味着它不会在子查询的 FROM 子句中呈现：
stmt = select(user_table.c.name, subq.label("address_count"))
print(stmt)

(SELECT count(address.id) AS count_1 
FROM address, user_account 
WHERE user_account.id = address.user_id)
(SELECT count(address.id) AS count_1 
FROM address, user_account 
WHERE user_account.id = address.user_id) = :param_1
SELECT user_account.name, (SELECT count(address.id) AS count_1 
FROM address 
WHERE user_account.id = address.user_id) AS address_count 
FROM user_account

# 简单的相关子查询通常会做所需的正确事情。然而，在相关性不明确的情况下，SQLAlchemy 会让我们知道需要更清晰的说明：
# 要指定 user_table 是我们寻求关联的对象，我们使用 ScalarSelect.correlate() 或 ScalarSelect.correlate_except() 方法指定这一点：
subq = (
    select(func.count(address_table.c.id))
    .where(user_table.c.id == address_table.c.user_id)
    .scalar_subquery()
    .correlate(user_table)
)

stmt = (
    select(
        user_table.c.name,
        address_table.c.email_address,
        subq.label("address_count"),
    )
    .join_from(user_table, address_table)
    .order_by(user_table.c.id, address_table.c.id)
)
print(stmt)

with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1 
FROM address 
WHERE user_account.id = address.user_id) AS address_count 
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
2024-01-17 16:28:32,949 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,950 INFO sqlalchemy.engine.Engine SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1 
FROM address 
WHERE user_account.id = address.user_id) AS address_count 
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
2024-01-17 16:28:32,951 INFO sqlalchemy.engine.Engine [generated in 0.00204s] ()
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), ('sandy', 'sandy@squirrelpower.org', 2)]
2024-01-17 16:28:32,952 INFO sqlalchemy.engine.Engine ROLLBACK

LATERAL correlation 横向相关性

LATERAL 关联是 SQL 关联的一个特殊子类别，它允许一个可选单元在单个 FROM 子句中引用另一个可选单元。 这是一个极其特殊的用例，虽然它是 SQL 标准的一部分，但仅受最新版本的 PostgreSQL 支持。

以下示例说明了使用 LATERAL 的 SQL 查询，选择上一节中讨论的“用户帐户/电子邮件地址计数”数据：

subq = (
    select(
        func.count(address_table.c.id).label("address_count"),
        address_table.c.email_address,
        address_table.c.user_id,
    )
    .where(user_table.c.id == address_table.c.user_id)
    .lateral()
)
stmt = (
    select(user_table.c.name, subq.c.address_count, subq.c.email_address)
    .join_from(user_table, subq)
    .order_by(user_table.c.id, subq.c.email_address)
)
print(stmt)

SELECT user_account.name, anon_1.address_count, anon_1.email_address 
FROM user_account JOIN LATERAL (SELECT count(address.id) AS address_count, address.email_address AS email_address, address.user_id AS user_id 
FROM address 
WHERE user_account.id = address.user_id) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.email_address

UNION、UNION ALL 和其他集合运算 ¶

SQLAlchemy 的 Select 构造使用 union() 、 intersect() 和 except_() 等函数以及“所有”对应项 union_all() 、 intersect_all() 和 except_all() 。这些函数都接受任意数量的子选项，这些子选项通常是 Select 结构，但也可能是现有的组合。

这些函数生成的构造是 CompoundSelect ，其使用方式与 Select 构造相同，只是它的方法较少。例如，由 union_all() 生成的 CompoundSelect 可以使用 Connection.execute() 直接调用：

from sqlalchemy import union_all
stmt1 = select(user_table).where(user_table.c.name == "sandy")
stmt2 = select(user_table).where(user_table.c.name == "spongebob")
u = union_all(stmt1, stmt2)
with engine.connect() as conn:
    result = conn.execute(u)
    print(result.all())

2024-01-17 16:28:32,973 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,974 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:32,975 INFO sqlalchemy.engine.Engine [generated in 0.00152s] ('sandy', 'spongebob')
[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]
2024-01-17 16:28:32,976 INFO sqlalchemy.engine.Engine ROLLBACK

# 要使用 CompoundSelect 作为子查询，就像 Select 一样，它提供了 SelectBase.subquery() 方法，该方法将生成带有 FromClause.c 可以在封闭的 select() 中引用的集合：

u_subq = u.subquery()
stmt = (
    select(u_subq.c.name, address_table.c.email_address)
    .join_from(address_table, u_subq)
    .order_by(u_subq.c.name, address_table.c.email_address)
)
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

2024-01-17 16:28:32,990 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:32,991 INFO sqlalchemy.engine.Engine SELECT anon_1.name, address.email_address 
FROM address JOIN (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 
FROM user_account 
WHERE user_account.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 
FROM user_account 
WHERE user_account.name = ?) AS anon_1 ON anon_1.id = address.user_id ORDER BY anon_1.name, address.email_address
2024-01-17 16:28:32,991 INFO sqlalchemy.engine.Engine [generated in 0.00184s] ('sandy', 'spongebob')
[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]
2024-01-17 16:28:32,993 INFO sqlalchemy.engine.Engine ROLLBACK

EXISTS subqueries 存在子查询 ¶

SQL EXISTS 关键字是一个运算符，与标量子查询一起使用，根据 SELECT 语句是否返回行来返回布尔值 true 或 false。 SQLAlchemy 包含 ScalarSelect 对象的一个​​变体，称为 Exists ，它将生成 EXISTS 子查询，并且最方便地使用 SelectBase.exists() 方法生成。下面我们生成一个 EXISTS，以便我们可以返回 address 中具有多个相关行的 user_account 行：

subq = (
    select(func.count(address_table.c.id))
    .where(user_table.c.id == address_table.c.user_id)
    .group_by(address_table.c.user_id)
    .having(func.count(address_table.c.id) > 1)
).exists()
with engine.connect() as conn:
    result = conn.execute(select(user_table.c.name).where(subq))
    print(result.all())

2024-01-17 16:28:33,004 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,005 INFO sqlalchemy.engine.Engine SELECT user_account.name 
FROM user_account 
WHERE EXISTS (SELECT count(address.id) AS count_1 
FROM address 
WHERE user_account.id = address.user_id GROUP BY address.user_id 
HAVING count(address.id) > ?)
2024-01-17 16:28:33,006 INFO sqlalchemy.engine.Engine [generated in 0.00121s] (1,)
[('sandy',)]
2024-01-17 16:28:33,007 INFO sqlalchemy.engine.Engine ROLLBACK

使用 SQL 函数 ¶

func 对象充当创建新 Function 对象的工厂，典型 SQL 函数的示例包括：

• count() 函数，一个聚合函数，用于计算返回的行数：

• lower() 函数，一个将字符串转换为小写的字符串函数：

• now() 函数，提供当前日期和时间；由于这是一个常用函数，SQLAlchemy 知道如何为每个后端以不同的方式呈现它，在 SQLite 使用 CURRENT_TIMESTAMP 函数的情况下：

由于大多数数据库后端都具有数十个甚至数百个不同的 SQL 函数，因此 func 尝试在其接受的内容上尽可能自由。从该命名空间访问的任何名称都会自动被视为将以通用方式呈现的 SQL 函数：

同时，一组相对较小的极其常见的 SQL 函数，例如 count 、 now 、 max 、 concat 包括预先其自身的打包版本提供正确的类型信息以及在某些情况下特定于后端的 SQL 生成。下面的示例对比了 now 函数的 PostgreSQL 方言和 Oracle 方言发生的 SQL 生成：

print(select(func.count()).select_from(user_table))
print(select(func.lower("A String With Much UPPERCASE")))
stmt = select(func.now())
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

SELECT count(*) AS count_1 
FROM user_account
SELECT lower(:lower_2) AS lower_1
2024-01-17 16:28:33,020 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,021 INFO sqlalchemy.engine.Engine SELECT CURRENT_TIMESTAMP AS now_1
2024-01-17 16:28:33,022 INFO sqlalchemy.engine.Engine [generated in 0.00181s] ()
[(datetime.datetime(2024, 1, 17, 8, 28, 33),)]
2024-01-17 16:28:33,023 INFO sqlalchemy.engine.Engine ROLLBACK

from sqlalchemy.dialects import oracle
from sqlalchemy.dialects import postgresql

print(select(func.some_crazy_function(user_table.c.name, 17)))

print(select(func.now()).compile(dialect=postgresql.dialect()))
print(select(func.now()).compile(dialect=oracle.dialect()))

SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1 
FROM user_account
SELECT now() AS now_1
SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL

函数有返回类型

由于函数是列表达式，因此它们也具有描述生成的 SQL 表达式的数据类型的 SQL 数据类型。

我们在这里将这些类型称为“SQL 返回类型”，指的是数据库端 SQL 表达式上下文中函数返回的 SQL 值的类型，而不是 Python 函数的“返回类型” 。

通常出于调试目的，可以通过引用 Function.type 属性来访问任何 SQL 函数的 SQL 返回类型：

当在较大表达式的上下文中使用函数表达式时，这些 SQL 返回类型非常重要；也就是说，当表达式的数据类型类似于 Integer 或 Numeric 时，数学运算符会更好地工作，JSON 访问器为了工作需要使用诸如 JSON 。某些类函数返回整行而不是列值，其中需要引用特定列；此类函数称为表值函数。

对于 SQLAlchemy 必须应用结果集处理的情况，函数的 SQL 返回类型在执行语句并取回行时也可能很重要。一个典型的例子是 SQLite 上与日期相关的函数，其中 SQLAlchemy 的 DateTime 和相关数据类型在收到结果行时承担从字符串值转换为 Python datetime() 对象的作用。

要将特定类型应用于我们正在创建的函数，我们使用 Function.type_ 参数传递它；类型参数可以是 TypeEngine 类或实例。在下面的示例中，我们传递 JSON 类来生成 PostgreSQL json_object() 函数，请注意 SQL 返回类型将为 JSON 类型：

from sqlalchemy import JSON
func.now().type

function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)

stmt = select(function_expr["def"])
print(stmt)

DateTime()



SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

内置函数具有预先配置的返回类型 ¶

对于常见的聚合函数，例如 count 、 max 、 min 以及极少数日期函数，例如 now 和字符串函数，例如 concat ，SQL 返回类型被适当设置，有时基于用法。 max 函数和类似的聚合过滤函数将根据给定的参数设置 SQL 返回类型：

日期和时间函数通常对应于 DateTime 、 Date 或 Time 描述的 SQL 表达式：

m1 = func.max(Column("some_int", Integer))
m1.type

m2 = func.max(Column("some_str", String))
m2.type

func.now().type
func.current_date().type

func.concat("x", "y").type

func.upper("lowercase").type

Integer()






String()






DateTime()






Date()






String()






NullType()

高级 SQL 函数技术 ¶

# 与窗口函数一起使用的常见函数是 row_number() 函数，它只计算行数。我们可以根据用户名对此行计数进行分区，以对各个用户的电子邮件地址进行编号：
stmt = (
    select(
        func.row_number().over(partition_by=user_table.c.name),
        user_table.c.name,
        address_table.c.email_address,
    )
    .select_from(user_table)
    .join(address_table)
)
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

# 上面使用了 FunctionElement.over.partition_by 参数，以便在 OVER 子句中呈现 PARTITION BY 子句。我们还可以使用 FunctionElement.over.order_by 来使用 ORDER BY 子句：
stmt = (
    select(
        func.count().over(order_by=user_table.c.name),
        user_table.c.name,
        address_table.c.email_address,
    )
    .select_from(user_table)
    .join(address_table)
)
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

2024-01-17 16:28:33,132 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,133 INFO sqlalchemy.engine.Engine SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, user_account.name, address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id
2024-01-17 16:28:33,134 INFO sqlalchemy.engine.Engine [generated in 0.00265s] ()
[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]
2024-01-17 16:28:33,136 INFO sqlalchemy.engine.Engine ROLLBACK
2024-01-17 16:28:33,138 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,139 INFO sqlalchemy.engine.Engine SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, user_account.name, address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id
2024-01-17 16:28:33,139 INFO sqlalchemy.engine.Engine [generated in 0.00150s] ()
[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]
2024-01-17 16:28:33,141 INFO sqlalchemy.engine.Engine ROLLBACK

within_group修饰符、filter过滤器 ¶

print(
    func.unnest(
        func.percentile_disc([0.25, 0.5, 0.75, 1]
                             ).within_group(user_table.c.name)
    )
)

stmt = (
    select(
        func.count(address_table.c.email_address).filter(
            user_table.c.name == "sandy"),
        func.count(address_table.c.email_address).filter(
            user_table.c.name == "spongebob"
        ),
    )
    .select_from(user_table)
    .join(address_table)
)
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))
2024-01-17 16:28:33,153 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,154 INFO sqlalchemy.engine.Engine SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 
FROM user_account JOIN address ON user_account.id = address.user_id
2024-01-17 16:28:33,154 INFO sqlalchemy.engine.Engine [generated in 0.00138s] ('sandy', 'spongebob')
[(2, 1)]
2024-01-17 16:28:33,156 INFO sqlalchemy.engine.Engine ROLLBACK

表值 SQL 函数

支持包含命名子元素的标量表示形式。表值函数通常用于 JSON 和面向 ARRAY 的函数以及 generate_series() 等函数，在 FROM 子句中指定，然后作为表引用，有时甚至作为列引用。这种形式的函数在 PostgreSQL 数据库中很突出，但是 SQLite、Oracle 和 SQL Server 也支持某些形式的表值函数。

SQLAlchemy 提供 FunctionElement.table_valued() 方法作为基本的“表值函数”构造，它将根据按位置传递的字符串名称将 func 对象转换为包含一系列命名列的 FROM 子句。这将返回一个 TableValuedAlias 对象，它是一个启用函数的 Alias 构造，可以用作使用别名中介绍的任何其他 FROM 子句。下面我们举例说明 json_each() 函数，该函数虽然在 PostgreSQL 上很常见，但现代版本的 SQLite 也支持该函数：

onetwothree = func.json_each('["one", "two", "three"]').table_valued("value")
stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"]))
with engine.connect() as conn:
    result = conn.execute(stmt)
    result.all()

2024-01-17 16:28:33,167 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,168 INFO sqlalchemy.engine.Engine SELECT anon_1.value 
FROM json_each(?) AS anon_1 
WHERE anon_1.value IN (?, ?)
2024-01-17 16:28:33,168 INFO sqlalchemy.engine.Engine [generated in 0.00190s] ('["one", "two", "three"]', 'two', 'three')





[('two',), ('three',)]



2024-01-17 16:28:33,171 INFO sqlalchemy.engine.Engine ROLLBACK

列值函数 - 作为标量列的表值函数

from sqlalchemy import select, func
stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
print(stmt)

SELECT x 
FROM json_array_elements(:json_array_elements_1) AS x

数据转换和类型强制

在 SQL 中，我们经常需要显式指示表达式的数据类型，要么是为了告诉数据库在不明确的表达式中需要什么类型，要么在某些情况下当我们想要将 SQL 表达式的隐含数据类型转换为其他类型时。

SQL CAST 关键字用于此任务，在 SQLAlchemy 中由 cast() 函数提供。该函数接受列表达式和数据类型对象作为参数，如下所示，我们从 user_table.c.id 列对象生成 SQL 表达式 CAST(user_account.id AS VARCHAR) ：

cast() 函数不仅呈现 SQL CAST 语法，还生成一个 SQLAlchemy 列表达式，该表达式也将充当 Python 端的给定数据类型。 cast() 到 JSON 的字符串表达式将获得 JSON 下标和比较运算符，例如：

from sqlalchemy import JSON
from sqlalchemy import cast
stmt = select(cast(user_table.c.id, String))
with engine.connect() as conn:
    result = conn.execute(stmt)
    result.all()

print(cast("{'a': 'b'}", JSON)["a"])

2024-01-17 16:28:33,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,191 INFO sqlalchemy.engine.Engine SELECT CAST(user_account.id AS VARCHAR) AS id 
FROM user_account
2024-01-17 16:28:33,192 INFO sqlalchemy.engine.Engine [generated in 0.00139s] ()





[('1',), ('2',), ('3',)]



2024-01-17 16:28:33,195 INFO sqlalchemy.engine.Engine ROLLBACK
CAST(:param_1 AS JSON)[:param_2]

type_coerce() - 仅限 Python 的“强制类型转换”

有时，出于上述所有原因，需要让 SQLAlchemy 了解表达式的数据类型，但又不想在 SQL 端呈现 CAST 表达式本身，因为它可能会干扰已经在没有它的情况下运行的 SQL 操作。

对于这个相当常见的用例，还有另一个函数 type_coerce() ，它与 cast() 密切相关，因为它将 Python 表达式设置为具有特定的 SQL 数据库类型，但不渲染数据库端的 CAST 关键字或数据类型。 type_coerce() 在处理 JSON 数据类型时尤其重要，该数据类型通常与不同平台上面向字符串的数据类型具有复杂的关系，甚至可能不是显式数据类型，例如在 SQLite 上和玛丽亚数据库。下面，我们使用 type_coerce() 将 Python 结构作为 JSON 字符串传递到 MySQL 的 JSON 函数之一：

import json
from sqlalchemy import JSON
from sqlalchemy import type_coerce
from sqlalchemy.dialects import mysql
s = select(type_coerce({"some_key": {"foo": "bar"}}, JSON)["some_key"])
print(s.compile(dialect=mysql.dialect()))

SELECT JSON_EXTRACT(%s, %s) AS anon_1

使用 UPDATE 和 DELETE 语句

update() 函数生成 Update 的新实例

它表示 SQL 中的 UPDATE 语句，该语句将更新表中的现有数据。

然而，某些后端支持可以同时修改多个表的 UPDATE 语句，并且 UPDATE 语句还支持 RETURNING，以便可以在结果集中返回匹配行中包含的列。

Update.values() 方法控制 UPDATE 语句的 SET 元素的内容。这与 Insert 构造共享的方法相同。通常可以使用列名作为关键字参数来传递参数。

from sqlalchemy import update
stmt = (
    update(user_table)
    .where(user_table.c.name == "patrick")
    .values(fullname="Patrick the Star")
)
print(stmt)

# UPDATE 支持 UPDATE 的所有主要 SQL 形式，包括针对表达式的更新，其中我们可以使用 Column 表达式：
stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
print(stmt)

UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1
UPDATE user_account SET fullname=(:name_1 || user_account.name)

为了支持“executemany”上下文中的 UPDATE，其中将针对同一语句调用许多参数集，可以使用 bindparam() 构造来设置绑定参数；这些替换了文字值通常所在的位置：

from sqlalchemy import bindparam
stmt = (
    update(user_table)
    .where(user_table.c.name == bindparam("oldname"))
    .values(name=bindparam("newname"))
)
with engine.begin() as conn:
    conn.execute(
        stmt,
        [
            {"oldname": "jack", "newname": "ed"},
            {"oldname": "wendy", "newname": "mary"},
            {"oldname": "jim", "newname": "jake"},
        ],
    )

2024-01-17 16:28:33,248 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,251 INFO sqlalchemy.engine.Engine UPDATE user_account SET name=? WHERE user_account.name = ?
2024-01-17 16:28:33,252 INFO sqlalchemy.engine.Engine [generated in 0.00104s] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]





<sqlalchemy.engine.cursor.CursorResult at 0x23d58950a60>



2024-01-17 16:28:33,253 INFO sqlalchemy.engine.Engine COMMIT

UPDATE 语句可以通过使用相关子查询来使用其他表中的行。子查询可以用在任何可以放置列表达式的地方：

scalar_subq = (
    select(address_table.c.email_address)
    .where(address_table.c.user_id == user_table.c.id)
    .order_by(address_table.c.id)
    .limit(1)
    .scalar_subquery()
)
update_stmt = update(user_table).values(fullname=scalar_subq)
print(update_stmt)

UPDATE user_account SET fullname=(SELECT address.email_address 
FROM address 
WHERE address.user_id = user_account.id ORDER BY address.id
 LIMIT :param_1)

UPDATE FROM

一些数据库（例如 PostgreSQL 和 MySQL）支持语法“UPDATE FROM”，其中可以在特殊的 FROM 子句中直接声明其他表。当附加表位于语句的 WHERE 子句中时，将隐式生成此语法：

update_stmt = (
    update(user_table)
    .where(user_table.c.id == address_table.c.user_id)
    .where(address_table.c.email_address == "patrick@aol.com")
    .values(fullname="Pat")
)
print(update_stmt)

UPDATE user_account SET fullname=:fullname FROM address WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

还有一种 MySQL 特定语法可以更新多个表。这要求我们在 VALUES 子句中引用 Table 对象，以便引用其他表：

from sqlalchemy.dialects import mysql
update_stmt = (
    update(user_table)
    .where(user_table.c.id == address_table.c.user_id)
    .where(address_table.c.email_address == "patrick@aol.com")
    .values(
        {
            user_table.c.fullname: "Pat",
            address_table.c.email_address: "pat@aol.com",
        }
    )
)
print(update_stmt.compile(dialect=mysql.dialect()))

UPDATE user_account, address SET address.email_address=%s, user_account.fullname=%s WHERE user_account.id = address.user_id AND address.email_address = %s

另一个仅限 MySQL 的行为是 UPDATE 的 SET 子句中的参数顺序实际上会影响每个表达式的计算。对于此用例， Update.ordered_values() 方法接受元组序列，以便可以控制此顺序 [2]：

# update_stmt = update(some_table).ordered_values(
#     (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
# )
# print(update_stmt)

delete() 函数生成 Delete 的新实例，它表示 SQL 中的 DELETE 语句，该语句将从表中删除行。

从 API 角度来看， delete() 语句与 update() 构造非常相似，传统上不返回任何行，但允许在某些数据库后端使用 RETURNING 变体。

from sqlalchemy import delete
stmt = delete(user_table).where(user_table.c.name == "patrick")
print(stmt)

DELETE FROM user_account WHERE user_account.name = :name_1

多表删除

与 Update 一样， Delete 支持在 WHERE 子句中使用相关子查询以及后端特定的多表语法，例如 MySQL 上的 DELETE FROM..USING ：

from sqlalchemy.dialects import mysql
delete_stmt = (
    delete(user_table)
    .where(user_table.c.id == address_table.c.user_id)
    .where(address_table.c.email_address == "patrick@aol.com")
)
print(delete_stmt.compile(dialect=mysql.dialect()))

DELETE FROM user_account USING user_account, address WHERE user_account.id = address.user_id AND address.email_address = %s

获取更新、删除影响的行数 ¶

该值可从 CursorResult.rowcount 属性获得：

with engine.begin() as conn:
    result = conn.execute(
        update(user_table)
        .values(fullname="Patrick McStar")
        .where(user_table.c.name == "patrick")
    )
    print(result.rowcount)

2024-01-17 16:28:33,352 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,354 INFO sqlalchemy.engine.Engine UPDATE user_account SET fullname=? WHERE user_account.name = ?
2024-01-17 16:28:33,355 INFO sqlalchemy.engine.Engine [generated in 0.00083s] ('Patrick McStar', 'patrick')
1
2024-01-17 16:28:33,356 INFO sqlalchemy.engine.Engine COMMIT

将 RETURNING 与 UPDATE、DELETE 一起使用 ¶

与 Insert 构造一样， Update 和 Delete 也支持使用 Update.returning() 和 Delete.returning() 对象中：

update_stmt = (
    update(user_table)
    .where(user_table.c.name == "patrick")
    .values(fullname="Patrick the Star")
    .returning(user_table.c.id, user_table.c.name)
)
print(update_stmt)
delete_stmt = (
    delete(user_table)
    .where(user_table.c.name == "patrick")
    .returning(user_table.c.id, user_table.c.name)
)
print(delete_stmt)

UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name
DELETE FROM user_account WHERE user_account.name = :name_1 RETURNING user_account.id, user_account.name

更新/删除的 API 文档：

• Update
• Delete

使用 ORM 进行数据操作

本节将构建 Session 的生命周期以及它如何与这些构造交互。

使用 ORM 工作单元模式插入行 ¶

使用 ORM 时， Session 对象负责构造 Insert 结构，并在正在进行的事务中将它们作为 INSERT 语句发出。我们指示 Session 执行此操作的方法是向其添加对象条目；然后， Session 确保这些新条目在需要时将使用称为刷新的过程发送到数据库。 Session 用于持久化对象的整个过程称为工作单元模式。

在前面的示例中，我们使用 Python 字典发出 INSERT 来指示我们想要添加的数据，而在 ORM 中，我们直接使用我们定义的自定义 Python 类，回到使用 ORM 声明式表单定义表元数据。在类级别， User 和 Address 类用作定义相应数据库表的外观的位置。这些类还充当可扩展的数据对象，我们也用它们来创建和操作事务中的行。下面我们将创建两个 User 对象，每个对象代表要插入的潜在数据库行：

squidward = User(name="squidward", fullname="Squidward Tentacles")
krabs = User(name="ehkrabs", fullname="Eugene H. Krabs")

类的实例代表行 ¶

我们可以使用映射列的名称作为构造函数中的关键字参数来构造这些对象。这是可能的，因为 User 类包含一个由 ORM 映射提供的自动生成的 init() 构造函数，以便我们可以使用列名称作为构造函数中的键来创建每个对象。

与 Insert 的核心示例类似，我们没有包含主键（即 id 列的条目），因为我们希望使用数据库的自动递增主键功能，在本例中为 SQLite，ORM 也与其集成。上述对象的 id 属性的值，如果我们查看它，会显示为 None ：

None 值由 SQLAlchemy 提供，指示该属性目前还没有值。 SQLAlchemy 映射的属性始终在 Python 中返回一个值，并且在处理尚未分配值的新对象时，如果缺少该值，则不会引发 AttributeError 。

目前，我们上面的两个对象处于称为瞬态的状态 - 它们不与任何数据库状态关联，并且尚未与可以为它们生成 INSERT 语句的 Session 对象关联。

squidward

User(id=None, name='squidward', fullname='Squidward Tentacles')

添加对象到会话 ¶

为了逐步说明添加过程，我们将在不使用上下文管理器的情况下创建一个 Session （因此我们必须确保稍后将其关闭！）：

然后使用 Session.add() 方法将对象添加到 Session 中。调用此方法时，对象处于挂起状态，尚未插入：

当我们有挂起的对象时，我们可以通过查看 Session 上名为 Session.new 的集合来查看此状态：

下面的视图使用了一个名为 IdentitySet 的集合，它本质上是一个 Python 集合，在所有情况下都会对对象标识进行哈希处理（即使用 Python 内置 id() 函数，而不是 Python hash() 函数）。

# from sqlalchemy.orm import sessionmaker,Session

session = Session(engine)

session.add(squidward)
session.add(krabs)

session.new

IdentitySet([User(id=None, name='squidward', fullname='Squidward Tentacles'), User(id=None, name='ehkrabs', fullname='Eugene H. Krabs')])

Flushing 刷新 ¶

Session 使用称为工作单元的模式。这通常意味着它一次累积一个更改，但直到需要时才实际将它们传送到数据库。这使得它能够根据一组给定的挂起更改就如何在事务中发出 SQL DML 做出更好的决策。

当它确实向数据库发出 SQL 以推出当前的更改集时，该过程称为刷新。

我们可以通过调用 Session.flush() 方法手动说明刷新过程：

下面我们观察到 Session 首先被调用来发出 SQL，因此它创建了一个新事务并为两个对象发出了适当的 INSERT 语句。事务现在保持打开状态，直到我们调用 Session 的任何 Session.commit() 、 Session.rollback() 或 Session.close() 方法。

虽然 Session.flush() 可用于手动推送当前事务的待处理更改，但通常没有必要，因为 Session 具有称为自动刷新的行为，我们将在稍后进行说明。每当调用 Session.commit() 时，它也会刷新更改。

session.flush()

2024-01-17 16:28:33,433 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,435 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
2024-01-17 16:28:33,436 INFO sqlalchemy.engine.Engine [generated in 0.00011s (insertmanyvalues) 1/2 (ordered; batch not supported)] ('squidward', 'Squidward Tentacles')
2024-01-17 16:28:33,437 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
2024-01-17 16:28:33,438 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/2 (ordered; batch not supported)] ('ehkrabs', 'Eugene H. Krabs')

自动生成的主键属性 ¶

插入行后，我们创建的两个 Python 对象就处于持久状态，它们与添加或加载它们的 Session 对象相关联，并且具有许多其他行为稍后将对此进行介绍。

INSERT 的另一个影响是 ORM 为每个新对象检索了新的主键标识符；在内部，它通常使用我们之前介绍的相同的 CursorResult.inserted_primary_key 访问器。 squidward 和 krabs 对象现在具有与它们关联的新主键标识符，我们可以通过访问 id 属性来查看它们：

squidward.id
krabs.id

4






5

通过主键从身份映射中获取对象 ¶

对象的主键标识对于 Session 非常重要，因为对象现在使用称为标识映射的功能链接到内存中的此标识。身份映射是内存中的存储，它将当前加载到内存中的所有对象链接到它们的主键身份。我们可以通过使用 Session.get() 方法检索上述对象之一来观察这一点，如果本地存在，该方法将从身份映射中返回一个条目，否则发出 SELECT：

关于身份映射需要注意的重要一点是，它在特定 Session 对象的范围内为每个特定数据库身份维护特定 Python 对象的唯一实例。我们可以观察到 some_squidward 引用与之前的 squidward 相同的对象：

some_squidward = session.get(User, squidward.id)  # 这里ID值可能会不一样 squidward.id

some_squidward

some_squidward is squidward

User(id=4, name='squidward', fullname='Squidward Tentacles')






True

Committing 提交 ¶

关于 Session 的工作原理还有很多话要说，我们将进一步讨论。现在我们将提交事务，以便我们可以在检查更多 ORM 行为和功能之前积累有关如何 SELECT 行的知识：

上述操作将提交正在进行的事务。我们处理过的对象仍然附加到 Session ，这是它们一直保持的状态，直到 Session 关闭（这在关闭会话中介绍）。

session.commit()

2024-01-17 16:28:33,480 INFO sqlalchemy.engine.Engine COMMIT

使用工作单元模式更新 ORM 对象 ¶

在前面的使用 UPDATE 和 DELETE 语句部分中，我们介绍了表示 SQL UPDATE 语句的 Update 构造。使用 ORM 时，有两种使用此构造的方法。主要方式是它作为 Session 使用的工作单元过程的一部分自动发出，其中 UPDATE 语句是基于每个主键发出的，对应于对其进行更改的各个对象。

# 假设我们将用户名 sandy 的 User 对象加载到事务中（还展示了 Select.filter_by() 方法以及 Result.scalar_one() 方法）：
sandy = session.execute(select(User).filter_by(name="sandy")).scalar_one()
# 前面提到的 Python 对象 sandy 充当数据库中行的代理，更具体地说，是当前事务中的数据库行，其主键标识为 2 :
sandy
# 如果我们更改此对象的属性， Session 会跟踪此更改：
sandy.fullname = "Sandy Squirrel"
# 该对象出现在名为 Session.dirty 的集合中，表明该对象是“脏”的
sandy in session.dirty

2024-01-17 16:28:33,499 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,500 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:33,501 INFO sqlalchemy.engine.Engine [cached since 0.8554s ago] ('sandy',)





User(id=2, name='sandy', fullname='Sandy Cheeks')






True

当 Session 下一次发出刷新时，将发出更新数据库中的该值的更新。

如前所述，在发出任何 SELECT 之前，会使用称为自动刷新的行为自动发生刷新。

# 我们可以直接查询该行的 User.fullname 列，我们将得到更新后的值：
sandy_fullname = session.execute(
    select(User.fullname).where(User.id == 2)).scalar_one()

print(sandy_fullname)

sandy in session.dirty

2024-01-17 16:28:33,517 INFO sqlalchemy.engine.Engine UPDATE user_account SET fullname=? WHERE user_account.id = ?
2024-01-17 16:28:33,518 INFO sqlalchemy.engine.Engine [generated in 0.00095s] ('Sandy Squirrel', 2)
2024-01-17 16:28:33,520 INFO sqlalchemy.engine.Engine SELECT user_account.fullname 
FROM user_account 
WHERE user_account.id = ?
2024-01-17 16:28:33,521 INFO sqlalchemy.engine.Engine [generated in 0.00104s] (2,)
Sandy Squirrel





False

但请注意，我们仍处于事务中，我们的更改尚未推送到数据库的永久存储中。由于桑迪的姓氏实际上是“Cheeks”而不是“Squirrel”，我们将在稍后回滚事务时修复此错误。但首先我们将进行更多的数据更改。

使用工作单元模式删除 ORM 对象 ¶

为了完成基本的持久性操作，可以使用 Session.delete() 方法在工作单元过程中将单个 ORM 对象标记为删除。

# 让我们从数据库加载 patrick ：
patrick = session.get(User, 3)
patrick
# 如果我们将 patrick 标记为删除，就像其他操作的情况一样，在刷新继续之前实际上什么也没有发生：
session.delete(patrick)
# 当前的 ORM 行为是 patrick 保留在 Session 中，直到刷新继续，正如前面提到的，如果我们发出查询，就会发生这种情况：
session.execute(select(User).where(User.name == "patrick")).first()

2024-01-17 16:28:33,537 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = ?
2024-01-17 16:28:33,538 INFO sqlalchemy.engine.Engine [generated in 0.00119s] (3,)





User(id=3, name='patrick', fullname='Patrick McStar')



2024-01-17 16:28:33,543 INFO sqlalchemy.engine.Engine SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id 
FROM address 
WHERE ? = address.user_id
2024-01-17 16:28:33,544 INFO sqlalchemy.engine.Engine [generated in 0.00099s] (3,)
2024-01-17 16:28:33,546 INFO sqlalchemy.engine.Engine DELETE FROM user_account WHERE user_account.id = ?
2024-01-17 16:28:33,547 INFO sqlalchemy.engine.Engine [generated in 0.00076s] (3,)
2024-01-17 16:28:33,548 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:33,549 INFO sqlalchemy.engine.Engine [cached since 0.9037s ago] ('patrick',)

上面，我们要求发出的 SELECT 之前有一个 DELETE，这表明 patrick 的挂起删除已继续。还有一个针对 address 表的 SELECT ，这是由ORM在该表中寻找可能与目标行相关的行提示的；此行为是级联行为的一部分，可以通过允许数据库自动处理 address 中的相关行来进行定制，以更有效地工作；删除部分包含有关此的所有详细信息。

除此之外，现在被删除的 patrick 对象实例不再被视为在 Session 中持久存在，如包含检查所示：

patrick in session

False

然而，就像我们对 sandy 对象所做的更新一样，我们在这里所做的每一个更改都是正在进行的事务的本地更改，如果我们不提交它，这些更改就不会成为永久性的。由于目前回滚事务实际上更有趣，因此我们将在下一节中进行此操作。

批量/多行插入、更新插入、更新和删除 ¶

本节讨论的工作单元技术旨在将 dml 或 INSERT/UPDATE/DELETE 语句与 Python 对象机制集成，通常涉及相互关联的对象的复杂图形。一旦使用 Session.add() 将对象添加到 Session 中，随着对象的属性被创建和修改，工作单元进程就会代表我们透明地发出 INSERT/UPDATE/DELETE 信号。

然而，ORM Session 还能够处理命令，使其能够直接发出 INSERT、UPDATE 和 DELETE 语句，而无需传递任何 ORM 持久对象，而是传递要 INSERTed、UPDATEd 的值列表、或 upserted 或 WHERE 条件，以便可以调用一次匹配多行的 UPDATE 或 DELETE 语句。

当必须影响大量行而无需构造和操作映射对象时，这种使用模式尤其重要，这对于简单的性能密集型任务（例如大批量插入）来说可能很麻烦且不必要。

ORM Session 的批量/多行功能直接使用 insert() 、 update() 和 delete() 结构，它们的用法类似于如何将它们与 SQLAlchemy Core 一起使用（本教程首先在使用 INSERT 语句以及使用 UPDATE 和 DELETE 语句中介绍）。当将这些构造与 ORM Session 而不是普通的 Connection 一起使用时，它们的构造、执行和结果处理与 ORM 完全集成。

有关使用这些功能的背景和示例，请参阅 ORM 查询指南中的启用 ORM 的 INSERT、UPDATE 和 DELETE 语句部分。

Rolling Back 滚回来

Session 有一个 Session.rollback() 方法，该方法按预期在正在进行的 SQL 连接上发出 ROLLBACK。但是，它也会对当前与 Session 关联的对象产生影响，在我们前面的示例中是 Python 对象 sandy 。当我们将 sandy 对象的 .fullname 更改为读取 "Sandy Squirrel" 时，我们希望回滚此更改。调用 Session.rollback() 不仅会回滚事务，还会使当前与此 Session 关联的所有对象过期，这将导致它们在下次使用称为延迟加载：

session.rollback()

2024-01-17 16:28:33,568 INFO sqlalchemy.engine.Engine ROLLBACK

为了更仔细地查看“过期”过程，我们可以观察到 Python 对象 sandy 在其 Python dict 中没有留下任何状态，除了一个特殊的 SQLAlchemy 内部状态对象：

sandy.__dict__

{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState at 0x23d5861a0f0>}

这是“过期”状态；再次访问该属性将自动开始一个新事务并使用当前数据库行刷新 sandy ：

sandy.fullname

2024-01-17 16:28:33,590 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,592 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = ?
2024-01-17 16:28:33,592 INFO sqlalchemy.engine.Engine [generated in 0.00089s] (2,)





'Sandy Cheeks'

我们现在可以观察到完整的数据库行也被填充到 sandy 对象的 dict 中：

sandy.__dict__

{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState at 0x23d5861a0f0>,
 'fullname': 'Sandy Cheeks',
 'id': 2,
 'name': 'sandy'}

对于已删除的对象，当我们之前注意到 patrick 不再在会话中时，该对象的标识也会恢复：

patrick in session

True

当然，数据库数据也再次出现：

session.execute(select(User).where(
    User.name == "patrick")).scalar_one() is patrick

2024-01-17 16:28:33,627 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account 
WHERE user_account.name = ?
2024-01-17 16:28:33,628 INFO sqlalchemy.engine.Engine [cached since 0.9827s ago] ('patrick',)





True

Closing a Session 关闭会话 ¶

在上面的部分中，我们在 Python 上下文管理器之外使用了 Session 对象，也就是说，我们没有使用 with 语句。没关系，但是如果我们以这种方式做事，最好在完成后明确关闭 Session ：

session.close()

2024-01-17 16:28:33,640 INFO sqlalchemy.engine.Engine ROLLBACK

关闭 Session ，这也是我们在上下文管理器中使用它时发生的情况，可以完成以下任务：

它将所有连接资源释放到连接池，取消（例如回滚）任何正在进行的事务。

它从 Session 中删除所有对象。

这意味着我们为此 Session 加载的所有 Python 对象（例如 sandy 、 patrick 和 squidward ）现在都处于一种状态称为分离。特别是，我们会注意到仍然处于过期状态的对象（例如由于调用 Session.commit() ）现在不起作用，因为它们不包含当前行的状态并且不再与任何要刷新的数据库事务关联：

分离的对象可以使用 Session.add() 方法与相同的或新的 Session 重新关联，这将重新建立它们与其特定数据库行的关系：

from sqlalchemy.orm.exc import DetachedInstanceError

try:
    squidward.name
except DetachedInstanceError:
    print("DetachedInstanceError")

DetachedInstanceError

session.add(squidward)
squidward.name

# 如果可能的话，尽量避免使用处于分离状态的对象。当 Session 关闭时，也会清除对所有先前附加对象的引用。对于需要分离对象的情况，通常是立即显示 Web 应用程序的刚刚提交的对象，其中 Session 在渲染视图之前关闭，请将 Session.expire_on_commit 标志设置为 < b3>。

2024-01-17 16:28:33,659 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,660 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = ?
2024-01-17 16:28:33,660 INFO sqlalchemy.engine.Engine [cached since 0.06885s ago] (4,)





'squidward'

使用 ORM 相关对象 ¶

在本节中，我们将介绍一个更重要的 ORM 概念，即 ORM 如何与引用其他对象的映射类进行交互。在声明映射类部分中，映射类示例使用了名为 relationship() 的构造。此构造定义了两个不同映射类之间的链接，或从映射类到其自身的链接，后者称为自引用关系。

为了描述 relationship() 的基本思想，首先我们将回顾一下简短形式的映射：

class User(Base):
    __tablename__ = "user_account"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(back_populates="user")  # 与 Address 之间的关系

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

# 我们上面的映射可以在没有类型注释的情况下编写为：
# class User(Base):
#     __tablename__ = "user_account"
#     id = mapped_column(Integer, primary_key=True)
#     name = mapped_column(String(30), nullable=False)
#     fullname = mapped_column(String)
#     addresses = relationship("Address", back_populates="user")


class Address(Base):
    __tablename__ = "address"
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id = mapped_column(ForeignKey("user_account.id"))
    user: Mapped[User] = relationship(back_populates="addresses") # 与 User 之间的关系

    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

User 类现在有一个属性 User.addresses ，而 Address 类有一个属性 Address.user 。

relationship() 构造与 Mapped 构造一起指示键入行为，将用于检查映射到映射的 Table 对象之间的表关系。 User 和 Address 类。

由于表示 address 表的 Table 对象有一个引用 user_account 表的 ForeignKeyConstraint ，

因此 relationship() 可以明确地确定沿着 User.addresses 关系从 User 类到 Address 类存在一对多关系； user_account 表中的某一特定行可能会被 address 表中的许多行引用。

所有一对多关系自然对应于另一个方向上的多对一关系，在本例中是 Address.user 标记的关系。上面看到的 relationship.back_populates 参数在引用另一个名称的两个 relationship() 对象上配置，确定这两个 relationship() 构造中的每一个都应被视为与彼此;我们将在下一节中看到这是如何进行的。

持久化和加载关系 ¶

我们可以首先说明 relationship() 对对象实例的作用。

# 如果我们创建一个新的 User 对象，我们可以注意到，当我们访问 .addresses 元素时，有一个 Python 列表：
u1 = User(name="pkrabs", fullname="Pearl Krabs")
u1.addresses

# 该对象是 SQLAlchemy 特定版本的 Python list ，它能够跟踪和响应对其所做的更改。当我们访问该属性时，该集合也会自动出现，即使我们从未将其分配给该对象。
# 这类似于使用 ORM 工作单元模式插入行中提到的行为，其中观察到我们未显式分配值的基于列的属性也会自动显示为 None ，而不是引发 AttributeError 是 Python 的通常行为。

# 由于 u1 对象仍然是瞬态的，并且我们从 u1.addresses 获得的 list 尚未发生突变（即附加或扩展），因此它实际上并不与还没有对象，但是当我们对其进行更改时，它将成为 User 对象状态的一部分。

# 该集合特定于 Address 类，它是唯一可以在其中保留的 Python 对象类型。使用 list.append() 方法我们可以添加一个 Address 对象：
a1 = Address(email_address="pearl.krabs@gmail.com")
u1.addresses.append(a1)

# 此时， u1.addresses 集合按预期包含新的 Address 对象：
u1.addresses

# 当我们将 Address 对象与 u1 实例的 User.addresses 集合关联起来时，还发生了另一种行为，
# 即 User.addresses 关系将自身与 关系同步，这样我们不仅可以从 User 对象导航到 Address 对象，还可以从 Address 对象返回到“父” User 对象：
a1.user

# 此同步是由于我们在两个 relationship() 对象之间使用 relationship.back_populates 参数而发生的。
# 此参数命名另一个 relationship() ，应为其进行补充属性分配/列表突变。它在另一个方向上也同样有效，
# 也就是说，如果我们创建另一个 Address 对象并分配给它的 Address.user 属性，那么 Address 就会成为 User 对象上的 User.addresses 集合：
a2 = Address(email_address="pearl@aol.com", user=u1)
u1.addresses

# 实际上，我们在 Address 构造函数中使用了 user 参数作为关键字参数，它就像 Address 上声明的任何其他映射属性一样被接受。班级。它相当于事后分配 Address.user 属性：
a2.user = u1

[]






[Address(id=None, email_address='pearl.krabs@gmail.com')]






User(id=None, name='pkrabs', fullname='Pearl Krabs')






[Address(id=None, email_address='pearl.krabs@gmail.com'),
 Address(id=None, email_address='pearl@aol.com')]

将对象级联到会话中 ¶

我们现在有一个 User 和两个 Address 对象，它们在内存中以双向结构关联，但正如前面使用 ORM 工作单元模式插入行中所述，这些对象被称为处于瞬态，直到它们与 Session 对象关联。

我们利用仍在进行中的 Session ，并注意，当我们将 Session.add() 方法应用于引导 User 对象时，相关的 Address 中：

上述行为是， Session 接收到 User 对象，并沿着 User.addresses 关系找到相关的 Address 对象。称为保存-更新级联，并在 Cascades 的 ORM 参考文档中详细讨论。

这三个对象现在处于挂起状态；这意味着它们已准备好成为 INSERT 操作的主体，但尚未继续；所有三个对象都尚未分配主键，此外， a1 和 a2 对象有一个名为 user_id 的属性，它引用 Column 列的 ForeignKeyConstraint ；这些也是 None ，因为对象尚未与真实的数据库行关联：

session.add(u1)
u1 in session
a1 in session
a2 in session

print(u1.id)
print(a1.user_id)

True






True






True



None
None

正是在这个阶段，我们可以看到工作单元流程提供的非常大的实用性；回想一下 INSERT 部分通常会自动生成“values”子句，使用一些复杂的语法将行插入到 user_account 和 address 表中，以便自动关联 address.user_id 行的列。此外，我们有必要先对 user_account 行发出 INSERT，然后再对 address 行发出 INSERT，因为 address 中的行依赖于它们在 user_account 获取 user_id 列中的值。

当使用 Session 时，所有这些单调乏味的事情都会为我们处理，即使是最顽固的 SQL 纯粹主义者也可以从 INSERT、UPDATE 和 DELETE 语句的自动化中受益。当我们 Session.commit() 事务时，所有步骤都以正确的顺序调用，此外， user_account 行新生成的主键将适当地应用于 address.user_id 列：

session.commit()

2024-01-17 16:28:33,701 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)
2024-01-17 16:28:33,702 INFO sqlalchemy.engine.Engine [generated in 0.00102s] ('pkrabs', 'Pearl Krabs')
2024-01-17 16:28:33,705 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
2024-01-17 16:28:33,706 INFO sqlalchemy.engine.Engine [generated in 0.00010s (insertmanyvalues) 1/2 (ordered; batch not supported)] ('pearl.krabs@gmail.com', 6)
2024-01-17 16:28:33,707 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
2024-01-17 16:28:33,707 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/2 (ordered; batch not supported)] ('pearl@aol.com', 6)
2024-01-17 16:28:33,708 INFO sqlalchemy.engine.Engine COMMIT

加载关系

在最后一步中，我们调用了 Session.commit() ，它为事务发出了 COMMIT，然后每个 Session.commit.expire_on_commit 使所有对象过期，以便它们为下一个事务刷新。

当我们下次访问这些对象的属性时，我们将看到为该行的主要属性发出的 SELECT，例如当我们查看 u1 对象新生成的主键时：

u1 User 对象现在有一个我们也可以访问的持久集合 User.addresses 。由于此集合由 address 表中的一组附加行组成，因此当我们也访问此集合时，我们再次看到为了检索对象而发出的延迟加载：

SQLAlchemy ORM 中的集合和相关属性持久化在内存中；填充集合或属性后，将不再发出 SQL，直到该集合或属性过期。我们可以再次访问 u1.addresses 以及添加或删除项目，这不会产生任何新的 SQL 调用：

u1.id

u1.addresses

u1.addresses

2024-01-17 16:28:33,721 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-17 16:28:33,723 INFO sqlalchemy.engine.Engine SELECT user_account.id AS user_account_id, user_account.name AS user_account_name, user_account.fullname AS user_account_fullname 
FROM user_account 
WHERE user_account.id = ?
2024-01-17 16:28:33,723 INFO sqlalchemy.engine.Engine [cached since 0.1317s ago] (6,)





6



2024-01-17 16:28:33,726 INFO sqlalchemy.engine.Engine SELECT address.id AS address_id, address.email_address AS address_email_address, address.user_id AS address_user_id 
FROM address 
WHERE ? = address.user_id
2024-01-17 16:28:33,727 INFO sqlalchemy.engine.Engine [cached since 0.1834s ago] (6,)





[Address(id=4, email_address='pearl.krabs@gmail.com'),
 Address(id=5, email_address='pearl@aol.com')]






[Address(id=4, email_address='pearl.krabs@gmail.com'),
 Address(id=5, email_address='pearl@aol.com')]

如果我们不采取明确的步骤来优化延迟加载，那么延迟加载发出的负载很快就会变得昂贵，但延迟加载的网络至少经过了相当好的优化，不会执行多余的工作；随着 u1.addresses 集合被刷新，根据身份映射，这些实际上与我们的 a1 和 a2 对象相同的 Address 实例。已经处理过了，所以我们已经加载了这个特定对象图中的所有属性：

a1
a2

Address(id=4, email_address='pearl.krabs@gmail.com')






Address(id=5, email_address='pearl@aol.com')

在查询中使用关系 ¶

使用关系加入 ¶

显式 FROM 子句和 JOIN 以及设置 ON 子句部分介绍了如何使用 Select.join() 和 Select.join_from() 方法来组成 SQL JOIN 子句。为了描述如何在表之间进行联接，这些方法要么根据链接两个表的表元数据结构中是否存在单个明确的 ForeignKeyConstraint 对象来推断 ON 子句，要么我们可以提供一个指示特定 ON 子句的显式 SQL 表达式构造。

# 使用 ORM 实体时，可以使用一种附加机制来帮助我们设置联接的 ON 子句，即利用我们在用户映射中设置的 relationship() 对象，如所示声明映射类。与 relationship() 对应的类绑定属性可以作为单个参数传递给 Select.join() ，它用于指示连接的右侧以及 ON 子句一次：
print(select(Address.email_address).select_from(User).join(User.addresses))

# 如果我们不指定的话， Select.join() 或 Select.join_from() 不会使用映射上 ORM relationship() 的存在来推断 ON 子句。这意味着，如果我们在没有 ON 子句的情况下从 User 联接到 Address ，它会起作用，因为两个映射的 Table 之间有 ForeignKeyConstraint 对象，不是因为 User 和 Address 类上的 relationship() 对象：
print(select(Address.email_address).join_from(User, Address))

SELECT address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id
SELECT address.email_address 
FROM user_account JOIN address ON user_account.id = address.user_id

关系 WHERE 运算符 ¶

relationship() 附带了一些其他类型的 SQL 生成帮助程序，它们在构建语句的 WHERE 子句时通常很有用。请参阅 ORM 查询指南中的关系 WHERE 运算符部分。

加载器策略 ¶

在加载关系部分中，我们介绍了这样一个概念：当我们使用映射对象的实例时，在默认情况下访问使用 relationship() 映射的属性将在集合未按顺序填充时发出延迟加载加载该集合中应存在的对象。

延迟加载是最著名的 ORM 模式之一，也是最有争议的一种。

当内存中的几十个 ORM 对象各自引用一些已卸载的属性时，对这些对象的例行操作可能会产生许多额外的查询，这些查询可以加起来（也称为 N 加一问题），更糟糕的是，它们会被发出隐含地。这些隐式查询可能不会被注意到，在不再有可用的数据库事务后尝试这些查询时可能会导致错误，或者当使用 asyncio 等替代并发模式时，它们实际上根本不起作用。

同时，当延迟加载与使用的并发方法兼容并且不会导致问题时，它是一种非常流行和有用的模式。由于这些原因，SQLAlchemy 的 ORM 非常重视能够控制和优化这种加载行为。

最重要的是，有效使用 ORM 延迟加载的第一步是测试应用程序、打开 SQL 回显并观察发出的 SQL。如果似乎有很多冗余的 SELECT 语句，看起来很像可以更有效地合并为一个，如果对于已从其 Session 分离的对象发生了不适当的加载，那么就应该这样做研究使用加载器策略。

每个加载器策略对象都会向语句添加某种信息， Session 稍后在决定各种属性应如何加载和/或访问时的行为时将使用这些信息。

# 加载器策略表示为可以使用 Select.options() 方法与 SELECT 语句关联的对象，例如：
from sqlalchemy.orm import selectinload

for user_obj in session.execute(
    select(User).options(selectinload(User.addresses))
).scalars():
    user_obj.addresses  # access addresses collection already loaded

2024-01-17 16:28:33,763 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account
2024-01-17 16:28:33,764 INFO sqlalchemy.engine.Engine [generated in 0.00084s] ()
2024-01-17 16:28:33,767 INFO sqlalchemy.engine.Engine SELECT address.user_id AS address_user_id, address.id AS address_id, address.email_address AS address_email_address 
FROM address 
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
2024-01-17 16:28:33,767 INFO sqlalchemy.engine.Engine [generated in 0.00098s] (1, 2, 3, 4, 5, 6)





[Address(id=1, email_address='spongebob@sqlalchemy.org')]






[Address(id=2, email_address='sandy@sqlalchemy.org'), Address(id=3, email_address='sandy@squirrelpower.org')]






[]






[]






[]






[Address(id=4, email_address='pearl.krabs@gmail.com'), Address(id=5, email_address='pearl@aol.com')]

# 它们也可以使用 relationship.lazy 选项配置为 relationship() 的默认值，例如：
# from sqlalchemy.orm import Mapped
# from sqlalchemy.orm import relationship

# class User(Base):
#     __tablename__ = "user_account"

#     addresses: Mapped[List["Address"]] = relationship(
#         back_populates="user", lazy="selectin"  # 使用 relationship.lazy 选项配置
#         )

Selectin Load 选择加载

现代 SQLAlchemy 中最有用的加载器是 selectinload() 加载器选项。此选项解决了最常见形式的“N 加一”问题，即引用相关集合的一组对象的问题。 selectinload() 将确保使用单个查询预先加载完整系列对象的特定集合。

它使用 SELECT 表单来执行此操作，在大多数情况下，可以单独针对相关表发出该表单，而无需引入 JOIN 或子查询，并且仅查询尚未加载集合的那些父对象。下面我们通过加载所有 User 对象及其所有相关 Address 对象来说明 selectinload() ；虽然我们只调用 Session.execute() 一次，但给定 select() 构造，当访问数据库时，实际上发出了两个 SELECT 语句，第二个是获取相关的 Address 对象：

from sqlalchemy.orm import selectinload
stmt = select(User).options(selectinload(User.addresses)).order_by(User.id)
for row in session.execute(stmt):
    print(
        f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})"
    )

2024-01-17 16:28:33,798 INFO sqlalchemy.engine.Engine SELECT user_account.id, user_account.name, user_account.fullname 
FROM user_account ORDER BY user_account.id
2024-01-17 16:28:33,799 INFO sqlalchemy.engine.Engine [generated in 0.00134s] ()
2024-01-17 16:28:33,802 INFO sqlalchemy.engine.Engine SELECT address.user_id AS address_user_id, address.id AS address_id, address.email_address AS address_email_address 
FROM address 
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
2024-01-17 16:28:33,802 INFO sqlalchemy.engine.Engine [cached since 0.03606s ago] (1, 2, 3, 4, 5, 6)
spongebob  (spongebob@sqlalchemy.org)
sandy  (sandy@sqlalchemy.org, sandy@squirrelpower.org)
patrick  ()
squidward  ()
ehkrabs  ()
pkrabs  (pearl.krabs@gmail.com, pearl@aol.com)

Joined Load 关联负载

joinedload() 急切加载策略是 SQLAlchemy 中最古老的急切加载器，它通过 JOIN（可能是外连接或内连接，具体取决于选项）增强了传递到数据库的 SELECT 语句，这可以然后加载相关对象。

joinedload() 策略最适合加载相关的多对一对象，因为这只需要将附加列添加到在任何情况下都会获取的主实体行。为了提高效率，它还接受选项 joinedload.innerjoin ，以便在如下情况下使用内部联接而不是外部联接，我们知道所有 Address 对象都有一个关联的 User ：

joinedload() 也适用于集合，意味着一对多关系，但是它具有以递归方式乘以每个相关项目的主行的效果，从而增加按订单发送给结果集的数据量对于嵌套集合和/或更大的集合来说，它的数量级很大，因此它的使用与其他集合的使用不同。

from sqlalchemy.orm import joinedload
stmt = (
    select(Address)
    .options(joinedload(Address.user, innerjoin=True))
    .order_by(Address.id)
)
for row in session.execute(stmt):
    print(f"{row.Address.email_address} {row.Address.user.name}")

2024-01-17 16:31:04,871 INFO sqlalchemy.engine.Engine SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname 
FROM address JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id ORDER BY address.id
2024-01-17 16:31:04,872 INFO sqlalchemy.engine.Engine [generated in 0.00102s] ()
spongebob@sqlalchemy.org spongebob
sandy@sqlalchemy.org sandy
sandy@squirrelpower.org sandy
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs

Explicit Join + Eager load 显式连接 + 预加载

如果我们要在使用 Select.join() 之类的方法连接到 user_account 表时加载 Address 行来呈现 JOIN，我们还可以按顺序利用该 JOIN立即加载返回的每个 Address 对象上的 Address.user 属性的内容。这本质上是我们使用“联合预加载”，但我们自己渲染 JOIN。此常见用例是通过使用 contains_eager() 选项来实现的。此选项与 joinedload() 非常相似，不同之处在于它假设我们自己设置了 JOIN，并且它仅指示 COLUMNS 子句中的附加列应加载到每个返回对象的相关属性中，例如例子：

上面，我们过滤了 user_account.name 上的行，并将 user_account 中的行加载到返回行的 Address.user 属性中。如果我们单独应用 joinedload() ，我们将得到一个不必要连接两次的 SQL 查询：

from sqlalchemy.orm import contains_eager
stmt = (
    select(Address)
    .join(Address.user)
    .where(User.name == "pkrabs")
    .options(contains_eager(Address.user))
    .order_by(Address.id)
)
for row in session.execute(stmt):
    print(f"{row.Address.email_address} {row.Address.user.name}")

Raiseload 提升加载 ¶

值得一提的另一个加载器策略是 raiseload() 。此选项用于通过导致通常是延迟加载的内容引发错误来完全阻止应用程序出现 N 加一问题。它有两个变体，通过 raiseload.sql_only 选项控制，以阻止需要 SQL 的延迟加载，而不是所有“加载”操作，包括那些只需要查阅当前 Session 的操作。

使用 raiseload() 的一种方法是在 relationship() 本身上配置它，方法是将 relationship.lazy 设置为值 "raise_on_sql" ，以便对于特定的映射时，某种关系永远不会尝试发出 SQL：

# from sqlalchemy.orm import Mapped
# from sqlalchemy.orm import relationship


# class User(Base):
#     __tablename__ = "user_account"
#     id: Mapped[int] = mapped_column(primary_key=True)
#     addresses: Mapped[List["Address"]] = relationship(
#         back_populates="user", lazy="raise_on_sql"
#     )


# class Address(Base):
#     __tablename__ = "address"
#     id: Mapped[int] = mapped_column(primary_key=True)
#     user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
#     user: Mapped["User"] = relationship(
#         back_populates="addresses", lazy="raise_on_sql")
    
# 使用这样的映射，应用程序将被阻止延迟加载，这表明特定查询需要指定加载器策略：

# u1 = session.execute(select(User)).scalars().first()
# u1.addresses

# 该异常表明该集合应该预先加载：

# u1 = (
#     session.execute(select(User).options(selectinload(User.addresses)))
#     .scalars()
#     .first()
# )

# lazy="raise_on_sql" 选项也尝试对多对一关系进行智能处理；上面，如果 Address 对象的 Address.user 属性未加载，但该 User 对象本地存在于同一个 Session 中， “raiseload”策略不会引发错误。

jupyter nbconvert --to markdown "SQLAlchemy 2.0 统一教程 P2_HAND.IPYNB"

UPDATE 2024-01-17 BY YULIKE