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: https://stackoverflow.com/questions/6699360/flask-sqlalchemy-update-a-rows-information/70816760#70816760 — Moemars, Jan 22 '22 at 20:15

score 3 · Accepted Answer · answered Oct 11 '20 at 09:04

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!

score 1 · Answer 2 · answered Oct 10 '20 at 15:40

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 '20 at 15:40

kielni

4,779
24
21

1

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 Oct 11 '20 at 09:07
1

The error thrown while using your answer is `Object '' is already attached to session '1' (this is '2')` – Ashish Mysterio Oct 11 '20 at 09:08
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." -- https://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. – kielni Oct 11 '20 at 15:12

flask_sqlalchemy UPDATE row details not updating mySQL database

2 Answers2