Updates to JSON field don't persist to DB
Solution 1
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()
Solution 2
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.
Solution 3
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)
Related videos on Youtube
trnc
Updated on June 06, 2022Comments
-
trnc almost 2 years
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?
-
Jesvin Jose about 7 years
-
univerio about 7 yearsYou need to use mutation tracking.
-
-
Bob Jordan almost 6 yearsI 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. -
dataflow over 5 yearsThank you @josé-vte-calderón. Took me quite a while to figure out that SQLAlchemy was not registering changes made to JSON fields. Thanks!
-
Michał Schielmann almost 5 yearsfor those who don't scroll too much down, please see the latest response from @humbledude
-
El Ruso almost 5 yearsSQLAlchemy==1.1.18 and PostgreSQL 9.6 works as expected with
JSONB
type -
Skulas almost 5 yearsHi @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
-
humbledude almost 5 yearsHi @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.
-
Denis Yakovlev over 4 yearsAlso, you have to cast all nested nodes to dict if you want to change them.
-
howMuchCheeseIsTooMuchCheese over 4 years@YakovlevDenis you could probably also
json.loads(json.dumps(dict(row.details)))
, but I haven't tested that. -
Roel over 2 years@howMuchCheeseIsTooMuchCheese tried the
json
approach and it worked for me.