pythonpython-3.xpyqtpyqt5qsqltablemodel

I cannot get my QSQLTableModel to connect to my database, so it does not populate the model when i call the select() method on the model,


The class below is a QMainWindow that is meant to use a QTableView as a central widget to show my contacts table in my database but I have been unable to get my QSQLTableModel object to connect to the database for some reason, the database file is stored in the same place as the code so i am not sure what i could be doing wrong

import sys
from PyQt5.QtCore import Qt
from PyQt5.QtSql import QSqlDatabase
from PyQt5.QtWidgets import QApplication
from PyQt5.QtWidgets import QLabel
from PyQt5.QtWidgets import QWidget
from PyQt5.QtWidgets import QFormLayout
from PyQt5.QtWidgets import QLineEdit
from PyQt5.QtWidgets import QMainWindow
from PyQt5.QtWidgets import QAction
from PyQt5.QtWidgets import QPushButton
from PyQt5.QtWidgets import QMenu
from PyQt5.QtSql    import  QSqlTableModel
from PyQt5.QtSql import QSqlQuery
from PyQt5.QtWidgets import QTableView

class Menu(QMainWindow):
    model = None
    con = None
    cursor = None
    def __init__(self):
        super(Menu,self).__init__()
        self.resize(415, 200)
        toolbar = self.addToolBar("Exit")
        self.model = QSqlTableModel(self)
        self.createConnection() 
        ##Here the connection is created to the database where the contatcts table is also created
        self.model.setTable("contacts") #Here i am seleecting the newly created table as the table we want to use
        print(self.con.open())
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.setHeaderData(0, Qt.Horizontal, "ISO_CODE")
        self.model.setHeaderData(1, Qt.Horizontal, "Location")
        self.model.setHeaderData(2, Qt.Horizontal, "Date")
        self.model.setHeaderData(3, Qt.Horizontal, "Email")
        self.model.setHeaderData(4, Qt.Horizontal, "New_cases")
        self.model.setHeaderData(3, Qt.Horizontal, "New_deaths")
        b = self.model.select() #I tried to check if the population was succesfull cause this method returns a booelean, it always returns false and i do get a blank view so i believe it does not populate the model
        print(b)
        # Set up the view
        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.resizeColumnsToContents()
        self.setCentralWidget(self.view)


  def createConnection(self):
        self.con = QSqlDatabase.addDatabase("QSQLITE")
        self.con.setDatabaseName(r"C:\Users\Gabri\PycharmProjects\PyQT\recordsTest.sqlite")
        if not self.con.open():
            QMessageBox.critical(
                None,
                "QTableView Example - Error!",
                "Database Error: %s" % con.lastError().databaseText(),
            )
            sys.exit(1)
        self.cursor = QSqlQuery()
        self.cursor.exec(
            """
            CREATE TABLE contacts (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                name VARCHAR(40) NOT NULL,
                job VARCHAR(50),
                email VARCHAR(40) NOT NULL
            )
            """
        )
        print(self.con.tables())






app = QApplication(sys.argv)
win = Menu()
win.show()


sys.exit(app.exec_())

Solution

  • If you check the docs for QSqlTableModel:

    QSqlTableModel::QSqlTableModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase())
    Creates an empty QSqlTableModel and sets the parent to parent and the database connection to db. If db is not valid, the default database connection will be used.

    The default edit strategy is OnRowChange.

    (emphasis mine)

    It is seen that by not passing a QSqlDataBase() then it will use the existing connection but in your case it does not exist so nothing will be loaded. The solution is to create the QSqlTableModel after connecting:

    self.createConnection() 
    self.model = QSqlTableModel(self)