sqlalchemy更新json 字段的部分字段

需求描述：
我们有个json字段，存储的数据形如下，现在需要修改love

{"dob":"21","subject":{"love":"programming"}}

工程结构
main.py

from sqlalchemy import Column, String, Integer,create_engine, JSON 
from sqlalchemy.orm import declarative_base,sessionmaker 
from sqlalchemy import update, func 
import os # Defining the path of database. 
BASE_DIR = os.path.dirname(os.path.realpath(__file__)) 
connection_string = "sqlite:///"+os.path.join(BASE_DIR, 'site.db') # Create a base class 
Base = declarative_base() # Create a new database engine instance 
engine = create_engine(connection_string, echo=True) # Creates a session for objects 
Session = sessionmaker(bind=engine) 
local_session = Session() # Defining the schema of the table 
class User(Base): __tablename__ = 'users'id = Column(Integer(), primary_key=True) username = Column(String(25), nullable=False, unique=True) info = Column(JSON, nullable=True) def __repr__(self): return f"<User username={self.username}>"Base.metadata.create_all(engine)

insertvalue.py

from main import User,local_session
# Store table data to the variable 
user1 = User(id=1, username="aditya", info={'dob': '21', 'subject': {'love':'math'}}) 
user2 = User(id=2, username="timmy", info={'dob': '22', 'subject': {'love':'science'}}) 
user3 = User(id=3, username="sushant", info={'dob': '23', 'subject': {'love':'programming'}}) # Add data to the session 
local_session.add(user1) 
local_session.add(user2) 
local_session.add(user3) # Perform the changes to the database. 
local_session.commit() # Retrieve data and print it 
result = local_session.query(User).filter_by(id=1).first() 
print(result)

数据
在这里插入图片描述

updatejson.py

from sqlalchemy import update, func 
from main import local_session,User# Declare variables 
value = 'programming'
id = 1# Update the JSON column data 
update_table = local_session.query(User).filter(User.id == id).update({ 'info': func.json_set( User.info, "$.subject.love", value ) }, synchronize_session='fetch') # Commit the changes in database 
local_session.commit()