Flask-SQLalchemy update a row's information

Just assigning the value and committing them will work for all the data types but JSON and Pickled attributes. Since pickled type is explained above I'll note down a slightly different but easy way to update JSONs.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    data = db.Column(db.JSON)

def __init__(self, name, data):
    self.name = name
    self.data = data

Let's say the model is like above.

user = User("Jon Dove", {"country":"Sri Lanka"})
db.session.add(user)
db.session.flush()
db.session.commit()

This will add the user into the MySQL database with data {"country":"Sri Lanka"}

Modifying data will be ignored. My code that didn't work is as follows.

user = User.query().filter(User.name=='Jon Dove')
data = user.data
data["province"] = "south"
user.data = data
db.session.merge(user)
db.session.flush()
db.session.commit()

Instead of going through the painful work of copying the JSON to a new dict (not assigning it to a new variable as above), which should have worked I found a simple way to do that. There is a way to flag the system that JSONs have changed.

Following is the working code.

from sqlalchemy.orm.attributes import flag_modified
user = User.query().filter(User.name=='Jon Dove')
data = user.data
data["province"] = "south"
user.data = data
flag_modified(user, "data")
db.session.merge(user)
db.session.flush()
db.session.commit()

This worked like a charm. There is another method proposed along with this method here Hope I've helped some one.

Models.py define the serializers

def default(o):
   if isinstance(o, (date, datetime)):
      return o.isoformat()

def get_model_columns(instance,exclude=[]):
    columns=instance.__table__.columns.keys()
    columns=list(set(columns)-set(exclude))
    return columns

class User(db.Model):
   __tablename__='user'
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   .......
   ####

    def serializers(self):
       cols = get_model_columns(self)
       dict_val = {}
       for c in cols:
           dict_val[c] = getattr(self, c)
       return json.loads(json.dumps(dict_val,default=default))

In RestApi, We can update the record dynamically by passing the json data into update query:

class UpdateUserDetails(Resource):
   @auth_token_required
   def post(self):
      json_data = request.get_json()
      user_id = current_user.id
      try:
         instance = User.query.filter(User.id==user_id)
         data=instance.update(dict(json_data))
         db.session.commit()
         updateddata=instance.first()
         msg={"msg":"User details updated successfully","data":updateddata.serializers()}
         code=200
      except Exception as e:
         print(e)
         msg = {"msg": "Failed to update the userdetails! please contact your administartor."}
         code=500
      return msg

I was looking for something a little less intrusive then @Ramesh's answer (which was good) but still dynamic. Here is a solution attaching an update method to a db.Model object.

You pass in a dictionary and it will update only the columns that you pass in.

class SampleObject(db.Model):
  id = db.Column(db.BigInteger, primary_key=True)
  name = db.Column(db.String(128), nullable=False)
  notes = db.Column(db.Text, nullable=False)

  def update(self, update_dictionary: dict):
    for col_name in self.__table__.columns.keys():
      if col_name in update_dictionary:
        setattr(self, col_name, update_dictionary[col_name])

    db.session.add(self)
    db.session.commit()

Then in a route you can do

object = SampleObject.query.where(SampleObject.id == id).first()
object.update(update_dictionary=request.get_json())

Update the Columns in flask

admin = User.query.filter_by(username='admin').first()
admin.email = 'my_new_email@example.com'
admin.save()