Assume table has three columns: username
, password
and no_of_logins
.
When user tries to login, it's checked for an entry with a query like
user = User.query.filter_by(username=form.username.data).first()
If password matches, he proceeds further. What I would like to do is count how many times the user logged in. Thus whenever he successfully logs in, I would like to increment the no_of_logins
field and store it back to the user table. I'm not sure how to run update query with SqlAlchemy.
This question is related to
python
sqlalchemy
flask-sqlalchemy
user.no_of_logins += 1
session.commit()
There are several ways to UPDATE
using 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()
With the help of user=User.query.filter_by(username=form.username.data).first()
statement you will get the specified user in user
variable.
Now you can change the value of the new object variable like user.no_of_logins += 1
and save the changes with the session
's commit method.
I didn't understand it until I played around with it myself, so I figured there would be others who were confused as well. Say you are working on the user whose id == 6
and whose no_of_logins == 30
when you start.
# 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
By referencing the class instead of the instance, you can get SQLAlchemy to be smarter about incrementing, getting it to happen on the database side instead of the Python side. Doing it within the database is better since it's less vulnerable to data corruption (e.g. two clients attempt to increment at the same time with a net result of only one increment instead of two). I assume it's possible to do the incrementing in Python if you set locks or bump up the isolation level, but why bother if you don't have to?
If you are going to increment twice via code that produces SQL like SET no_of_logins = no_of_logins + 1
, then you will need to commit or at least flush in between increments, or else you will only get one increment in total:
# 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
I wrote telegram bot, and have some problem with update rows. Use this example, if you have 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's why >>> SQLAlchemy: What's the difference between flush() and commit()?
Source: Stackoverflow.com