Python SQLite3 command won't execute in python but also causes no reported errors-CodePudding

I want the table to have a maximum of three rows so once the table has 3, it should delete the oldest one (rowid 1) and then add on the new one. In the case that the table doesn't exist yet or hasn't reached 3 rows, it will just create the record normally. Everything works except for deleting the first row. Although there is no error feedback either and when the command is executed in the DB browser 'execute SQL' it works perfectly, it just doesn't work when run from my IDE. The new record is made but on top of the three already there instead of being added as the third after the first is deleted.

 cursor.execute("SELECT count(*) from TableOne")
 searchResults = cursor.fetchone()
 NoOfRows=searchResults[0]
 if NoOfRows ==3:
     cursor.execute("DELETE FROM TableOne WHERE rowid=1")
     connection.close()
     CreateNew()
 else:   
     CreateNew()

Note that the connection to the database is established before this code and 'CreateNew' is a function that creates the new record in the table. Additionally, I have tried:

Num=1
cursor.execute("DELETE FROM TableOne WHERE rowid=?",[Num])

Only to have the same result.

CodePudding user response：

I like the idea from @jarh to use a trigger in sqlite3: Here is a small mockup:

import sqlite3
    
sql1 = """CREATE TABLE IF NOT EXISTS table_one (
        id integer PRIMARY KEY,
        name text NOT NULL
        );"""

################## TRIGGER START ####################
sqlt = """CREATE TRIGGER IF NOT EXISTS rem_col_one
        BEFORE INSERT ON table_one
        WHEN (SELECT count(*) FROM table_one WHERE rowid > 2) 
        BEGIN
            DELETE FROM table_one WHERE rowid = last_insert_rowid()-2;
        END
        """
################## TRIGGER  END #####################

  
def db_insert(cur, name):
    sql2 = """INSERT INTO table_one (name) VALUES(?);"""
    sql3 = """SELECT * FROM table_one"""
    cur.execute(sql2,(name,))
    cur.execute(sql3)
    print(cur.fetchall())
     
def main():
    con = sqlite3.connect('Test.db')
    cur = con.cursor()
    cur.execute(sql1)
    cur.execute(sqlt)
    
    value_db = None
    while value_db != 'quit':
        value_db = input(f"Enter the next Name [or 'quit']: ")
        if value_db != 'quit':
            db_insert(cur, value_db) 
            con.commit()
    con.close()

if __name__ == "__main__":
    main()

The output will be like:

Enter the next Name: Hugo
[(1, 'Hugo')]
Enter the next Name: Max
[(1, 'Hugo'), (2, 'Max')]
Enter the next Name: Moritz
[(1, 'Hugo'), (2, 'Max'), (3, 'Moritz')]
Enter the next Name: Dilbert
[(2, 'Max'), (3, 'Moritz'), (4, 'Dilbert')]
Enter the next Name: Dagobert
[(3, 'Moritz'), (4, 'Dilbert'), (5, 'Dagobert')]
Enter the next Name: