ORM 快速入门¶

ORM Quick Start

中文

对于想快速了解基本 ORM 使用的新用户，这里是 SQLAlchemy 统一教程中使用的映射和示例的简短形式。此处的代码可以从一个干净的命令行完全运行。

由于本节中的描述故意 非常简短(very short)，请继续阅读完整的 SQLAlchemy 统一教程以获取每个概念的更深入描述。

在 2.0 版本发生变更: ORM 快速入门已更新为最新的 PEP 484 感知功能，使用了包括 mapped_column() 在内的新构造。有关迁移信息，请参阅部分 ORM Declarative Models。

英文

For new users who want to quickly see what basic ORM use looks like, here’s an abbreviated form of the mappings and examples used in the SQLAlchemy 统一教程. The code here is fully runnable from a clean command line.

As the descriptions in this section are intentionally very short, please proceed to the full SQLAlchemy 统一教程 for a much more in-depth description of each of the concepts being illustrated here.

在 2.0 版本发生变更: The ORM Quickstart is updated for the latest PEP 484-aware features using new constructs including mapped_column(). See the section ORM Declarative Models for migration information.

声明模型¶

Declare Models

中文

在这里，我们定义了模块级别的结构，这些结构将形成我们从数据库中查询的内容。这种结构称为声明性映射，它同时定义了一个Python对象模型，以及描述实际存在或将存在于特定数据库中的SQL表的数据库元数据

>>> from typing import List
>>> from typing import Optional
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy import String
>>> 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 User(Base):
...     __tablename__ = "user_account"
...
...     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", cascade="all, delete-orphan"
...     )
...
...     def __repr__(self) -> str:
...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

>>> class Address(Base):
...     __tablename__ = "address"
...
...     id: Mapped[int] = mapped_column(primary_key=True)
...     email_address: Mapped[str]
...     user_id: Mapped[int] = 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})"

映射从一个基类开始，上面的例子中称为``Base``，它是通过简单地对:class:`_orm.DeclarativeBase`类进行子类化创建的。

然后，通过创建 Base 的子类来创建单个映射类。映射类通常指一个特定的数据库表，其名称通过使用 __tablename__ 类级别属性来指示。

接下来，通过添加包含称为 Mapped 的特殊类型注释的属性，声明表的一部分的列。每个属性的名称对应于要成为数据库表一部分的列的名称。每列的数据类型首先从与每个 Mapped 注释关联的Python数据类型中获取； int 对应 INTEGER ， str 对应 VARCHAR 等。可为空性来自于是否使用了 Optional[] 类型修饰符。可以在右侧 mapped_column() 指令中使用SQLAlchemy类型对象来指示更具体的类型信息，例如在 User.name 列中使用的 String 数据类型。可以使用类型注释映射自定义Python类型和SQL类型之间的关联。

mapped_column() 指令用于所有需要更具体定制的基于列的属性。除了类型信息外，该指令还接受广泛的参数，用于指示有关数据库列的具体细节，包括服务器默认值和约束信息，例如主键和外键的成员资格。 mapped_column() 指令接受SQLAlchemy Column 类接受的参数的超集，SQLAlchemy Core使用该类表示数据库列。

所有ORM映射类都需要至少一个列声明为主键的一部分，通常通过在应成为主键一部分的那些 mapped_column() 对象上使用 Column.primary_key 参数。在上面的例子中， User.id 和 Address.id 列被标记为主键。

总体而言，字符串表名称和列声明列表的组合在SQLAlchemy中称为表元数据。在统一教程的:ref:tutorial_working_with_metadata 中介绍了使用Core和ORM方法设置表元数据。上面的映射是称为注释声明性表配置的示例。

还有其他变体的 Mapped 可用，最常见的是上面指示的 relationship() 构造。与基于列的属性相反， relationship() 表示两个ORM类之间的链接。在上面的例子中， User.addresses 将 User 链接到` Address`，而 Address.user 将 Address 链接到 User 。在统一教程的使用 ORM 相关对象中介绍了 relationship() 构造。

