title: flask-sqlalchemy: map data between 2 tables
body: <p>I have a following two tables in a SQLite3 database:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>My <code>Golfer</code> and <code>Score</code> tables contain the following data respectively:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm using <a href="http://flask-sqlalchemy.pocoo.org/2.1/" rel="nofollow noreferrer">Flask-SQLAlchemy</a> to fetch the data from these tables. I want to map each name in the <code>Golfer</code> table to all of their scores in the <code>Score</code> table. Here's what I'm doing so far but it's very messy and inefficient, especially as my data grows:</p>
  
  <pre><code>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)
  # {&lt;Golfer obj&gt;: [&lt;Score obj&gt;, &lt;Score obj&gt;], ...}
  </code></pre>
  
  <p>Is there a simpler way using a SQL operation via Flask-SQLAlchemy? Maybe generate a column in the <code>Golfer</code> table which stores a list of all the scores (aka rows in <code>Score</code> table) associated with that golfer?</p>