使用事务和 DBAPI¶

Engine 对象准备就绪后，我们可以深入了解 Engine 的基本操作及其主要端点、Connection 和 结果。我们还将介绍 ORM 的 Facade 对于这些对象，称为 Session。

ORM 读者注意事项

使用 ORM 时，Engine 由 会话。现代 SQLAlchemy 中的 Session 强调事务和 SQL 执行模式，该模式与下面讨论的 Connection 基本相同，因此，虽然本小节以 Core 为中心，但这里的所有概念也与 ORM 使用相关，建议所有 ORM 学习者使用。Connection 使用的执行模式将与本节末尾的 Session 进行比较。

由于我们还没有介绍 SQLAlchemy 的主要功能 SQLAlchemy 表达式语言，我们将在这个包中使用一个名为 text（） 结构的简单结构，将 SQL 语句编写为文本 SQL。请放心，文本 SQL 是日常 SQLAlchemy 使用的例外而不是规则，但它始终可用。

获取连接¶

Engine 的目的是通过提供 Connection 对象来连接到数据库。当直接与 Core 一起工作时，Connection 对象是完成与数据库的所有交互的方式。由于 Connection 针对数据库创建开放资源，因此我们希望将此对象的使用限制为特定上下文。最好的方法是使用 Python 上下文管理器，也称为 with 语句。下面我们使用文本 SQL 语句来显示 “Hello World”。文本 SQL 是使用名为 text（） 的构造创建的，我们将在后面更详细地讨论：

>>> fromsqlalchemyimporttext>>> withengine.connect()asconn:...     result = conn.execute(text("select 'hello world'"))...     print(result.all())BEGIN (implicit)
select 'hello world'
[...] ()
[('hello world',)]ROLLBACK

在上面的示例中，上下文管理器创建一个数据库连接并在事务中执行操作。Python DBAPI 的默认行为是事务始终在进行中;释放连接时，将发出 ROLLBACK 以结束事务。事务不会自动提交;如果我们想提交数据，我们需要调用 Connection.commit（） 我们将在下一节中看到。

提示

“autocommit” 模式可用于特殊情况。该部分设置 Transaction Isolation Levels including DBAPI Autocommit 对此进行了讨论。

我们的 SELECT 结果在名为 结果，我们将在后面讨论。现在，我们要补充一点，最好在 “connect” 块中使用这个对象，并且不要在我们的连接范围之外使用它。

提交更改¶

我们刚刚了解到 DBAPI 连接不会自动提交。如果我们想提交一些数据怎么办？我们可以更改上面的示例，创建一个表，插入一些数据，然后使用 Connection.commit（） 方法在我们拥有 Connection 对象的块内提交事务：

# "commit as you go">>> withengine.connect()asconn:...     conn.execute(text("CREATE TABLE some_table (x int, y int)"))...     conn.execute(...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),...         [{"x": 1, "y": 1}, {"x": 2, "y": 4}],...     )...     conn.commit()BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(1, 1), (2, 4)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

在上面，我们执行两个 SQL 语句，一个 “CREATE TABLE” 语句 [1] 和一个参数化的 “INSERT” 语句（我们讨论参数化语法稍后在发送多个参数中）。为了提交我们在区块中所做的工作，我们调用 Connection.commit（） 方法提交事务。在此之后，我们可以继续运行更多的 SQL 语句并调用 Connection.commit（） 再次用于这些声明。 SQLAlchemy 将此样式称为 commit as 你去吧。

还有另一种提交数据的样式。我们可以声明我们的 “connect” 区块设置为前面的交易区块。为此，我们使用 Engine.begin（） 方法获取连接，而不是 Engine.connect（） 方法。此方法将管理 Connection 的范围，并在区块成功时在末尾使用 COMMIT 将事务内的所有内容包含在内，如果引发异常，则在 ROLLBACK 末尾使用 ROLLBACK。这种样式称为 begin once：

# "begin once">>> withengine.begin()asconn:...     conn.execute(...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),...         [{"x": 6, "y": 8}, {"x": 9, "y": 10}],...     )BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(6, 8), (9, 10)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

您应该更喜欢 “begin once” 样式，因为它更短，并且预先显示了整个块的意图。但是，在本教程中，我们将使用 “commit as you go” 样式，因为它更灵活地用于演示目的。

