SQLAlchemy queries - when do they execute?

Question

I've got a pyramid web page setup. One of the views does something like this:-

sql_list = do_a_query()
handle_a_post_request(request)
return dict(sql_list=sql_list)

def do_a_query():
    request.db.query(WhatIAmLookingFor)

The (mako, but I think that's irrelevant) template then handles displaying my web page based on the data in sql_list.

In the handle_a_post_request function I modify the session (and run commit()) based on the post request. These modifications show up in the resulting page, which suggests to me that the query itself actually 'runs' or executes when it's called in my template. Since I'm using mako the calling is done using:-

% for row in sql_list:
<tr>
    <td> ${row[0]} </td>
    <td> ${row[1]} </td>
    <td> ${row[2]} </td>
</tr>

Is my conclusion correct? When exactly is an sqlalchemy query 'actualized', such that changes to the session after that point no longer show up in the 'result' of the query? Or is my understanding fundamentally flawed somewhere?

What concerns me is that in future a change to the do_a_query() function (for example, iterating over its results for some pre-processing prior to display) will change behaviour of my web page. Of course the 'right' answer is just to move handle_a_post_request() earlier, but I'd like a thorough understanding of what is happening first.

Answer 1

A query is generally executed when you iterate through a Query instance. Query.all() is a convenience method of building a list from the Query iterator. (Queries are also executed automatically when you attempt to load expired attributes, but for the purpose of this answer let's ignore those.)

It's important to note that at no point are changes to the session invisible. By default, SQLAlchemy ensures that the session is flushed at appropriate times such that changes to the session are reflected on the next query.

In your case, you should return the result from .all() in do_a_query() instead of returning the query itself.