如何使用psycopg2为PostgreSQL 14的JSON列新增键值对？

针对你的需求，这里提供两种实用的实现方式，都基于PostgreSQL 14的JSON操作能力，结合psycopg2的参数化查询保证安全：

方法1：用||操作符合并JSON对象 #

PostgreSQL的jsonb类型支持通过||操作符直接合并两个JSON对象，我们可以先把原json列转为jsonb，合并新键值对后再转回json类型存回。

示例代码：

import psycopg2

# 建立数据库连接
conn = psycopg2.connect(
    dbname="你的数据库名",
    user="你的用户名",
    password="你的密码",
    host="你的主机地址",
    port="你的端口"
)
cur = conn.cursor()

# 更新语句（假设表名为your_table，JSON列名为json_col，更新条件为id=1）
update_sql = """
UPDATE your_table
SET json_col = (json_col::jsonb || %s::jsonb)::json
WHERE id = %s;
"""

# 要添加的键值对、目标记录ID
new_kv = '{"Value B": 25}'
target_id = 1

# 执行更新并提交
cur.execute(update_sql, (new_kv, target_id))
conn.commit()

# 关闭连接
cur.close()
conn.close()

方法2：用jsonb_set函数精确控制 #

如果需要避免覆盖已存在的键，可以使用jsonb_set函数，它支持指定是否仅添加不存在的键：

update_sql = """
UPDATE your_table
SET json_col = jsonb_set(
    json_col::jsonb,
    '{Value B}',
    %s,
    true  # true=键不存在则添加，存在则覆盖；false=仅当键不存在时才添加
)::json
WHERE id = %s;
"""

# 直接传入值25（无需包装成JSON字符串）
cur.execute(update_sql, (25, target_id))
conn.commit()

额外提示 #

• 如果你有权限修改表结构，建议把json类型列改成jsonb类型，这样操作更高效，还能省去每次类型转换的步骤。
• 无论哪种方法，都必须使用参数化查询，绝对不要直接拼接SQL字符串，防止SQL注入风险。

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