FastAPI with SQLalchemy orm update problem

Question

this is my question. I can't understand why my database still not update after add, commit and refresh. The following is my code, please help me.

print(db_bag.feature)
db.add(db_bag)
db.commit()
db.refresh(db_bag)
print(db_bag.feature)

and the result like,

[{'name': 'feature2', 'images': [{'name': 'cat.jpg', 'score': None, 'data_image_url': 'data/test112/feature2/data/cat.jpg'}], 'inference_image_url': 'data/test112/feature2/inference/inference.jpg'}]
[{'name': 'feature2', 'images': [{'name': 'cat.jpg', 'score': None, 'data_image_url': 'data/test112/feature2/data/cat.jpg'}], 'inference_image_url': None}]

why the field inference_image_url is not update after I reset it attribute.

my db setting just like below,

engine = create_engine(SQLALCHEMY_DATABASE_URL)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


def get_db_session():
    db = SessionLocal()
    try:
        yield db
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()

please help.

Edit: my bag model just like below,

class Bag(Base):
    __tablename__ = 'bag'
    id = Column(Integer, primary_key=True, index=True)
    sno = Column(String(255))
    name = Column(String(255))
    description = Column(String(255), nullable=True)
    style_id = Column(Integer, ForeignKey('style.id'))
    feature = Column(JSON, nullable=False)
    created_time = Column(DateTime, server_default=func.now())
    updated_time = Column(DateTime, server_default=func.now(), onupdate=func.now())
    # relation for orm system
    style = relationship("Style", back_populates="bags")

and inference_image_url just in the feature JSON field.

updated

@router.patch('/bags/{bag_id}/inference', status_code=200, tags=['bags'])
def inference_bag(inference_data: schemas.InferenceBase, bag_id: int, db: Session= Depends(get_db)):
    # Step 1: get bag instance
    db_bag = crud.get_bag_by_id(db, bag_id=bag_id)
    if db_bag is None:
        raise HTTPException(status_code=404, detail="bag not fund")

    # Step 2: save inference images
    request_data = inference_data.dict(exclude_unset=True)
    features = request_data['feature']
    for i in features:
        # create dir
        path = os.path.join('data', db_bag.sno, i['name'], 'inference')
        if not os.path.isdir(path):
            os.makedirs(path, mode=0o777)
        # save image
        base64_image = i['image'].split(',')[1]
        image_file = Image.open(io.BytesIO(base64.decodebytes(bytes(base64_image, "utf-8"))))
        image_file_path = os.path.join(path, 'inference.jpg')
        image_jpg = image_file.convert("RGB")
        image_jpg.save(image_file_path)
        for j in db_bag.feature:
            if j['name'] == i['name']:
                j['inference_image_url'] = image_file_path

    # update db
    # FIXME: update not working.
    print(db_bag.feature)
    db.add(db_bag)
    db.commit()
    # db.flush()
    db.refresh(db_bag)
    print(db_bag.feature)

    # TODO inference
    # Step 3: inference

    return db_bag

What populates the `inference_image_url` value? What gets stored in the database fields if you look at the table itself? Which databare are you using? What does the model of `db_bag` look like? — MatsLindh, Apr 21 '22 at 11:02
@MatsLindh dears, I just edit my Bag model, and inference_image_url just a string value in feature JSON field. — Michael Lin, Apr 21 '22 at 11:09
Does the field in the JSON column contain any content in the database for that row? — MatsLindh, Apr 21 '22 at 11:20
yes, now in database just like ```[{'name': 'feature2', 'images': [{'name': 'cat.jpg', 'score': None, 'data_image_url': 'data/test112/feature2/data/cat.jpg'}], 'inference_image_url': None}]```, but I want to change this row to ```[{'name': 'feature2', 'images': [{'name': 'cat.jpg', 'score': None, 'data_image_url': 'data/test112/feature2/data/cat.jpg'}], 'inference_image_url': 'data/test112/feature2/inference/inference.jpg'}]```. — Michael Lin, Apr 21 '22 at 11:35
Seems weird, given that all the other properties are being persisted. Do you have a minimal example that show the issue properly, instead of only the few lines that do db.add/refresh? — MatsLindh, Apr 21 '22 at 11:41

score 0 · Answer 1 · answered Apr 21 '22 at 13:07

I reslove my problem with Updates to JSON field don't persist to DB.

Modify The model's 『feature』 field to:

import from sqlalchemy.ext.mutable import MutableList
from sqlalchemy import Column, JSON

feature = Column(MutableList.as_mutable(JSON))

and modify the following,

flag_modified(db_bag, 'feature')
db.add(db_bag)
db.commit()

Bang! DB JSON field can be modified!

FastAPI with SQLalchemy orm update problem

updated

1 Answers1