使用 INSERT 语句

当使用 Core 以及使用 ORM 进行批量操作时，SQL INSERT 语句是通过 insert() 函数直接生成的 - 此函数会生成一个新的 Insert 实例，它表示 SQL 中的 INSERT 语句，用于将新数据添加到表中。

ORM 阅读者 -

本节详细介绍了 Core 生成单个 SQL INSERT 语句以将新行添加到表中的方法。使用 ORM 时，我们通常使用另一种工具来完成此操作，该工具被称为 工作单元，它会自动生成许多 INSERT 语句。但是，了解 Core 如何处理数据创建和操作对于理解 ORM 为我们执行的操作非常有用。此外，ORM 支持使用名为 批量/多行 INSERT、upsert、UPDATE 和 DELETE 的功能直接使用 INSERT。

要直接跳到使用 ORM 使用正常的工作单元模式插入行的操作，请参阅 使用 ORM 工作单元模式插入行。

insert() SQL 表达式构造函数

一个简单的 Insert 示例，展示了目标表和 VALUES 子句

>>> from sqlalchemy import insert
>>> stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")

上面的 stmt 变量是 Insert 的一个实例。大多数 SQL 表达式可以原位字符串化，以便查看生成的内容的一般形式

>>> print(stmt)

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

字符串化形式是通过生成对象的 Compiled 形式创建的，其中包含语句的特定于数据库的字符串 SQL 表示形式；我们可以使用 ClauseElement.compile() 方法直接获取此对象

>>> compiled = stmt.compile()

我们的 Insert 构造函数是“参数化”构造函数的一个示例，之前在 发送参数 中进行了说明；要查看 name 和 fullname 绑定参数，这些参数也可以从 Compiled 构造函数中获得

>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

执行语句

调用语句，我们可以将一行插入 user_table。INSERT SQL 以及捆绑参数可以在 SQL 日志中看到

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT

在上面的简单形式中，INSERT 语句不会返回任何行，如果只插入一行，它通常会包含返回有关在插入该行期间生成的列级默认值的信息的能力，最常见的是整数主键值。在上面的情况下，SQLite 数据库中的第一行通常会返回 1 作为第一个整数主键值，我们可以使用 CursorResult.inserted_primary_key 访问器获取此值

>>> result.inserted_primary_key
(1,)

提示

CursorResult.inserted_primary_key 返回一个元组，因为主键可能包含多个列。这被称为 组合主键。 CursorResult.inserted_primary_key 旨在始终包含刚刚插入的记录的完整主键，而不仅仅是“cursor.lastrowid”之类的值，并且还旨在无论是否使用“自动递增”来填充，因此，为了表达完整的组合主键，它是一个元组。

在版本 1.4.8 中变更: 现在，由 CursorResult.inserted_primary_key 返回的元组现在是一个命名元组，通过将其作为 Row 对象返回来填充。

INSERT 通常会自动生成 "values" 子句

上面的示例使用了 Insert.values() 方法来显式创建 SQL INSERT 语句的 VALUES 子句。如果我们没有实际使用 Insert.values() 并且只打印出一个“空”语句，我们就会得到针对表中每一列的 INSERT

>>> print(insert(user_table))

INSERT INTO user_account (id, name, fullname) VALUES (:id, :name, :fullname)

