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.
