title: SQL Alchemy, how to insert data into two tables and reference foreign key?
body: <p>I am inserting a list of python dictionaries into a Postgres database using SQL Alchemy (via Flask_sqlalchemy). </p>
  
  <p>One of the tables is a list of all unique items (table 1), while the second is a time series of data related to an item (table2).</p>
  
  <p>In essence, I want to insert any new row (with unique hash) in to table 1, then insert it's data to table 2. If it already exists in table 1, just insert the "child" in table 2 referencing the entry in table 1. </p>
  
  <p>This is one item in the list, the list has a few hundred of these.</p>
  
  <pre><code>{'placement': '5662448s608653114', 't1': datetime.datetime(2018, 4, 15, 17, 47, 7, 434982), 't2': datetime.datetime(2018, 4, 25, 17, 47, 7, 434994), 'camp_id': 1, 'clicks': '0', 'visits': '3', 'conversions': '0', 'revenue': '0'}
  </code></pre>
  
  <p>I would like to insert 5662448s608653114 into <strong>table1</strong>, and then insert all the other data into <strong>table2</strong>, where i reference the item not by 5662448s608653114, but by it's id in table 1</p>
  
  <p>So I'd get:</p>
  
  <p>Table 1:</p>
  
  <pre><code>____________________
  1| 5662448s608653114
  2| 5520103
  </code></pre>
  
  <p>Table 2:</p>
  
  <pre><code>ID | Pl id | T1 | T2 | cost | revenue | clicks
  _______________________________________________
  499| 1     |
  500| 2     |
  </code></pre>
  
  <p>I tested this, which does not work:</p>
  
  <pre><code>    def write_tracker_data(self):
  
      for item in self.data:
          ts = Placements(placement_ts_hash=item["placement"])
          pl = TrackerPlacementData(placement_id=ts.id, t1=item["t1"], t2=item["t2"], camp_id=1,  revenue=item["revenue"], clicks=item["clicks"], conversions=item["conversions"])
          db.session.add(pl)
  
      db.session.commit()
  </code></pre>
  
  <p>The above code inserts the data, but with none instead of the id from the Table 1. It also doesn't seem very efficient, you know that feeling when something can definitely be done a better way ... </p>
  
  <p>Here's the model classes for reference:</p>
  
  <pre><code>class Placements(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      traffic_source = db.Column(db.Integer, db.ForeignKey('ts_types.id'))
      placement_ts_hash = db.Column(db.String, index=True)
      placement_url = db.Column(db.String)
      placement_type = db.Column(db.String)
  
      # Relationship betwwen unique placement table and tracker_placeemnt_data
      tracker_data = db.relationship("TrackerPlacementData", backref="placement_hash")
  
  class TrackerPlacementData(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      t1 = db.Column(db.DateTime(timezone=True))
      t2 = db.Column(db.DateTime(timezone=True), index=True)
      camp_id = db.Column(db.Integer, db.ForeignKey('campaigns.id'), nullable=False)
      placement_id = db.Column(db.Integer, db.ForeignKey('placements.id'), nullable=True, index=True)
      revenue = db.Column(db.Float)
      clicks = db.Column(db.Integer)
      conversions = db.Column(db.Integer)
  </code></pre>
  
  <p>Thanks in advance.</p>
  
  <p>Edit: This <em>works</em>, but it doesn't seem very good due to a new session for every item in the loop :/</p>
  
  <p>def write_tracker_data(self):</p>
  
  <pre><code>for item in self.data:
      ts = Placements(placement_ts_hash=item["placement"])
      db.session.add(ts)
      db.session.commit()
  
      pl = TrackerPlacementData(placement_hash=ts, t1=item["t1"], t2=item["t2"], camp_id=1,
                                revenue=item["revenue"], clicks=item["clicks"], conversions=item["conversions"])
      db.session.add(pl)
  
      db.session.commit()
  </code></pre>