Adding table content to SQLite DB

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.

JonB

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

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.

DouglinhasZN

@SGaist
This is how the code looks on my editor, i have no idea why the layout here looked different

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:

SGaist

Again: read the module documentation: executemany.

DouglinhasZN

@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

DouglinhasZN

anyone got a clue?

jsulm

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

SGaist

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

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)

jsulm

@DouglinhasZN Please read what @SGaist wrote...

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

JonB

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