处理事务和 DBAPI

优质

小牛编辑

136浏览

2023-12-01

SQLAlchemy 1.4 / 2.0 Tutorial

此页是 SQLAlchemy 1.4/2.0教程 .

上一页：建立连接-引擎 |下一步： |next|

处理事务和DBAPI

与 Engine 对象准备好了，我们现在可以开始深入研究一个 Engine 以及它的主要交互端点 Connection 和 Result . 我们还将介绍ORM facade 对于这些对象，称为 Session .

ORM读者须知

使用ORM时 Engine 由另一个名为 Session . 这个 Session 在现代SQLAlchemy中，强调事务和SQL执行模式，它与 Connection 下面将讨论，因此虽然本小节是以核心为中心的，但是这里的所有概念本质上也与ORM的使用相关，并且推荐给所有ORM学习者。使用的执行模式 Connection 将与 Session 在本节末尾。

由于我们还没有介绍SQLAlchemy的主要特性SQLAlchemy表达式语言，我们将在这个包中使用一个名为 text() 构造，它允许我们将SQL语句编写为 文本SQL . 请放心，在日常SQLAlchemy中使用文本SQL到目前为止是大多数任务的例外，而不是规则，尽管它始终保持完全可用。

获取连接

唯一的目的 Engine 从面向用户的角度来看，对象将提供到数据库的连接单元，称为 Connection . 直接使用核心时 Connection 对象是如何完成与数据库的所有交互。作为 Connection 表示针对数据库的开放资源，我们希望始终将此对象的使用范围限制在特定上下文中，最好的方法是使用Python上下文管理器表单，也称为 the with statement . 下面我们将使用文本SQL语句演示“helloworld”。文本SQL是使用一个名为 text() 稍后将详细讨论：

>>> from sqlalchemy import text

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

在上面的示例中，上下文管理器为数据库连接提供了一个框架，并在事务内部构造了操作。PythonDBAPI的默认行为包括事务始终在进行中；当连接的范围是 released ，则发出回滚以结束事务。交易是 不自动提交 ；当我们要提交数据时，通常需要调用 Connection.commit() 我们将在下一节中看到。

小技巧

“自动提交”模式适用于特殊情况。部门设置事务隔离级别，包括DBAPI Autocommit 讨论这个。

SELECT的结果也返回到一个名为 Result 这将在后面讨论，但是目前我们要补充的是，最好确保这个对象是在“connect”块中使用的，并且不会在连接范围之外传递。

提交更改

我们刚刚了解到DBAPI连接是非自动提交的。如果我们想提交一些数据呢？我们可以修改上面的示例来创建一个表并插入一些数据，然后使用 Connection.commit() 调用的方法里面我们在那里收购了 Connection 对象：

# "commit as you go"
>>> with engine.connect() as conn:
...     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语句，“createtable”语句 1 以及一个参数化的“INSERT”语句（上面的参数化语法将在下面的发送多个参数 ). 当我们希望我们已经完成的工作被提交到我们的块中时，我们调用 Connection.commit() 提交事务的方法。在我们在块内调用这个方法之后，我们可以继续运行更多的SQL语句，如果我们选择调用 Connection.commit() 对于以后的陈述也一样。SQLAlchemy将此样式称为 边做边做 .

还有另一种提交数据的方式，那就是我们可以预先将“connect”块声明为事务块。对于这种操作模式，我们使用 Engine.begin() 方法获取连接，而不是 Engine.connect() 方法。此方法将同时管理 Connection 并在事务结束时使用COMMIT将事务内部的所有内容括起来，假设块成功，或者在异常引发时回滚。这种风格可以称为 开始一次 ：

# "begin once"
>>> with engine.begin() as conn:
...     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（implicit）”implicit”这里的意思是SQLAlchemy 实际上没有发出任何命令 它只将这视为DBAPI隐式事务的开始。你可以注册 event hooks 例如拦截这个事件。

1: DDL 指指示数据库创建、修改或删除架构级结构（如表）的SQL子集。建议将诸如“createtable”之类的DDL放在以COMMIT结尾的事务块中，因为许多数据库使用事务性DDL，因此在提交事务之前不会发生模式更改。然而，正如我们稍后将看到的，我们通常让SQLAlchemy为我们运行DDL序列，作为更高级别操作的一部分，我们通常不需要担心提交。

语句执行基础

我们已经看到了一些使用名为 Connection.execute() ，与一个名为 text() ，并返回一个名为 Result . 在本节中，我们将更详细地说明这些组件的机制和相互作用。

当使用 Session.execute() 方法，其工作原理与 Connection.execute() ，包括使用相同的 Result 核心使用的接口。

正在获取行

我们将首先说明 Result 通过使用前面插入的行，在创建的表上运行文本SELECT语句，可以更紧密地访问对象：

>>> with engine.connect() as conn:
...     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: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
ROLLBACK

在上面，我们执行的“SELECT”字符串选择了表中的所有行。返回的对象被调用 Result 和表示结果行的iterable对象。

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

这个 Row 对象本身的作用类似于Python named tuples . 下面我们将演示访问行的各种方法。

