SqlAlchemy query in a template page

Question

I have a query like this:

session.query(System).filter_by(id_system = s.id_system).join(Command).filter_by(id_command=c.id_command).first()

I'd like to do this query in a template page (I'm using mako), but it doesn't work:

% for c in s.commands:
    code = session.query(System).filter....
% endfor

What is best way to do a query in pages? Or is it not possible?

Answer 1

Template is not a right place to do such operations. All business logic needs to be included into controllers according to MVC (or RV as Pyramid) paradigm.

Do query in your view function:

results = DBSession.query(model.Articles).all()
return dict(status=True, results=results)

or even better create module which contains common database operations (then import function and call it in view function) and return results to template by

import your_project.lib.dbfunctions
results = dbfunctions.get_articles()
return dict(status=True, results=results)

than use them into template:

<div>
% for r in results:
    ${r}
% endfor
</div>

I hope it will help you.

Answer 2

While it may be possible by injecting the session and other needed functions into the template context, this is not the right way to do things. The data, for the most part, should be handled outside the template, then passed to the context when rendering.

The view/function that is calling render for this template should be making this query and passing it to the template.