0

I am learning Flask and I want to create 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')
    jobs = db.relationship('Job', backref='company_')

class Job(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='job_')

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)
    job_id = db.Column(db.Integer, db.ForeignKey('job.id'))

db.create_all()

My I idea is to have a for loop (which eventualy I will write with Jinja2, but for now python code is fine) in such a way that I can print for each company the job that I had and what task I accomplished. Of course I could have all the data in just one table but I am trying to learn.

Therefore the output should be something like this:

for company in Companies:
    for job in Jobs:
        for Task in Tasks:
            print(company, job, task)

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

Federico Gentile
  • 5,650
  • 10
  • 47
  • 102
  • you could add a static variable inside the classes which will be a list. Every time the constructor is called you can do something like `COMPANIES.append(self)` inside the `__init__` function. If you want to use the database then you should do something like `Company.query.all()` which will return a list of all companies inside your db. – geoph9 Jun 02 '20 at 14:43
  • @geoph9 Hello thanks for the comment! Would you be able to suggest an aswer using the second approach please? – Federico Gentile Jun 02 '20 at 14:47
  • The usual way is to not use nested for loops at all, but let the database produce the data you need: https://stackoverflow.com/questions/6044309/sqlalchemy-how-to-join-several-tables-by-one-query. This is called a JOIN in SQL and it produces a new table by combining 2 tables (on some criteria). You can then again join against the new table, producing yet another table. Of course the database system does not actually do it that way under the hood, but you do not need to care, usually. – Ilja Everilä Jun 02 '20 at 17:24

1 Answers1

3

If I understand correct you want a list of all your Companies, Jobs, Tasks. You can get these by querying your database (assuming you have already inserted some tuples):

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)
Federico Gentile
  • 5,650
  • 10
  • 47
  • 102
geoph9
  • 357
  • 3
  • 18
  • Great thanks a lot!! I think I made it! Btw, would you be able to elaborate more on the use of __repr__? I have added the following lines of code for each class: def __repr__(self): return self._repr(id = self.id, position = self.position, start_date = self.start_date, end_date = self.end_date, company_id = self.company_id, tasks = self.tasks). But I am not sure what to do next... how do I access the values then? – Federico Gentile Jun 02 '20 at 15:31
  • `__repr__` is something like `toString()` in Java. For example the content of `def __repr__(self):` could be: `return "ID: {}, name: {}, start_date: {}, end_date: {}, location: {}, jobs: {}".format(self.id, self.name, self.start_date, self.end_date, self.location, self.jobs)`. So whenever you do `print(company)` the output would be `ID: 1, name: Test, ...`. – geoph9 Jun 02 '20 at 15:38
  • 1
    As a note this produces the Cartesian product company × job × task, so all jobs are united with all companies, and all tasks are united with all jobs, which likely is not "for each company the job that I had and what task I accomplished", unless you had 1 job in 1 company only, or all the jobs in all the companies and so on. A single SQL query (instead of 3) could give you the right result. Read on how to use JOIN – Ilja Everilä Jun 02 '20 at 17:12
  • @IljaEverilä Thanks for you comment! mmm... I actaully tried with an arbitrary number of tasks per job and companies and it seems to work fine for me: 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) – Federico Gentile Jun 03 '20 at 12:15
  • @geoph9 if you are interested there is a follow up question that I posted: https://stackoverflow.com/questions/62172093/how-to-loop-through-related-tables-with-jinja2 :) – Federico Gentile Jun 03 '20 at 12:17
  • It seems you tried and edited this answer to use the configured ORM relationships, instead of fetching all companies, jobs, and tasks. That's an entirely valid approach and does fulfill your original requirement. Just keep in mind that depending on the relationship configuration the ORM will fire a query for each company to fetch jobs, and queries for each job to fetch tasks. Depending on the amount of data this can be just fine, but it can also become a bottleneck. If it does, either read about using eager loading, or—again—joins. – Ilja Everilä Jun 03 '20 at 12:25