元组赋值 -这是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} {row.y}")
```
映射访问 -以Python形式接收行映射对象，它本质上是Python的公共接口的只读版本 dict 对象 Result 可能是转化变成一个 MappingResult 对象使用 Result.mappings() 修饰符；这是一个结果对象，它生成类似dictionary的 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() 方法因此也接受参数，这些参数被称为 bound parameters . 一个简单的例子是，如果我们只想将SELECT语句限制为满足特定条件的行，例如“y”值大于传递给函数的某个值的行。

为了实现这一点，以便SQL语句可以保持不变，并且驱动程序可以正确地清理值，我们在语句中添加了一个WHERE条件，它命名了一个名为“y”的新参数 text() 构造使用冒号格式“:y”接受这些内容。“`:y”的实际值随后作为第二个参数传递给 Connection.execute() 以字典的形式：

>>> with engine.connect() as conn:
...     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: 4
x: 6  y: 8
x: 9  y: 10
ROLLBACK

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

始终使用绑定参数

正如本节开头所提到的，文本SQL并不是我们处理SQLAlchemy的常用方式。但是，当使用文本SQL时，Python文本值，即使是像整数或日期这样的非字符串，也应该 永远不要直接串成SQL字符串 ；参数应该总是被利用。这就是众所周知的当数据不可信时如何避免SQL注入攻击。然而，它也允许SQLAlchemy方言和/或DBAPI正确地处理后端的输入。在纯文本SQL用例之外，SQLAlchemy的核心表达式API可以确保Python文本值在适当的地方作为绑定参数传递。

发送多个参数

在示例中提交更改，我们执行了一个INSERT语句，在该语句中我们似乎能够同时向数据库中插入多行。对于那些 对数据进行操作，但不返回结果集 ，即 DML 像“INSERT”这样的语句不包括“RETURNING”这样的短语，我们可以发送 多参数 到 Connection.execute() 方法，方法是传递字典列表而不是单个字典，从而允许针对每个参数集分别调用单个SQL语句：

>>> with engine.connect() as conn:
...     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

在幕后， Connection 对象使用名为 cursor.executemany() . 此方法执行的等效操作是分别针对每个参数集调用给定的SQL语句。DBAPI可以通过各种方式优化此操作，方法是使用准备好的语句，或者在某些情况下将参数集连接到单个SQL语句中。一些SQLAlchemy方言也可能会在这种情况下使用替代API，例如 psycopg2 dialect for PostgreSQL 它为这个用例使用了更多的performantapi。

小技巧

您可能已经注意到这个部分没有被标记为ORM概念。多个使用参数是因为通常用于INSERT语句，当使用ORM时，将以不同的方式调用这些语句。还可以将多个参数与UPDATE和DELETE语句一起使用，以每行为基础发出不同的更新/删除操作，但是在使用ORM时，通常有一种不同的技术分别用于更新或删除多个单独的行。

将参数与语句绑定

前面两个例子说明了一系列参数随SQL语句一起传递。对于单参数语句的执行，SQLAlchemy对参数的使用实际上更多地是由捆绑带有语句本身的参数，这是SQL表达式语言的一个主要特性，它使得查询可以自然地组合，同时在所有情况下仍然使用参数化。这一概念将在接下来的章节中进行更详细的讨论；为了进行简要的预览 text() 构造本身作为SQL表达式语言的一部分，通过使用 TextClause.bindparams() 方法；这是一个 generative 方法，该方法返回SQL构造的新副本，并添加了其他状态，在本例中是要传递的参数值：

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     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: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14
ROLLBACK

有趣的是，尽管我们只通过了一个论点， stmt ，到 Connection.execute() 方法时，语句的执行说明了SQL字符串以及单独的参数元组。

使用ORM会话执行

如前所述，上面的大多数模式和示例也适用于ORM，所以在这里我们将介绍这种用法，以便在本教程继续进行时，我们将能够从核心和ORM一起使用的角度来说明每个模式。

使用ORM时，基本的事务/数据库交互对象称为 Session . 在现代SQLAlchemy中，这个对象的使用方式与 Connection ，事实上 Session 是指 Connection 它在内部使用它来发出SQL。

当 Session 与非ORM构造一起使用，它传递我们给它的SQL语句，并且通常与 Connection 所以我们可以用我们已经学过的简单的文本SQL操作来说明它。

这个 Session 有一些不同的创作模式，但在这里我们将说明最基本的一个，它准确地跟踪 Connection 用于在上下文管理器中构造它：

>>> from sqlalchemy.orm import Session

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)
>>> with Session(engine) as session:
...     result = session.execute(stmt)
...     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: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14
ROLLBACK

上述示例可与中的上一节中的示例进行比较将参数与语句绑定 -我们直接替换 with engine.connect() as conn 具有 with Session(engine) as session ，然后利用 Session.execute() 就像我们用 Connection.execute() 方法。

还有，像 Connection , the Session 使用 Session.commit() 方法，如下所示，使用文本更新语句更改部分数据：

>>> with Session(engine) as session:
...     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

在上面，我们使用在发送多个参数，以“边执行边提交”结束块。

小技巧

这个 Session 不能真正抓住 Connection 对象结束事务后。它得到了一个新的 Connection 从 Engine 下一步需要对数据库执行SQL时。

这个 Session 显然，它有比这更多的诀窍，无论如何理解它有一个 Session.execute() 方法，该方法的用法与 Connection.execute() 将让我们从后面的示例开始。

SQLAlchemy 1.4 / 2.0 Tutorial

下一个教程部分：使用数据库元数据