现在我正在做一个有数据库的项目。当我删除一些行时,我希望它能够重置ID以便它们之间没有间隔。 这是我想要的一个例子。在
当前数据库。在
ID: 1 Date: Date Exercise: Squat Sets: 3 Reps: 3 Weight: 3.0
ID: 2 Date: Date Exercise: Squat Sets: 3 Reps: 3 Weight: 3.0
ID: 3 Date: Date Exercise: Squat Sets: 1 Reps: 2 Weight: 3.0
ID: 4 Date: Date Exercise: Squat Sets: 1 Reps: 2 Weight: 3.0
ID: 5 Date: Date Exercise: Squat Sets: 1 Reps: 2 Weight: 3.0
ID: 6 Date: Date Exercise: Squat Sets: 1 Reps: 2 Weight: 3.0
ID: 7 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
ID: 8 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
ID: 9 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
ID: 10 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
删除第3行到第7行将得到。。。。。。在
^{pr2}$这就是我想要的。。。。。在
ID: 1 Date: Date Exercise: Squat Sets: 3 Reps: 3 Weight: 3.0
ID: 2 Date: Date Exercise: Squat Sets: 3 Reps: 3 Weight: 3.0
ID: 3 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
ID: 4 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
ID: 5 Date: Date Exercise: Squat Sets: 1 Reps: 1 Weight: 4.0
这是我目前拥有的功能。。。在
def delete_range(self):
"""
Deletes a range of rows in the SQL events table. The range is determined by entries in the starting and ending
DeleteRowEntry entries.
"""
starting_index = int(self.startingRowEntry.get())
ending_index = int(self.endingRowEntry.get())
conn = lite.connect('fit.db')
cursor = conn.cursor()
cursor.execute("""DELETE FROM events WHERE(id >= (?))
AND (id <= (?))""",(starting_index,ending_index))
cursor.execute("""DELETE FROM SQLITE_SEQUENCE WHERE NAME = 'events'""")
conn.commit()
conn.close
这是我正在操作的表的设置。在
cursor.execute('''CREATE TABLE IF NOT EXISTS events( id INTEGER PRIMARY KEY , date text, exercise_id int, sets int, reps int, weight real)''')
您将更新}之间的增量来创建一个新的
ending_index
之后的所有行,以便根据ending_index
和{id
:也不要从
SQLITE_SEQUENCE
表中删除events
行,而是在上面的UPDATE
之后用events
中的MAX(id)
来更新它。在但通常情况下,您并不希望这样做;其他表和外部系统希望
PRIMARY KEY
行id保持稳定。在如果您担心用户被事件标识中的空白所迷惑,那么不要向最终用户显示这些标识。您可以始终按顺序对GUI中的可见行进行编号,但仍然单独跟踪实际的数据库id。在
这几乎肯定是一个非常糟糕的想法,键的全部意义在于它们是引用记录的一种稳定的方式(在另一个带外键的take中,或者在应用程序中,等等)。在
但如果您真的想,只需按ID顺序遍历行,并将它们设置为“right”值,如下所示:
您可以通过两种方式对此进行优化:
ending_index
之后的那些,并使用一个UPDATE
将每个修改都设置为id - gap
。在相关问题 更多 >
编程相关推荐