Discrepancy between my SQL python data and the DB Browser












-1














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.










share|improve this question
























  • To understand what is going wrong we need the code you are using @Bahlsen.
    – Mr.Zeus
    Nov 13 at 21:12
















-1














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.










share|improve this question
























  • To understand what is going wrong we need the code you are using @Bahlsen.
    – Mr.Zeus
    Nov 13 at 21:12














-1












-1








-1







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.










share|improve this 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.







python sqlite db-browser-sqlite






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 at 21:42

























asked Nov 13 at 21:02









Bahlsen

215




215












  • To understand what is going wrong we need the code you are using @Bahlsen.
    – Mr.Zeus
    Nov 13 at 21:12


















  • To understand what is going wrong we need the code you are using @Bahlsen.
    – Mr.Zeus
    Nov 13 at 21:12
















To understand what is going wrong we need the code you are using @Bahlsen.
– Mr.Zeus
Nov 13 at 21:12




To understand what is going wrong we need the code you are using @Bahlsen.
– Mr.Zeus
Nov 13 at 21:12












2 Answers
2






active

oldest

votes


















1














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.






share|improve this answer





























    0














    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()





    share|improve this answer





















      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289443%2fdiscrepancy-between-my-sql-python-data-and-the-db-browser%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1














      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.






      share|improve this answer


























        1














        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.






        share|improve this answer
























          1












          1








          1






          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.






          share|improve this answer












          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 14 at 16:21









          Bahlsen

          215




          215

























              0














              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()





              share|improve this answer


























                0














                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()





                share|improve this answer
























                  0












                  0








                  0






                  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()





                  share|improve this answer












                  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()






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 13 at 22:42









                  bruceskyaus

                  316210




                  316210






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289443%2fdiscrepancy-between-my-sql-python-data-and-the-db-browser%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Guess what letter conforming each word

                      Port of Spain

                      Run scheduled task as local user group (not BUILTIN)