Discrepancy between my SQL python data and the DB Browser

Question

I am using sqlite combined with tkinter to write and delete records within my Python program. The deletion works perfectly fine in my program and also when I restart the program, the record does not exist anymore. However, I always cross check using the Linux standard software DB Browser for SQLite and look at my SQL Table. Strangely, all records still exist in the DB Browser. Now I am wondering, why's that? Why is it gone within my Python sqlite queries but not in the DB Browser? Somehow the records are still there. How can I completely destroy my records?

For deletion I use: (The user can chose a specific entry using a listbox. Eventually, I "translate" the selected item into its specific ID and trigger the deletion.)

self.c.execute("DELETE FROM financial_table WHERE ID=?",(entry,))
self.conn.commit()

For my query I use: (I query the data for a specific year and month.)

self.c.execute("SELECT ID, Date, Item, Price FROM financial_table WHERE strftime('%Y-%m', Date) = '{}' ORDER BY Date ".format(date))
single_dates = self.c.fetchall()

Thank you very much for your help.

Answer 1

You may have an problem with controlling transactions on your database, but it could also be the connection itself. Make sure you don't have any uncommitted DML statements on a different connection (i.e. an INSERT, UPDATE or DELETE in your DB Browser that wasn't committed), this could cause the conn.commit() to fail. With SQLite, an uncommitted transaction could lock the entire database - for a brief period of time.

Try ensuring that there is a new cursor for the delete statement and call conn.close() after the conn.commit(). Before you execute the code, make sure that no other connections are accessing the database - including the DB Browser. Only check in the DB Browser when you have shut down the application (for this test). This eliminates multithreading or locking as a possible cause. See also SQLite - Data Persistence and SQLite - Controlling Transactions

It is also helpful to trap all errors for DML statements using a try...except block. Something like this:

import sqlite3

try:
    self.conn = sqlite3.connect('mydb.db')
    self.c = conn.cursor()    
    self.c.execute("DELETE FROM financial_table WHERE ID=?",(entry,))
    self.conn.commit()
except sqlite3.Error as e:
    print("An error occurred:", e.args[0])
finally:
    self.conn.close()

Answer 2

The solution to my question is: I am stupid! I was tired yesterday evening and looked at the wrong sql file in a subfolder which had the same name than the one from my python program. So it is actually working. Please excuse my stupidity.

@Bruceskyaus Despite my stupidity I learned from your answer, especially the try ... except block. I am going to implement it. Thanks.