CODEWITHSUNDEEP
sqlalchemy
Asterism
Asterism

Reputation: 75

Does sqlalchemy preserve order in many-to-one/many-to-many relationships?

I'm using sqlalchemy (with sqlite for now, but this may change later) to build a database in which order of insertions and thus rowids 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 object to message.refs, I'd like to be sure that when I commit this to the database the Refs 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 flush after each append?

Upvotes: 7

Views: 1931

Answers (1)

van
van

Reputation: 77072

No: even if in in your simple case it would probably work this way, sqlalchemy does not make any guarantee regarding the order of insertion.

If you really need the rowids to be globally ordered as requested, there is no simple way of making it work out-of-the box.

However, if what you need is ordering of Ref within the Message, then this can be achieved by adding another column to Ref for indicating the ordering, in which case using the Ordering List extension is the easiest way to achieve this. This will do both loading and inserting of Ref objects keeping the ordering.

Upvotes: 6

Related Questions