语句执行基础¶

我们已经看到了一些针对数据库运行 SQL 语句的示例，它使用名为 Connection.execute（） 的方法，结合名为 text（） 的对象，并返回一个名为 结果。在本节中，我们将更详细地说明这些组件的机制和交互。

本节中的大部分内容在使用 Session.execute（） 方法时同样适用于现代 ORM 使用，该方法的工作方式与 Connection.execute（） 非常相似，包括 ORM 结果行使用相同的 Result 交付 Core 使用的接口。

获取行¶

首先，我们将通过使用之前插入的行来更详细地说明 Result 对象，在我们创建的表上运行文本 SELECT 语句：

>>> withengine.connect()asconn:...     result = conn.execute(text("SELECT x, y FROM some_table"))...     for row in result:...         print(f"x: {row.x}  y: {row.y}")BEGIN (implicit)
SELECT x, y FROM some_table
[...] ()
x: 1  y: 1x: 2  y: 4x: 6  y: 8x: 9  y: 10ROLLBACK

在上面，我们执行的 “SELECT” 字符串从表中选择了所有行。返回的对象称为 Result，表示结果行的可迭代对象。

Result 有很多用于获取和转换行的方法，例如 Result.all（） 方法，该方法返回所有 Row 对象。它还实现了 Python 迭代器接口，以便我们可以直接迭代 Row 对象的集合。

Row 对象本身旨在像 Python 一样工作命名元组。下面我们说明了访问行的多种方法。

Tuple Assignment - 这是最惯用的 Python 风格，即在收到变量时按位置将变量分配给每一行：
```
result = conn.execute(text("select x, y from some_table"))

for x, y in result:
    ...
```

整数索引 - 元组是 Python 序列，因此也可以使用常规整数访问：

result = conn.execute(text("select x, y from some_table"))

for row in result:
    x = row[0]

属性名称 - 由于这些是 Python 命名的元组，因此元组具有与每列名称匹配的动态属性名称。这些名称通常是 SQL 语句分配给每行中的列的名称。虽然它们通常是相当可预测的，并且也可以通过标签进行控制，但在不太明确的情况下，它们可能会受到特定于数据库的行为的影响：
```
result = conn.execute(text("select x, y from some_table"))

for row in result:
    y = row.y

    # illustrate use with Python f-strings
    print(f"Row: {row.x} {y}")
```
映射访问 - 将行作为 Python 映射对象接收，这本质上是 Python 通用字典接口的只读版本对象，则 Result 可能会转换为 MappingResult 对象使用 Result.mappings（） 修饰符;这是一个 Result 对象，它产生类似字典的 RowMapping 对象，而不是 Row 对象：
```
result = conn.execute(text("select x, y from some_table"))

for dict_row in result.mappings():
    x = dict_row["x"]
    y = dict_row["y"]
```

发送参数¶

SQL 语句通常附带要通过 statement 本身，正如我们在前面的 INSERT 示例中看到的那样。这因此，Connection.execute（） 方法也接受参数，这些参数称为绑定参数。一个基本的例子可能是，如果我们想将 SELECT 语句限制为满足特定条件的行，例如 “y” 值大于传递给函数的某个值的行。

为了实现这一点，以便 SQL 语句可以保持固定，并且驱动程序可以正确地清理该值，我们在语句中添加了一个 WHERE 条件，该条件命名了一个名为“y”的新参数;文本（） construct 使用冒号格式 “：y” 接受这些 API。然后，“：y” 的实际值将作为第二个参数传递给 Connection.execute（） 以字典的形式：

>>> withengine.connect()asconn:...     result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})...     for row in result:...         print(f"x: {row.x}  y: {row.y}")BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ?
[...] (2,)
x: 2  y: 4x: 6  y: 8x: 9  y: 10ROLLBACK

在记录的 SQL 输出中，我们可以看到绑定参数 ：y 在发送到 SQLite 数据库时被转换为问号。这是因为 SQLite 数据库驱动程序使用一种称为“qmark 参数样式”的格式，这是 DBAPI 规范允许的六种不同格式之一。SQLAlchemy 将这些格式抽象为一种格式，即使用冒号的 “named” 格式。

