DatabaseSessionIsOver with Pony ORM due to lazy loading?

Question

I am using Pony ORM for a flask solution and I've come across the following.

Consider the following:

@db_session
def get_orders_of_the_week(self, user, date):
    q = select(o for o in Order for s in o.supplier if o.user == user)
    q2 = q.filter(lambda o: o.date >= date and o.date <= date+timedelta(days=7))
    res = q2[:]

    #for r in res:
    #    print r.supplier.name

    return res

When I need the result in Jinja2 -- which is looks like this

{% for order in res %}
    Supplier: {{ order.supplier.name }}
{% endfor %}

I get a

DatabaseSessionIsOver: Cannot load attribute Supplier[3].name: the database session is over

If I uncomment the for r in res part, it works fine. I suspect there is some sort of lazy loading that doesn't get loaded with res = q2[:]. Am I completely missing a point or what's going on here?

Answer 1

I just added prefetch functionality that should solve your problem. You can take working code from the GitHub repository. This feature will be part of the upcoming release Pony ORM 0.5.4.

Now you can write:

q = q.prefetch(Supplier)

or

q = q.prefetch(Order.supplier)

and Pony will automatically load related supplier objects.

Below I'll show several queries with prefetching, using the standard Pony example with Students, Groups and Departments.

from pony.orm.examples.presentation import *

Loading Student objects only, without any prefetching:

students = select(s for s in Student)[:]

Loading students together with groups and departments:

students = select(s for s in Student).prefetch(Group, Department)[:]

for s in students: # no additional query to the DB is required
    print s.name, s.group.major, s.group.dept.name

The same as above, but specifying attributes instead of entities:

students = select(s for s in Student).prefetch(Student.group, Group.dept)[:]

for s in students: # no additional query to the DB is required
    print s.name, s.group.major, s.group.dept.name

Loading students and its courses (many-to-many relationship):

students = select(s for s in Student).prefetch(Student.courses)

for s in students:
    print s.name
    for c in s.courses: # no additional query to the DB is required
        print c.name

As a parameters of the prefetch() method you can specify entities and/or attributes. If you specified an entity, then all to-one attributes with this type will be prefetched. If you specified an attribute, then this specific attribute will be prefetched. The to-many attributes are prefetched only when specified explicitly (as in the Student.courses example). The prefetching goes recursively, so you can load long chain of attributes, such as student.group.dept.

When object is prefetched, then by default all of its attributes are loaded, except lazy attributes and to-many attributes. You can prefetch lazy and to-many attributes explicitly if it is needed.

I hope this new method fully covers your use-case. If something is not working as expected, please start new issue on GitHub. Also you can discuss functionality and make feature requests at Pony ORM mailing list.

P.S. I'm not sure that repository pattern that you use give your serious benefits. I think that it actually increase coupling between template rendering and repo implementation, because you may need to change repo implementation (i.e. add new entities to prefetching list) when template code start using of new attributes. With the top-level @db_session decorator you can just send query result to the template and all happens automatically, without the need of explicit prefetching. But maybe I'm missing something, so I will be interested to see additional comments about the benefits of using the repository pattern in your case.

Answer 2

This happens because you're trying to access the related object which was not loaded and since you're trying to access it outside of the database session (the function decorated with the db_session), Pony raises this exception.

The recommended approach is to use the db_session decorator at the top level, at the same place where you put the Flask's app.route decorator:

@app.route('/index')
@db_session
def index():
    ....
    return render_template(...)

This way all calls to the database will be wrapped with the database session, which will be finished after a web page is generated.

If there is a reason that you want to narrow the database session to a single function, then you need to iterate the returning objects inside the function decorated with the db_session and access all the necessary related objects. Pony will use the most effective way for loading the related objects from the database, avoiding the N+1 Query problem. This way Pony will extract all the necessary objects within the db_session scope, while the connection to the database is still active.

--- update:

Right now, for loading the related objects, you should iterate over the query result and call the related object attribute:

for r in res:
    r.supplier.name 

It is similar to the code in your example, I just removed the print statement. When you 'touch' the r.supplier.name attribute, Pony loads all non-lazy attributes of the related supplier object. If you need to load lazy attributes, you need to touch each of them separately.

Seems that we need to introduce a way to specify what related objects should be loaded during the query execution. We will add this feature in one of the future releases.