DatabaseSessionIsOver with Pony ORM due to lazy loading?

Question

I use Pony ORM to solve the jar, and I came 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 a result in Jinja2 - it looks like

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

I get

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

If I uncomment the for r in res , it works fine. I suspect there is some kind of lazy loading that doesn't load with res = q2[:] . Have I completely missed the point or what is happening here?

Answer 1

This is because you are trying to access a related object that has not been loaded, and since you are trying to access it outside the database session (a function decorated with db_session ), Pony raises this exception.

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

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

Thus, all calls to the database will be wrapped by the database session, which will be completed after the creation of the web page.

If there is a reason why you want to narrow the database session to a single function, you need to iterate the returned objects inside the function decorated with db_session and gain access to all the necessary related objects. Pony will use the most efficient way to load related objects from the database, avoiding the problem of querying N + 1. Thus, Pony will retrieve all the necessary objects in the db_session area while the database connection is still active.

--- update:

Right now, to load related objects, you should iterate over the result of the query and call the associated attribute of the object:

 for r in res: r.supplier.name 

It looks like the code in your example, I just deleted the print statement. When you touch the r.supplier.name attribute, Pony loads all the non-default attributes of the associated supplier object. If you need to load lazy attributes, you need to touch each of them separately.

It looks like we need to introduce a way to indicate which related objects should be loaded at query time. We will add this feature in one of the future releases.

Answer 2

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

Now you can write:

 q = q.prefetch(Supplier) 

or

 q = q.prefetch(Order.supplier) 

and Pony will automatically load the associated supplier objects.

Below I will show some prefetched queries using the standard Pony example with students, groups, and departments.

 from pony.orm.examples.presentation import * 

Loading only student objects without preliminary selection:

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

Loading students 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 

Same as above, but specifying attributes instead of objects:

 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 their courses (many-to-many relationships):

 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 

You can specify objects and / or attributes as parameters of the prefetch() method. If you specify an object, then all the one-to-one attributes with this type will be pre-programmed. If you specified an attribute, this special attribute will be preselected. The to-many attributes are preselected only when explicitly specified (as in Student.courses example). Prefetching is recursive, so you can load a long chain of attributes like student.group.dept .

When an object is preprogrammed, by default all its attributes are loaded, with the exception of lazy attributes and many attributes. You can pre-filter lazy and many attributes explicitly if necessary.

I hope this new method fully covers your use case. If something does not work properly, it will start a new problem on GitHub . You can also discuss features and perform feature requests on the Pony ORM mailing list .

PS I'm not sure if the repository template that you use gives you serious benefits. I think this actually increases the connection between rendering the template and the implementation of the repo, because you may need to change the implementation of the repo (i.e. add new entities to the prefetch list) when the template code starts using new attributes. With the top-level decorator @db_session you can simply send the result of the request to the template, and everything will happen automatically, without the need for explicit prefetching. But maybe I am missing something, so I will be interested to see additional comments about the benefits of using the repository template in your case.