How to update SQLAlchemy row record?

Question

Suppose a table has three columns: username , password and no_of_logins .

When the user tries to log in, he checks the record with the request like

 user = User.query.filter_by(username=form.username.data).first() 

If the password matches, it continues. What I would like to do is count how many times the user has logged in. Thus, whenever it successfully logs in, I would like to increase the no_of_logins field and save it back to the users table. I am not sure how to start an update request with SqlAlchemy.

Answer 1

 user.no_of_logins += 1 session.commit() 

Answer 2

There are several ways to sqlalchemy with sqlalchemy

 1) user.no_of_logins += 1 session.commit() 2) session.query().\ filter(User.username == form.username.data).\ update({"no_of_logins": (User.no_of_logins +1)}) session.commit() 3) conn = engine.connect() stmt = User.update().\ values(no_of_logins=(User.no_of_logins + 1)).\ where(User.username == form.username.data) conn.execute(stmt) 4) setattr(user, 'no_of_logins', user.no_of_logins+1) session.commit() 

Answer 3

Using the operator user=User.query.filter_by(username=form.username.data).first() you will get the specified user in the user variable.

Now you can change the value of the new object variable as user.no_of_logins += 1 and save the changes using the session commit method.

Answer 4

I wrote a Telegram bot and I had problems updating the strings. Use this example if you have a Model.

 def update_state(chat_id, state): try: value = Users.query.filter(Users.chat_id == str(chat_id)).first() value.state = str(state) db.session.flush() db.session.commit() #db.session.close() except: print('Error in def update_state') 

Why use db.session.flush() ? That is why >>> SQLAlchemy: What is the difference between flush () and commit ()?

Answer 5

Examples to clarify an important issue in accepted responses to comments

I did not understand this until I played with it myself, so I thought that there would be others who would be baffled. Let's say you are working with a user whose id == 6 and whose no_of_logins == 30 at startup.

 # 1 (bad) user.no_of_logins += 1 # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 2 (bad) user.no_of_logins = user.no_of_logins + 1 # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 3 (bad) setattr(user, 'no_of_logins', user.no_of_logins + 1) # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 4 (ok) user.no_of_logins = User.no_of_logins + 1 # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 # 5 (ok) setattr(user, 'no_of_logins', User.no_of_logins + 1) # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 

Essence

By referring to a class instead of an instance, you can make SQLAlchemy be smarter with respect to the increment, so that this happens on the database side rather than on the Python side. Doing this in the database is better because it is less vulnerable to data corruption (for example, two clients try to grow at the same time with the net result of only one increment instead of two). I assume that you can increment in Python if you set locks or increase isolation, but why bother if you don't need it?

Warning

If you are going to increase twice using the code that generates SQL, for example, SET no_of_logins = no_of_logins + 1 , then you will need to commit or at least reset between increments, otherwise you will get only one increase:

 # 6 (bad) user.no_of_logins = User.no_of_logins + 1 user.no_of_logins = User.no_of_logins + 1 session.commit() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 # 7 (ok) user.no_of_logins = User.no_of_logins + 1 session.flush() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 user.no_of_logins = User.no_of_logins + 1 session.commit() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6