Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flask-SQLalchemy update a row's information

People also ask

How do I update a row in SQLAlchemy?

Update a row entry in SQLAlchemyGet the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.


Retrieve an object using the tutorial shown in the Flask-SQLAlchemy documentation. Once you have the entity that you want to change, change the entity itself. Then, db.session.commit().

For example:

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

user = User.query.get(5)
user.name = 'New Name'
db.session.commit()

Flask-SQLAlchemy is based on SQLAlchemy, so be sure to check out the SQLAlchemy Docs as well.


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.


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}

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