python - Added items to the list is not in expected ordered -

contacts added list in 1,2,3 order, query return shows list strange order 2,1,3. follow test gives pass, pay attention last 3 lines before , after commit.

class staff(mydb.model):     __tablename__ = 'staff'     staff_id = mydb.column(mydb.string(20), primary_key = true)     first_name = mydb.column(mydb.string(64))     last_name = mydb.column(mydb.string(64))     email = mydb.column(mydb.string(62), unique=true, nullable = false)     password = mydb.column(mydb.string(), nullable = false)  class staffcontact(mydb.model):     __tablename__ = 'staff_contact'     staff_id = mydb.column(mydb.string(20), mydb.foreignkey('staff.staff_id'), primary_key = true)     department = mydb.column(mydb.string(50), primary_key = true)     office = mydb.column(mydb.string(50))     phone = mydb.column(mydb.string(15))     staff_obj = mydb.relationship('staff', backref='contact_list')  def test_add_contact_to_new_staff(self):     staff = staff(         staff_id='qwe',         first_name='qwe',         last_name='qwe',         email='qwe',         password='qwe')     mydb.session.add(staff)     mydb.session.commit()     staff_rst1 = mydb.session.query(staff).filter_by(staff_id='qwe').first()     self.assertequals(staff_rst1, staff)     self.assertis(staff_rst1, staff)         contact_1 = staffcontact(         staff_id = 'xyz',         department='xyz',         office='xyz',         phone='xyz'         )     contact_2 = staffcontact(         staff_id = 'abc',         department='abc',         office='abc',         phone='abc'         )        contact_3 = staffcontact(         staff_id = 'efg',         department='efg',         office='efg',         phone='efg'         )                            staff_rst1.contact_list=[contact_1, contact_2, contact_3]     mydb.session.add(staff_rst1)     self.assertis(staff_rst1.contact_list[0], contact_1)     self.assertis(staff_rst1.contact_list[1], contact_2)     self.assertis(staff_rst1.contact_list[2], contact_3)     mydb.session.commit()     staff_rst2 = mydb.session.query(staff).filter_by(staff_id='qwe').first()     self.assertis(staff_rst2.contact_list[0], contact_2)     self.assertis(staff_rst2.contact_list[1], contact_1)     self.assertis(staff_rst2.contact_list[2], contact_3)     

the database returns contacts staff instance in order best fits internal implementation, not in order inserted them relationship.

you'll have specify how database should order contacts then, using order_by argument relationship():

contact_list = relationship('staffcontact', order_by='staffcontact.id') 

if contact_list backreference, can specify order_by on backref() object:

from sqlalchemy.orm import backref  staff_obj = mydb.relationship(     'staff', backref=backref('contact_list', order_by='staffcontact.id')) 

this'll order concats primary id. if specific order needs maintained cannot derived information contained in database, you'll have add position column contacts , use ordering_list object maintain order.