How do I add a widget to display SQLite data from data base?-CodePudding

Sorry upfront for the length of this post. I tried about 10 different was to fix my issue, with no luck. I wanted to give the most context posable.

I'm new to Pyqt5. I am making a contacts app, and I am able to input data into a sqlite3 'contacts.db' file using a pyqt5 GUI app. I have the GUI input fields on the left side of the app. Where I keep getting stuck is how do I display the content of the 'contacts.db' file in a display box on the right side of the GUI app?

Here is a screenshot:

Here is my code so far:

import sys
from PyQt5 import  QtWidgets
from PyQt5.QtWidgets import QMainWindow,  QLabel, QLineEdit
from PyQt5.QtWidgets import QPushButton
from PyQt5.QtCore import QSize
from sqlite3 import *  

class MainWindow(QMainWindow):
    
    # Creating Main drop window
    def __init__(self):
        QMainWindow.__init__(self)
        
        # Sets dimentions for main drop window
        self.setMinimumSize(QSize(1000, 425)) 

        # Sets title for main drop window    
        self.setWindowTitle("Contacts") 
        
        def firstname():

            # Creates a label for first field
            self.nameLabel1 = QLabel(self)

            # Creates label text for first label
            self.nameLabel1.setText('First Name: ')

            # Creates first field
            self.line1 = QLineEdit(self)

            # Sets placement for first field
            self.line1.move(95, 50)

            # Sets size for first field
            self.line1.resize(200, 32)

            # Sets placement for first label
            self.nameLabel1.move(20, 50)

        firstname() 

        def lastname():
            self.nameLabel2 = QLabel(self)
            self.nameLabel2.setText('Last Name: ')
            self.line2 = QLineEdit(self)
            self.line2.move(95, 100)
            self.line2.resize(200, 32)
            self.nameLabel2.move(20, 100)
        lastname()

        def email():
            self.nameLabel3 = QLabel(self)
            self.nameLabel3.setText('Email: ')
            self.line3 = QLineEdit(self)
            self.line3.move(95, 150)
            self.line3.resize(200, 32)
            self.nameLabel3.move(20, 150)
        email()

        def phone():
            self.nameLabel4 = QLabel(self)
            self.nameLabel4.setText('Phone: ')
            self.line4 = QLineEdit(self)
            self.line4.move(95, 200)
            self.line4.resize(200, 32)
            self.nameLabel4.move(20, 200)
        phone()
        
        def phone():
            self.nameLabel5 = QLabel(self)
            self.nameLabel5.setText('Location: ')
            self.line5 = QLineEdit(self)
            self.line5.move(95, 250)
            self.line5.resize(200, 32)
            self.nameLabel5.move(20, 250)
        phone()
        
        def button():

            # Creates button, and button label
            pybutton1 = QPushButton('Add Contact', self)

            # Creates framwork for button funtion
            pybutton1.clicked.connect(self.clickMethod1)

            # Sets button size
            pybutton1.resize(200,32)

            # Sets button placement
            pybutton1.move(95, 325)   
        
        button()
    # Creates action that will be preformed when first button is pressed
    def clickMethod1(self):
        conn = connect('Contacts.db')
        c = conn.cursor()
        first = self.line1.text()
        last = self.line2.text()
        email = self.line3.text()
        phone = self.line4.text()
        loc = self.line5.text()

        c.execute(f"""INSERT INTO contacts VALUES('{first}',
        '{last}', '{email}', '{phone}', '{loc}')
        """)
    
        conn.commit()
        conn.close()

if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    mainWin = MainWindow()
    mainWin.show()
    sys.exit( app.exec_() )

To make the same database I have you can run this in python:

from sqlite3 import *

conn = connect('Contacts.db')
c = conn.cursor()

# Make table
def make():
    c.execute("""CREATE TABLE IF NOT EXISTS contacts(
    first TEXT,
    last TEXT,
    email TEXT,
    phone TEXT,
    location TEXT        
    )""")
make()

conn.commit()
conn.close()

Thank you very much for your help!

