flask-sqlalchemy: map data between 2 tables

Question

I have a following two tables in a SQLite3 database:

class Golfer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=True)
    scores = db.relationship('Score', backref='associated_golfer', lazy='dynaic')

class Score(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    score = db.Column(db.Integer, nullable=False)
    golfer_name = db.Column(db.String(100), db.ForeignKey('golfer.name'), nullable=False)

My Golfer and Score tables contain the following data respectively:

id|name
1|Johnny Metz
2|Cody Blick
3|Zack Bailey

id|score|golfer_name
1|71|Johnny Metz
2|72|Cody Blick
3|68|Cody Blick
4|70|Zack Bailey
5|73|Zack Bailey
6|66|Johnny Metz

I'm using Flask-SQLAlchemy to fetch the data from these tables. I want to map each name in the Golfer table to all of their scores in the Score table. Here's what I'm doing so far but it's very messy and inefficient, especially as my data grows:

golfers = Golfer.query.all()  # get list of all rows in Golfer table
scores = Score.query.all()  # get list of all rows in Score table
golfer_to_scores = {}
for golfer in golfers:
    golfer_to_scores[golfer] = []
    for score in scores:
        if score.golfer_name == golfer.name:
            golfer_to_scores[golfer].append(score)
print(golfer_to_scores)
# {<Golfer obj>: [<Score obj>, <Score obj>], ...}

Is there a simpler way using a SQL operation via Flask-SQLAlchemy? Maybe generate a column in the Golfer table which stores a list of all the scores (aka rows in Score table) associated with that golfer?

Danil Speransky Danil Speransky · Accepted Answer · 2017-08-29T21:13:16

If relationships are configured properly then just:

golfer_to_scores = {golfer: list(golfer.scores) for golfer in Golfer.query.all()}

Though it would do one query per golfer, so you may want to do a join.

flask-sqlalchemy: map data between 2 tables

1 Answers