Does sqlalchemy keep order in many ways?

Question

I use sqlalchemy (now with sqlite, but this may change later) to create a database in which the order of attachments and therefore is important. I basically have the following:

class Message(Base): __tablename__ = 'messages' __table_args__ = {'sqlite_autoincrement':True} id = Column(Integer, primary_key=True) refs = relationship('Ref') class Ref(Base): __tablename__ = 'refs' __table_args__ = {'sqlite_autoincrement':True} id = Column(Integer, primary_key=True) message_id = Column(Integer, ForeignKey('messages.id')) 

When I create a Message object and append a Ref for message.refs , I would like to be sure that when I pass this to the database, Ref will be inserted in the same order in which they appear in the list. Does sqlalchemy do this by default or do I need to clean up after every addition?

Answer 1

No : even if in your simple case this is likely to work this way, sqlalchemy do not make any guarantee regarding the insertion order.

---

If you really need rowids be ordered worldwide on demand, there is no easy way to get it working out of the box.

However, if you need to order Ref in Message , you can do this by adding another column to Ref to indicate the order, in which case using the Ordering List extension is the easiest way to achieve this. This will result in loading and pasting Ref objects that maintain order.