How to insert new row data into QTableView model view database in Python with SQLITE3

+1 vote
asked Sep 13, 2017 by lloydyu24

I want to use a Dialog window, which contains the form where the user can input details of an item into, which pops-up when the user clicks on "Add Item" button. The database will be viewed in a QTableView in the MainWindow (main.py).

How do I add a new item after completing the form in the Dialog window into the database incorporating the model-view-controller?

class Ui_Dialog(QtGui.QDialog):
    def __init__(self):
        QtGui.QDialog.__init__(self)
        self.setupUi(self)

    def setupUi(self, Dialog):
        Dialog.setObjectName(_fromUtf8("Dialog"))
        Dialog.resize(413, 382)

        self.okButton = QtGui.QPushButton(Dialog)
        self.okButton.setGeometry(QtCore.QRect(320, 90, 80, 30))

        self.cancelButton = QtGui.QPushButton(Dialog)
        self.cancelButton.setGeometry(QtCore.QRect(320, 170, 80, 30))

        self.resetButton = QtGui.QPushButton(Dialog)
        self.resetButton.setGeometry(QtCore.QRect(320, 130, 80, 30))

        self.lineEdit_3 = QtGui.QLineEdit(Dialog)
        self.lineEdit_3.setGeometry(QtCore.QRect(100, 85, 201, 31))
        self.lineEdit_3.setObjectName(_fromUtf8("companyName"))

        self.lineEdit_4 = QtGui.QLineEdit(Dialog)
        self.lineEdit_4.setGeometry(QtCore.QRect(100, 120, 201, 31))
        self.lineEdit_4.setText(_fromUtf8(""))
        self.lineEdit_4.setObjectName(_fromUtf8("brandName"))

        self.lineEdit_5 = QtGui.QLineEdit(Dialog)
        self.lineEdit_5.setGeometry(QtCore.QRect(100, 155, 201, 31))
        self.lineEdit_5.setObjectName(_fromUtf8("genericName"))

        self.lineEdit_6 = QtGui.QLineEdit(Dialog)
        self.lineEdit_6.setGeometry(QtCore.QRect(100, 190, 201, 31))
        self.lineEdit_6.setObjectName(_fromUtf8("itemCategory"))

        self.lineEdit_7 = QtGui.QLineEdit(Dialog)
        self.lineEdit_7.setGeometry(QtCore.QRect(100, 260, 201, 31))
        self.lineEdit_7.setObjectName(_fromUtf8("itemPrice"))

        self.lineEdit_8 = QtGui.QLineEdit(Dialog)
        self.lineEdit_8.setGeometry(QtCore.QRect(100, 295, 201, 31))
        self.lineEdit_8.setText(_fromUtf8(""))
        self.lineEdit_8.setObjectName(_fromUtf8("initialQty"))

        self.lineEdit_9 = QtGui.QLineEdit(Dialog)
        self.lineEdit_9.setGeometry(QtCore.QRect(100, 225, 201, 31))
        self.lineEdit_9.setObjectName(_fromUtf8("dosageForm"))

        self.lineEdit_10 = QtGui.QLineEdit(Dialog)
        self.lineEdit_10.setGeometry(QtCore.QRect(100, 330, 201, 31))
        self.lineEdit_10.setObjectName(_fromUtf8("expiryDate"))

Dialog Window

This is the main python file:

class Database:
    def __init__(self):
        self.data = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.data.setDatabaseName("inventory.db")
        self.data.open()

class Model(QtSql.QSqlRelationalTableModel):
    header_labels = ['Date Updated', 'Item ID', 'Company', 'Brand Name', 'Generic Name', 'Category',
                     'Item Cost', 'Quantity', 'Dosage Form', 'Expiry Date']
    def __init__(self, parent=None):
        super(Model, self).__init__(parent)
        self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
        self.setTable("inventory")
        self.select()

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if role == QtCore.Qt.DisplayRole and orientation == QtCore.Qt.Horizontal:
            return self.header_labels[section]
        return QtSql.QSqlTableModel.headerData(self, section, orientation, role)

