使用事务和 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**。请放心，在日常 SQLAlchemy 使用中，文本 SQL 只是例外，而不是规则，但它始终可用。

获取连接¶

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” 模式适用于特殊情况。本节设置事务隔离级别，包括 DBAPI Autocommit 讨论了这一点。

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**

# "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() 的对象，并返回一个名为 Result 的对象。在本节中，我们将更详细地说明这些组件的机制和交互。

本节中的大部分内容同样适用于使用 Session.execute() 方法时的现代 ORM 使用，该方法的工作原理与 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 将其转换为 MappingResult 对象，使用 Result.mappings() 修饰符；这是一个结果对象，它产生字典式的 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 将这些格式抽象为一种格式，即使用冒号的“命名”格式。

发送多个参数¶

在提交更改中的示例中，我们执行了一个 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() 方法指示 RETURNING。在这种情况下，SQLAlchemy 会使用特殊逻辑来重新组织 INSERT 语句，以便可以针对多行调用它，同时仍然支持 RETURNING。

另请参阅

executemany - 在术语表中，描述了用于大多数“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

上面的示例可以与上一节中发送参数中的示例进行比较 - 我们直接用 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 对象的基本创建和使用模式。

SQLAlchemy 2.0 文档