Updates to JSON field don't persist to DB

Question

We have a model with a JSON field where user flags get inserted. Inserting does work as expected, but when removing certain flags, they stay in the field and changes don't get persisted to the DB.

We have the following method in our model:

def del_flag(self, key):
    if self.user_flags is None or not key in self.user_flags:
        return False
    else:
        del self.user_flags[key]
        db.session.commit()        
        return True

The databasse is postgres and we use the SQLalchemy JSON field dialect for the field type. Any advice on this?

http://stackoverflow.com/questions/29830229/force-object-to-be-dirty-in-sqlalchemy — Jesvin Jose, Mar 02 '17 at 15:52
You need to use [mutation tracking](http://docs.sqlalchemy.org/en/latest/orm/extensions/mutable.html). — univerio, Mar 02 '17 at 20:15

score 95 · Accepted Answer · answered Aug 25 '17 at 07:41

95

If you are using Postgres < 9.4 you can't update JSON field directly. You need flag_modified function to report the change to SQLAlchemy:

from sqlalchemy.orm.attributes import flag_modified
model.data['key'] = 'New value'
flag_modified(model, "data")
session.add(model)
session.commit()

answered Aug 25 '17 at 07:41

José Vte. Calderón

1,348
12
17

13

I am using Postgres 10.3 and SQLAlchemy 1.2.8 with a column that looks like `data = Column(MutableDict.as_mutable(JSON))` and I still had to do this to make an update work. – Bob Jordan Aug 07 '18 at 14:24
2

Thank you @josé-vte-calderón. Took me quite a while to figure out that SQLAlchemy was not registering changes made to JSON fields. Thanks! – dataflow Oct 17 '18 at 16:48
for those who don't scroll too much down, please see the latest response from @humbledude – Michał Schielmann Jun 13 '19 at 09:52
2

There is no need to add the model object to the DB unless it is new. If we are modifying an existing object then session.commit() will save the changes down. – Jake Jul 28 '22 at 18:05
Spent lot of time to solve this problem, but this is the place I found solution at – Yash Pokar Jan 18 '23 at 10:14

score 35 · Answer 2 · answered Jun 11 '19 at 04:58

35

I'm using JSON field and I referred below document.

https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=json#sqlalchemy.types.JSON

It shows how to make JSON-dict field mutable. (Default is immutable)

like this..

from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy import Column, Integer, JSON

class TableABC(Base):
    __tablename__ = 'table_abc'
    id = Column(Integer, primary_key=True)
    info = Column(MutableDict.as_mutable(JSON))

Then I could update json field as ORM.

answered Jun 11 '19 at 04:58

humbledude

479
4
6

2

SQLAlchemy==1.1.18 and PostgreSQL 9.6 works as expected with `JSONB` type – El Ruso Jun 18 '19 at 09:28
2

Hi @humbledude. This method did not work for me, using sqlalchemy version 1.3.6. I'd recommend the solution using flag_modified as somebody else suggested. It works well – Skulas Jul 25 '19 at 14:24
1

Hi @Skulas. this code seems to work depending on version of DB. I am using Postgresql(Enterprise) 10.1.5 and MySql 5.7.17. I couldn't find version limitation info on official SQLAlchemy doc. – humbledude Aug 06 '19 at 02:07

score 34 · Answer 3 · answered Dec 30 '18 at 13:01

34

My issue was referencing the row object returned from SQLAlchemy when creating the new row. e.g. this does not work:

row = db.session.query(SomeTable).filter_by(id=someId).first()
print(row.details)
newDetails = row.details
newDetails['key'] = 'new data'
row.details = newDetails
db.session.commit()

but creating a new dict does work

row = db.session.query(SomeTable).filter_by(id=someId).first()
print(row.details)
newDetails = dict(row.details)
newDetails['key'] = 'new data'
row.details = newDetails
db.session.commit()

notice dict(row.details)

answered Dec 30 '18 at 13:01

howMuchCheeseIsTooMuchCheese

1,602
1
22
30

1

Also, you have to cast all nested nodes to dict if you want to change them. – Denis Yakovlev Oct 09 '19 at 05:18
1

@YakovlevDenis you could probably also `json.loads(json.dumps(dict(row.details)))`, but I haven't tested that. – howMuchCheeseIsTooMuchCheese Oct 09 '19 at 12:40
@howMuchCheeseIsTooMuchCheese tried the `json` approach and it worked for me. – Shift 'n Tab Jan 24 '22 at 10:31
I've been searching for a solution for a long time, this is the only one that made sense to me and worked. thanks! – Shlok Sharma Jul 29 '23 at 17:30

score 0 · Answer 4 · edited May 21 '23 at 06:24

0

What worked for me is this-

user = db.query(User).filter(User.id==id).first()

#change user to dictionary and make required changes in the object and create modified_user_dict

db.query(User).filter(User.id==modified_user_dict['id']).update(values=modified_user_dict)
db.commit()

edited May 21 '23 at 06:24

moken

3,227
8
13
23

answered May 17 '23 at 14:42

user21914273

1

Answer needs supporting information Your answer could be improved with additional supporting information. Please [edit](https://stackoverflow.com/posts/76273344/edit) to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](https://stackoverflow.com/help/how-to-answer). – moken May 21 '23 at 06:25

Updates to JSON field don't persist to DB

4 Answers4

Linked

Related