最后，上面的示例类包括一个 __repr__() 方法，这不是必需的，但对调试很有用。可以使用数据类自动生成类似 __repr__() 的方法来创建映射类。有关数据类映射的更多信息，请参见声明式Dataclass映射。

英文

Here, we define module-level constructs that will form the structures which we will be querying from the database. This structure, known as a Declarative Mapping, defines at once both a Python object model, as well as database metadata that describes real SQL tables that exist, or will exist, in a particular database:

>>> from typing import List
>>> from typing import Optional
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy import String
>>> 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 User(Base):
...     __tablename__ = "user_account"
...
...     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", cascade="all, delete-orphan"
...     )
...
...     def __repr__(self) -> str:
...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

>>> class Address(Base):
...     __tablename__ = "address"
...
...     id: Mapped[int] = mapped_column(primary_key=True)
...     email_address: Mapped[str]
...     user_id: Mapped[int] = 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})"

The mapping starts with a base class, which above is called Base, and is created by making a simple subclass against the DeclarativeBase class.

Individual mapped classes are then created by making subclasses of Base. A mapped class typically refers to a single particular database table, the name of which is indicated by using the __tablename__ class-level attribute.

Next, columns that are part of the table are declared, by adding attributes that include a special typing annotation called Mapped. The name of each attribute corresponds to the column that is to be part of the database table. The datatype of each column is taken first from the Python datatype that’s associated with each Mapped annotation; int for INTEGER, str for VARCHAR, etc. Nullability derives from whether or not the Optional[] type modifier is used. More specific typing information may be indicated using SQLAlchemy type objects in the right side mapped_column() directive, such as the String datatype used above in the User.name column. The association between Python types and SQL types can be customized using the type annotation map.

The mapped_column() directive is used for all column-based attributes that require more specific customization. Besides typing information, this directive accepts a wide variety of arguments that indicate specific details about a database column, including server defaults and constraint information, such as membership within the primary key and foreign keys. The mapped_column() directive accepts a superset of arguments that are accepted by the SQLAlchemy Column class, which is used by SQLAlchemy Core to represent database columns.

All ORM mapped classes require at least one column be declared as part of the primary key, typically by using the Column.primary_key parameter on those mapped_column() objects that should be part of the key. In the above example, the User.id and Address.id columns are marked as primary key.

Taken together, the combination of a string table name as well as a list of column declarations is known in SQLAlchemy as table metadata. Setting up table metadata using both Core and ORM approaches is introduced in the SQLAlchemy 统一教程 at 使用数据库元数据. The above mapping is an example of what’s known as Annotated Declarative Table configuration.

Other variants of Mapped are available, most commonly the relationship() construct indicated above. In contrast to the column-based attributes, relationship() denotes a linkage between two ORM classes. In the above example, User.addresses links User to Address, and Address.user links Address to User. The relationship() construct is introduced in the SQLAlchemy 统一教程 at 使用 ORM 相关对象.

Finally, the above example classes include a __repr__() method, which is not required but is useful for debugging. Mapped classes can be created with methods such as __repr__() generated automatically, using dataclasses. More on dataclass mapping at 声明式Dataclass映射.

创建引擎¶

Create an Engine

中文

Engine 是一个 工厂(factory)，可以为我们创建新的数据库连接，同时还将连接保存在连接池中以便快速重用。为了学习目的，我们通常使用一个方便的 SQLite 内存数据库:

>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite://", echo=True)

小技巧

echo=True 参数指示连接发出的SQL语句将被记录到标准输出。

有关 Engine 的完整介绍请参见建立连接 - Engine 。

英文

The Engine is a factory that can create new database connections for us, which also holds onto connections inside of a Connection Pool for fast reuse. For learning purposes, we normally use a SQLite memory-only database for convenience:

>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite://", echo=True)

小技巧

The echo=True parameter indicates that SQL emitted by connections will be logged to standard out.

A full intro to the Engine starts at 建立连接 - Engine.

发出 CREATE TABLE DDL¶

Emit CREATE TABLE DDL

中文

