本文详解如何使用 psycopg3 的 sql.identifier 和 sql.sql 组合，安全、灵活地为从 json 字段（如 feature -> 'key'）动态提取的列设置别名，避免 sql 注入，同时保持代码可维护性与执行效率。
本文详解如何使用 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”的安全底线，又保留了动态查询所需的灵活性与表达力。
