使用Python脚本更新数据库User表的JSONType列

原代码的问题 #

你当前的代码update({User.json_data: name + surname})会直接把json_data列的整个JSON对象替换成字符串"emptyempty2"，完全丢失原有JSON里的view_data、email等字段，不符合只更新name和surname的需求。

解决方案 #

方案一：用数据库原生JSON函数批量更新（推荐，高效） #

根据你使用的数据库类型，选择对应的JSON更新函数：

1. PostgreSQL（JSONB类型）

PostgreSQL支持jsonb_set函数，可精准修改指定字段并保留原有数据：

from sqlalchemy import func

def update_json_column_in_table_in_db_by_list_of_uid():
    uid_list = ['25a00f0e-58a5-4356-8b91-b18ea2eed71d', '68ccc759-97ae-48a2-bc42-5c2f1fa7a0ba', '9e2ee469-f777-4622-bca1-68d924caed0f']
    name = 'empty'
    surname = 'empty2'

    # 嵌套调用jsonb_set分别更新name和surname字段
    User.query.filter(User.uid.in_(uid_list)).update(
        {
            User.json_data: func.jsonb_set(
                func.jsonb_set(
                    User.json_data,
                    '{name}',
                    func.to_json(name)
                ),
                '{surname}',
                func.to_json(surname)
            )
        },
        synchronize_session=False  # 批量更新时关闭会话同步提升性能
    )
    db.session.commit()

2. MySQL（JSON类型）

MySQL使用JSON_SET函数，语法更简洁：

from sqlalchemy import func

def update_json_column_in_table_in_db_by_list_of_uid():
    uid_list = ['25a00f0e-58a5-4356-8b91-b18ea2eed71d', '68ccc759-97ae-48a2-bc42-5c2f1fa7a0ba', '9e2ee469-f777-4622-bca1-68d924caed0f']
    name = 'empty'
    surname = 'empty2'

    User.query.filter(User.uid.in_(uid_list)).update(
        {
            User.json_data: func.json_set(
                User.json_data,
                '$.name',
                name,
                '$.surname',
                surname
            )
        },
        synchronize_session=False
    )
    db.session.commit()

方案二：查询记录后在Python层面修改（适合少量数据） #

如果数据量不大，可先查询出目标记录，在Python中修改字典字段后保存：

def update_json_column_in_table_in_db_by_list_of_uid():
    uid_list = ['25a00f0e-58a5-4356-8b91-b18ea2eed71d', '68ccc759-97ae-48a2-bc42-5c2f1fa7a0ba', '9e2ee469-f777-4622-bca1-68d924caed0f']
    name = 'empty'
    surname = 'empty2'

    users = User.query.filter(User.uid.in_(uid_list)).all()
    for user in users:
        # 将JSON列转为Python字典
        json_data = user.json_data
        # 修改指定字段
        json_data['name'] = name
        json_data['surname'] = surname
        # 赋值回对象
        user.json_data = json_data
    db.session.commit()

注意事项 #

• 确保db是你的SQLAlchemy会话对象，比如from your_app import db
• 批量更新时添加synchronize_session=False可避免SQLAlchemy自动刷新会话，提升性能
• 若使用SQLite，需确保版本在3.38.0以上，该版本开始支持JSON_SET函数

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