psycopg3 动态JSON字段提取的列别名安全设置

这篇文章会详细讲解，如何利用 psycopg3 的 `sql.identifier` 和 `sql.sql` 组合，安全、灵活地为从 JSON 字段（比如 `feature -> 'key'`）动态提取的列设置别名，彻底避免 SQL 注入，同时让代码既好维护又高效。

在 PostgreSQL 里处理嵌套 JSON 数据，免不了要经常用 `->` 或 `->>` 操作符动态提取多个字段，再给结果列起个有意义的别名，比如 `feature -> 'temperature' AS "temperature"`。如果直接拼接字符串来生成 SQL，那 SQL 注入风险就悬在头顶了；可要是完全依赖参数化查询（`%(param)s`），又没法对标识符（列别名、JSON 键名）做安全插值——因为 `%(feature)s` 只能绑定字面量值，根本用不了在标识符这种上下文里。

psycopg3 的 `sql` 模块（`psycopg.sql`）正好解决了这个问题，它支持类型化构建 SQL：`sql.Identifier` 用来安全转义数据库对象名（表名、列名、别名），`sql.Literal` 用来安全插入字面量值，`sql.Composed` 则可以灵活组合它们。这里的关键在于：JSON 键名属于字面量，应该用 `sql.Literal`；而列别名属于标识符，必须用 `sql.Identifier`。搞反了，代码就会出问题。

下面是一个健壮、可复用的实现方案，直接拿来就能用：

from psycopg import sql

def json_field_with_alias(json_column: str, json_key: str, alias: str) -> sql.Composed:
    """
    构建安全的 JSON 字段提取表达式，带标准别名。
    示例：feature -> 'temperature' AS "temperature"
    """
    return sql.Composed([
        sql.SQL(f"{json_column} -> ").join([
            sql.Literal(json_key),
            sql.SQL(" AS "),
            sql.Identifier(alias)
        ])
    ])

# 动态构建 SELECT 子句中的多个 JSON 字段
features = ["temperature", "humidity", "pressure"]
select_fields = [
    json_field_with_alias("feature", key, key) for key in features
]

# 主查询模板（仅含固定结构，动态部分由 Composed 构建）
QUERY = sql.SQL("""
    SELECT
        current_database() AS project,
        timestamp,
        location,
        {json_fields}
    FROM {table}
    WHERE lower(location) = %s
      AND timestamp BETWEEN %s AND %s
""").format(
    json_fields=sql.SQL(", ").join(select_fields),
    table=sql.Identifier("table_1")
)

# 执行时仅传入值参数（location, start_dt, end_dt），无需重建 SQL
with connection.cursor() as cur:
    cur.execute(QUERY, (location, start_dt, end_dt))
    results = cur.fetchall()

优势说明：

• 安全性：所有用户可控的输入（JSON 键名、别名、表名）都通过 `sql.Literal` 或 `sql.Identifier` 严格转义，注入？不存在的。
• 性能：SQL 结构在运行前就已经静态编译好了，只有值参数在执行时才绑定，避免了重复解析的开销。
• 可读性：逻辑分离得清清楚楚——模板定义结构，函数封装模式，列表推导生成字段，一眼就能看懂。
• 兼容性：完全遵循 psycopg3 官方的推荐实践，适配 `psycopg.Connection` 和 `psycopg.Cursor`，放心用。

注意事项：

• `sql.Identifier` 只接受字符串或者元组（用于带 schema 的名称，比如 `("schema", "table")`），千万别往里传变量表达式。
• `sql.Literal` 适用于 JSON 键、时间字符串、位置值这些字面量内容，但绝对不能用于列名或表名，那会出大问题。
• 如果你需要支持 `->>`（文本提取）或者嵌套路径（比如 `'sensor.temp'`），只需要调整 `json_field_with_alias` 函数里的 SQL 片段就行，其他部分不用动。
• 生产环境里，强烈建议使用连接池（比如 `psycopg.ConnectionPool`）来管理连接，别频繁创建和销毁连接，太浪费资源了。

总的来说，相比手动字符串格式化或者过度依赖参数化占位符，用 `psycopg.sql` 模块分层构建，是处理动态 SQL 加安全别名的最优方案。它既守住了“绝不拼接 SQL”的安全底线，又保留了动态查询所需要的灵活性和表达力。这才是真正靠谱的做法。