sqlalchemy更新json 字段的部分字段_sqlalchemy json字段

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

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

1. 工程结构
   
2. 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) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

2. 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) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

数据

3. 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() 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

数据