Accessing Sql Data From A List Entry

First, I will describe and show how my mini program works visually so my problem is easy to understand Program Description Below are the 2 widows that I use for the program: Main W

Solution 1:

Do not reinvent the wheel, Qt provides classes to interact with the database, for example to handle information on tables you can use QSqlQueryModel, QSqlTableModel, etc. To add a row you must create a QSqlRecord and add it to the model, for the edition you can use a QDataWidgetMapper that maps and edits the information of a row, and to delete you must remove the row and reload the entire table. To indicate which field should be displayed in the QListView, use the setModelColumn() method, which should be the column of the "title" field.

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql


def create_connection(database):
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(database)
    if not db.open():
        print("Cannot open database")
        print(
            "Unable to establish a database connection.\n"
            "This example needs SQLite support. Please read "
            "the Qt SQL driver documentation for information "
            "how to build it.\n\n"
            "Click Cancel to exit."
        )
        return False

    query = QtSql.QSqlQuery()
    if not query.exec_(
        """CREATE TABLE IF NOT EXISTS Macros (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "title" TEXT,
    "description" TEXT)"""
    ):
        print(query.lastError().text())
        return False
    return True


class AddMacroDialog(QtWidgets.QDialog):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.title_le = QtWidgets.QLineEdit()
        self.description_te = QtWidgets.QPlainTextEdit()

        button_box = QtWidgets.QDialogButtonBox(self)
        button_box.setOrientation(QtCore.Qt.Horizontal)
        button_box.setStandardButtons(
            QtWidgets.QDialogButtonBox.Cancel | QtWidgets.QDialogButtonBox.Ok
        )

        button_box.accepted.connect(self.accept)
        button_box.rejected.connect(self.reject)

        lay = QtWidgets.QVBoxLayout(self)
        lay.addWidget(self.title_le)
        lay.addWidget(self.description_te)
        lay.addWidget(button_box)

    @property
    def title(self):
        return self.title_le.text()

    @property
    def description(self):
        return self.description_te.toPlainText()


class EditMacroDialog(QtWidgets.QDialog):
    def __init__(self, model, index, parent=None):
        super().__init__(parent)

        self.title_le = QtWidgets.QLineEdit()
        self.description_te = QtWidgets.QPlainTextEdit()

        mapper = QtWidgets.QDataWidgetMapper(
            self, submitPolicy=QtWidgets.QDataWidgetMapper.ManualSubmit
        )
        mapper.setModel(model)
        mapper.addMapping(self.title_le, model.record().indexOf("title"))
        mapper.addMapping(self.description_te, model.record().indexOf("description"))
        mapper.setCurrentIndex(index)

        button_box = QtWidgets.QDialogButtonBox(self)
        button_box.setOrientation(QtCore.Qt.Horizontal)
        button_box.setStandardButtons(
            QtWidgets.QDialogButtonBox.Cancel | QtWidgets.QDialogButtonBox.Ok
        )

        button_box.accepted.connect(self.accept)
        button_box.rejected.connect(self.reject)
        button_box.accepted.connect(mapper.submit)

        lay = QtWidgets.QVBoxLayout(self)
        lay.addWidget(self.title_le)
        lay.addWidget(self.description_te)
        lay.addWidget(button_box)


class MainWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super().__init__(parent)

        self._model = QtSql.QSqlTableModel(self)
        self.model.setTable("Macros")
        self.model.select()

        self.sql_list_view = QtWidgets.QListView()
        self.sql_list_view.setModel(self.model)
        self.sql_list_view.setModelColumn(self.model.record().indexOf("title"))

        self.new_button = QtWidgets.QPushButton(self.tr("New"))
        self.edit_button = QtWidgets.QPushButton(self.tr("Edit"))
        self.remove_button = QtWidgets.QPushButton(self.tr("Remove"))

        central_widget = QtWidgets.QWidget()
        self.setCentralWidget(central_widget)

        grid_layout = QtWidgets.QGridLayout(central_widget)
        grid_layout.addWidget(
            QtWidgets.QLabel(self.tr("Macros"), alignment=QtCore.Qt.AlignCenter)
        )
        grid_layout.addWidget(self.sql_list_view, 1, 0)

        vlay = QtWidgets.QVBoxLayout()
        vlay.addWidget(self.new_button)
        vlay.addWidget(self.edit_button)
        vlay.addWidget(self.remove_button)
        grid_layout.addLayout(vlay, 1, 1)
        self.resize(640, 480)

        self.new_button.clicked.connect(self.new)
        self.edit_button.clicked.connect(self.edit)
        self.remove_button.clicked.connect(self.remove)

        self.sql_list_view.selectionModel().selectionChanged.connect(
            self.onSelectionChanged
        )
        self.onSelectionChanged()

    @property
    def model(self):
        return self._model

    @QtCore.pyqtSlot()
    def new(self):
        d = AddMacroDialog()
        if d.exec_() == QtWidgets.QDialog.Accepted:
            r = self.model.record()
            r.setValue("title", d.title)
            r.setValue("description", d.description)
            if self.model.insertRecord(self.model.rowCount(), r):
                self.model.select()

    @QtCore.pyqtSlot()
    def edit(self):
        ixs = self.sql_list_view.selectionModel().selectedIndexes()
        if ixs:
            d = EditMacroDialog(self.model, ixs[0].row())
            d.exec_()

    @QtCore.pyqtSlot()
    def remove(self):
        ixs = self.sql_list_view.selectionModel().selectedIndexes()
        if ixs:
            self.model.removeRow(ixs[0].row())
            self.model.select()

    @QtCore.pyqtSlot()
    def onSelectionChanged(self):
        state = bool(self.sql_list_view.selectionModel().selectedIndexes())
        self.edit_button.setEnabled(state)
        self.remove_button.setEnabled(state)


if __name__ == "__main__":
    import sys

    database = "entry name"  # ":memory:"
    app = QtWidgets.QApplication(sys.argv)
    if not create_connection(database):
        sys.exit(app.exec_())
    w = MainWindow()
    w.show()
    sys.exit(app.exec_())