Reset increment value in flask-sqlalchemy

Question

how do I reset the increment count in flask-sqlalchemy after deleting a row so that the next insert will get the id of deleted row? ie :

table users:
user_id | name |
________________
3       | mbuvi
4       | meshack
5       | You

when I delete user with id=5; the next insertion into users is having id = 6 but I want it to have id=5;

user_id | name |
________________
3       | mbuvi
4       | meshack
6       | me

How do I solve this?

To set the increment count, here's the sql command: ALTER TABLE `table` AUTO_INCREMENT = number; Don't know if their a sqlalchemy method that do the same thing. — Yassine Faris, Apr 17 '18 at 08:54
Try db.engine.execute("ALTER TABLE users AUTO_INCREMENT = 5") — Yassine Faris, Apr 17 '18 at 09:17

score 0 · Answer 1 · answered Apr 17 '18 at 09:17

Your database will keep track your auto increment id! so you can't do something like this.BTW it's no about the flask-sqlalchemy question! If you really want to do this, you have to calculater the left id which you can used and fill it with that number! for example:

+----+--------+
| id | number |
+----+--------+
|  1 |    819 |
|  2 |    829 |
|  4 |    829 |
|  5 |    829 |
+----+--------+

And you have to find the id (3) and then insert with id. so this cause a query all the table util you got that position! don't no why you need to do this, but still have solution!

step01, you gotta use a cache to do this! here I recommand use redis

step02, If you want to delete any row, just simply cache your id into the redis list, the Order-Set is best optionl for you! before delete any row, save it to the cache!

step03, before insert any new row, check see if there any id aviable in your redis! if true, pop it out and insert it with the id which you pop out!

step04, the code should like below:

def insert_one(data):
   r = redis.Redis()
   _id = r.pop('ID_DB')
   if _id:
      cursor.execute("insert into xxx(id, data)values(%s, %s)", data)
   else:
      # use default increment id
      cursor.execute("insert into xxx(data)values(%s)", data)

def delete(data, id):
      # query the target which you will delete
      # if you delete by condtion `id` that's best
      r = redis.Redis()
      r.push('ID_DB',id)
      # DO the rest of you want ...
      # delete ....

Reset increment value in flask-sqlalchemy

1 Answers1