flask_sqlalchemy UPDATE row details not updating mySQL database

Question

I have a flask app and a mySQL database connected to it. I am using POSTMAN to send GET/POST requests to the flask endpoints for user add, update and delete. I am able to successfully add users to the database but while I am updating the flask app does not throw any errors where as the postman also displays a successful JSON response that "User updated!!!" But when I perform SELECT * FROM Users operation in my SQL Workbench the details are not being updated.

My implementation is as follows - I have created a model User with two columns username and password.

The user update API looks like -

from flask_restful import Resource
from flask_sqlalchemy import SQLAlchemy
from model import User


class Update_User(Resource):
    def post(self):
        data = request.get_json(force=True)
        usr = User.query.filter_by(username=data['username']).first()
        print(1, usr.username, usr.password)
        
        usr.password = data['new_password']
        print(2, usr.username, usr.password)

        db.session.commit()
        db.session.close()

        return {"res": "User updated!!!!"}

flask output:

1 Ash 12345
2 Ash 00000

Just for better understanding, what is better to use ? Python SQLAlchemy or flask's own SQLAlchemy ? Because there are other instances as well where I am facing trouble like while updating two rows simultaneously using add_all()

Here is another variant: stackoverflow.com/questions/6699360/… — Moemars
– Moemars, Commented Jan 22, 2022 at 20:15

Ashish Mysterio · Accepted Answer · 2020-10-11 09:04:53Z

3

After struggling I found out where I was wrong, someone else facing the issue can try the following thing.

What worked for me -

usr.password = data['new_password']
db.session.merge(usr)
db.session.commit()

Things that did not work for me despite having them as answer on some of the stackoverflow questions -

usr.data["password"] = data['new_password']
db.session.commit()
--------------------------------------------
usr.password = data['new_password']
db.session.add(usr)
db.session.commit()
--------------------------------------------
usr.data = { "password": data['new_password'] }
db.session.commit()

Hope this might help you!

answered Oct 11, 2020 at 9:04

Ashish Mysterio

3072 gold badges6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kielni · Accepted Answer · 2020-10-10 15:40:55Z

1

You need to add the object to the session to tell SQLAlchemy that you want to you've made changes (after updating it, before committing):

usr.password = data['new_password']
db.session.add(usr)
db.session.commit()

See more details in the SQLAlchemy docs: https://docs.sqlalchemy.org/en/13/orm/tutorial.html#adding-and-updating-objects

answered Oct 10, 2020 at 15:40

kielni

5,0571 gold badge26 silver badges21 bronze badges

3 Comments

Ashish Mysterio Over a year ago

This does not work for me! However, if you might want to take an another look at your answer and replace .add(usr) with .merge(usr) it works absolutely fine.

Ashish Mysterio Over a year ago

The error thrown while using your answer is Object '<usr at 0xe4828>' is already attached to session '1' (this is '2')

kielni Over a year ago

That sounds like you have loaded this user object into another session somewhere else: "Session.merge() transfers state from an outside object into a new or already existing instance within a session." -- docs.sqlalchemy.org/en/13/orm/session_basics.html Read the SQLAlchemy docs on sessions; you should only be working with one session at a time.

Collectives™ on Stack Overflow

flask_sqlalchemy UPDATE row details not updating mySQL database

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related