Marlen T. B. Marlen T. B. -4 years ago 170
Python Question

Why is the order of SQLAlchemy InstrumentedList not persistent?

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:

  1. I start with a list of Address attached to a User object.

  2. Then I replace the first element of the "addresses" list with a
    new Address.

  3. Then I print the list of addresses ... so far the order is what I would expect.

  4. 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 though 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 "<User(name='%s', fullname='%s', password='%s')>" % (, 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(''))

user = relationship("User", back_populates="addresses")

def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address

if __name__ == "__main__":
engine = create_engine('sqlite:///:memory:', echo=False)
session = Session()

user = User(name='ed', fullname='Ed Jones', password='edspassword')
user.addresses = [Address(email_address=''), Address(email_address='')]

user = session.query(User).filter_by(name='ed').first()

print("Current order of addresses list at start.")

new_primary_address = Address(email_address='')
user.addresses[0] = new_primary_address

print("Current order of addresses list before commit.")
print("But after chaning addresses[0].")


user = session.query(User).filter_by(name='ed').first()

print("Current order of addresses list after commit.")

print("Why is the order of the InstrumentedList not persistent?")
print("Isn't persistent order what makes a list a list?")

Answer Source

It is "databasing" in general. An InstrumentedList does act like an actual list with the added ORM instrumentation Python side, but when you commit the Session's default behaviour is to expire all database loaded state of ORM-managed attributes, and so the list has to be refreshed upon next access. This means that a SELECT such as

2017-05-21 13:32:31,124 INFO sqlalchemy.engine.base.Engine SELECT AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id

is issued to fetch the list contents. In SQL the order of a SELECT is unspecified, if not explicitly chosen, so you may or may not get the items in the same order as before. Also note that the ORM operation

user.addresses[0] = new_primary_address

translates to an UPDATE that sets the user_id of the old address tuple to NULL and INSERTs a new one in the table, so you'd not get the order you thought, even if the rows were returned in insertion order.

If the order of addresses matters to you, you must choose ordering. Use the order_by parameter of relationship:

class User(Base):
    addresses = relationship("Address", back_populates="user",

would order the addresses by email address, when fetched. SQLAlchemy also provides (thank you for digging that up) a helper collection class for mutable ordered relationships: orderinglist, which helps managing index/position on changes, if used as the ordering.

It seems you'd like the order of addresses to signify which is the primary address of a user. A separate flag column would work for this better.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download