[python] Flask-SQLalchemy update a row's information

How can I update a row's information?

For example I'd like to alter the name column of the row that has the id 5.

This question is related to python sqlalchemy flask-sqlalchemy

The answer is


There is a method update on BaseQuery object in SQLAlchemy, which is returned by filter_by.

num_rows_updated = User.query.filter_by(username='admin').update(dict(email='[email protected]')))
db.session.commit()

The advantage of using update over changing the entity comes when there are many objects to be updated.

If you want to give add_user permission to all the admins,

rows_changed = User.query.filter_by(role='admin').update(dict(permission='add_user'))
db.session.commit()

Notice that filter_by takes keyword arguments (use only one =) as opposed to filter which takes an expression.


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()

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

    def serializers(self):
       dict_val={"id":self.id,"created_by":self.created_by,"created_at":self.created_at,"updated_by":self.updated_by,"updated_at":self.updated_at}
       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

This does not work if you modify a pickled attribute of the model. Pickled attributes should be replaced in order to trigger updates:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from pprint import pprint

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqllite:////tmp/users.db'
db = SQLAlchemy(app)


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

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

    def __repr__(self):
        return '<User %r>' % self.username

db.create_all()

# Create a user.
bob = User('Bob', {})
db.session.add(bob)
db.session.commit()

# Retrieve the row by its name.
bob = User.query.filter_by(name='Bob').first()
pprint(bob.data)  # {}

# Modifying data is ignored.
bob.data['foo'] = 123
db.session.commit()
bob = User.query.filter_by(name='Bob').first()
pprint(bob.data)  # {}

# Replacing data is respected.
bob.data = {'bar': 321}
db.session.commit()
bob = User.query.filter_by(name='Bob').first()
pprint(bob.data)  # {'bar': 321}

# Modifying data is ignored.
bob.data['moo'] = 789
db.session.commit()
bob = User.query.filter_by(name='Bob').first()
pprint(bob.data)  # {'bar': 321}

Examples related to python

programming a servo thru a barometer Is there a way to view two blocks of code from the same file simultaneously in Sublime Text? python variable NameError Why my regexp for hyphenated words doesn't work? Comparing a variable with a string python not working when redirecting from bash script is it possible to add colors to python output? Get Public URL for File - Google Cloud Storage - App Engine (Python) Real time face detection OpenCV, Python xlrd.biffh.XLRDError: Excel xlsx file; not supported Could not load dynamic library 'cudart64_101.dll' on tensorflow CPU-only installation

Examples related to sqlalchemy

How to install mysql-connector via pip How to delete a record by id in Flask-SQLAlchemy How to write DataFrame to postgres table? ImportError: No module named MySQLdb sqlalchemy IS NOT NULL select SQLAlchemy create_all() does not create tables How to execute raw SQL in Flask-SQLAlchemy app Flask-SQLAlchemy how to delete all rows in a single table SQLAlchemy default DateTime How to count rows with SELECT COUNT(*) with SQLAlchemy?

Examples related to flask-sqlalchemy

How to delete a record by id in Flask-SQLAlchemy Flask Download a File How to execute raw SQL in Flask-SQLAlchemy app Flask-SQLAlchemy how to delete all rows in a single table Flask SQLAlchemy query, specify column names ImportError: No module named sqlalchemy How to update SQLAlchemy row entry? jsonify a SQLAlchemy result set in Flask Flask-SQLalchemy update a row's information