How to loop through related tables with Jinja2?

Question

Description

I am learning Flask and I created a database which contains 3 tables which are related between them:

1. Company: info about the company I worked for (made up :) )
2. Job: info about the role I had in each company
3. Task: info about the tasks i had in each job i had in each company.

Here is the code:

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///work_experience.db'
db = SQLAlchemy(app)

class Company(db.Model):
    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    start_date = db.Column(db.DateTime, nullable=False)
    end_date   =  db.Column(db.DateTime, nullable=False, default=None)
    location   = db.Column(db.String(20), nullable=False, default='N/A')
    roles = db.relationship('Role', backref='company_')

    def __repr__(self):
        return "id: {}, name: {}, start_date: {}, end_date: {}, location: {}, roles: {}".format(self.id, self.name, self.start_date, self.end_date, self.location, self.roles)

class Role(db.Model):
    id   = db.Column(db.Integer, primary_key=True)
    position = db.Column(db.String(100), nullable=False)
    start_date = db.Column(db.DateTime, nullable=False)
    end_date   =  db.Column(db.DateTime, nullable=False)
    company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    tasks = db.relationship('Task', backref='role_')

    def __repr__(self):
        return "id: {}, position: {}, start_date: {}, end_date: {}, company_id: {}, tasks: {}".format(self.id, self.position, self.start_date, self.end_date, self.company_id, self.tasks)

class Task(db.Model):
    id   = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.String(100), nullable=False)
    start_date = db.Column(db.DateTime, nullable=False)
    end_date   =  db.Column(db.DateTime, nullable=False)
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'))

    def __repr__(self):
        return "id: {}, description: {}, start_date: {}, end_date: {}, role_id: {}".format(self.id, self.description, self.start_date, self.end_date, self.role_id)

db.create_all()

items = Company.query.all()
@app.route('/working_experience')
def work():
    return render_template('working_experience.html', items=items)

GOAL

My I idea is to have a for loop wirtten with Jinja2 in such a way that I can print for each company the job that I had and what task I accomplished. After populating the database with some data, I was able to achive my goal in python with the following lines of code:

for c in Company.query.all():
    print(c.name)
    for r in c.roles:
        print('\t',r.position)
        for t in r.tasks:
            print('\t\t',t.description)

WHAT I TRIED

However when I try to achieve the same result using Jinja:

{% for company in items %}
    <h2>{{ company.name }}</h2>
    {% for role in company.roles %}
        <h3>{{ role.position }}</h3>
        {% for task in role.tasks %}
            <h4>{{ task.description }}</h4>
        {% endfor %}
    {% endfor %}
{% endfor %}

I get the folliwng error:

sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 8324 and this is thread id 15588. [SQL: SELECT role.id AS role_id, role.position AS role_position, role.start_date AS role_start_date, role.end_date AS role_end_date, role.company_id AS role_company_id FROM role WHERE ? = role.company_id] [parameters: [{'%(2378184897992 param)s': 1}]] (Background on this error at: http://sqlalche.me/e/f405)

Can you suggest me a smart and elegant way to achieve my goal? Thanks in advance.

Answer 1

I was able to achive my goal in this (NON ELEGANT) way. First of all I queried all the content from all the tables and placed them as an input variable for the html rendering:

c = Company.query.all()
r = Role.query.all()
t = Task.query.all()

@app.route('/working_experience')
def work():
   return render_template('working_experience.html', c=c, r=r, t=t)

Then I added the Jinja nested for loops like so:

{% for company in c %}
    <h2>{{ company.name }}</h2>

    <ul>
    {% for role in r %}
        {% if company.id == role.company_id %}
            <li><h6>{{ role.position }}</h6></li>

            <ul>
            {% for task in t %}
                {% if role.id == task.role_id %}
                    <li><h6>{{ task.description }}</h6></li>
                {% endif %} 
            {% endfor %}
            </ul>
        {% endif %} 
    {% endfor %}
    </ul>
    <hr>

{% endfor %}

If you are aware of a better solution please let me know and I woudl really apreciate it!