Sqlalchemy query is being modified after execution-CodePudding

I am trying to build a messages system for a flask app. The messages are stored in a database. Each entry has a "Viewed" or "Unviewed" property. I would like unviewed messages to appear differently on the page than viewed. The logic is as follows. When a message is created, it obtains a value of "Unviewed". Once the user travels to the messages page, all unviewed messages are updated with the value viewed, so that the next time the user travels to the page they are read as viewed. The problem is, I query unviewed messages and store to a variable to pass to the frontend BEFORE I commit the change from viewed to unviewed. But when I return the variable of unviewed messages, it is empty, suggesting that all messages are viewed. The code is below.

@app.route("/messages", methods=['GET', 'POST'])
@login_required
def message_logs():
    unviewed_messages = Messages.query.filter(Messages.username==current_user.username).filter(Messages.viewed=='Unviewed')
    viewed_messages = Messages.query.filter(Messages.username==current_user.username).filter(Messages.viewed=='Viewed')
    read_messages = Messages.query.filter_by(username=current_user.username).update({Messages.viewed : 'Viewed'})
    db.session.commit()
return render_template('messages.html', title='messages', viewed_messages=viewed_messages, unviewed_messages=unviewed_messages)

If I put a print statement below the unviewed messages variable querying a specific unread row ID, it successfully prints the ID, proving that the messages are still unread at that point in the code. However, the "unviewed_messages" variable that is supposed to be containing these unviewed messages is returning empty to the front end. My theory is that the variable is just storing the query and not actually executing it, and it is being executed later, after "Viewed" has already updated the unviewed rows. Does anyone know why this is? Or if it is something else can someone explain what I am missing? Thanks

CodePudding user response：

The unviewed and viewed queries will not be executed until the template is rendered, which is after the update has been committed.You could execute them before then by calling .all() on them, but that won't help as the resulting objects will be expired on commit. This means that if accessed after the commit their state will be reloaded from the database.

Your options are:

create your sessions with expire_on_commit=False. You can do this with db = SQLAlchemy(app, session_options={'expire_on_commit': False}) but be aware that this is a global change.
excecute the query before commit and then load the values that you need into a list of dicts or similar and have the template use this list.
Maintain command-query separation by doing the updates in one request and the queries in another.