使用我们的表元数据和引擎，我们可以使用称为 MetaData.create_all() 的方法在目标SQLite数据库中一次性生成我们的模式：

>>> Base.metadata.create_all(engine)BEGIN (implicit)
PRAGMA main.table_...info("user_account")
...
PRAGMA main.table_...info("address")
...
CREATE TABLE user_account (
    id INTEGER NOT NULL,
    name VARCHAR(30) NOT NULL,
    fullname VARCHAR,
    PRIMARY KEY (id)
)
...
CREATE TABLE address (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES user_account (id)
)
...
COMMIT

从我们编写的这段Python代码中发生了很多事情。有关表元数据的完整概述，请继续阅读教程使用数据库元数据。

英文

Using our table metadata and our engine, we can generate our schema at once in our target SQLite database, using a method called MetaData.create_all():

>>> Base.metadata.create_all(engine)BEGIN (implicit)
PRAGMA main.table_...info("user_account")
...
PRAGMA main.table_...info("address")
...
CREATE TABLE user_account (
    id INTEGER NOT NULL,
    name VARCHAR(30) NOT NULL,
    fullname VARCHAR,
    PRIMARY KEY (id)
)
...
CREATE TABLE address (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES user_account (id)
)
...
COMMIT

A lot just happened from that bit of Python code we wrote. For a complete overview of what’s going on on with Table metadata, proceed in the Tutorial at 使用数据库元数据.

创建对象并持久化¶

Create Objects and Persist

中文

现在我们已经准备好向数据库插入数据了。我们通过创建具有已由声明性映射过程自动建立的 __init__() 方法的 User 和 Address 类的实例来完成此操作。然后，我们使用一个称为 Session 的对象将它们传递到数据库，该对象利用 Engine 与数据库交互。这里使用 Session.add_all() 方法一次添加多个对象，并使用 Session.commit() 方法来刷新任何待处理的更改到数据库，然后提交当前的数据库事务，只要使用 Session，该事务就始终在进行中：

>>> from sqlalchemy.orm importSession>>> withSession(engine)assession:...     spongebob = User(...         name="spongebob",...         fullname="Spongebob Squarepants",...         addresses=[Address(email_address="spongebob@sqlalchemy.org")],...     )...     sandy = User(...         name="sandy",...         fullname="Sandy Cheeks",...         addresses=[...             Address(email_address="sandy@sqlalchemy.org"),...             Address(email_address="sandy@squirrelpower.org"),...         ],...     )...     patrick = User(name="patrick", fullname="Patrick Star")......     session.add_all([spongebob, sandy, patrick])......     session.commit()BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('spongebob', 'Spongebob Squarepants')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('sandy', 'Sandy Cheeks')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('patrick', 'Patrick Star')
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('spongebob@sqlalchemy.org', 1)
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('sandy@sqlalchemy.org', 2)
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('sandy@squirrelpower.org', 2)
COMMIT

小技巧

建议以上述方式在上下文管理器样式中使用 Session，即使用Python with: 语句。 Session 对象表示活动的数据库资源，因此在完成一系列操作后，确保关闭它是很好的。在下一节中，我们将保持 Session 打开只是为了说明。

创建 Session 的基础知识在使用 ORM 会话执行，更多内容在使用会话的基础知识。

然后，在使用 ORM 工作单元模式插入行中介绍了一些基本持久化操作的变体。

英文

We are now ready to insert data in the database. We accomplish this by creating instances of User and Address classes, which have an __init__() method already as established automatically by the declarative mapping process. We then pass them to the database using an object called a Session, which makes use of the Engine to interact with the database. The Session.add_all() method is used here to add multiple objects at once, and the Session.commit() method will be used to flush any pending changes to the database and then commit the current database transaction, which is always in progress whenever the Session is used:

