Python flask - SQLAlchemy query results not passing to jinja template as expected

Question

I'm reasonably new to Python / Flask / SQLAlchemy and have been searching many SO posts. There are plenty which sound similar to my issue but the resolutions don't work for me.

I've created three models in SQLAlchemy - Individual, Parents and FamilyLink. The models are defined within this code (I've been asked to post the full code below it):

class FamilyLink(db.Model):
    __tablename__ = "family_link"

    individual_id = db.Column(db.Integer, db.ForeignKey("individual.id"), primary_key=True)
    parents_id = db.Column(db.Integer, db.ForeignKey("parents.id"), primary_key=True)

    def __init__(self, individual_id, parents_id):
        self.individual_id = individual_id
        self.parents_id = parents_id


class Individual(db.Model):
    __tablename__ = "individual"

    id = db.Column(db.Integer, primary_key=True)
    forenames = db.Column(db.Text)
    surname = db.Column(db.Text)
    fullname = db.Column(db.Text)

    parents = db.relationship("Parents", secondary=FamilyLink.__table__)

    def __init__(self, surname, fullname=None, forenames=None):
        self.forenames = forenames
        self.surname = surname
        self.fullname = fullname

    # def __repr__(self):
    #     return (str(self.fullname))


class Parents(db.Model):
    __tablename__ = "parents"

    id = db.Column(db.Integer, primary_key=True)
    father_id = db.Column(db.Integer, db.ForeignKey("individual.id"))
    mother_id = db.Column(db.Integer, db.ForeignKey("individual.id"))

    children = db.relationship("Individual", secondary=FamilyLink.__table__)

    def __init__(self, father_id=None, mother_id=None):
        self.father_id = father_id
        self.mother_id = mother_id

db.create_all()

@app.route("/", methods=["GET", "POST"])
def index():
    form = AddIndividual()

    if request.method == "POST":

        if request.form.get("addfather") == "Add":
            add_father(form)

            return redirect(url_for("show_family", parentsid=session["partners.id"]))

        if request.form.get("addmother") == "Add":
            add_mother(form)

            return redirect(url_for("show_family", parentsid=session["partners.id"]))

    return render_template("home.html", form=form)


@app.route("/family/<parentsid>", methods=["GET", "POST"])
def show_family(parentsid):
    form = AddIndividual()

    childlist = db.session.query(Individual.fullname).join(FamilyLink).filter(Parents.id == parentsid).all()
    children = [c[0] for c in childlist]

    try:
        father_fullname = Individual.query.get(Parents.query.get(parentsid).father_id).fullname
    except:
        father_fullname = None

    try:
        mother_fullname = Individual.query.get(Parents.query.get(parentsid).mother_id).fullname
    except:
        mother_fullname = None

    if request.method == "POST":

        if request.form.get("addfather") == "Add":
            add_father(form)

            return redirect(url_for("show_family", parentsid=session["partners.id"]))

        if request.form.get("addmother") == "Add":
            add_mother(form)

            return redirect(url_for("show_family", parentsid=session["partners.id"]))

        if request.form.get("addchild") == "Add":
            child_forenames = form.child_forenames.data
            child_surname = form.child_surname.data
            child_fullname = fullname(child_forenames, child_surname)

            new_child = Individual(child_surname, child_fullname, child_forenames)
            db.session.add(new_child)

            db.session.commit()
            db.session.flush()

            session["child.id"] = new_child.id

            link_child(individual_id=session["child.id"], parents_id=session["partners.id"])

            children = Individual.query.join(FamilyLink, FamilyLink.individual_id == Individual.id). \
                join(Parents, Parents.id == FamilyLink.parents_id). \
                add_columns(Individual.id, Individual.forenames, Individual.surname, Individual.fullname,
                            Parents.id).all()

            # children = db.session.query(Individual).join(FamilyLink).filter(Parents.id == parentsid).all()
            # children = [c[0] for c in childlist]

            print(children)
            print(type(children))

            # for individual, familylink, parents in children:
            #     print(individual.fullname, parents.father_id.forenames)
            session["children"] = children

            return redirect(url_for("show_family", parentsid=session["partners.id"], children=children))

    return render_template("home.html", form=form, father_fullname=father_fullname, mother_fullname=mother_fullname,
                           children=children)


