Flask-SQLalchemy updates row information

Question

How to update row information?

For example, I would like to change the row name column with id 5.

Answer 1

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

For example:

 admin = User.query.filter_by(username='admin').first() admin.email = '[e-mail]' 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 SQLAlchemy Docs as well.

Answer 2

In the BaseQuery object in SQLAlchemy, there is an update method that returns filter_by .

 admin = User.query.filter_by(username='admin').update(dict(email='[e-mail]'))) db.session.commit() 

The advantage of using update over modifying an object occurs when there are many objects to update.

If you want to grant add_user permission add_user all admin s,

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

Note that filter_by accepts keyword arguments (use only one = ), not filter , which accepts an expression.

Answer 3

This does not work if you change the pickled attribute of the model. Pickled attributes must be replaced 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} 

Answer 4

Just assigning a value and executing it will work for all data types, but with the JSON and Pickled attributes. Since the pickled type is explained above, I will stop a slightly different but simple way to update JSON.

 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 similar to the above.

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

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

Data changes will be ignored. My code that did not 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 hard work of copying JSON into a new dict (without assigning it to a new variable as above), which should have worked, I found an easy way to do this. There is a way to mark a system that has changed JSON.

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

It worked like a charm. There is another method suggested along with this method here. I hope I helped someone.