Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Adding table content to SQLite DB



  • 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.



  • @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.


  • Lifetime Qt Champion

    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.



  • @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


  • Lifetime Qt Champion

    Again: read the module documentation: executemany.



  • @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



  • anyone got a clue?


  • Lifetime Qt Champion

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


  • Lifetime Qt Champion

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



  • @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)

  • Lifetime Qt Champion

    @DouglinhasZN Please read what @SGaist wrote...



  • @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



  • @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.


Log in to reply