Python中使用sqlalchemy更新postgresql中JSONB里的某些字段

数据库表 User，User 格式为 (id, name, data)

其中 data 是 JSONB 格式的字段 user = User() # user 是 User 的一个实例

举例：

data={'a':1, 'b': 3}

更新时：

data['a'] =4

然后加上 data 变动的标记: flag_modified(user, 'data')

db.session.add(user)

db.session.commit() # 提交


Python中使用sqlalchemy更新postgresql中JSONB里的某些字段

1 回复 

from sqlalchemy import create_engine, Column, Integer, JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import jsonb

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    data = Column(JSONB)

# 创建数据库连接
engine = create_engine('postgresql://user:password@localhost/dbname')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 方法1：直接赋值更新整个JSONB字段
user = session.query(User).filter(User.id == 1).first()
if user:
    user.data['name'] = 'new_name'
    user.data['age'] = 30
    session.commit()

# 方法2：使用jsonb_set函数（推荐，只更新指定路径）
from sqlalchemy import func

# 更新嵌套字段 data->'profile'->>'email'
session.query(User).filter(User.id == 1).update({
    User.data: func.jsonb_set(
        User.data, 
        '{profile,email}', 
        '"new_email@example.com"'
    )
})

# 更新数组中的元素 data->'tags'[0]
session.query(User).filter(User.id == 1).update({
    User.data: func.jsonb_set(
        User.data,
        '{tags,0}',
        '"new_tag"'
    )
})

# 方法3：使用jsonb_set创建不存在的路径
session.query(User).filter(User.id == 1).update({
    User.data: func.jsonb_set(
        User.data,
        '{new,nested,field}',
        '"value"',
        True  # create_missing=True
    )
})

# 方法4：合并更新（PostgreSQL 9.5+）
session.query(User).filter(User.id == 1).update({
    User.data: User.data.op('||')({'status': 'active', 'updated_at': '2024-01-01'})
})

session.commit()
session.close()

核心要点：

  1. 直接修改对象：先查询获取对象，修改Python字典后提交
  2. jsonb_set函数：最常用的方法，可以精确更新JSONB中的特定路径
  3. 合并操作符||：用于合并JSON对象，适合批量更新多个字段
  4. 路径表示：使用花括号表示路径，如{profile,email}对应data->'profile'->>'email'

注意jsonb_set的参数：

  • 第一个参数：要修改的JSONB列
  • 第二个参数：路径数组（文本数组）
  • 第三个参数：新值（必须是有效的JSON文本）
  • 第四个参数（可选）：create_missing，为True时会创建不存在的路径

根据你的具体需求选择合适的方法。如果只是更新几个字段，方法1最简单；如果需要精确控制更新路径，方法2最合适。

总结：优先使用jsonb_set进行精确更新。

回到顶部