>>> from sqlalchemy.orm importSession>>> withSession(engine)assession:...     spongebob = User(...         name="spongebob",...         fullname="Spongebob Squarepants",...         addresses=[Address(email_address="spongebob@sqlalchemy.org")],...     )...     sandy = User(...         name="sandy",...         fullname="Sandy Cheeks",...         addresses=[...             Address(email_address="sandy@sqlalchemy.org"),...             Address(email_address="sandy@squirrelpower.org"),...         ],...     )...     patrick = User(name="patrick", fullname="Patrick Star")......     session.add_all([spongebob, sandy, patrick])......     session.commit()BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('spongebob', 'Spongebob Squarepants')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('sandy', 'Sandy Cheeks')
INSERT INTO user_account (name, fullname) VALUES (?, ?) RETURNING id
[...] ('patrick', 'Patrick Star')
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('spongebob@sqlalchemy.org', 1)
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('sandy@sqlalchemy.org', 2)
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[...] ('sandy@squirrelpower.org', 2)
COMMIT

小技巧

It’s recommended that the Session be used in context manager style as above, that is, using the Python with: statement. The Session object represents active database resources so it’s good to make sure it’s closed out when a series of operations are completed. In the next section, we’ll keep a Session opened just for illustration purposes.

Basics on creating a Session are at 使用 ORM 会话执行 and more at 使用会话的基础知识.

Then, some varieties of basic persistence operations are introduced at 使用 ORM 工作单元模式插入行.

简单 SELECT¶

Simple SELECT

中文

在数据库中有一些行后，这里是发出SELECT语句以加载一些对象的最简单形式。要创建SELECT语句，我们使用 select() 函数创建一个新的 Select 对象，然后使用 Session 调用它。在查询ORM对象时，经常有用的方法是 Session.scalars() 方法，它将返回一个 ScalarResult 对象，该对象将迭代我们选择的ORM对象：

>>> from sqlalchemy importselect>>> session=Session(engine)>>> stmt=select(User).where(User.name.in_(["spongebob","sandy"]))>>> foruserinsession.scalars(stmt):...     print(user)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name IN (?, ?)
[...] ('spongebob', 'sandy')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=2, name='sandy', fullname='Sandy Cheeks')

上面的查询还使用了 Select.where() 方法来添加WHERE条件，并且还使用了所有SQLAlchemy列类构造的一部分 ColumnOperators.in_() 方法来使用SQL IN操作符。

有关如何选择对象和单个列的更多详细信息，请参见选择 ORM 实体和列。

英文

With some rows in the database, here’s the simplest form of emitting a SELECT statement to load some objects. To create SELECT statements, we use the select() function to create a new Select object, which we then invoke using a Session. The method that is often useful when querying for ORM objects is the Session.scalars() method, which will return a ScalarResult object that will iterate through the ORM objects we’ve selected:

>>> from sqlalchemy importselect>>> session=Session(engine)>>> stmt=select(User).where(User.name.in_(["spongebob","sandy"]))>>> foruserinsession.scalars(stmt):...     print(user)BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name IN (?, ?)
[...] ('spongebob', 'sandy')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')User(id=2, name='sandy', fullname='Sandy Cheeks')

The above query also made use of the Select.where() method to add WHERE criteria, and also used the ColumnOperators.in_() method that’s part of all SQLAlchemy column-like constructs to use the SQL IN operator.

More detail on how to select objects and individual columns is at 选择 ORM 实体和列.

使用 JOIN 进行 SELECT¶

SELECT with JOIN

中文

在SQL中，JOIN关键字是同时查询多个表的主要方式。join 方法来创建连接：

>>> stmt=(...     select(Address)...     .join(Address.user)...     .where(User.name == "sandy")...     .where(Address.email_address == "sandy@sqlalchemy.org")... )>>> sandy_address=session.scalars(stmt).one()SELECT address.id, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? AND address.email_address = ?
[...] ('sandy', 'sandy@sqlalchemy.org')
>>> sandy_addressAddress(id=2, email_address='sandy@sqlalchemy.org')

上面的查询说明了多个WHERE条件如何自动用AND链接在一起，以及如何使用SQLAlchemy类列对象来创建“等式”比较，这使用了重载的Python方法 ColumnOperators.__eq__() 来生成SQL条件对象。

有关上述概念的更多背景信息，请参见 WHERE 子句和显式 FROM 子句和 JOIN。

英文

