Release: 1.4.25 | Release Date: September 22, 2021

SQLAlchemy 1.4 Documentation

SQLAlchemy 1.4 / 2.0 Tutorial

此页是 SQLAlchemy 1.4/2.0教程 .

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

处理事务和DBAPI

Engine 对象准备好了,我们现在可以开始深入研究一个 Engine 以及它的主要交互端点 ConnectionResult . 我们还将介绍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 对象结束事务后。它得到了一个新的 ConnectionEngine 下一步需要对数据库执行SQL时。

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

SQLAlchemy 1.4 / 2.0 Tutorial

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

Previous: 建立连接-引擎 Next: 使用数据库元数据