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

Can you provide more information? How do you get `model` from the database in the first place? What do you think *should* be causing the data integrity error? — David, Jun 18 '15 at 13:17
i just create an instance of model. IntegretyError raises when im trying to save data that already in db — ayb, Jun 18 '15 at 13:59
If you're creating a new instance then it's not a duplicate. It's a *new instance*. An integrity error would happen if some data integrity check fails when saving the data. What is the data integrity check in the database that you think should be failing? Do you have an example of the data you're saving and the data already in the database which should cause it to fail? — David, Jun 18 '15 at 14:02
im parsing rss feeds, and i do not want to duplicate entries so i set url column to unique. I just need mysql to increment only when it's writes something to db. In SQLite is ok. It's increments only when it writing something — ayb, Jun 18 '15 at 14:17
You need to be *specific*. Try to understand that I can't see your screen from here, nor can I look at your database or debug your code for you. The database is going to use whatever rules you set it to use. It's not *lying to you* about the data. Show in your question the data that you're inserting and the data that you *think* should be causing that insert to fail. — David, Jun 18 '15 at 14:23
Nothing in your update explains why the record *shouldn't* be inserted into the database. You've claimed that you're trying to insert data into the database and that it *should* fail, but isn't failing. But you haven't explained *why* it should fail. Databases are generally pretty good at storing data. Usually when you insert data into a database, that data gets inserted into the database. There's no reason presented in this question why you think it *shouldn't* be inserted. — David, Jun 18 '15 at 14:47
because im trying to insert data that already in db and it's actualy do not duplicates in db. For example : i have a table with two columns : id and name. Name is set to unique. I saved _john_ first time. _john_ saved with id = 1. When im trying to save _john_ second time script raises `IntegrityError`. When im saving _jack_ db gives it id = 3. — ayb, Jun 18 '15 at 14:56
... Then what's the problem? When you tried to save `"john"` again, the error was raised correctly. When you tried to save `"jack"` there was no error. The value `"jack"` is, in fact, *different* from the value `"john"`. Why do you expect that it should cause an error? — David, Jun 18 '15 at 14:58
The problem is that i want to save _jack_ with id=2. Anyway i let my app control ids, not db — ayb, Jun 19 '15 at 14:54
If you want your app to control the IDs then don't use `autoincrement`. That puts the responsibility *at the database*. Have your app supply the ID in the `INSERT` statement instead. (Note, however, that this puts a lot of responsibility on your application. Particularly if you're using an incrementing integer ID. Race conditions, transactional integrity, etc. are going to become *your* responsibility, and those are responsibilities which databases already handle quite well.) — David, Jun 19 '15 at 14:54

score 2 · Accepted Answer · answered Jun 18 '15 at 15:08

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.

score 0 · Answer 2 · answered Jun 18 '15 at 19:26

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.

peewee, mysql and auto incrementing id

2 Answers2