Flask-SQLAlchemy + MySQL + JSON Column. Update not working

Question

I use SQLAlchemy in my project. When i modify JSON column in sqlalchemy model, it is not changes at all. Thats my code:

class Order(DB.Model):

    uid = DB.Column(DB.Integer, primary_key=True, index=True, unique=True)
    status_uid = DB.Column( DB.Integer, default=1 )
    json_data = DB.Column( DB.JSON, nullable=True )

orderobj = Order(json_data{'key':'old value'})
DB.session.add( orderobj )

#That is work fine
DB.session.commit()


# Trying to update
orderobj = Order.query.filter_by( status_uid=1 ).first()
orderobj.json_data['key'] = 'new value'

print(orderobj.json_data['key']) # -> 'old value'

DB.session.commit()

print(orderobj.json_data['key']) # -> 'old value'

Related, if not a dupe of [In SQLAlchemy, how does the dict update method interact with the ORM?](https://stackoverflow.com/questions/36298077/in-sqlalchemy-how-does-the-dict-update-method-interact-with-the-orm) — Ilja Everilä, Apr 26 '18 at 19:38
Does this answer your question? [Updates to JSON field don't persist to DB](https://stackoverflow.com/questions/42559434/updates-to-json-field-dont-persist-to-db) — Michal Sylwester, Mar 06 '21 at 20:52

score 2 · Answer 1 · answered Mar 28 '19 at 09:59

2

class Order(DB.Model):

    uid = DB.Column(DB.Integer, primary_key=True, index=True, unique=True)
    status_uid = DB.Column( DB.Integer, default=1 )
    json_data = DB.Column( DB.JSON, nullable=True )

orderobj = Order(json_data{'key':'old value'})
DB.session.add( orderobj )

#That is work fine
DB.session.commit()


# Trying to update
orderobj = Order.query.filter_by( status_uid=1 ).first()

json_data = deepcopy(orderobj.json_data)
json_data['key'] = 'new value'

orderobj.json_data=json_data

print(orderobj.json_data['key']) # -> 'old value'

DB.session.commit()

print(orderobj.json_data['key']) # -> 'old value'

answered Mar 28 '19 at 09:59

Monster Rob

29
2

5

It would be helpful if you add some comments to your solution and explain what you changed and why it was necessary that change. – f-CJ Mar 28 '19 at 10:15
For more context check out this post called "Beware of JSON fields in SQLAlchemy": https://amercader.net/blog/beware-of-json-fields-in-sqlalchemy/ . – Eddie Forson Feb 19 '23 at 11:39

Flask-SQLAlchemy + MySQL + JSON Column. Update not working

1 Answers1