peewee, mysql and auto incrementing id

Question

I have model in peewee ORM with unique=True field. Im saving data to my MySQL db like this :

try:
    model.save()
except IntegrityError: # do not save if it's already in db
    pass

But when peewee trying to save data that already in db, MySQL increments id and ids order is broken. How to avoid this behavior ?

Here's my model im trying to save :

class FeedItem(Model):
    vendor = ForeignKeyField(Vendor, to_field='name')
    url = CharField(unique=True)
    title = CharField()
    pub = DateTimeField()
    rating = IntegerField(default=0)
    img = CharField(default='null')

def construct(self, vendor, url, title):
    self.vendor = vendor
    self.url = url
    self.title = title
    self.pub = datetime.now()
    self.save()

class Meta:
    database = db

There's im saving it:

for article in feedparser.parse(vendor.feed)['items']:
                try:
                    entry = FeedItem()
                    entry.construct(vendor.name, article.link, article.title)
                except IntegrityError:
                    pass

Answer 1

MySQL increments id and ids order is broken. How to avoid this behavior?

You don't.

The database-generated identifier is outside your control. It's generated by the database. There's no guarantee that all identifiers have to be sequential and without gaps, just that they're unique. There are any number of things which would result in a number not being present in that sequence, such as:

• A record was deleted.
• A record was attempted to be inserted, which generated an ID, but the insert in some way failed after that ID was generated.
• A record was inserted as part of a transaction which wasn't committed.
• A set of IDs was generated to memory as part of an internal optimization in the database engine and the engine went down before the IDs were used.
• A record was inserted with an explicit ID, causing the auto-increment feature to re-adjust to the new value.

There may be more I'm not considering. But the point is that you simply don't control that value, the database engine does.

If you want to control that value then don't use autoincrement. Though be aware that this would come with a whole host of other problems that you'd need to solve which autoincrement solves for you. Or you'd have to switch to a GUID instead of an integer, which itself could result in other considerations you'd need to account for.

Answer 2

I'm not positive if this will work but you can try something like:

try:
    with database.atomic():
        model.save()
except IntegrityError:
    pass  # Model already exists.

By wrapping in atomic() the code will execute in a transaction (or savepoint if you are already in a transaction). This may lead to the ID sequence remaining intact.

I agree with David's answer, though, which is that really this is a database detail and should not be part of your application logic. If you need monotonically incrementing IDs you should implement that yourself.