15

For single record update, I have tried the following code and it's working.

uid=1

info = Info.query.filter_by(id=uid).first()
info.sold_status ='1'
db.session.add(info)
db.session.commit()

Now I want to update one column for multiple rows. I have tried:

for row in db.session.query(Uninstall.status).all():
     print row.status

abc=row.status
abc=uid

db.session.add(row.status)
db.session.commit()

but it's not working.

Thanks in advance :)

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
Dipnesh Parakhiya
  • 516
  • 1
  • 5
  • 17
  • 2
    http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update – r-m-n Jul 07 '16 at 10:51

3 Answers3

41

The suggested is not optimized. You can use the options to update: 1. 

for row in Uninstall.query:  # all() is extra
    row.status = new_status
Session.commit()

If there are lots of rows, it can be faster, but it will invoke UPDATE for every row. Not good, is it?

2.

Uninstall.query.update({Uninstall.status: new_status}) 
Session.commit()

Invokes only one UPDATE query for all rows.

slybloty
  • 6,346
  • 6
  • 49
  • 70
antonio_antuan
  • 1,293
  • 9
  • 22
  • sorry @antonio_antuan it's not work :( ....... bcz its store null value in database....not a updated value – Dipnesh Parakhiya Jul 11 '16 at 05:59
  • There is no difference what is the value. It compiles to the query: `UPDATE uninstalls SET status = NEW_STATUS`. Could you show me DDL of the table or/and class? Or SQLA raises some exception? – antonio_antuan Jul 11 '16 at 06:20
  • uid = request.json.get('uid') for row in Uninstall.query.all(): # all() is extra row.status = uid db.session.commit() #your code is not working – Dipnesh Parakhiya Jul 11 '16 at 06:32
  • Show Uninstall model definition. And the value in "uid" – antonio_antuan Jul 11 '16 at 06:36
  • class Uninstall(db.Model): id = db.Column(db.Integer, primary_key=True) device_id=db.Column(db.String(80)) status=db.Column(db.String(80),default="0") class Uninstall_Schema(Schema): id=fields.Int(dump_only=True) device_id=fields.Str() status=fields.Str() – Dipnesh Parakhiya Jul 11 '16 at 06:40
  • Check the value of `uid` variable. Looks like the value is `None` - only one possibility of storing `NULL` in DB after commit. – antonio_antuan Jul 11 '16 at 06:43
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116958/discussion-between-dipnesh-and-antonio-antuan). – Dipnesh Parakhiya Jul 11 '16 at 06:45
3

2021 Answer

As of sqlalchemy==1.4 and the upcoming release of 2.0, the recommended way of performing an UPDATE is via the SA-builtin update function, rather than the .update method.

Example:

from sqlalchemy import update

uid=1

# MY_CONDITION is whatever condition triggers the change in status value
update(Uninstall).where(MY_CONDITION).values(status=uid)

If you want to change the status value to be equal to uid for all columns, then you can simply do it unconditionally:

update(Uninstall).values(status=uid)

Finally, don't forget to commit:

session.commit()

Source: https://docs.sqlalchemy.org/en/14/core/dml.html?highlight=update#sqlalchemy.sql.expression.update

pcko1
  • 833
  • 12
  • 22
-2

query the table to get the rows and you can update the columns while iterating over them and commit once its done.

for row in Uninstall.query.all():
    row.status = <new status>
db.session.commit()
Shivaraj
  • 400
  • 5
  • 16
Tushar Santoki
  • 96
  • 6