title: Sqlalchemy: bulk correlated update to link tables
body: <p>I have two related tables already populated with data (using SQLAlchemy ORM). However, the individual records are not linked as of yet, i.e. the foreign key column is left empty. I need to bulk update the foreign key column, based a match on different column(s). </p>
  
  <p>To illustrate:</p>
  
  <pre><code>class Location(Base):
      __tablename__ = 'locations'
      id = Column(Integer, primary_key=True)
      x = Column(Float)
      y = Column(Float)
  
  
  class Stopover(Base):
      __tablename__ = 'stopovers'
      id = Column(Integer, primary_key=True)
      x = Column(Float)
      y = Column(Float)
      location_id = Column(Integer, ForeignKey("locations.id"))
      location = relationship("Location", backref=backref("stopovers"))
  </code></pre>
  
  <p>So essentially, I need to associate each of 20,000+ 'Stopover' records with a 'Location', by matching the 'x' and 'y' columns - i.e., bulk-update the location_id column.</p>
  
  <p>This code generates the _location_id_ correctly:</p>
  
  <pre><code>for stpvr in session.query(Stopovers).all():
         stpvr.location_id = session.query(Location.id).\
                             filter_by(x=stpvr.x).\
                             filter_by(y=stpvr.y).one()[0]
         session.commit()
  </code></pre>
  
  <p>However, it doesn't seem to be working - exploring the database through Sqliteman shows that the location_ids havent been updated. Besides, I'm guessing there must be a far more elegant way of approaching this.</p>
  
  <p>In the docs, I found <a href="http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#correlated-updates" rel="nofollow">Correlated Updates</a> to be closest to what I'm looking for. However, the docs only refer to the SQL Expression Language, whereas I'm using the ORM. I'm new to SQLAlchemy, and my attempts to translate the docs to ORM haven't been successful.</p>
  
  <p>I would appreciate any help in finding the most elegant way of performing this bulk update. Thanks in advance.</p>