Updating Jsontype column in table database

Jack Grealish 0
Viewed 33

Trying to update Json column in table User with python script.

So, I have a list of UID(stored in uid_list variable), by this list of uid I would like to update following uids in database. json_data = Column(JSONType) - column, that need to be updated, name and surname. The data that stores in this column: {"view_data": {"active": false, "text": "", "link": "http://google.com/"}, "name": "John", "surname": "Black", "email": "john@gmail.com"}

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: name + surname})
sqlalchemy python
1 Answers

You need to do two things:
use .where() instead of .filter()
use func.jsonb_set or func.json_set

from sqlalchemy import func
stmt = update(User).values(json_data=func.json_set(User.json_data, '{name}', name)).where(User.uid.in_(uid_list))
bitflip0
Related