Added items to the list is not in expected ordered

Question

contacts are added into the list in 1,2,3 order, but the query return shows the list is a strange order 2,1,3. Only the follow test gives a pass, pay attention to the last 3 lines before and 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)    

Answer 1

The database returns contacts for your staff instance in the order that best fits the internal implementation, not in the order you inserted them into the relationship.

You'll have to specify how the database should order your contacts then, using the order_by argument to relationship():

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

If contact_list is a backreference, you can specify the order_by on a backref() object:

from sqlalchemy.orm import backref

staff_obj = mydb.relationship(
    'Staff', backref=backref('contact_list', order_by='StaffContact.id'))

This'll order the concats by their primary id. If a specific order needs to be maintained that cannot be derived from information already contained in the database, you'll have to add a position column to your contacts and use a ordering_list object to help maintain that order.