CODEWITHSUNDEEP
pythonmysqlsqlalchemy
lang2
lang2

Reputation: 11966

sqlalchemy: order of query result unexpected

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?

Upvotes: 1

Views: 666

Answers (1)

univerio
univerio

Reputation: 20518

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.

Upvotes: 1

Related Questions