python SQLite3 update row id when removing one row

Question

I've been building a python/flask website, and it works so far, but because I actually need/use the count(id) (where id is the auto-increment primary key), I can't just remove some rows randomly.

Do anyone know the best way to update every other higher IDs when removing one row, kinda like a list, so the count() and the id matches. (first ID = 1, so it should match perfectly without update).

I can put the update function in a standalone script and run it manually, if it's too heavy for huge tables.

`count(id)` is not affected by gaps in the sequence. What exactly do you think is a problem with not updating the higher IDs? — CL., Jun 13 '16 at 18:01
Related: http://stackoverflow.com/questions/14023292/how-to-get-rownum-like-column-in-sqlite-iphone/19199219#19199219 — Robᵩ, Jun 13 '16 at 18:03
My problem is exactly the fact that count(id) is not affected by row id. It's fine like that since that's I want, but I wanted to know how to update all the higher ID occurences in a table, so the ids will be equal to the count (no id missing), then updating the table sequence. — Artemis, Jun 13 '16 at 18:43
I don't use foreign keys. The database consists on only 2 tables, completely different and unrelated. The concerned table is id (int primary notnull AI) and content (text notnull) Soo that still don't answer the problem — Artemis, Jun 13 '16 at 18:52
Okay, if I tried to do it without updating all IDs, how, from a certain ID, can I get the ID of the next row ? (Example: 5 - 6 - 8 (7 removed), I'm on 6, how can I know what is the ID of the next rows ? — Artemis, Jun 13 '16 at 19:20
If your application depends on the row IDs being without gap you're doing something wrong. The purpose of IDs is to uniquely identify a row/object over its lifetime; you're supposed to not change them. — Murphy, Jun 14 '16 at 13:21

Robᵩ · Accepted Answer · 2016-06-13T20:50:43.413

One way to recreate sequential numbering of your key is to drop the table and reconstruct it. Consider the renumber() function in the code below:

import sqlite3
from 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 into S ( content ) VALUES ("one")')
    db.execute('insert into S ( content ) VALUES ("two")')
    db.execute('insert into S ( content ) VALUES ("three")')
    db.execute('insert into S ( content ) VALUES ("four")')
    db.commit()

def dump(db):
    for row in db.execute('select * from S order by ID'):
        print row
    print

def renumber(db):
    # To reorganize the primary key, create a new table
    db.execute('create temp table temp_S as select content from S order by id')
    db.execute('drop table S')
    db.execute(schema)
    db.execute('insert into S (content) '
              '  select content from temp_S order by rowid')
    db.commit()


db = sqlite3.connect(':memory:')
init(db)
dump(db)
db.execute('delete from S where 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')

python SQLite3 update row id when removing one row

1 Answers1