Sqlite3 Deleting and Re indexing items-CodePudding

How can i reindex all datas in Sqlite3 database after deleting one from data table. Example operation: I am using INTEGER PRIMARY KEY for id's and i need to do if i delete data where id=3, id=4 must be new id=3 and id=5 must be new id=4 (same thing for all datas). Is there any way to do it?

This is how i created the database.

conn = sqlite3.connect("AccStorage.db")
c = conn.cursor()
c.execute("""CREATE TABLE  if not exists acc_storage (
acc_id INTEGER PRIMARY KEY AUTOINCREMENT,
acc_platform text, 
username text, 
user_mail text, 
user_password text, 
acc_creation_date text, 
acc_recovery_codes text, 
acc_phone_number text, 
acc_recovery_mail text
)""")

c.execute("""INSERT INTO acc_storage (acc_platform, 
username, 
user_mail, 
user_password, 
acc_creation_date, 
acc_recovery_codes, 
acc_phone_number, 
acc_recovery_mail) VALUES ('Reddit', 'Fare', '[email protected]', 'abcdefg', '5.5.5', 'xxxxaaaa', '9305010', '[email protected]')""")
conn.commit()
conn.close()

This is how i delete the item from database

        def DeleteFromDatabase(self):
        Value_No = 65536
        Value_Yes = 16384
        isconfirm = self.ask_for_delete()
        if isconfirm == Value_Yes:
            account_id = int(self.exm_acc_ui.aditaccLabel.text().split(": ")[-1])
            conn6 = sqlite3.connect("AccStorage.db")
            c6 = conn6.cursor()
            c6.execute(f"""DELETE FROM acc_storage WHERE acc_id = {account_id-1}""")
            conn6.commit()
            conn6.close()
            self.editaccWindow.close()
            self.examineAccWindow.close()
            self.acc_info_read()
        elif isconfirm == Value_No:
            pass

account_id coming from QTableWidgets.currentRow() (which is a selected row from a table) I matched id and row and without deleting it works perfect. But if i delete a data from database (assume that there 5 datas)... If i delete 3th data rows in the table decreases to 4 row which contains (data1,data2,data4,data5). After that if i select data4 to examine its properties. App crashes. Because data4's id is still 4 but data4's row is became 3 after deleting data3. So data4's id must update itself after deleting data3. Same thing for data5 and other datas (data5 ). How to do that?

CodePudding user response：

You do not re-index the rows in the database. In fact, there is only one meaning for the "index" term within the database context and it has nothing to do with what you mean, which is an array index. Do not think of a table as an array of rows, because it is not. A table is a SET of rows, so you cannot extract a row using its position within your application, because there is no position in the table (well, strictly speaking, you can get ROWID, but this column has nothing to do with an array index). If you load database rows into an array in your application and use array index to identify items, you have to maintain a map from your array index to table PK. If possible, store item PK in an item attribute.

CodePudding user response：

I think the general way is by reset auto-increment (sequence) you can add this property to your table id by never you enter a value for ID, after deleting the row from the table must write and execute this query UPDATE sqlite_sequenceSETseq= 0 WHEREname = 'table_name';