Session commit changes the order of a relationship elements

[Stuart Bannerman]

If you have a model that contains a basic relationship like so:

from sqlalchemy import Column, Integer, String, Boolean, ForeignKey, Table, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Thing(Base)

    parts = relationship('Part', backref='thing')

class Part(Base):

    name = Column(String, primary_key=True, default='')

And then you do:

thing = Thing()

part1 = Part(name="Part1")
part2 = Part(name="Part2") 

# Note we do part2 then part1
thing.parts.append(part2)
thing.parts.append(part1)

print thing.parts
# [Part2, Part1]

session.commit()

print thing.parts
# [Part1, Part2]

It seems like the session orders the result based on primary key (by default), which I guess is expected. But if the session already contains the instances, then should it change the order?

[Mike Bayer]

this is not a complete test case because I don't see any interaction with the objects in terms of the Session, however the Session will emit INSERT statements for a particular class of objects in the order in which they were added to the session, which if using "append-to-collection" as the method would mean append order should be controlling the order of INSERT - but if the objects were added beforehand somehow, then that would change it.

Above all, SQL has no ordering, and you should never expect the order of elements in a relationship-bound collection to be anything unless you specify order_by to your relationship.

This is a usage question (report ends with a question mark) so feel free to continue on the mailing list.