1

Let's say this is a sample json.

{
  "id": 1,
  "details": [
    {
      "a": "100",
    }
  ]
}

The result that i want is add {'b': '200'} to details and it will look like this.

{
  "id": 1,
  "details": [
    {
      "a": "100",
    },
    {
      "b": "200",
    },
  ]
}

In my file, I have tried many times but it still doesn't work So, how to add dict with sqlalchemy

class Models(Base):
    __tablename__ = 'Table'
    id = Column(Integer, primary_key=True)
    details = Column(JSONB, nullable=False, server_default='[]')


@router.post("/post2")
async def post2(id: int = 1):
    session = Session()
    user = session.query(Models).filter(
        Models.id == id).first()
    for var in user.details:
        var.append({"b": "200"})
    session.commit()
    return user
T_Ner
  • 137
  • 1
  • 8

1 Answers1

2

You probably need to use flag_modified, becuase you cannot update JSONB directly:

from sqlalchemy.orm.attributes import flag_modified

@router.post("/post2")
async def post2(id: int = 1):
    session = Session()
    user = session.query(Models).filter(
        Models.id == id
    ).first()
    
    user.details.append({"b": "200"})
    flag_modified(user, "details")    
    session.commit()
    return user

or rewrite whole collection:

import copy

@router.post("/post2")
async def post2(id: int = 1):
    session = Session()
    user = session.query(Models).filter(
        Models.id == id
    ).first()
    
    details = copy.deepcopy(user.details)
    details.append({"b": "200"})
    user.details = details   
    session.commit()
    return user

Here is similar topic: Updates to JSON field don't persist to DB

jorzel
  • 1,216
  • 1
  • 8
  • 12
  • Thanks, Based on your advice, which one should I use flag_modified or copy.deepcopy? – T_Ner Dec 07 '21 at 07:23
  • If you modify a huge part of your dict I would rewrite structure using deepcopy. But when you add only one key, flag_modified looks more appropriate. – jorzel Dec 07 '21 at 13:31