title: Updating a field in an Association Object in SQLAlchemy
body: <p>I have an association object in SQLAlchemy that has some extra information (actually a single field) for 2 other objects.</p>
  
  <p>The first object is a <code>Photo</code> model, the second object is a <code>PhotoSet</code> and the association object is called <code>PhotoInSet</code> which holds the <code>position</code> attribute which tells us in what position is the <code>Photo</code> in the current <code>PhotoSet</code>.</p>
  
  <pre><code>class Photo(Base):
  
      __tablename__ = 'photos'
      id = Column(Integer, primary_key=True)
      filename = Column(String(128), index=True)
      title = Column(String(256))
      description = Column(Text)
      pub_date = Column(SADateTime)
  
  class PhotoInSet(Base):
  
      __tablename__ = 'set_order'
      photo_id = Column(Integer, ForeignKey('photos.id'), primary_key=True)
      photoset_id = Column(Integer, ForeignKey('photo_set.id'), primary_key=True)
      position = Column(Integer)
      photo = relationship('Photo', backref='sets')
  
      def __repr__(self):
          return '&lt;PhotoInSet %r&gt;' % self.position
  
  
  class PhotoSet(Base):
  
      __tablename__ = 'photo_set'
      id = Column(Integer, primary_key=True)
      name = Column(String(256))
      description = Column(Text)
      timestamp = Column(SADateTime)
      user_id = Column(Integer, ForeignKey('users.id'))
      user = relationship('User', backref=backref('sets', lazy='dynamic'))
      photo_id = Column(Integer, ForeignKey('photos.id'))
      photos = relationship('PhotoInSet', backref=backref('set', lazy='select'))
  </code></pre>
  
  <p>I have no problems creating a new <code>PhotoSet</code> saving the <code>position</code> and creating the relationship, which is (roughly) done like this:</p>
  
  <pre><code>    # Create the Set
      new_set = PhotoSet(name, user)
  
      # Add the photos with positions applied in the order they came
      new_set.photos.extend(
          [
              PhotoInSet(position=pos, photo=photo)
              for pos, photo in
              enumerate(photo_selection)
          ]
      )
  </code></pre>
  
  <p>But I am having a lot of trouble attempting to figure out how to update the <code>position</code> when the order changes.</p>
  
  <p>If I had, say, 3 <code>Photo</code> objects with ids: 1, 2, and 3, and positions 1, 2, and 3 respectively, would look like this after creation:</p>
  
  <pre><code>&gt;&gt;&gt; _set = PhotoSet.get(1)
  &gt;&gt;&gt; _set.photos
  [&lt;PhotoInSet 1&gt;, &lt;PhotoInSet 2&gt;, &lt;PhotoInSet 3&gt;]
  </code></pre>
  
  <p>If the order changes, (lets invert the order for this example), is there anyway SQLAlchemy can help me update the <code>position</code> value? So far I am not happy with any of the approaches I can come up with.</p>
  
  <p>What would be the most concise way to do this?</p>