Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Adding table content to SQLite DB
Forum Updated to NodeBB v4.3 + New Features

Adding table content to SQLite DB

Scheduled Pinned Locked Moved Unsolved General and Desktop
13 Posts 4 Posters 950 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • D DouglinhasZN

    I am trying to add the contents of my table to my SQLite DB

            Test_ID_Summary = self.SummaryTestIDLineEdit.text()
            Summary_Findings_Name = [self.SummaryFindingTable_Window.item(row, 0).text() for row in range(self.SummaryFindingTable_Window.rowCount())]
            Summary_Findings_Desc = [self.SummaryFindingTable_Window.item(row, 1).text() for row in range(self.SummaryFindingTable_Window.rowCount())]
            Summary_Findings_Risk = [self.SummaryFindingTable_Window.item(row, 2).text() for row in range(self.SummaryFindingTable_Window.rowCount())]
            
            conn = sqlite3.connect('Main_Database.db')
            c = conn.cursor()
            try:
                with conn:
                    c = conn.cursor
                    c.execute("CREATE TABLE IF NOT EXISTS Findings_Summaries(Test_ID TEXT, Summary_Findings_Name TEXT, Summary_Findings_Desc TEXT, Summary_Findings_Risk TEXT)")
                    c.execute("INSERT INTO Findings_Summaries(Test_ID, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk)" "VALUES('%s', '%s', '%s'. '%s')" %(''.join(Test_ID_Summary),''.join(Summary_Findings_Name),''.join(Summary_Findings_Desc),''.join(Summary_Findings_Risk)))
                    conn.commit()
                    c.close()
                    conn.close()
            except:
                pass
    

    That's one of the many attempts I had at this. Could anyone help ?
    I have tried using VALUES(?,?,?,?) and VALUES('{}', '{}','{}', '{}') but to no avail.

    JonBJ Offline
    JonBJ Offline
    JonB
    wrote on last edited by
    #2

    @DouglinhasZN
    If you have errors going on replace your except: pass with something that prints the error message.

    It would help if you edited your code to bring the "missing" lines in from all the way out there to the right.

    Your code just uses the Python connector, nothing Qt, so that is where the problem/answer lies.

    1 Reply Last reply
    1
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #3

      Hi,

      Since you are using the Python sqlite3 module, you should check its documentation.

      From a quick look, your insert statement contains one issue, you have a point rather than a comma to separate the last two fields and you are trying to pass one string as parameter while you have several placeholder values.

      You really should check what your query string looks like before executing it.

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      D 1 Reply Last reply
      0
      • SGaistS SGaist

        Hi,

        Since you are using the Python sqlite3 module, you should check its documentation.

        From a quick look, your insert statement contains one issue, you have a point rather than a comma to separate the last two fields and you are trying to pass one string as parameter while you have several placeholder values.

        You really should check what your query string looks like before executing it.

        D Offline
        D Offline
        DouglinhasZN
        wrote on last edited by DouglinhasZN
        #4

        @SGaist
        This is how the code looks on my editor, i have no idea why the layout here looked different
        5cedf9d3-5279-41e4-92e0-9e9c27f7ebb1-image.png
        that point was a spelling error when pasting the code here.

        @JonB thank you, this is what it returned: object has no attribute 'execute' I have a similar structure for other stuff i saved into the database and works fine.
        I missed () after the con.cursor
        It now saved into the database.

        However, it saves all rows in table in the same row inside the database, does anyone know how to fix this?

        EDIT: I changed my execute to this:

        c.execute("INSERT INTO Findings_Summaries(Test_ID, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk) VALUES (?,?,?,?)", (Test_ID_Summary, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk))
        

        and added str() to the variables. Now it still prints all rows in table to the same row in the table, but now its in list:
        911d9295-301f-4c75-82a0-a73ffe18b6c1-image.png

        1 Reply Last reply
        0
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #5

          Again: read the module documentation: executemany.

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          D 1 Reply Last reply
          0
          • SGaistS SGaist

            Again: read the module documentation: executemany.

            D Offline
            D Offline
            DouglinhasZN
            wrote on last edited by
            #6

            @SGaist I have encoporated the executemany, but it still executes the table rows data in the same row inside the database as shown in the picture above your post

            1 Reply Last reply
            0
            • D Offline
              D Offline
              DouglinhasZN
              wrote on last edited by
              #7

              anyone got a clue?

              jsulmJ 1 Reply Last reply
              0
              • D DouglinhasZN

                anyone got a clue?

                jsulmJ Offline
                jsulmJ Offline
                jsulm
                Lifetime Qt Champion
                wrote on last edited by
                #8

                @DouglinhasZN Show how you're using executemany...

                https://forum.qt.io/topic/113070/qt-code-of-conduct

                D 1 Reply Last reply
                1
                • SGaistS Offline
                  SGaistS Offline
                  SGaist
                  Lifetime Qt Champion
                  wrote on last edited by
                  #9

                  Well if you turned all your lists to strings then no wonder it does only one insertion.

                  Interested in AI ? www.idiap.ch
                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                  1 Reply Last reply
                  2
                  • jsulmJ jsulm

                    @DouglinhasZN Show how you're using executemany...

                    D Offline
                    D Offline
                    DouglinhasZN
                    wrote on last edited by DouglinhasZN
                    #10

                    @jsulm

                        def submitting_finding_summary(self):
                        
                                Test_ID_Summary = self.SummaryTestIDLineEdit.text()
                        
                                Summary_Findings_Name = str([self.SummaryFindingTable_Window.item(row, 0).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                        
                                Summary_Findings_Desc = str([self.SummaryFindingTable_Window.item(row, 1).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                        
                                Summary_Findings_Risk = str([self.SummaryFindingTable_Window.item(row, 2).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                        
                                conn = sqlite3.connect('Main_Database.db')
                        
                                c = conn.cursor()
                        
                                try:
                        
                                    with conn:
                        
                                        c = conn.cursor()
                        
                                        c.execute("CREATE TABLE IF NOT EXISTS Findings_Summaries(Test_ID TEXT, Summary_Findings_Name TEXT, Summary_Findings_Desc TEXT, Summary_Findings_Risk TEXT)")
                        
                                        c.executemany("INSERT INTO Findings_Summaries(Test_ID, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk) VALUES (?,?,?,?)", (Test_ID_Summary, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk)
                        
                        
                                        conn.commit()
                        
                                        c.close()
                        
                                        conn.close()
                        
                                except Exception as e:
                        
                                    print(e)
                    jsulmJ 1 Reply Last reply
                    0
                    • D DouglinhasZN

                      @jsulm

                          def submitting_finding_summary(self):
                          
                                  Test_ID_Summary = self.SummaryTestIDLineEdit.text()
                          
                                  Summary_Findings_Name = str([self.SummaryFindingTable_Window.item(row, 0).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                          
                                  Summary_Findings_Desc = str([self.SummaryFindingTable_Window.item(row, 1).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                          
                                  Summary_Findings_Risk = str([self.SummaryFindingTable_Window.item(row, 2).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                          
                                  conn = sqlite3.connect('Main_Database.db')
                          
                                  c = conn.cursor()
                          
                                  try:
                          
                                      with conn:
                          
                                          c = conn.cursor()
                          
                                          c.execute("CREATE TABLE IF NOT EXISTS Findings_Summaries(Test_ID TEXT, Summary_Findings_Name TEXT, Summary_Findings_Desc TEXT, Summary_Findings_Risk TEXT)")
                          
                                          c.executemany("INSERT INTO Findings_Summaries(Test_ID, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk) VALUES (?,?,?,?)", (Test_ID_Summary, Summary_Findings_Name, Summary_Findings_Desc, Summary_Findings_Risk)
                          
                          
                                          conn.commit()
                          
                                          c.close()
                          
                                          conn.close()
                          
                                  except Exception as e:
                          
                                      print(e)
                      jsulmJ Offline
                      jsulmJ Offline
                      jsulm
                      Lifetime Qt Champion
                      wrote on last edited by
                      #11

                      @DouglinhasZN Please read what @SGaist wrote...

                      https://forum.qt.io/topic/113070/qt-code-of-conduct

                      D 1 Reply Last reply
                      0
                      • jsulmJ jsulm

                        @DouglinhasZN Please read what @SGaist wrote...

                        D Offline
                        D Offline
                        DouglinhasZN
                        wrote on last edited by DouglinhasZN
                        #12

                        @jsulm I've removed the strings but it just shows this error instead:

                        Incorrect number of bindings supplied. The current statement uses 4, and there are 7 supplied.
                        

                        thats why i attempted the strings instead.
                        I already added a "," at the end but to no avail

                        JonBJ 1 Reply Last reply
                        0
                        • D DouglinhasZN

                          @jsulm I've removed the strings but it just shows this error instead:

                          Incorrect number of bindings supplied. The current statement uses 4, and there are 7 supplied.
                          

                          thats why i attempted the strings instead.
                          I already added a "," at the end but to no avail

                          JonBJ Offline
                          JonBJ Offline
                          JonB
                          wrote on last edited by JonB
                          #13

                          @DouglinhasZN
                          You need to stop and really read and follow the pattern from https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany. Until your code has an iterator or a generator in it, like they show there, executemany() is never going to work. If you can't get the hang of/don't want to use that, go over to to doing your inserts explicitly in a loop calling just execute() each time. You will never want to go:

                          Summary_Findings_Name = str([self.SummaryFindingTable_Window.item(row, 0).text() for row in range(self.SummaryFindingTable_Window.rowCount())])
                          

                          with that str() at the start, as that's going to make a single string out of each of the rows' values, which can never be the right approach.

                          1 Reply Last reply
                          2

                          • Login

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • Users
                          • Groups
                          • Search
                          • Get Qt Extensions
                          • Unsolved