Bài 24: Xử lý dữ liệu dạng bảng- QTableWidget và SQLite database–Part 3

# Form implementation generated from reading ui file 'MainWindow.ui'
#
# Created by: PyQt6 UI code generator 6.4.2
#
# WARNING: Any manual changes made to this file will be lost when pyuic6 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt6 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(560, 511)
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap("images/ic_logo.jpg"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        MainWindow.setWindowIcon(icon)
        self.centralwidget = QtWidgets.QWidget(parent=MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.label = QtWidgets.QLabel(parent=self.centralwidget)
        self.label.setGeometry(QtCore.QRect(30, 20, 101, 21))
        self.label.setObjectName("label")
        self.lineEditSQLite = QtWidgets.QLineEdit(parent=self.centralwidget)
        self.lineEditSQLite.setGeometry(QtCore.QRect(140, 20, 301, 22))
        self.lineEditSQLite.setObjectName("lineEditSQLite")
        self.pushButtonPickSQLite = QtWidgets.QPushButton(parent=self.centralwidget)
        self.pushButtonPickSQLite.setGeometry(QtCore.QRect(450, 20, 93, 28))
        icon1 = QtGui.QIcon()
        icon1.addPixmap(QtGui.QPixmap("images/ic_pickdatabase.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonPickSQLite.setIcon(icon1)
        self.pushButtonPickSQLite.setObjectName("pushButtonPickSQLite")
        self.label_2 = QtWidgets.QLabel(parent=self.centralwidget)
        self.label_2.setGeometry(QtCore.QRect(30, 60, 101, 21))
        self.label_2.setObjectName("label_2")
        self.cboTable = QtWidgets.QComboBox(parent=self.centralwidget)
        self.cboTable.setGeometry(QtCore.QRect(140, 60, 301, 22))
        self.cboTable.setObjectName("cboTable")
        self.pushButtonFetchMore = QtWidgets.QPushButton(parent=self.centralwidget)
        self.pushButtonFetchMore.setGeometry(QtCore.QRect(30, 430, 111, 31))
        icon2 = QtGui.QIcon()
        icon2.addPixmap(QtGui.QPixmap("images/ic_fetchmore.png"), QtGui.QIcon.Mode.Normal, QtGui.QIcon.State.Off)
        self.pushButtonFetchMore.setIcon(icon2)
        self.pushButtonFetchMore.setObjectName("pushButtonFetchMore")
        self.tableWidget = QtWidgets.QTableWidget(parent=self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(30, 100, 511, 311))
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(0)
        self.tableWidget.setRowCount(0)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(parent=MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 560, 26))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(parent=MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Trần Duy Thanh - QTableWidget - SQLite"))
        self.label.setText(_translate("MainWindow", "Choose SQLite:"))
        self.pushButtonPickSQLite.setText(_translate("MainWindow", "..."))
        self.label_2.setText(_translate("MainWindow", "Choose table:"))
        self.pushButtonFetchMore.setText(_translate("MainWindow", "Fetch More"))

import os.path

from PyQt6.QtSql import QSqlDatabase, QSqlTableModel
from PyQt6.QtWidgets import QFileDialog, QTableWidgetItem, QMessageBox
from MainWindow import Ui_MainWindow

class MainWindowEx(Ui_MainWindow):
    def setupUi(self, MainWindow):
        super().setupUi(MainWindow)
        self.MainWindow=MainWindow
        self.pushButtonPickSQLite.clicked.connect(self.processPickSQLite)
        self.cboTable.activated.connect(self.processSelectedTable)
        self.pushButtonFetchMore.clicked.connect(self.processFetchMore)

def processPickSQLite(self):
    #setup for QFileDialog
    filters = "SQLite database (*.sqlite);;All files(*)"
    filename, selected_filter = QFileDialog.getOpenFileName(
        self.MainWindow,
        filter=filters,
    )
    #get selected file name and showing on the QLineEdit
    self.lineEditSQLite.setText(filename)
    #create base dir
    baseDir = os.path.dirname(__file__)
    #set the database path
    databasePath = os.path.join(baseDir, filename)
    #create QSqlDatabase object
    self.db = QSqlDatabase("QSQLITE")
    #set the database selected path
    self.db.setDatabaseName(databasePath)
    #Open the SQLite database
    self.db.open()
    #get all tables in the selected SQLite
    tables= self.db.tables()
    self.cboTable.clear()
    #show all the table names into the QCombobox:
    for i in range(len(tables)):
        tableName=tables[i]
        self.cboTable.addItem(tableName)

def processSelectedTable(self):
    #Get the current Table Name in QCombobox
    tableName=self.cboTable.currentText()
    #Create QSqlTableModel object, and self.db is assigned
    self.model = QSqlTableModel(db=self.db)
    #select table name to invoke data
    self.model.setTable(tableName)
    #active for selecting data
    self.model.select()
    #reset QTableWidget to 0 row
    self.tableWidget.setRowCount(0)
    #get the column count for selected Table as automatic
    self.columns=self.model.record().count()
    #set columns count for QTableWidget
    self.tableWidget.setColumnCount(self.columns)
    #create labels array for Columns Headers
    labels=[]
    for i in range(self.columns):
        #get column name:
        fieldName=self.model.record().fieldName(i)
        #store the column name
        labels.append(fieldName)
    #set the columns header with labels
    self.tableWidget.setHorizontalHeaderLabels(labels)
    #loop for insert new row:
    for i in range(self.model.rowCount()):
        #insert new row:
        self.tableWidget.insertRow(i)
        #get a record with i index:
        record=self.model.record(i)
        #loop column to get value for each cell:
        for j in range(self.columns):
            #create QTableWidgetItem object
            item=QTableWidgetItem(str(record.value(j)))
            #set value for each CELL:
            self.tableWidget.setItem(i,j,item)

def processFetchMore(self):
    #check if the model can fetch more:
    if self.model.canFetchMore():
        #set the i index for last rowcount:
        i=self.model.rowCount()
        #call fetchmore method:
        self.model.fetchMore()
        #loop for new batch data:
        for i in range(i,self.model.rowCount()):
            # insert new row:
            self.tableWidget.insertRow(i)
            # get a record with i index:
            record = self.model.record(i)
            # loop column to get value for each cell:
            for j in range(self.columns):
                # create QTableWidgetItem object
                item = QTableWidgetItem(str(record.value(j)))
                # set value for each CELL:
                self.tableWidget.setItem(i, j, item)
    else:
        msg=QMessageBox()
        msg.setText("No more records to fetch")
        msg.exec()

import os.path

from PyQt6.QtSql import QSqlDatabase, QSqlTableModel
from PyQt6.QtWidgets import QFileDialog, QTableWidgetItem, QMessageBox
from MainWindow import Ui_MainWindow

class MainWindowEx(Ui_MainWindow):
    def setupUi(self, MainWindow):
        super().setupUi(MainWindow)
        self.MainWindow=MainWindow
        self.pushButtonPickSQLite.clicked.connect(self.processPickSQLite)
        self.cboTable.activated.connect(self.processSelectedTable)
        self.pushButtonFetchMore.clicked.connect(self.processFetchMore)
    def processPickSQLite(self):
        #setup for QFileDialog
        filters = "SQLite database (*.sqlite);;All files(*)"
        filename, selected_filter = QFileDialog.getOpenFileName(
            self.MainWindow,
            filter=filters,
        )
        #get selected file name and showing on the QLineEdit
        self.lineEditSQLite.setText(filename)
        #create base dir
        baseDir = os.path.dirname(__file__)
        #set the database path
        databasePath = os.path.join(baseDir, filename)
        #create QSqlDatabase object
        self.db = QSqlDatabase("QSQLITE")
        #set the database selected path
        self.db.setDatabaseName(databasePath)
        #Open the SQLite database
        self.db.open()
        #get all tables in the selected SQLite
        tables= self.db.tables()
        self.cboTable.clear()
        #show all the table names into the QCombobox:
        for i in range(len(tables)):
            tableName=tables[i]
            self.cboTable.addItem(tableName)
    def processSelectedTable(self):
        #Get the current Table Name in QCombobox
        tableName=self.cboTable.currentText()
        #Create QSqlTableModel object, and self.db is assigned
        self.model = QSqlTableModel(db=self.db)
        #select table name to invoke data
        self.model.setTable(tableName)
        #active for selecting data
        self.model.select()
        #reset QTableWidget to 0 row
        self.tableWidget.setRowCount(0)
        #get the column count for selected Table as automatic
        self.columns=self.model.record().count()
        #set columns count for QTableWidget
        self.tableWidget.setColumnCount(self.columns)
        #create labels array for Columns Headers
        labels=[]
        for i in range(self.columns):
            #get column name:
            fieldName=self.model.record().fieldName(i)
            #store the column name
            labels.append(fieldName)
        #set the columns header with labels
        self.tableWidget.setHorizontalHeaderLabels(labels)
        #loop for insert new row:
        for i in range(self.model.rowCount()):
            #insert new row:
            self.tableWidget.insertRow(i)
            #get a record with i index:
            record=self.model.record(i)
            #loop column to get value for each cell:
            for j in range(self.columns):
                #create QTableWidgetItem object
                item=QTableWidgetItem(str(record.value(j)))
                #set value for each CELL:
                self.tableWidget.setItem(i,j,item)
    def processFetchMore(self):
        #check if the model can fetch more:
        if self.model.canFetchMore():
            #set the i index for last rowcount:
            i=self.model.rowCount()
            #call fetchmore method:
            self.model.fetchMore()
            #loop for new batch data:
            for i in range(i,self.model.rowCount()):
                # insert new row:
                self.tableWidget.insertRow(i)
                # get a record with i index:
                record = self.model.record(i)
                # loop column to get value for each cell:
                for j in range(self.columns):
                    # create QTableWidgetItem object
                    item = QTableWidgetItem(str(record.value(j)))
                    # set value for each CELL:
                    self.tableWidget.setItem(i, j, item)
        else:
            msg=QMessageBox()
            msg.setText("No more records to fetch")
            msg.exec()
    def show(self):
        self.MainWindow.show()

from PyQt6.QtWidgets import QApplication, QMainWindow

from MainWindowEx import MainWindowEx

app=QApplication([])
myWindow= MainWindowEx()
myWindow.setupUi(QMainWindow())
myWindow.show()
app.exec()