发送多个参数¶

在 Committing Changes 的示例中，我们执行了一个 INSERT 语句，看起来我们能够一次将多行 INSERT 到数据库中。对于 “INSERT”、“UPDATE” 和 “DELETE” 等 DML 语句，我们可以将多个参数集发送到 Connection.execute（） 方法，通过传递字典列表而不是单个字典，这表示应多次调用单个 SQL 语句，每个参数集调用一次。这种执行方式称为 executemany：

>>> withengine.connect()asconn:...     conn.execute(...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),...         [{"x": 11, "y": 12}, {"x": 13, "y": 14}],...     )...     conn.commit()BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(11, 12), (13, 14)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

上述作等效于为每个参数集运行一次给定的 INSERT 语句，不同之处在于该作将进行优化，以便在许多行中获得更好的性能。

“execute” 和 “executemany” 之间的一个关键行为差异是后者不支持返回结果行，即使语句包含 RETURNING 子句。一个例外是当使用 Core 时 insert（） 结构，本教程稍后将在使用 INSERT 语句，这也表示使用 Insert.returning（） 方法。在这种情况下，SQLAlchemy 使用特殊逻辑来重组 INSERT 语句，以便可以针对许多行调用它，同时仍然支持 RETURNING。

另请参阅

executemany - 在术语表中，描述了 DBAPI 级别 cursor.executemany（）方法。

INSERT 语句的“插入多个值”行为 - 在“使用引擎和连接”中，描述了 Insert.returning（） 用于传递具有“executemany”执行的结果集的专用逻辑。

使用 ORM 会话执行¶

如前所述，上面的大多数模式和示例也适用于 ORM 的使用，因此在这里我们将介绍这种用法，以便随着教程的进行，我们将能够同时从 Core 和 ORM 的使用来说明每种模式。

使用 ORM 时，基本的事务/数据库交互对象称为 Session。在现代 SQLAlchemy 中，此对象的使用方式与 Connection 非常相似，实际上，当使用 Session 时，它引用了一个 Connection 内部，用于发出 SQL。

当 Session 与非 ORM 结构一起使用时，它会通过我们给它的 SQL 语句，并且通常与 Connection 直接执行的作没有太大区别，因此我们可以在这里用我们已经学过的简单文本 SQL作来说明它。

Session 有几种不同的创建模式，但在这里我们将说明最基本的模式，它与 Connection 的使用方式完全一致，即在上下文管理器中构建它：

>>> fromsqlalchemy.ormimportSession>>> stmt=text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")>>> withSession(engine)assession:...     result = session.execute(stmt, {"y": 6})...     for row in result:...         print(f"x: {row.x}  y: {row.y}")BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
[...] (6,)
x: 6  y: 8x: 9  y: 10x: 11  y: 12x: 13  y: 14ROLLBACK

上面的示例可以与上一节中的示例进行比较发送参数 - 我们直接替换对 将 engine.connect（）作为 conn 与 with Session(engine) as session ，然后使用 Session.execute（） 方法，就像我们使用 Connection.execute（） 方法一样。

此外，与 Connection 一样，Session 使用 Session.commit（） 方法具有 “submit as you go” 行为，如下所示，使用文本 UPDATE 语句来更改我们的一些数据：

>>> withSession(engine)assession:...     result = session.execute(...         text("UPDATE some_table SET y=:y WHERE x=:x"),...         [{"x": 9, "y": 11}, {"x": 13, "y": 15}],...     )...     session.commit()BEGIN (implicit)
UPDATE some_table SET y=? WHERE x=?
[...] [(11, 9), (15, 13)]
COMMIT

在上面，我们使用发送多个参数中引入的 bound-parameter 的 “executemany” 执行样式调用了一个 UPDATE 语句，以 “commit as you go” 提交结束区块。

提示

Session 实际上并没有保留 Connection 对象。它从 Engine 获取新的 Connection 下次需要对数据库执行 SQL 时。

Session 显然有更多的技巧，但是要知道它有一个 Session.execute（） 方法，该方法的使用方式与 Connection.execute（） 相同，这将使我们从后面的示例开始。

另请参阅

使用 Session 的基础知识 - 介绍 Session 对象的基本创建和使用模式。

SQLAlchemy 2.0 文档