如果我们获取一个没有调用 Insert.values() 的 Insert 构造函数并执行它而不是打印它，该语句将根据我们传递给 Connection.execute() 方法的参数编译成字符串，并且只包含与传递的参数相关的列。这实际上是 Insert 用于插入行而无需键入显式 VALUES 子句的常用方式。下面的示例展示了如何使用一次参数列表执行一个包含两列的 INSERT 语句

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(user_table),
...         [
...             {"name": "sandy", "fullname": "Sandy Cheeks"},
...             {"name": "patrick", "fullname": "Patrick Star"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')]
COMMIT

上面的执行展示了“executemany”形式，首次在发送多个参数中说明，但与使用text()构造时不同，我们无需拼写任何 SQL。通过将字典或字典列表传递给Connection.execute()方法，并结合Insert构造，Connection会确保传递的列名将自动在Insert构造的 VALUES 子句中表达。

深层炼金术

你好，欢迎来到第一期深层炼金术。左边的人物被称为炼金术士，你会注意到他们不是巫师，因为尖顶帽子没有向上翘。炼金术士会来解释一些通常更高级和/或棘手，并且通常不需要的事情，但出于某种原因，他们觉得你应该了解 SQLAlchemy 可以做到的这些事情。

在本期中，为了在address_table中拥有有趣的数据，下面是一个更高级的例子，说明如何显式使用Insert.values()方法，同时包含从参数生成的额外 VALUES。一个标量子查询被构建，它利用了下一节介绍的select()构造，而子查询中使用的参数是通过bindparam()构造建立的显式绑定参数名称设置的。

这只是一些稍微更深层的炼金术，这样我们就可以在不将主键标识符从user_table操作提取到应用程序中的情况下添加相关行。大多数炼金术士会简单地使用 ORM，它会为我们处理这类事情。

>>> from sqlalchemy import select, bindparam
>>> scalar_subq = (
...     select(user_table.c.id)
...     .where(user_table.c.name == bindparam("username"))
...     .scalar_subquery()
... )

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(address_table).values(user_id=scalar_subq),
...         [
...             {
...                 "username": "spongebob",
...                 "email_address": "[email protected]",
...             },
...             {"username": "sandy", "email_address": "[email protected]"},
...             {"username": "sandy", "email_address": "[email protected]"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
[...] [('spongebob', '[email protected]'), ('sandy', '[email protected]'),
('sandy', '[email protected]')]
COMMIT

有了这些，我们的表中就有了更多有趣的数据，我们将在接下来的章节中使用它们。

提示

如果我们用没有参数的Insert.values()指示，将会生成一个真正的“空” INSERT，它只插入表的“默认值”，而不包含任何显式值；并非所有数据库后端都支持此功能，但以下是 SQLite 生成的内容

>>> print(insert(user_table).values().compile(engine))

INSERT INTO user_account DEFAULT VALUES

INSERT…RETURNING

为了检索最后一个插入的主键值以及服务器默认值，支持的后端的 RETURNING 子句会自动使用。但是 RETURNING 子句也可以使用Insert.returning()方法显式指定；在这种情况下，当语句执行时返回的Result对象具有可以获取的行

>>> insert_stmt = insert(address_table).returning(
...     address_table.c.id, address_table.c.email_address
... )
>>> print(insert_stmt)

INSERT INTO address (id, user_id, email_address)
VALUES (:id, :user_id, :email_address)
RETURNING address.id, address.email_address

它也可以与Insert.from_select()结合使用，如下面的例子所示，它建立在INSERT…FROM SELECT中说明的例子之上

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account RETURNING address.id, address.email_address

提示

RETURNING 功能也受 UPDATE 和 DELETE 语句的支持，这些语句将在本教程的后面介绍。

对于 INSERT 语句，RETURNING 功能可用于单行语句以及一次插入多行的语句。对具有 RETURNING 的多行 INSERT 的支持是特定于方言的，但所有包含在 SQLAlchemy 中支持 RETURNING 的方言都支持它。有关此功能的背景信息，请参见“Insert Many Values” Behavior for INSERT statements部分。

另请参阅

ORM 也支持带或不带 RETURNING 的批量 INSERT。有关参考文档，请参见ORM 批量 INSERT 语句。

INSERT…FROM SELECT

Insert 的一个不太常用的功能，但为了完整起见，Insert 构造可以组合一个 INSERT，该 INSERT 直接从 SELECT 中获取行，使用Insert.from_select() 方法。此方法接受一个select() 构造，将在下一节讨论，以及在实际 INSERT 中要针对的目标列名的列表。在下面的例子中，将向address表中添加行，这些行来自user_account表中的行，为每个用户提供一个免费的aol.com 电子邮件地址

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account

当想要将数据从数据库的某个其他部分直接复制到一组新行中，而无需实际从客户端获取和重新发送数据时，就会使用此构造。

另请参阅

Insert - 在 SQL 表达式 API 文档中