SQLAlchemy 统一教程 SQLAlchemy Unified Tutorial P1
SQLAlchemy 呈现为两个不同的 API，一个构建在另一个之上。这些 API 称为 Core 和 ORM。
SQLAlchemy Core 是 SQLAlchemy 作为“数据库工具包”的基础架构。该库提供了用于管理数据库连接、与数据库查询和结果交互以及 SQL 语句的编程构造的工具。
主要仅限核心的部分不会引用 ORM。这些部分中使用的 SQLAlchemy 构造将从 sqlalchemy 命名空间导入。作为主题分类的附加指标，它们还将在右侧包含深蓝色边框。使用 ORM 时，这些概念仍然有效，但在用户代码中不太明确。 ORM 用户应该阅读这些部分，但不要期望直接将这些 API 用于以 ORM 为中心的代码。
SQLAlchemy ORM 构建在 Core 之上，提供可选的对象关系映射功能。 ORM 提供了一个额外的配置层，允许将用户定义的 Python 类映射到数据库表和其他构造，以及称为会话的对象持久性机制。然后，它扩展了核心级 SQL 表达式语言，以允许根据用户定义的对象来组合和调用 SQL 查询。
主要仅涉及 ORM 的部分的标题应包含短语“ORM”，以便清楚地表明这是一个 ORM 相关主题。这些部分中使用的 SQLAlchemy 构造将从 sqlalchemy.orm 命名空间导入。最后，作为主题分类的附加指标，它们还将在左侧包含浅蓝色边框。仅核心用户可以跳过这些。
本教程中的大多数部分都讨论了 ORM 中显式使用的核心概念。 SQLAlchemy 2.0 尤其具有在 ORM 中使用核心 API 的更高级别的集成。
对于每个部分，都会有介绍性文本讨论 ORM 用户应该期望使用这些编程模式的程度。这些部分中的 SQLAlchemy 构造将从 sqlalchemy 命名空间导入，同时可能使用 sqlalchemy.orm 构造。作为主题分类的附加指标，这些部分还将包括左侧较细的浅色边框和右侧较粗的深色边框。核心用户和 ORM 用户应该同样熟悉这些部分中的概念。
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
本教程是使用名为 doctest 的系统编写的。所有使用 >>> 编写的代码摘录实际上都作为 SQLAlchemy 测试套件的一部分运行，并且邀请读者使用自己的 Python 解释器实时处理给出的代码示例。
如果运行示例，建议读者执行快速检查以验证我们使用的是 SQLAlchemy 2.0 版本：
import sqlalchemy
sqlalchemy.__version__
'2.0.25'
建立连接 - 引擎
任何 SQLAlchemy 应用程序的开始都是一个名为 Engine 的对象。该对象充当特定数据库连接的中央源，为这些数据库连接提供工厂以及称为连接池的保存空间。该引擎通常是为特定数据库服务器仅创建一次的全局对象，并使用 URL 字符串进行配置，该字符串将描述它应如何连接到数据库主机或后端。
在本教程中，我们将使用仅内存中的 SQLite 数据库。这是一种简单的测试方法，无需设置实际的预先存在的数据库。 Engine 是使用 create_engine() 函数创建的：
from sqlalchemy import create_engine
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)
create_engine 的主要参数是一个字符串 URL，上面作为字符串 "sqlite+pysqlite:///:memory:" 传递。该字符串向 Engine 指示三个重要事实：
我们正在与什么样的数据库进行通信？这是上面的 sqlite 部分，它在 SQLAlchemy 中链接到称为方言的对象。
我们使用什么 DBAPI？ Python DBAPI 是 SQLAlchemy 用于与特定数据库交互的第三方驱动程序。在本例中，我们使用名称 pysqlite ，在现代 Python 中使用的是 SQLite 的 sqlite3 标准库接口。如果省略，SQLAlchemy 将为所选的特定数据库使用默认 DBAPI。
我们如何定位数据库呢？在本例中，我们的 URL 包含短语 /:memory: ，它向 sqlite3 模块指示我们将使用仅内存数据库。这种数据库非常适合实验，因为它不需要任何服务器，也不需要创建新文件。
我们还指定了一个参数 create_engine.echo ，它将指示 Engine 将其发出的所有 SQL 记录到将写入标准输出的 Python 记录器。该标志是更正式地设置 Python 日志记录的快捷方式，对于脚本中的实验非常有用。许多 SQL 示例将在 [SQL] 链接下方包含此 SQL 日志记录输出，单击该链接将显示完整的 SQL 交互。
使用事务和 DBAPI ¶
随着 Engine 对象准备就绪，我们现在可以继续深入了解 Engine 及其主要交互端点 Connection 和 Result 。我们还将额外介绍这些对象的 ORM 外观，称为 Session 。
使用 ORM 时， Engine 由另一个名为 Session 的对象管理。现代 SQLAlchemy 中的 Session 强调事务和 SQL 执行模式，该模式与下面讨论的 Connection 基本相同，因此虽然本小节是以核心为中心的，但这里的所有概念本质上也与 ORM 使用相关，建议所有 ORM 学习者使用。 Connection 使用的执行模式将在本节末尾与 Session 的执行模式进行对比。
由于我们尚未介绍 SQLAlchemy 表达式语言（它是 SQLAlchemy 的主要功能），因此我们将使用此包中的一个简单构造，称为 text() 构造，它允许我们将 SQL 语句编写为文本 SQL 。请放心，对于大多数任务来说，日常 SQLAlchemy 使用中的文本 SQL 是例外，而不是规则，尽管它始终保持完全可用。
Getting a Connection 获取连接
从面向用户的角度来看， Engine 对象的唯一目的是提供一个称为 Connection 的数据库连接单元。当直接使用 Core 时， Connection 对象是完成与数据库的所有交互的方式。由于 Connection 代表针对数据库的开放资源，因此我们希望始终将该对象的使用范围限制在特定上下文中，而最好的方法是使用 Python 上下文管理器形式，也称为 with 语句。下面我们使用文本 SQL 语句来说明“Hello World”。文本 SQL 是使用名为 text() 的构造发出的，稍后将更详细地讨论该构造：
在上面的示例中，上下文管理器提供了数据库连接，并且还构建了事务内部的操作。 Python DBAPI 的默认行为包括事务始终在进行中；当连接范围被释放时，会发出 ROLLBACK 来结束事务。事务不会自动提交；当我们想要提交数据时，我们通常需要调用 Connection.commit() ，我们将在下一节中看到。
我们的 SELECT 的结果也在一个名为 Result 的对象中返回，稍后将讨论该对象，但是目前我们将添加最好确保该对象在“connect”块中使用，并且不会在我们的连接范围之外传递。
from sqlalchemy import text
with engine.connect() as conn:
result = conn.execute(text("select 'hello world'"))
print(result.all())
2024-01-16 09:32:43,391 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:32:43,392 INFO sqlalchemy.engine.Engine select 'hello world'
2024-01-16 09:32:43,393 INFO sqlalchemy.engine.Engine [generated in 0.00195s] ()
[('hello world',)]
2024-01-16 09:32:43,394 INFO sqlalchemy.engine.Engine ROLLBACK
Committing Changes 提交更改 ¶
我们刚刚了解到 DBAPI 连接是非自动提交的。如果我们想提交一些数据怎么办？我们可以更改上面的示例来创建一个表并插入一些数据，然后使用 Connection.commit() 方法提交事务，该方法在我们获取 Connection 对象的块内调用：
面，我们发出了两个通常是事务性的 SQL 语句，一个“CREATE TABLE”语句 [1] 和一个参数化的“INSERT”语句（上面的参数化语法将在下面的发送多个参数中讨论）。由于我们希望在块内提交已完成的工作，因此我们调用提交事务的 Connection.commit() 方法。在块内调用此方法后，我们可以继续运行更多 SQL 语句，如果我们选择，我们可以为后续语句再次调用 Connection.commit() 。 SQLAlchemy 将这种风格称为“随时提交”。
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()
# 上面，我们发出了两个通常是事务性的 SQL 语句，一个“CREATE TABLE”语句 [1] 和一个参数化的“INSERT”语句（上面的参数化语法将在下面的发送多个参数中讨论）。
# 由于我们希望在块内提交已完成的工作，因此我们调用提交事务的 Connection.commit() 方法。在块内调用此方法后，我们可以继续运行更多 SQL 语句，
# 如果我们选择，我们可以为后续语句再次调用 Connection.commit() 。 SQLAlchemy 将这种风格称为“随时提交”。
2024-01-16 09:33:51,580 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:33:51,581 INFO sqlalchemy.engine.Engine CREATE TABLE some_table (x int, y int)
2024-01-16 09:33:51,582 INFO sqlalchemy.engine.Engine [generated in 0.00152s] ()
2024-01-16 09:33:51,586 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2024-01-16 09:33:51,587 INFO sqlalchemy.engine.Engine [generated in 0.00092s] [(1, 1), (2, 4)]
2024-01-16 09:33:51,588 INFO sqlalchemy.engine.Engine COMMIT
还有另一种提交数据的方式，即我们可以预先将“连接”块声明为事务块。对于这种操作模式，我们使用 Engine.begin() 方法来获取连接，而不是 Engine.connect() 方法。此方法将管理 Connection 的范围，并且还将事务内的所有内容包含在事务内，假设成功阻止，则在末尾使用 COMMIT，或者在引发异常时使用 ROLLBACK。这种风格称为开始一次：
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}],
)
2024-01-16 09:48:09,926 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:48:09,927 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2024-01-16 09:48:09,928 INFO sqlalchemy.engine.Engine [cached since 858.3s ago] [(6, 8), (9, 10)]
2024-01-16 09:48:09,929 INFO sqlalchemy.engine.Engine COMMIT
获取行 ¶
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}")
2024-01-16 09:50:05,141 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:50:05,142 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2024-01-16 09:50:05,143 INFO sqlalchemy.engine.Engine [generated in 0.00215s] ()
x: 1 y: 1
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
2024-01-16 09:50:05,144 INFO sqlalchemy.engine.Engine ROLLBACK
返回的对象称为 Result 并表示结果行的可迭代对象。Result 有许多用于获取和转换行的方法，例如前面介绍的 Result.all() 方法，它返回所有 Row 对象的列表。它还实现了Python迭代器接口，以便我们可以直接迭代 Row 对象的集合。Row 对象本身的作用类似于 Python 命名元组。下面我们举例说明访问行的多种方法。
元组分配 - 这是最符合 Python 习惯的风格，即在收到变量时按位置将变量分配给每一行：
with engine.connect() as conn:
result = conn.execute(text("SELECT x, y FROM some_table"))
for x,y in result:
print(f"x: {x} y: {y}")
2024-01-16 09:54:29,680 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:54:29,681 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2024-01-16 09:54:29,682 INFO sqlalchemy.engine.Engine [cached since 264.5s ago] ()
x: 1 y: 1
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
2024-01-16 09:54:29,683 INFO sqlalchemy.engine.Engine ROLLBACK
整数索引 - 元组是 Python 序列，因此也可以使用常规整数访问：
with engine.connect() as conn:
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
x = row[0]
y = row[1]
print(x, y)
2024-01-16 09:55:25,091 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:55:25,091 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table
2024-01-16 09:55:25,092 INFO sqlalchemy.engine.Engine [cached since 320s ago] ()
1 1
2 4
6 8
9 10
2024-01-16 09:55:25,093 INFO sqlalchemy.engine.Engine ROLLBACK
属性名称 - 由于这些是 Python 命名的元组，因此元组具有与每列名称匹配的动态属性名称。这些名称通常是 SQL 语句分配给每行中的列的名称。 虽然它们通常是相当可预测的，并且也可以通过标签进行控制，但在定义较少的情况下，它们可能会受到特定于数据库的行为的影响：
with engine.connect() as conn:
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}")
2024-01-16 09:56:22,415 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:56:22,416 INFO sqlalchemy.engine.Engine select x, y from some_table
2024-01-16 09:56:22,417 INFO sqlalchemy.engine.Engine [cached since 19.69s ago] ()
Row: 1 1
Row: 2 4
Row: 6 8
Row: 9 10
2024-01-16 09:56:22,418 INFO sqlalchemy.engine.Engine ROLLBACK
映射访问 - 要接收行作为 Python 映射对象（本质上是 Python 通用 dict 对象接口的只读版本）， Result 可以转换为 MappingResult 修饰符的 对象；这是一个结果对象，它生成类似字典的 RowMapping 对象而不是 Row 对象：
with engine.connect() as conn:
result = conn.execute(text("select x, y from some_table"))
for dict_row in result.mappings():
x = dict_row["x"]
y = dict_row["y"]
print(x, y)
2024-01-16 09:56:58,712 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:56:58,713 INFO sqlalchemy.engine.Engine select x, y from some_table
2024-01-16 09:56:58,714 INFO sqlalchemy.engine.Engine [cached since 55.99s ago] ()
1 1
2 4
6 8
9 10
2024-01-16 09:56:58,715 INFO sqlalchemy.engine.Engine ROLLBACK
with engine.connect() as conn:
result = conn.execute(text("select * from some_table"))
print(result.all())
2024-01-16 09:48:15,608 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:48:15,609 INFO sqlalchemy.engine.Engine select * from some_table
2024-01-16 09:48:15,610 INFO sqlalchemy.engine.Engine [cached since 836.3s ago] ()
[(1, 1), (2, 4), (6, 8), (9, 10)]
2024-01-16 09:48:15,611 INFO sqlalchemy.engine.Engine ROLLBACK
发送参数
为了实现这一点，以便 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}")
2024-01-16 09:58:10,071 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 09:58:10,071 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ?
2024-01-16 09:58:10,072 INFO sqlalchemy.engine.Engine [generated in 0.00172s] (2,)
x: 2 y: 4
x: 6 y: 8
x: 9 y: 10
2024-01-16 09:58:10,073 INFO sqlalchemy.engine.Engine ROLLBACK
在记录的 SQL 输出中，我们可以看到绑定参数 :y 在发送到 SQLite 数据库时被转换为问号SELECT x, y FROM some_table WHERE y > ?。这是因为 SQLite 数据库驱动程序使用一种称为“qmark 参数样式”的格式，这是 DBAPI 规范允许的六种不同格式之一。 SQLAlchemy 将这些格式抽象为一种，即使用冒号的“命名”格式。
发送多个参数
在提交更改的示例中，我们执行了一条 INSERT 语句，看起来我们能够一次将多行插入数据库。对于“INSERT”、“UPDATE”和“DELETE”等 DML 语句，我们可以通过传递字典列表而不是单个字典来向 Connection.execute() 方法发送多个参数集，这表明单个字典SQL 语句应调用多次，每个参数集调用一次。这种执行方式称为executemany：
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()
2024-01-16 10:01:44,707 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 10:01:44,708 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2024-01-16 10:01:44,709 INFO sqlalchemy.engine.Engine [cached since 1673s ago] [(11, 12), (13, 14)]
2024-01-16 10:01:44,710 INFO sqlalchemy.engine.Engine COMMIT
使用 ORM 会话执行
使用 ORM 时的基本事务/数据库交互对象称为 Session 。在现代 SQLAlchemy 中，该对象的使用方式与 Connection 非常相似，事实上，在使用 Session 时，它指的是 Connection 在内部它用来发出 SQL。
Session 有几种不同的创建模式，但在这里我们将说明最基本的模式，它精确跟踪 Connection 的使用方式，即在上下文管理器中构造它：
from sqlalchemy.orm import Session
stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
with Session(engine) as session:
result = session.execute(stmt, {"y": 6})
for row in result:
print(f"x: {row.x} y: {row.y}")
2024-01-16 10:04:54,960 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 10:04:54,961 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
2024-01-16 10:04:54,962 INFO sqlalchemy.engine.Engine [generated in 0.00089s] (6,)
x: 6 y: 8
x: 9 y: 10
x: 11 y: 12
x: 13 y: 14
2024-01-16 10:04:54,966 INFO sqlalchemy.engine.Engine ROLLBACK
上面的例子可以与上一节发送参数中的例子进行比较 - 我们直接用 with Session(engine) as session 替换对 with engine.connect() as conn 的调用，然后使用 Session.execute() 方法做 Connection.execute() 所做的那样。
此外，与 Connection 一样， Session 具有使用 Session.commit() 方法的“随用随付”行为，如下图所示，使用文本 UPDATE 语句来更改某些内容我们的数据：
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()
2024-01-16 10:07:06,855 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-01-16 10:07:06,857 INFO sqlalchemy.engine.Engine UPDATE some_table SET y=? WHERE x=?
2024-01-16 10:07:06,858 INFO sqlalchemy.engine.Engine [generated in 0.00078s] [(11, 9), (15, 13)]
2024-01-16 10:07:06,859 INFO sqlalchemy.engine.Engine COMMIT
jupyter nbconvert --to markdown "SQLAlchemy 2.0 统一教程 P1_HAND.ipynb"
UPDATE 2024-01-17 BY YULIKE
