sql - python SQLite3 update row id when removing one row -

i've been building python/flask website, , works far, because need/use count(id) (where id auto-increment primary key), can't remove rows randomly.

do know best way update every other higher ids when removing 1 row, kinda list, count() , id matches. (first id = 1, should match without update).

i can put update function in standalone script , run manually, if it's heavy huge tables.

one way recreate sequential numbering of key drop table , reconstruct it. consider renumber() function in code below:

import sqlite3 pprint import pprint  schema = '''     create table s (         id integer primary key autoincrement not null,         content text not null)''' def init(db):     db.execute('drop table if exists s')     db.execute(schema)     db.execute('insert s ( content ) values ("one")')     db.execute('insert s ( content ) values ("two")')     db.execute('insert s ( content ) values ("three")')     db.execute('insert s ( content ) values ("four")')     db.commit()  def dump(db):     row in db.execute('select * s order id'):         print row     print  def renumber(db):     # reorganize primary key, create new table     db.execute('create temp table temp_s select content s order id')     db.execute('drop table s')     db.execute(schema)     db.execute('insert s (content) '               '  select content temp_s order rowid')     db.commit()   db = sqlite3.connect(':memory:') init(db) dump(db) db.execute('delete s id in (1,3)') db.commit() dump(db) renumber(db) dump(db) 

result:

(1, u'one') (2, u'two') (3, u'three') (4, u'four')  (2, u'two') (4, u'four')  (1, u'two') (2, u'four')