Python Insert Data from Qtable Widget into Ms Access with QSqlDatabase

Question

This is what I have so far:

 def save_invoice(self):

    con = QSqlDatabase.addDatabase("QODBC")
    con.setDatabaseName("C:/Users/Egon/Documents/Invoice/Invoice.accdb")

    # Open the connection
    con.open()        
    # Creating a query for later execution using .prepare()
    insertDataQuery = QSqlQuery()
    insertDataQuery.prepare(
        """
        INSERT INTO Test01 (
            Quantity,
            ProductId,
            Description,
            Price,
            Tax,
            NetTotal,
            GrossTotal
        )
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """
    )
    
    data = getData(self.tableWidgetInvoiceItem)     

    for Quantity, ProductId, Description, Price, Tax, NetTotal, GrossTotal in data:
        insertDataQuery.addBindValue(Quantity)
        insertDataQuery.addBindValue(ProductId)
        insertDataQuery.addBindValue(Description)
        insertDataQuery.addBindValue(Price)
        insertDataQuery.addBindValue(Tax)
        insertDataQuery.addBindValue(NetTotal)
        insertDataQuery.addBindValue(GrossTotal)
        insertDataQuery.exec_()     
        print(insertDataQuery.lastError().text())
        con.commit()

Fetch the data from the QTableWidget and return it as data.

def getData(table: QTableWidget) -> List[Tuple[str]]:


data = []
for row in range(table.rowCount()):
    rowData = []
    for col in range(table.columnCount()):
        rowData.append(table.item(row, col).data(Qt.EditRole))
    data.append(tuple(rowData))

return data    

No error message is displayed but also no records are inserted into database. How can I solve this?

Answer 1

Try using QSqlDatabase.commit() instead of con.commit().