It’s very common to query amongst multiple tables at once, and in SQL the JOIN keyword is the primary way this happens. The Select construct creates joins using the Select.join() method:

>>> stmt=(...     select(Address)...     .join(Address.user)...     .where(User.name == "sandy")...     .where(Address.email_address == "sandy@sqlalchemy.org")... )>>> sandy_address=session.scalars(stmt).one()SELECT address.id, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? AND address.email_address = ?
[...] ('sandy', 'sandy@sqlalchemy.org')
>>> sandy_addressAddress(id=2, email_address='sandy@sqlalchemy.org')

The above query illustrates multiple WHERE criteria which are automatically chained together using AND, as well as how to use SQLAlchemy column-like objects to create “equality” comparisons, which uses the overridden Python method ColumnOperators.__eq__() to produce a SQL criteria object.

Some more background on the concepts above are at WHERE 子句 and 显式 FROM 子句和 JOIN.

进行更改¶

Make Changes

中文

Session 对象与我们的ORM映射类 User 和 Address 一起，自动跟踪对对象所做的更改，这些更改会在下次 Session 刷新时发出SQL语句。下面，我们更改了与“sandy”关联的一个电子邮件地址，并向“patrick”添加了一个新电子邮件地址，在发出SELECT语句以检索“patrick”的行之后：

>>> stmt=select(User).where(User.name=="patrick")>>> patrick=session.scalars(stmt).one()SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('patrick',)
>>> patrick.addresses.append(Address(email_address="patrickstar@sqlalchemy.org"))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
[...] (3,)
>>> sandy_address.email_address="sandy_cheeks@sqlalchemy.org">>> session.commit()UPDATE address SET email_address=? WHERE address.id = ?
[...] ('sandy_cheeks@sqlalchemy.org', 2)
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('patrickstar@sqlalchemy.org', 3)
COMMIT

注意当我们访问 patrick.addresses 时，发出了一个SELECT语句。这被称为延迟加载。关于使用更多或更少SQL访问相关项目的不同方式的背景信息，请参见加载器策略。

有关ORM数据操作的详细演练，请参见使用 ORM 进行数据操作。

英文

The Session object, in conjunction with our ORM-mapped classes User and Address, automatically track changes to the objects as they are made, which result in SQL statements that will be emitted the next time the Session flushes. Below, we change one email address associated with “sandy”, and also add a new email address to “patrick”, after emitting a SELECT to retrieve the row for “patrick”:

>>> stmt=select(User).where(User.name=="patrick")>>> patrick=session.scalars(stmt).one()SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('patrick',)
>>> patrick.addresses.append(Address(email_address="patrickstar@sqlalchemy.org"))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
[...] (3,)
>>> sandy_address.email_address="sandy_cheeks@sqlalchemy.org">>> session.commit()UPDATE address SET email_address=? WHERE address.id = ?
[...] ('sandy_cheeks@sqlalchemy.org', 2)
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('patrickstar@sqlalchemy.org', 3)
COMMIT

Notice when we accessed patrick.addresses, a SELECT was emitted. This is called a lazy load. Background on different ways to access related items using more or less SQL is introduced at 加载器策略.

A detailed walkthrough on ORM data manipulation starts at 使用 ORM 进行数据操作.

进行一些删除¶

Some Deletes

中文

所有事物都必须结束，就像我们的一些数据库行一样——这里是两种不同形式删除操作的快速演示，根据具体的用例，这两种形式都很重要。

首先，我们将删除“sandy”用户的一个 Address 对象。当 Session 下一次刷新时，这将导致行被删除。这种行为是我们在映射中配置的，称为级联删除。我们可以使用 Session.get() 按主键获取 sandy 对象，然后处理该对象：

>>> sandy=session.get(User,2)BEGIN (implicit)
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 = ?
[...] (2,)
>>> sandy.addresses.remove(sandy_address)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
[...] (2,)

上面的最后一个SELECT语句是延迟加载操作，以便加载 sandy.addresses 集合，以便我们可以移除 sandy_address 成员。还有其他方法可以进行这一系列操作，不会发出这么多SQL。

