python 3.x - sqlite3 table into QTableWidget, sqlite3, PyQt5

Question

I have created a program that manages tables in a database, now i'm trying to make a gui for it, and i figured the easiest way would be to do it with Qt Designer and then convert it to python with PyQt5, and so i did it, i know how to bind functions to buttons, but i don't know more complicated things like displaying table columns and rows into a Qtablewidget, suppose i have the database bellow:

import sqlite3
conn = sqlite3.connect('realscheduler.db')
c = conn.cursor()
c.execute('pragma foreign_keys = ON;')
conn.commit()

With a table named professors, with 6 columns in it, what do i do to display this table, its columns and its rows on the QtableWidget bellow?

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'testui.ui'
#
# Created by: PyQt5 UI code generator 5.4.1
#
# WARNING! All changes made in this file will be lost!

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.horizontalLayout = QtWidgets.QHBoxLayout(self.centralwidget)
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.verticalLayout = QtWidgets.QVBoxLayout()
        self.verticalLayout.setObjectName("verticalLayout")
        self.tabWidget = QtWidgets.QTabWidget(self.centralwidget)
        self.tabWidget.setObjectName("tabWidget")
        self.tab = QtWidgets.QWidget()
        self.tab.setObjectName("tab")
        self.verticalLayout_2 = QtWidgets.QVBoxLayout(self.tab)
        self.verticalLayout_2.setObjectName("verticalLayout_2")
        self.tableWidget = QtWidgets.QTableWidget(self.tab)
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(0)
        self.tableWidget.setRowCount(0)
        self.verticalLayout_2.addWidget(self.tableWidget)
        self.tabWidget.addTab(self.tab, "")
        self.tab_2 = QtWidgets.QWidget()
        self.tab_2.setObjectName("tab_2")
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout(self.tab_2)
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.tableWidget_2 = QtWidgets.QTableWidget(self.tab_2)
        self.tableWidget_2.setObjectName("tableWidget_2")
        self.tableWidget_2.setColumnCount(0)
        self.tableWidget_2.setRowCount(0)
        self.horizontalLayout_2.addWidget(self.tableWidget_2)
        self.tabWidget.addTab(self.tab_2, "")
        self.verticalLayout.addWidget(self.tabWidget)
        self.horizontalLayout.addLayout(self.verticalLayout)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        self.tabWidget.setCurrentIndex(1)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "Tab 1"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "Tab 2"))

Answer 1

Before I answer this question, I highly recommend you open up your UI file in the Qt Designer and change your object names from tableWidget_2 and pushButton to something more appropriate as you are going to drive yourself insane.

Second of all, PyQt provides an entire API to work with databases called QtSql. You can access the api like so:

from PyQt5.QtSql import (QSqlDatabase, QSqlQuery) #and so on.

Rant over. I will answer your question now.

QTableWidgets are quite precarious to work with and require a couple of things:

1. A Counter Flag
2. Row Count
3. Column Count
4. Data (Obviously)

To insert data into the table you could do something like this.

def add_values(self):
    self.count = self.count + 1 # this is incrementing counter
    self.tableWidget_2.insertRow(self.count)
    self.tableWidget_2.setRowCount(self.count)
    # these are the items in the database
    item = [column1, column2, column3]
    # here we are adding 3 columns from the db table to the tablewidget
    for i in range(3):
        self.tableWidget_2.setItem(self.count - 1, i, QTableWidgetItem(item[i]))

However, if you just want to load the data into the QTableWidget you could do something like this and call it in the setup:

def load_initial_data(self):
    # where c is the cursor
    self.c.execute('''SELECT * FROM table ''')
    rows = self.c.fetchall()

    for row in rows:
        inx = rows.index(row)
        self.tableWidget_2.insertRow(inx)
        # add more if there is more columns in the database.
        self.tableWidget_2.setItem(inx, 0, QTableWidgetItem(row[1]))
        self.tableWidget_2.setItem(inx, 1, QTableWidgetItem(row[2]))
        self.tableWidget_2.setItem(inx, 2, QTableWidgetItem(row[3]))