3

I intend to use func function to update a specific JSON field in Sqlalchemy, but I get some problem, here is my code to update field:

self.db.query(TestModel).filter(TestModel.test_id == self._test_id).update(
    {field_name: func.json_set(
        field_name,
        "$." + key,
        formatted_val)}
    , synchronize_session='fetch'
)
self.db.commit()

I ran the code above and got the error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) malformed JSON

So, I go to check the log, found Sqlalchemy form an SQL clause like that:

UPDATE test_model SET field_name=json_set('field_name', '$.keyname', 'value') WHERE test_model.test_id = 1;

the problem is Sqlalchemy should not use 'field_name' to specific the field it should use field_name to specific the field, and I try to run corrected sql clause below in sql client:

UPDATE test_model SET field_name=json_set(field_name, '$.keyname', 'value') WHERE test_model.test_id = 1;

and it work find

I just want to know how to make the Sqlalchemy form the correct field from 'field_name' to field_name?

halfer
  • 19,824
  • 17
  • 99
  • 186
Gary
  • 1,199
  • 1
  • 9
  • 23

1 Answers1

1

You should pass first parameter with a name of model to function func.json_set:

self.db.query(TestModel).filter(TestModel.test_id == self._test_id).update(
    {field_name: func.json_set(
        TestModel.field_name,
        "$." + key,
        formatted_val)}, 
     synchronize_session='fetch'
)
self.db.commit()
yars
  • 33
  • 6