SQLAlchemy – about session problems
SQLAlchemy, like many other ORMs, works with the database through sessions. This is different from working directly with the database. Not understanding how sessions work can lead to annoying errors.
Errors
sqlalchemy.orm.exc.DetachedInstanceError: Instance
is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/13/bhk3) “Attribute refresh operation cannot proceed”% (state_str (state))
sqlalchemy.orm.exc.DetachedInstanceError: Instanceis not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/13/bhk3)
How to avoid errors using the example code below, where everything works correctly:
# Put all user IDs in a variable (to work with another session later)
user_ids = [x.id for x in db.session.query(User.id).distinct()]
for x_id in user_ids:
# Now we can work with a new session
user = db.session…query(User)…filter_by(id=x_id)…first()
for setting in user…settings:
row = vars(setting)
if row[‘name’] == ‘market_direction’:
self…send_market_direction(user)
elif row[‘name’] == ‘change_percent’:
self…send_change_percent(user, row[‘percent’])
# Update the date
db.session…query(UserSetting)…filter_by(name=row[‘name’])…update({‘key’: value})
# Commit all changes after processing one user
db.session…commit()
This code could have 2 errors in the intersection of sessions.
1.
If we used db.session.commit () inside second cycle forthen SQLAlchemy would close the session assuming the task is complete.
Instead, we update with the method update our class instance Usercreated above. And at the very end we do commit…
2.
But db.session.commit () is still inside the first cycle and we cannot bear it outside of it. Therefore, we put the user IDs at the top in a variable user_ids… IDs are collected separately and when the session is closed, we are not afraid that the session will close.
For example, this code, when interacting above, would cause errors with sessions:
users = db.session…query(User)…all()
for user in users:
settings = db.session…query(UserSetting)…filter_by(user_id=user…id)…all()
This would be due to db.session.commit () inside the first loop for… After the first pass in the variable users nothing would be left. And in the second pass there would be an error Instance