Issue
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.
Solution
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 %}
Answered By – Tobin
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0