SQLAlchemy JSON TypeDecorator not saving correctly, issues with session.commit()

Question

My models.py file has two models, User and Job.

Each job will have multiple Users associated with it.

A user can be associated with multiple jobs.

I need to be able to do something like job.getUsers() --> list of User objects

I tried to use TypeDecorators to store a JSON int array in my Job table. Each int represents a User's pk, which I can later use to find the correct row in the db.

At first glance, this was working fine, but I'm getting some weird bugs (bottom of post, after my code).

class Json(TypeDecorator):

    impl = String

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        return json.loads(value)


class Job(Base):
    __tablename__ = 'jobs'
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    workers = Column(Json(128))

    def __init__(self):
        self.workers = []

Here is my weird output

>>> db_session = scoped_session(sessionmaker(autocommit=False, autoflush=True, bind=engine))

>>> job = Job()
>>> job.workers
[]
>>> job.workers.append(1)
>>> job.workers
[1]
>>> db_session.add(job)
>>> job.workers
[1]
>>> db_session.commit()
>>> job.workers
[1]
>>> job = Job.query.filter(Job.id == 1).first()
>>> job.workers
[1]

At this point, it looks like everything ok. When I try adding a second item to the list, things start to go wrong.

>>> job.workers.append(2) # let's try adding another item to the list.
>>> job.workers
[1, 2]
>>> db_session.add(job) # is this necessary? added for debugging purposes, seems to have no effect on anything
>>> job.workers
[1, 2]
>>> db_session.commit() # autoflush is on
>>> job.workers
[1] # !!!!!!!!!!!!!!!!!!!??????????????????????/

Please let me know if you have any idea what I'm doing wrong. Alternatively, let me know if there is a better way to do this instead of a JSON TypeDecorator. Thanks!

Answer 1

SQLAlchemy has issues persisting JSON that's been updated. I had this exact same frustrating issue but found the answer here :

https://bashelton.com/2014/03/updating-postgresql-json-fields-via-sqlalchemy/

In summary, use 'flag_modified' method from sqlalchemy.orm.attributes

from sqlalchemy.orm.attributes import flag_modified    

jobs.workers.append(2)
flag_modified(jobs, "workers")
db_session.commit()

Answer 2

This is what helped me.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.mutable import MutableDict # <--- !

app = Flask(__name__)
db = SQLAlchemy(app)

class SomeModel(db.Model):
id = db.Column(db.Integer, primary_key=True)
data = db.Column(MutableDict.as_mutable(db.JSON)) # <--- !