RowIDs on SQLite3 sort wrong in Python while getting-CodePudding

I am trying to get rowIDs with ages together, but i've seen that rowIds sorted wrong. They start from 6 and goes 6 times more of the last rowId. I think it's an interestig mistake. Weird!

import sqlite3

conn = sqlite3.connect("tempDB.db");
sqliteobj = conn.cursor()
xx = sqliteobj.execute("SELECT rowid, age from team")
conn.commit()
print(xx.fetchall())

CodePudding user response：

I made my own Row IDs thanks to 'enumerate()'. That storages all database in a variable,after that, it deletes everything in database and adds ones except for the data i deleted,again. Also,it reorganizes the numbers without mistake. I didn't share any codes, so that it's easy. As final, I recommend that you don't work with rowid in sqlite3 because Python has an issue about this. You'd better make your own rowids at all time.

import sqlite3

class University:
    def __init__(self):
        self.status = True
        self.conn = sqlite3.connect("university.db")
        self.crsr = self.conn.cursor()
        self.crsr.execute("CREATE TABLE IF NOT EXISTS students(Number INT, Name TEXT, Lastname TEXT, Faculty TEXT, Department TEXT, id TEXT, ED_Type INT, status TEXT)")
        self.conn.commit()

. . . .

def addSt(self):  #WHEN I ADD A STUDENT
    name        = input("Student's name : ").lower().capitalize()
    lastname    = input("Student's lastname : ").upper()
    faculty     = input("Student's faculty : ").lower().capitalize()
    department  = input("Student's department : ").lower().capitalize()
    id          = input("Student's ID : ").upper()

    while True:
        try:
            edType      = int(input("Education Type (1 or 2) : "))
            if edType in (1,2):
                break
            else:
                continue
        except:
            print("It must be between [1-2]")

    status      = int(input("Education Status (1- Active, 2- Graduated, 3- Frozen) : "))
    self.adder(name,lastname,faculty,department,id,edType,status)
    print("Success!")





def adder(self,name,lastname,faculty,department,id,edType,status:int):
            stts = ["Active", "Graduated", "Frozen"]
            number = 1
            self.crsr.execute("SELECT * FROM students")
            stList = self.crsr.fetchall()
            if stList != []:
                number = int(stList[-1][0] 1)
                self.crsr.execute("INSERT INTO students VALUES({}, '{}', '{}', '{}', '{}', '{}', {}, '{}')".format(number,name,lastname,faculty,department,id,edType,stts[status-1]))
            else:
                self.crsr.execute("INSERT INTO students VALUES({}, '{}', '{}', '{}', '{}', '{}', {}, '{}')".format(number,name,lastname,faculty,department,id,edType,stts[status-1]))
            self.conn.commit()






   def rmvSt(self):
        self.crsr.execute("SELECT * FROM students")
        getSt = self.crsr.fetchall()
        for x in getSt:
            print("{}) {}".format(x[0]," ".join([str(y) for y in x[1:]])))
        while True:
            try:
                usr = int(input("Select Student : "))
                if usr < 1 or usr > len(getSt):
                    print("Wrong value!")
                    continue
                else:
                    self.crsr.execute("DELETE FROM students WHERE Number={}".format(usr))
                    self.conn.commit()
                    print("Success!")
                    break
            except:
                print("Error!")
        self.dbUpdater()

. . .

   def dbUpdater(self):
        self.crsr.execute("SELECT * FROM students")
        getSt = [x[1:] for x in self.crsr.fetchall()]
        self.crsr.execute("DELETE FROM students")
        self.conn.commit()
        numerate = list(enumerate(getSt,1))
        for x,y in numerate:
            self.crsr.execute("INSERT INTO students VALUES({}, '{}', '{}', '{}', '{}', '{}', {}, '{}')".format(x,y[0],y[1],y[2],y[3],y[4],y[5],y[6]))
            #print(x,y[0],y[1],y[2],y[3],y[4],y[5],y[6])
        print(numerate)
        self.conn.commit()

DB image

It has been better than the original one :)