我开始在python中使用SQL数据库,并且我希望有多个表,我可以在其中按ID引用彼此表中的行。因此,我使用的是“testID 整数主键”列。
列的值会根据需要增加,但是如果我删除具有最大 ID 的行,然后添加另一个条目,它将接收之前已设置的 ID。这很有用,因为删除最新的行会导致列中的最大ID降低,这对我来说很有意义。
我现在想知道,有没有办法让数据库记住之前设置的每个ID,而不是设置相同的ID两次,即使从数据库中删除最大ID?
MWE也许可以更清楚地说明这一点:
conn = sqlite3.connect("db_problem.db")
c = conn.cursor()
with conn:
c.execute("CREATE TABLE test ("
"testID integer PRIMARY KEY, "
"col1 integer)")
c.execute("INSERT INTO test (col1) VALUES (1)")
c.execute("INSERT INTO test (col1) VALUES (2)")
c.execute("DELETE FROM test WHERE col1 LIKE 1")
c.execute("SELECT testID FROM test WHERE col1 LIKE 2")
print(c.fetchall()) # this stays 2, even tho there is only one row left, which works fine
c.execute("INSERT INTO test (col1) VALUES (3)")
c.execute("DELETE FROM test WHERE col1 LIKE 3")
c.execute("INSERT INTO test (col1) VALUES (4)")
c.execute("SELECT testID FROM test WHERE col1 LIKE 4")
print(c.fetchall()) # Here the autoincrement was set to 3 although it is the fourth entry made
慕慕森
相关分类