title: Why is the order of SQLAlchemy InstrumentedList not persistent?
body: <p>Quick Summary: I want to have an ordered list of Addresses in SQLAlchemy.
  But the order of my list changes when I commit.
  Why does this happen and how can I change it?</p>
  
  <p>Long explanation:</p>
  
  <ol>
  <li>I start with a list of Address attached to a User object.</li>
  <li>Then I replace the first element of the "addresses" list with a
  new Address.</li>
  <li>Then I print the list of addresses ... so far the order is what I would expect.</li>
  <li>Finally I commit. After my commit I do a query but the order of my
  addresses list has changed.</li>
  </ol>
  
  <p>So is this just something about databasing in general that I don't understand? Or does a SQLAlchemy InstrumentedList not act like an actual list? I thought I could change the order of elements in a relationship but I don't see how.</p>
  
  <hr>
  
  <pre><code>from sqlalchemy import Column, Integer, String
  from sqlalchemy import create_engine
  from sqlalchemy import ForeignKey
  from sqlalchemy.orm import relationship
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker
  
  Base = declarative_base()
  Session = sessionmaker()
  
  class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
      name = Column(String(50))
      fullname = Column(String(50))
      password = Column(String(12))
  
      addresses = relationship("Address", back_populates="user")
  
      def __repr__(self):
          return "&lt;User(name='%s', fullname='%s', password='%s')&gt;" % (
                                  self.name, self.fullname, self.password)
  
  class Address(Base):
      __tablename__ = 'addresses'
      id = Column(Integer, primary_key=True)
      email_address = Column(String, nullable=False)
      user_id = Column(Integer, ForeignKey('users.id'))
  
      user = relationship("User", back_populates="addresses")
  
      def __repr__(self):
          return "&lt;Address(email_address='%s')&gt;" % self.email_address
  
  
  if __name__ == "__main__":
      engine = create_engine('sqlite:///:memory:', echo=False)
      Session.configure(bind=engine)
      Base.metadata.create_all(engine)
      session = Session()
  
      user = User(name='ed', fullname='Ed Jones', password='edspassword')
      user.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
      session.add(user)
      session.commit()
  
      user = session.query(User).filter_by(name='ed').first()
  
      print("Current order of addresses list at start.")
      print(user.addresses)
      print()
  
      new_primary_address = Address(email_address='primary@google.com')
      user.addresses[0] = new_primary_address
  
      print("Current order of addresses list before commit.")
      print("But after chaning addresses[0].")
      print(user.addresses)
      print()
  
      session.commit()
  
      user = session.query(User).filter_by(name='ed').first()
  
      print("Current order of addresses list after commit.")
      print(user.addresses)
      print()
  
      print("Why is the order of the InstrumentedList not persistent?")
      print("Isn't persistent order what makes a list a list?")
  </code></pre>