def fullname(first, last):
    return first + " " + last


def create_partners(father_id=None, mother_id=None):
    if db.session.query(Parents).filter_by(father_id=father_id,
                                           mother_id=mother_id).scalar() is None:
        parents = Parents(father_id, mother_id)
        db.session.add(parents)
        db.session.commit()
        db.session.flush()

        session["partners.id"] = parents.id
        parentsid = parents.id
        return parentsid


def update_partners(partners_id, father_id=None, mother_id=None):
    if db.session.query(Parents).filter_by(id=partners_id, father_id=father_id).scalar() is None:
        updated_father = db.session.query(Parents).get(partners_id)
        parentsid = session["partners.id"]
        updated_father.father_id = session["father.id"]
        db.session.commit()
        db.session.flush()
        return parentsid
    elif db.session.query(Parents).filter_by(id=partners_id, mother_id=mother_id).scalar() is None:
        updated_mother = db.session.query(Parents).get(partners_id)
        parentsid = session["partners.id"]
        updated_mother.mother_id = session["mother.id"]
        db.session.commit()
        db.session.flush()
        return parentsid


def link_child(individual_id, parents_id):
    if db.session.query(FamilyLink).filter_by(individual_id=individual_id,
                                              parents_id=parents_id).scalar() is None:
        c = FamilyLink(individual_id, parents_id)
        db.session.add(c)
        db.session.commit()
        db.session.flush()


def add_father(form):
    father_forenames = form.father_forenames.data
    father_surname = form.father_surname.data
    father_fullname = fullname(father_forenames, father_surname)

    new_father = Individual(father_surname, father_fullname, father_forenames)
    db.session.add(new_father)

    db.session.commit()
    db.session.flush()

    session["father.id"] = new_father.id
    session["father_fullname"] = father_fullname

    if session.get("mother.id") is None:
        create_partners(father_id=session["father.id"], mother_id=None)
    else:
        update_partners(partners_id=session["partners.id"], father_id=session["father.id"],
                        mother_id=session["mother.id"])

    return


def add_mother(form):
    mother_forenames = form.mother_forenames.data
    mother_surname = form.mother_surname.data
    mother_fullname = fullname(mother_forenames, mother_surname)

    new_mother = Individual(mother_surname, mother_fullname, mother_forenames)
    db.session.add(new_mother)

    db.session.commit()
    db.session.flush()
    session["mother.id"] = new_mother.id

    if session.get("father.id") is None:
        create_partners(father_id=None, mother_id=session["mother.id"])
    else:
        update_partners(partners_id=session["partners.id"], father_id=session["father.id"],
                        mother_id=session["mother.id"])

    return


if __name__ == "__main__":
    app.run(debug=True)

Everything works in terms of creating individuals and linking the relationships correctly.

In order to find all of the Individual IDs which belong to a pair of parents, I've run the following query, ready to pass it to my jinja template:

children = db.session.query(Individual).join(FamilyLink).filter(Parents.id == parentsid).all()

I then pass the result of this query to the jinja template:

return redirect(url_for("show_family", parentsid=session["partners.id"], children=children))

The jinja template reads:

{% for child in children %}
    <li class="list-group-item"><h3>{{ child.forenames }}</h3></li>
{% endfor %}

I just get blank rows - no errors - just blank rows. The database entries all save correctly.

I ultimately want to be able to use attributes like child.forenames and child.id in the template.

Any ideas what I'm doing wrong please?

Thanks for any help.

Answer 1

This is what your query should look like (I haven't tested it, but you could use it to refine your query).

children = Individual.query.join(FamilyLink, FamilyLink.individual_id == Individual.id). \
    join(Parents, Parents.id == FamilyLink.parents_id). \
    add_columns(Individual.id, Individual.forenames, Individual.surname, Individual.fullname, Parents.id).all()

Jinja Template:

{% for child in children %}
    <li class="list-group-item"><h3>{{ child.forenames }} {{ child.surname }} {{ child.fullname }}</h3></li>
{% endfor %}