CodePudding user response：

Qt already provides support for SQL through the QtSql module, so you usually do not need to use a separate sql module for generic usage.

Data is loaded using a model, and eventually displayed using a view (read more about the Model/View Programming paradigm of Qt.

You essentially do the following:

load a database engine;
select a database;
create a model (usually a QSqlTableModel);
create a view (normally a QTableView);
set the model for that view, using setModel();

In the following example, you can see that the model is connected to the database, and since the modifications are done directly to the model, the table is automatically updated along with it.

^{Note that I reworked your code, as it had many issues: first of all, a QMainWindow should always have a central widget, then you should also always use layout managers instead of arbitrary sizes and positions; I also removed the functions in the __init__ as they are unnecessary and only make the code distracting to read.}

from PyQt5 import QtCore, QtWidgets, QtSql

class MainWindow(QtWidgets.QMainWindow):
    def __init__(self):
        super().__init__()
        central = QtWidgets.QWidget()
        mainLayout = QtWidgets.QGridLayout(central)
        mainLayout.setColumnStretch(0, 1)
        mainLayout.setColumnStretch(1, 2)

        formLayout = QtWidgets.QFormLayout()
        mainLayout.addLayout(formLayout, 0, 0)

        self.firstNameEdit = QtWidgets.QLineEdit()
        self.lastNameEdit = QtWidgets.QLineEdit()
        self.emailEdit = QtWidgets.QLineEdit()
        self.phoneEdit = QtWidgets.QLineEdit()
        self.locEdit = QtWidgets.QLineEdit()

        formLayout.addRow('First name:', self.firstNameEdit)
        formLayout.addRow('Last name:', self.lastNameEdit)
        formLayout.addRow('Email:', self.emailEdit)
        formLayout.addRow('Phone:', self.phoneEdit)
        formLayout.addRow('Location:', self.locEdit)

        self.addButton = QtWidgets.QPushButton('Add contact', enabled=False)
        mainLayout.addWidget(self.addButton, 1, 0)

        self.table = QtWidgets.QTableView()
        mainLayout.addWidget(self.table, 0, 1, 2, 1)
        self.table.setEditTriggers(self.table.NoEditTriggers)

        self.setCentralWidget(central)

        db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName('Contacts.db')
        self.model = QtSql.QSqlTableModel()
        self.model.setTable('contacts')
        self.model.select()
        self.table.setModel(self.model)

        self.mandatoryFields = self.firstNameEdit, self.lastNameEdit
        self.otherFields = self.emailEdit, self.phoneEdit, self.locEdit
        self.allFields = self.mandatoryFields   self.otherFields

        for field in self.allFields:
            field.textChanged.connect(self.validate)

        self.addButton.clicked.connect(self.addRecord)

    def addRecord(self):
        record = self.model.record()
        for i, field in enumerate(self.allFields):
            record.setValue(i, field.text())
        row = self.model.rowCount()
        self.model.insertRow(row)
        self.model.setRecord(row, record)

    def validate(self):
        for field in self.mandatoryFields:
            if not field.text():
                self.addButton.setEnabled(False)
                return
        for field in self.otherFields:
            if field.text():
                break
        else:
            self.addButton.setEnabled(False)
            return
        self.addButton.setEnabled(True)

Also note that Qt provides a very convenient class, QDataWidgetMapper, which allows to connect individual widgets with fields of a model. In this way you can automatically update the fields when the user selects an index, or update the database with all fields.

Here is a slightly modified version of the code above, implementing the mapper (for simplicity I'm posting the whole code again, as the modifications are too complex):

from PyQt5 import QtCore, QtWidgets, QtSql

class MainWindow(QtWidgets.QMainWindow):
    def __init__(self):
        super().__init__()
        central = QtWidgets.QWidget()
        mainLayout = QtWidgets.QGridLayout(central)
        mainLayout.setColumnStretch(0, 1)
        mainLayout.setColumnStretch(1, 2)

        formLayout = QtWidgets.QFormLayout()
        mainLayout.addLayout(formLayout, 0, 0)

        self.firstNameEdit = QtWidgets.QLineEdit()
        self.lastNameEdit = QtWidgets.QLineEdit()
        self.emailEdit = QtWidgets.QLineEdit()
        self.phoneEdit = QtWidgets.QLineEdit()
        self.locEdit = QtWidgets.QLineEdit()

        formLayout.addRow('First name:', self.firstNameEdit)
        formLayout.addRow('Last name:', self.lastNameEdit)
        formLayout.addRow('Email:', self.emailEdit)
        formLayout.addRow('Phone:', self.phoneEdit)
        formLayout.addRow('Location:', self.locEdit)

        self.updateButton = QtWidgets.QPushButton('Update contact', enabled=False)
        self.addButton = QtWidgets.QPushButton('Add contact', enabled=False)
        buttonLayout = QtWidgets.QHBoxLayout()
        buttonLayout.addWidget(self.updateButton)
        buttonLayout.addWidget(self.addButton)
        mainLayout.addLayout(buttonLayout, 1, 0)

        self.table = QtWidgets.QTableView()
        mainLayout.addWidget(self.table, 0, 1, 2, 1)
        self.table.setEditTriggers(self.table.NoEditTriggers)

        self.setCentralWidget(central)

        db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        db.setDatabaseName('Contacts.db')
        self.model = QtSql.QSqlTableModel()
        self.model.setTable('contacts')
        self.model.select()
        self.table.setModel(self.model)
        self.table.setSelectionMode(self.table.SingleSelection)

        self.mandatoryFields = self.firstNameEdit, self.lastNameEdit
        self.otherFields = self.emailEdit, self.phoneEdit, self.locEdit
        self.allFields = self.mandatoryFields   self.otherFields

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

        for i, field in enumerate(self.allFields):
            field.textChanged.connect(self.validate)
            self.mapper.addMapping(field, i)

        self.addButton.clicked.connect(self.addRecord)
        self.updateButton.clicked.connect(self.updateRecord)
        self.table.selectionModel().selectionChanged.connect(self.selectionChanged)
        self.mapper.currentIndexChanged.connect(self.validate)
        self.table.doubleClicked.connect(self.doubleClicked)
        self.table.viewport().installEventFilter(self)

    def eventFilter(self, obj, event):
        if event.type() == event.MouseButtonPress:
            # clear all fields when clicking on an empty area of the table
            index = self.table.indexAt(event.pos())
            if not index.isValid():
                self.table.selectionModel().select(
                    index, QtCore.QItemSelectionModel.ClearAndSelect)
        return super().eventFilter(obj, event)

    def doubleClicked(self, index):
        widget = self.mapper.mappedWidgetAt(index.column())
        if widget:
            widget.setFocus()

    def selectedRow(self):
        selected = self.table.selectedIndexes()
        if selected:
            return selected[-1].row()
        return -1

    def selectionChanged(self):
        row = self.selectedRow()
        self.mapper.setCurrentIndex(row)
        if row < 0:
            for field in self.allFields:
                field.setText('')
        self.validate()

    def addRecord(self):
        # we cannot use submit(), as it will use the *current* index, nor we can
        # insert the new row and set the new index, as it would clear all fields
        record = self.model.record()
        for i, field in enumerate(self.allFields):
            record.setValue(i, field.text())
        row = self.model.rowCount()
        self.model.insertRow(row)
        self.model.setRecord(row, record)
        self.table.selectionModel().select(self.model.index(row, 0), 
            QtCore.QItemSelectionModel.ClearAndSelect)

    def updateRecord(self):
        row = self.selectedRow()
        if row < 0:
            self.updateButton.setEnabled(False)
            return
        self.mapper.submit()

    def validate(self):
        valid = True
        for field in self.mandatoryFields:
            if not field.text():
                valid = False
                break
        else:
            for field in self.otherFields:
                if field.text():
                    break
            else:
                valid = False
        self.addButton.setEnabled(valid)
        self.updateButton.setEnabled(valid and bool(self.table.selectedIndexes()))