In this article, I'll show how to partially update JSON column using SQLAlchemy expressions in MySQL database.
Say, we have a table named TableName
with a JSON column named JsonColumn
and the values in this JSON column is in the following format;
{
"JsonAttribute": 5,
"AnotherJsonAttribute": "Hello"
}
SQL statement to update a single JSON attribute is as below;
UPDATE TableName SET JsonColumn = JSON_SET(JsonColumn, '$.JsonAttribute', 10) WHERE Id = 100;
Converting the above SQL statement to the corresponding SQLAlchemy expression is as below;
from sqlalchemy import update, func
table_name_id = 100
json_attribute_value = 10
update_sql = update(TableName).values(
JsonColumn=func.json_set(TableName.JsonColumn, '$.JsonAttribute', json_attribute_value)).\
where(TableName.Id == table_name_id)
result = db.session.execute(update_sql)
For running a bulk update on the array of dicts using WHERE
condition.
from sqlalchemy import update, func, bindparam
update_data = [{
'_Id': 100,
'Value': 10
}, {
'_Id': 101,
'Value': 11
}, {
'_Id': 102,
'Value': 12
}]
update_sql = update(TableName).values(
JsonColumn=func.json_set(TableName.JsonColumn, '$.JsonAttribute', bindparam('Value'))).\
where(TableName.Id == bindparam('_Id'))
result = db.session.execute(update_sql, update_data)
Hope this helps!