我们可以选择使用 Session.flush() 方法发出DELETE SQL语句，而不提交事务：

>>> session.flush()DELETE FROM address WHERE address.id = ?
[...] (2,)

接下来，我们将完全删除“patrick”用户。对于对象本身的顶级删除，我们使用 Session.delete() 方法；此方法实际上并不执行删除，而是设置对象在下一次刷新时被删除。该操作还会根据我们配置的级联选项级联到相关对象，在本例中为相关的``Address``对象：

>>> session.delete(patrick)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 = ?
[...] (3,)
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
[...] (3,)

在这种特殊情况下，Session.delete() 方法发出了两个SELECT语句，尽管它没有发出DELETE语句，这可能看起来令人惊讶。这是因为当方法检查对象时，发现 patrick 对象已经过期，这是当我们上次调用 Session.commit() 时发生的，发出的SQL是为了从新事务中重新加载行。这种过期是可选的，在正常使用中，我们通常会在不适用的情况下将其关闭。

为了说明行被删除，下面是提交操作：

>>> session.commit()DELETE FROM address WHERE address.id = ?
[...] (4,)
DELETE FROM user_account WHERE user_account.id = ?
[...] (3,)
COMMIT

教程在使用工作单元模式删除 ORM 对象中讨论了ORM删除操作。对象过期的背景信息在过期/刷新；级联在级联中有详细讨论。

英文

All things must come to an end, as is the case for some of our database rows - here’s a quick demonstration of two different forms of deletion, both of which are important based on the specific use case.

First we will remove one of the Address objects from the “sandy” user. When the Session next flushes, this will result in the row being deleted. This behavior is something that we configured in our mapping called the delete cascade. We can get a handle to the sandy object by primary key using Session.get(), then work with the object:

>>> sandy=session.get(User,2)BEGIN (implicit)
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 = ?
[...] (2,)
>>> sandy.addresses.remove(sandy_address)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
[...] (2,)

The last SELECT above was the lazy load operation proceeding so that the sandy.addresses collection could be loaded, so that we could remove the sandy_address member. There are other ways to go about this series of operations that won’t emit as much SQL.

We can choose to emit the DELETE SQL for what’s set to be changed so far, without committing the transaction, using the Session.flush() method:

>>> session.flush()DELETE FROM address WHERE address.id = ?
[...] (2,)

Next, we will delete the “patrick” user entirely. For a top-level delete of an object by itself, we use the Session.delete() method; this method doesn’t actually perform the deletion, but sets up the object to be deleted on the next flush. The operation will also cascade to related objects based on the cascade options that we configured, in this case, onto the related Address objects:

>>> session.delete(patrick)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 = ?
[...] (3,)
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
[...] (3,)

The Session.delete() method in this particular case emitted two SELECT statements, even though it didn’t emit a DELETE, which might seem surprising. This is because when the method went to inspect the object, it turns out the patrick object was expired, which happened when we last called upon Session.commit(), and the SQL emitted was to re-load the rows from the new transaction. This expiration is optional, and in normal use we will often be turning it off for situations where it doesn’t apply well.

To illustrate the rows being deleted, here’s the commit:

>>> session.commit()DELETE FROM address WHERE address.id = ?
[...] (4,)
DELETE FROM user_account WHERE user_account.id = ?
[...] (3,)
COMMIT

The Tutorial discusses ORM deletion at 使用工作单元模式删除 ORM 对象. Background on object expiration is at 过期/刷新; cascades are discussed in depth at 级联.

深入了解上述概念¶

Learn the above concepts in depth

中文

对于新用户来说，上面的部分可能是一次旋风之旅。每一步中都有很多重要的概念没有涵盖。在快速了解事物外观之后，建议通读 SQLAlchemy 统一教程，以深入了解上面的内容。祝你好运！

英文

For a new user, the above sections were likely a whirlwind tour. There’s a lot of important concepts in each step above that weren’t covered. With a quick overview of what things look like, it’s recommended to work through the SQLAlchemy 统一教程 to gain a solid working knowledge of what’s really going on above. Good luck!