sqlalchemy: order of query result unexpected

Question

I'm using SQLAlchemy with MySQL and have a table with two foreign keys:

class CampaignCreativeLink(db.Model):
  __tablename__ = 'campaign_creative_links'
  campaign_id = db.Column(db.Integer, db.ForeignKey('campaigns.id'),
    primary_key=True)
  creative_id = db.Column(db.Integer, db.ForeignKey('creatives.id'),
    primary_key=True)

Then I use a for loop to insert 3 items into the table like this:

session.add(8, 3)
session.add(8, 2)
session.add(8, 1)

But when I checked the table, the items are ordered reversely

8 1
8 2
8 3

And the query shows the order reversely too. What's the reason for this and how can I keep the order same as when they were added?

score 1 · Answer 1 · answered Feb 29 '16 at 19:00

1

A table is a set of rows and are therefore not guaranteed to have any order unless you specify ORDER BY.

In MySQL (InnoDB), the primary key acts as the clustered index. This means that the rows are physically stored in the order specified by the primary key, in this case (campaign_id, created_id), regardless of the order of insertion. This is usually the order the rows are returned in if you don't specify an ORDER BY.

If you need your rows returned in a certain order, specify ORDER BY when you query.

answered Feb 29 '16 at 19:00

univerio

19,548
3
66
68

Does it help to add an `id` field in this table? – lang2 Mar 01 '16 at 02:33
@lang2 Yes, I think that works, if you have an auto-incrementing non-primary key column and always sort on that. – univerio Mar 01 '16 at 02:57
I did try to add and `id` field but got `Field 'id' doesn't have a default value` error message afterwards. – lang2 Mar 03 '16 at 10:16
@lang2 Did you specify `AUTO_INCREMENT` on the `id` column? – univerio Mar 03 '16 at 18:41

sqlalchemy: order of query result unexpected

1 Answers1

Linked