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

Question

There are two models in my models.py file: User and Job.

Each job will have several users associated with it.

A user can be associated with several tasks.

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

I tried using TypeDecorators to store an int JSON array in my Job table. Each int represents a custom pk, which I can use later to find the correct line in db.

At first glance, this worked fine, but I get some strange errors (at the bottom of the message, 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 strange conclusion

 >>> 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 the moment, everything looks fine. When I try to add the second item to the list, everything starts to go wrong.

 >>> job.workers.append(2) # let 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 an idea of ​​what I am doing wrong. Alternatively, let me know if there is a better way to do this instead of JSON TypeDecorator. Thanks!

Answer 1

SQLAlchemy has problems updating JSON. I had the same nasty problem, but found the answer here:

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

Finally, use the 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

I just ran into the same problem, it seems that SQLAlchemy update will not be performed if json filed has the same identifier in python.

So my solution is to assign a new json filed object. Below codes should work:

 job.workers = job.workers + [2, ] db_session.add(job) db_session.save() 

Answer 3

In my application, I circumvented this by copying the data first:

 import copy workers = copy.deepcopy(job.workers) workers.append(2) job.workers = workers 

I used deepcopy because my data is a more complex object.