Why is the order of SQLAlchemy InstrumentedList not persistent?

Question

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?

Long explanation:

I start with a list of Address attached to a User object.
Then I replace the first element of the "addresses" list with a new Address.
Then I print the list of addresses ... so far the order is what I would expect.
Finally I commit. After my commit I do a query but the order of my addresses list has changed.

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.

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 "" % (
                                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 "" % 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?")

Why is the order of SQLAlchemy InstrumentedList not persistent?

1 Answers