class Main(QtGui.QMainWindow):
    def __init__(self):
        QtGui.QMainWindow.__init__(self)

        self.db = Database()
        self.model = Model(self)
        self.ui = MainWindow_ui()
        self.ui.setupUi(self)

        self.ui.inventoryTable.setModel(self.model)
        self.ui.inventoryTable.setSelectionMode(QtGui.QAbstractItemView.SingleSelection)
        self.ui.inventoryTable.setSelectionBehavior(QtGui.QAbstractItemView.SelectRows)
        self.ui.inventoryTable.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)

        self.ui.addItem.clicked.connect(lambda : self.start_Form())

        self.completer = QtGui.QCompleter()

        self.comModel = QtSql.QSqlQueryModel(self.model)
        self.comModel.setQuery("SELECT DISTINCT company FROM inventory")

        self.categoryModel = QtSql.QSqlQueryModel(self.model)
        self.categoryModel.setQuery("SELECT DISTINCT category FROM inventory")

        self.ui.comboBox_3.setModel(self.comModel)
        self.ui.comboBox_3.setDuplicatesEnabled(False)

        self.ui.comboBox_5.setModel(self.categoryModel)
        self.ui.comboBox_5.setDuplicatesEnabled(False)

    def start_Form(self):
        self.itemform = ItemDialog(self)
        self.itemform.show()

class ItemDialog(QtGui.QDialog):
    def __init__(self, parent=None):
        super(ItemDialog, self).__init__(parent)
        self.model = Model()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)

        self.ui.okButton.clicked.connect(self.accept)
        self.ui.cancelButton.clicked.connect(self.reject)
        self.ui.resetButton.clicked.connect(self.reset)

    def accept(self):
        self.close()

    def reject(self):
        self.close()

    def reset(self):
        self.ui.lineEdit_3.setText("")
        self.ui.lineEdit_4.setText("")
        self.ui.lineEdit_5.setText("")
        self.ui.lineEdit_6.setText("")
        self.ui.lineEdit_7.setText("")
        self.ui.lineEdit_8.setText("")
        self.ui.lineEdit_9.setText("")
        self.ui.lineEdit_10.setText("YYYY-MM-DD")

    def closeEvent(self, event):
        msg = QtGui.QMessageBox.question(self, 'Confirmation', 'Are you done?', QtGui.QMessageBox.Yes, QtGui.QMessageBox.No)
        if msg == QtGui.QMessageBox.Yes:
            event.accept()
        else:
            event.ignore()

def main():
    app = QtGui.QApplication(sys.argv)
    window = Main()
    window.showMaximized()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

1 Answer

0 votes
answered Sep 13, 2017 by lloydyu24

So basically, you want to add a new row on the lower end of your model/database. You have to create mappings before doing this or it won't work.

class ItemDialog(QtGui.QDialog):
    def __init__(self, parent=None):
        super(ItemDialog, self).__init__(parent)
        self.model = Model()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)

        self.mapper = QtGui.QDataWidgetMapper()
        self.mapper.setModel(self.model)
        self.mapper.setSubmitPolicy(QtGui.QDataWidgetMapper.ManualSubmit)

        self.mapper.addMapping(self.ui.lineEdit_3, 2)
        self.mapper.addMapping(self.ui.lineEdit_4, 3)
        self.mapper.addMapping(self.ui.lineEdit_5, 4)
        self.mapper.addMapping(self.ui.lineEdit_6, 5)
        self.mapper.addMapping(self.ui.lineEdit_7, 6)
        self.mapper.addMapping(self.ui.lineEdit_8, 7)
        self.mapper.addMapping(self.ui.lineEdit_9, 8)
        self.mapper.addMapping(self.ui.lineEdit_10, 9)

    def accept(self):
        row = int(self.model.rowCount()) //finds which row you add your new row
        self.model.insertRow(row) //creates your new row
        self.index = QtCore.QModelIndex(self.model.index(row, 0)) // finds your new row
        self.mapper.setCurrentModelIndex(self.index) // Selects your new row
        self.mapper.submit()  // Writes data into your new row
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...