如何使用pypika生成包含OUTPUT子句的SQL Server插入查询？

pypika本身未内置SQL Server OUTPUT子句的支持，但不需要完全手写拼接SQL，有两种可落地的实现路径，都可以最大化复用pypika的字段转义、表名渲染、参数处理能力，避免纯硬拼字符串带来的兼容性问题。

方案一：扩展pypika的InsertQuery类（生产环境推荐） #

这个方案的核心是复用pypika的原有SQL渲染流程，仅在原有语法结构中插入OUTPUT子句的渲染逻辑，适配性最强。

• 实现逻辑步骤：
  • 继承pypika原生的InsertQuery类，新增存储OUTPUT返回字段的属性
  • 新增链式调用方法.output()，支持传入需要返回的字段对象
  • 重写SQL渲染方法，在原有INSERT语句的字段列表闭合后、VALUES关键字之前，插入OUTPUT子句的渲染内容
• 参考可直接运行的实现代码：

  from pypika import InsertQuery, Table
  
  # 定义SQL Server的INSERTED伪表对象，复用pypika的字段转义规则
  INSERTED = Table("INSERTED")
  
  class MSSQLInsertQuery(InsertQuery):
      def __init__(self, *args, **kwargs):
          super().__init__(*args, **kwargs)
          self._output_fields = []
  
      def output(self, *fields):
          """链式添加OUTPUT子句需要返回的字段"""
          self._output_fields.extend(fields)
          return self
  
      def get_sql(self, *args, **kwargs):
          # 先调用父类方法生成基础的INSERT+VALUES语句
          base_sql = super().get_sql(*args, **kwargs)
          if not self._output_fields:
              return base_sql
          # 渲染OUTPUT子句的字段列表
          output_clause = ", ".join(
              field.get_sql(**kwargs) for field in self._output_fields
          )
          # 定位VALUES关键字的位置，在其前方插入OUTPUT子句
          values_split_pos = base_sql.find(" VALUES ")
          return (
              base_sql[:values_split_pos]
              + f" OUTPUT {output_clause} "
              + base_sql[values_split_pos + 1:]
          )
  

• 使用方式：

  User = Table("User")
  query = MSSQLInsertQuery(User) \
      .columns(User.Name, User.Email) \
      .insert("test_name", "test@example.com") \
      .output(INSERTED.Id, INSERTED.Name, INSERTED.Email)
  
  # 可通过quote_char参数设置SQL Server的方括号标识符包裹规则
  print(query.get_sql(quote_char="[]"))
  

• 生成的最终语句完全匹配目标语法：

  INSERT INTO [User] ([Name], [Email]) OUTPUT [INSERTED].[Id], [INSERTED].[Name], [INSERTED].[Email] VALUES ('test_name', 'test@example.com')
  

方案二：基础SQL字符串插值（轻量场景适用） #

如果不需要复杂的嵌套查询、表别名处理，可直接在pypika生成的基础INSERT语句上做字符串插入，不需要维护自定义类：

• 实现逻辑：
  1. 用pypika生成不带OUTPUT子句的基础INSERT语句
  2. 定位语句中VALUES关键字的位置，将预先写好的OUTPUT子句插入到该位置之前
• 参考代码：

  from pypika import Query, Table
  
  User = Table("User")
  base_sql = Query.into(User) \
      .columns(User.Name, User.Email) \
      .insert("name", "email") \
      .get_sql(quote_char="[]")
  
  output_clause = "OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.Email"
  final_sql = base_sql.replace(" VALUES ", f" {output_clause} VALUES ")
  

• 注意事项：
  • 必须以VALUES关键字为锚点插入内容，不能直接替换字符串的固定位置，避免字段值中存在"VALUES"文本时出现匹配错误
  • 该方法仅适合简单查询场景，复杂多表关联、嵌套子查询的INSERT语句不适用

通用注意事项 #

• 无论哪种方案，涉及INSERTED伪表的字段，优先用pypika的Table对象封装，不要直接手写拼接字符串，这样后续调整标识符引用规则（比如换用方括号、双引号包裹）时，字段部分可以自动适配
• 基于扩展类的实现完全兼容pypika的参数化查询能力，OUTPUT子句仅包含元数据引用，不会引入用户输入内容，无SQL注入风险
• 开发前可先检查项目引入的pypika第三方方言扩展，部分社区实现的MSSQL方言已经内置了OUTPUT子句支持，不需要从零写扩展

内容的提问来源于stack exchange，提问作者chopd