使用事务和 DBAPI¶

准备好 Engine 对象后，我们可以深入了解 Engine 的基本操作及其主要端点，即 Connection 和 Result。我们还将介绍 ORM 对这些对象的外观模式，称为 Session。

ORM 阅读者请注意

当使用 ORM 时，Engine 由 Session 管理。现代 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()，我们将在下一节中看到。

提示

“自动提交”模式可用于特殊情况。设置事务隔离级别，包括 DBAPI 自动提交一节讨论了这一点。

我们的 SELECT 的结果在名为 Result 的对象中返回，稍后将讨论它。目前我们补充一点，最好在 “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 将此样式称为逐个提交。

还有另一种提交数据的方式。我们可以预先声明我们的 “connect” 代码块为事务代码块。为此，我们使用 Engine.begin() 方法获取连接，而不是 Engine.connect() 方法。此方法将管理 Connection 的范围，并将所有内容都包含在事务中，如果代码块成功，则在末尾使用 COMMIT，如果引发异常，则使用 ROLLBACK。这种风格被称为一次开始

# "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

您应该主要首选 “一次开始” 样式，因为它更简洁，并且预先显示了整个代码块的意图。但是，在本教程中，我们将使用 “逐个提交” 样式，因为它更灵活，便于演示。

语句执行基础¶

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

本节中的大部分内容同样适用于现代 ORM 使用，当使用 Session.execute() 方法时，它的工作方式与 Connection.execute() 非常相似，包括 ORM 结果行使用 Core 使用的相同 Result 接口传递。

获取行¶

我们将首先更详细地说明 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 命名元组一样工作。下面我们说明访问行的各种方法。

元组赋值 - 这是最符合 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 用于常见 dict 对象的只读接口，可以使用 Result.mappings() 修饰符将 Result 转换为 MappingResult 对象；这是一个结果对象，它生成类似字典的 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 语句通常附带有要与语句本身一起传递的数据，正如我们在之前的 INSERT 示例中看到的那样。Connection.execute() 方法因此也接受参数，这些参数称为绑定参数。一个基本的示例可能是，如果我们想将 SELECT 语句仅限于满足特定条件的行，例如 “y” 值大于传递给函数的某个值的行。

为了实现这一点，以便 SQL 语句可以保持不变，并且驱动程序可以正确地清理该值，我们在语句中添加了一个 WHERE 条件，该条件命名了一个名为 “y” 的新参数；text() 构造接受使用冒号格式 “:y” 的参数。然后，将 “: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” 格式。

发送多个参数¶

在提交更改中的示例中，我们执行了一个 INSERT 语句，在其中似乎能够一次将多行插入到数据库中。对于 DML 语句（如 “INSERT”、“UPDATE” 和 “DELETE”），我们可以将多个参数集发送到 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() 方法指示 RETURNING。在这种情况下，SQLAlchemy 使用特殊逻辑来重组 INSERT 语句，以便可以为多行调用它，同时仍然支持 RETURNING。

另请参阅

executemany - 在术语表中，描述了 DBAPI 级别的 cursor.executemany() 方法，该方法用于大多数 “executemany” 执行。

INSERT 语句的“插入多个值”行为 - 在使用 Engine 和 Connection 中，描述了 Insert.returning() 使用的特殊逻辑，用于在使用 “executemany” 执行时传递结果集。

使用 ORM Session 执行¶

如前所述，以上大多数模式和示例也适用于 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

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

此外，与 Connection 一样，Session 也具有使用 Session.commit() 方法的 “逐个提交” 行为，如下面的示例所示，该示例使用文本 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

上面，我们使用在发送多个参数中介绍的绑定参数 “executemany” 执行样式调用了 UPDATE 语句，并以 “逐个提交” 提交结束了代码块。

提示

Session 实际上在结束事务后不会保留 Connection 对象。下次需要针对数据库执行 SQL 时，它会从 Engine 获取新的 Connection。

Session 显然还有更多技巧，但是理解它有一个 Session.execute() 方法，其使用方式与 Connection.execute() 相同，这将使我们开始学习后面示例。

另请参阅

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

SQLAlchemy 2.0 文档