How to add autoincrement to a column in SQLite3 database?

Question

Python

def add():
    kind1 = kind_Entry.get()
    species1 = species_Entry.get()
    birth1 = birth_Entry.get()
    weight1 = weight_Entry.get()
    cursor.execute("INSERT INTO animal (kind, species, birth_day, weight) VALUES (?, ?, ?, ?)", (kind1, species1,
                                                                                                 birth1, weight1))
    db.commit()
    cursor.execute("SELECT * FROM animal;")
    db.commit()
    note.destroy()
with sqlite3.connect('D:\Education\practice.db') as db:
    cursor = db.cursor()

    cursor.execute("""CREATE TABLE IF NOT EXISTS
        animal(
            id        INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
            kind      TEXT,
            species   TEXT,
            birth_day DATE,
            weight    INT)
    """)
    db.commit()

Here is a piece of code, I created a database, where I indicated that the id will be auto-incremented (if there is such a word), and the function in which I add data to the database (in the function where I use get () I just take the values ​​​​from the tkinter form , I think it's optional here). But there is a problem, for example, I have 4 rows in the database, if I delete the last 4 row, and then I want to add new data to the database, then my id will already be No. 5, that is, it continues to grow. How can I make sure that when deleting a row, my id does not increase just by one each time?

Answer 1

Try like this

DELETE FROM animal;
DELETE FROM sqlite_sequence WHERE name = 'animal';

so try

cursor.execute("INSERT INTO animal (kind, species, birth_day, weight) VALUES (?, ?, ?, ?)", (kind1, species1, birth1, weight1))

Answer 2

you can write a trigger to add a new record to update the field you need by doing something like select max+1
ps for identifiers, this is bad practice, do not mix identification and sequence number, especially if data can be deleted as soon as you start referencing records from outside identifier, then you will start conflicts
pps and if you remove 'from the middle' of your sequence, and then add a new one, do you want to get a new value or an old one freed?