Qt Designer + PyQt + SQLite 資料庫的技術與應用（二）

Objective:

學習製作結合 SQLite 資料庫與 Python （PySQLite）的 GUI 應用程式。

範例 1：製作一簡單的 GUI 並連接到資料庫，透過表格選取，以 TableView 陳列表格內容，如下右圖。

注意事項：

第一次嘗試在 GUI 連接資料庫，盡量用最簡單的方式，將連接資料庫、執行 SQL 指令與呈現資料在表格上的一貫程序確實完成。
設計 ui 檔時，先將三個表格名稱輸入到 comboBox 元件，分別是 Authors, PaperAuthors 及 Papers。這個方式並不理想，不過一開始先讓事情簡單些，待下一個範例再來處理如何自從資料庫直接取得表格名稱，再寫入 comboBox 內。
這個應用程式功能不多，但該做的事一樣不少，因此程式碼輕易超過百行。
程式碼的第一個 class Tablemodel 主要負責表格 tableView 的資料呈現，而且採取 pandas 的格式。只要格式固定，這個部分可以直接略過不看，成為固定的角色。
經過執行查尋的 SQL 指令後，除了從 cur.fetchall() 取得欄位資料外，還可以從 cur.description 取得欄位名稱，如下列程式碼 names = [description[0] for description in cur.description]。隨後將 names 交給 pandas 的欄位做後續的呈現，譬如 self.df.columns = names
圖中的表格做左邊的列號（row index）不是從 1 開始，試著去調整 pandas 的 dataframe 的 index 屬性，譬如 self.df.index = …
在執行 SQL 指令的函數 SQLExecute() 做了「例外」處理，即當搜尋的結果為 0 筆時，啟動一對話視窗，告知使用者「Nothing Found」，免得使用者感覺好像什麼事也沒發生。其實，在這個簡單的 App，僅查詢三個表格，而且確定都有資料，並沒有機會引動這個例外處理，在此只是做個示範，代表這是這個函數必備的處理程序。

import sys
from PyQt6 import QtCore, QtGui, QtWidgets, uic
from PyQt6.QtWidgets import QMessageBox 
from PyQt6.QtCore import Qt
import pandas as pd
import sqlite3
from sqlite3 import Error

class TableModel(QtCore.QAbstractTableModel):

    def __init__(self, data):
        super(TableModel, self).__init__()
        self._data = data

    def data(self, index, role):
        if role == Qt.ItemDataRole.DisplayRole:
            value = self._data.iloc[index.row(), index.column()] #pandas's iloc method
            return str(value)

        if role == Qt.ItemDataRole.TextAlignmentRole:          
            return Qt.AlignmentFlag.AlignVCenter + Qt.AlignmentFlag.AlignHCenter
        
        if role == Qt.ItemDataRole.BackgroundRole and (index.row()%2 == 0):
            return QtGui.QColor('#d8ffdb')

    def rowCount(self, index):
        return self._data.shape[0]

    def columnCount(self, index):
        return self._data.shape[1]

    # Add Row and Column header
    def headerData(self, section, orientation, role):
        # section is the index of the column/row.
        if role == Qt.ItemDataRole.DisplayRole: # more roles
            if orientation == Qt.Orientation.Horizontal:
                return str(self._data.columns[section])

            if orientation == Qt.Orientation.Vertical:
                return str(self._data.index[section])


class MainWindow(QtWidgets.QMainWindow):

    def __init__(self):
        super().__init__()
        uic.loadUi('PySQLite_Designer_1.ui', self)
        self.table = self.tableView
        
        database = r"../Database/database.sqlite"
        # create a database connect
        self.conn = create_connection(database)
        with self.conn: # with can handle the exceptions, like resources released, cleaning...
            select_table(self, "Authors")

        # Signals
        self.select_table.currentIndexChanged.connect(self.queryTable)
        self.pBut_exit.clicked.connect(self.appEXIT)

    # Slots
    def queryTable(self,i):
        tbname = self.select_table.currentText()
        select_table(self, tbname)
    
    def appEXIT(self):
        self.conn.close() # close database
        self.close() # close app
    
def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def select_table(self, tbname):
    sql = "select * from " + tbname
    SQLExecute(self, sql)
    
def SQLExecute(self, SQL):
    """
    Execute a SQL command and display the requested items on the QTableView
    :param conn: SQL command
    :return: None
    """
    cur = self.conn.cursor()
    cur.execute(SQL)
    rows = cur.fetchall()
    if len(rows) == 0: # nothing found
        # raise a messageBox here
        dlg = QMessageBox(self)
        dlg.setWindowTitle("SQL Information: ")
        dlg.setText("Nothing Found !!!")
        dlg.setStandardButtons(QMessageBox.StandardButton.Yes)
        buttonY = dlg.button(QMessageBox.StandardButton.Yes)
        buttonY.setText('OK')
        dlg.setIcon(QMessageBox.Icon.Information)
        button = dlg.exec()
        return
    
    # Process fetched output
    names = [description[0] for description in cur.description]# extract column names
    self.df = pd.DataFrame(rows)
    self.model = TableModel(self.df)
    self.table.setModel(self.model)
    self.df.columns = names
    self.table.resizeColumnToContents(0) # resize the width of the 1st column

def main():
    app = QtWidgets.QApplication(sys.argv)
    main = MainWindow()
    main.show()
    sys.exit(app.exec())

if __name__ == '__main__':
    main()

範例 2：範例 1 的 comboBox 內容代表資料庫的表格，最好能直接從資料庫擷取，而非在 GUI 設計階段置入。本範例加入查詢資料庫的表格名稱，再寫入 comboBox 元件當作選項。

注意事項：

如果只是針對固定的資料庫做查詢，這個選取表格名稱的動作並非必要。不過如果應用成面擴大到可以先選取資料庫，再依所選取的資料庫選擇表格，那麼自動選取表格名稱一定是必備的技術。
下列程式碼片段用來從資料庫截取表格資訊。

...
        database = r"../Database/database.sqlite"
        # create a database connect
        self.conn = create_connection(database)
        with self.conn: # with can handle the exceptions, like resources released, cleaning...
            tblNames = select_tblName(self)
            for tbl in tblNames:
                self.select_table.addItem(tbl[0])

            select_table(self, tblNames[0][0]) #先選擇第一個

...

def select_tblName(self):
        sql = "SELECT name FROM sqlite_schema WHERE type = 'table' order by 1"
        cur = self.conn.cursor()
        cur.execute(sql)
        rows = cur.fetchall()
        return rows

練習：學了基本的技術後，必須嘗試做一個類似的 app 才能確定自己的理解對或不對。換句話說，學習程式語言不是光看就會，需要實作。製作一個如下圖的資料庫 SQL 語言練習的 app。只做三件事：一、接收使用者輸入的 SQL 指令，二、執行並取得結果後，呈現在表格上，三、處理 SQL 語法錯誤的訊息呈現。

功能：

設定每次呈現在表格的資料筆數，譬如 10 筆。於是要加入頁數的處裡，如圖右側的上一頁、下一頁、第一頁與最後一頁的處理。
除利用上、下頁捲動資料以外，還可以製作一 comboBox 將所有頁碼放在裡面，可以跳到指定的頁碼。
製作一 label 呈現目前表格內的資料所屬的頁碼。
另外如果能呈現每次查詢結果的總筆數及總頁碼，對使用者來說更體貼了。

注意事項：

這是一個非常好的練習題，除了熟悉資料庫的存取之外，還不斷地演練 GUI 的技術，譬如對於 comboBox 的項目寫入與選取的處理。還有處理上一頁、下一頁這類的需求，都必須一再地練習，讓技術更穩定、成熟。
可想而知，處理頁碼的捲動，只是針對 SQL 指令所得到的資料，不是每次都需要執行 SQL 指令。因此必須設定一個共用變數，可以在任何函數裡取得內容，譬如，將 SQL 指令所得的資料放在 self.Data。
題目的三大功能之一是 SQL 指令錯誤的處理。當使用者的 SQL 指令語法不對時，如何捕捉錯誤的訊息，並告知使用者？這便是寫程式常說的「例外處理」（exception handling）或錯誤處理（error handling），如圖二、圖三的兩個錯誤的警告對話視窗。作法如下列程式碼。不過，一般的 app 並不會有 SQL 語法的錯誤，因為 SQL 指令都是在程式裡面建構，當然都是經過調校過的正確指令。這個範例的做法是比較特殊的。
下列程式碼的函數 SQLExecute() 只專注在 SQL 指令的執行，並回傳結果。這是一般程式的經典處理方式。在函數裡加入 try … except …，負責捕捉錯誤訊息 Error，一旦出現錯誤訊息，便將該訊息傳遞給另一個專門呈現警告對話視窗的函數 display_message()，傳遞前，還將錯誤訊息轉成字串，即 str(e)，代表原來的錯誤訊息並非以字串格式存在。如果想知道錯誤訊息到底是甚麼格式，除了查詢手冊外，也可以採 debug 模式執行程式，並停在 except 裡面來觀察。
圖一的四個按鈕多了 icon 的小圖，適當的小圖示能潤色整個畫面，不會只是乾扁扁的文字與固定元件型態。免費的 icons 圖可以從 https://feathericons.com/ 下載。

# SQL 指令錯誤的例外處理
...
def SQLExecute(self, SQL):
    self.cur = self.conn.cursor()
    try:
        self.cur.execute(SQL)
    except Error as e:
        display_message(str(e))
        return None
    
    rows = self.cur.fetchall()
    if len(rows) == 0: # nothing found
        # raise a messageBox here
        display_message("No data found for this query!")
        return
    return rows
...
...
def display_message(message):
    dlg = QMessageBox()
    dlg.setWindowTitle("SQL Information: ")
    dlg.setText(message)
    dlg.setStandardButtons(QMessageBox.StandardButton.Yes)
    buttonY = dlg.button(QMessageBox.StandardButton.Yes)
    buttonY.setText('OK')
    dlg.setIcon(QMessageBox.Icon.Information)
    dlg.exec()

範例 3：依循前面範例的技術，開始做簡單的關鍵字查詢，逐步擴展功能，朝商業化應用程式邁進。本範例規劃兩個單獨的關鍵字查詢，分別依作者姓名與論文標題查詢論文的資料，並可以選擇呈現哪些欄位資料（標題 Title、型態 Eventtype、摘要 Abstract、全文 PaperText）。GUI 畫面如下圖一、二所示。

功能：

利用 checkBox 元件，作為欄位的選擇（可多選）。
雙擊表格中任一篇論文的任一欄位，隨即在右邊的 TextBrowser 呈現出該篇論文的所有作者名字與摘要，如圖三。這牽涉到點擊 TableView 引動的 signal（參考下列程式）。
本範例安排在左上角 File 做 Save Data 的功能，並啟動 SaveFile 的對話機制，最後將查詢到的資料儲存成 EXCEL 檔。如圖四所示。

注意事項：

讀者想從下列程式碼學習所有細節的處理時，最好採逐步測試的方式。譬如，先依樣畫葫蘆設計好 ui 檔，元件的名稱可以從程式去比對。先準備好 ui 檔，能順利開啟，再來逐步測試程式的所有功能。因此最好先蓋掉（comment）大部分的程式碼，再慢慢解開，依序執行。
根據論文標題關鍵字查詢的 SQL 指令較單純，可以先做。
根據作者姓名查詢牽涉到三個表格的 Joint query，可以採兩階段或甚至三階段的 SQL 指令。先求完成任務，再來考慮是否有更精簡的 SQL 指令（譬如 select id, title, eventtype, abstract from papers where id in ( select paperid from paperauthors A, authors B where B.name like ‘%smith%’ and A.authorid=B.id)）
本範例使用的資料庫很小，因此不需要考慮 SQL 指令的效率問題。因為不管怎麼做，速度都在彈指之間。
點擊表格後，下列程式碼做了兩個不同的處理；第一、從方才查詢到的資料裡面，找到 Abstract 並寫到 TextBrowser。資料裡面有可能不包括 Abstract，因此必須先做檢查，否則會引動錯誤的訊息並關掉程式。第二、對作者名字的部分則另啟 SQL 指令，取得作者姓名，所根據的便是該論文的 paperid。當然，Abstract 的部分也可以另啟 SQL 指令，以 paperid 為條件。

from PyQt6 import QtCore, QtGui, QtWidgets, uic
from PyQt6.QtWidgets import QMessageBox 
from PyQt6.QtCore import Qt
import pandas as pd
import sqlite3
from sqlite3 import Error
import sys

class TableModel(QtCore.QAbstractTableModel):

    def __init__(self, data):
        super(TableModel, self).__init__()
        self._data = data

    def data(self, index, role):
        if role == Qt.ItemDataRole.DisplayRole:
            value = self._data.iloc[index.row(), index.column()] #pandas's iloc method
            return str(value)

        if role == Qt.ItemDataRole.TextAlignmentRole:          
            return Qt.AlignmentFlag.AlignVCenter + Qt.AlignmentFlag.AlignHCenter
        
        if role == Qt.ItemDataRole.BackgroundRole and (index.row()%2 == 0):
            return QtGui.QColor('#d8ffdb')

    def rowCount(self, index):
        return self._data.shape[0]

    def columnCount(self, index):
        return self._data.shape[1]

    # Add Row and Column header
    def headerData(self, section, orientation, role):
        # section is the index of the column/row.
        if role == Qt.ItemDataRole.DisplayRole: # more roles
            if orientation == Qt.Orientation.Horizontal:
                return str(self._data.columns[section])

            if orientation == Qt.Orientation.Vertical:
                return str(self._data.index[section])


class MainWindow(QtWidgets.QMainWindow):

    def __init__(self):
        super().__init__()
        uic.loadUi('PySQLite_Designer_3.ui', self)
        self.table = self.tableView
        
        database = r"../Database/database.sqlite"
        # create a database connect
        self.conn = create_connection(database)
        self.setWindowTitle('Paper Query System')

        # Signals
        self.actionEXIT.triggered.connect(self.appEXIT)
        self.lineEdit_title.returnPressed.connect(self.searchByTitle)
        self.p_But_by_title.clicked.connect(self.searchByTitle)
        self.table.doubleClicked.connect(self.rowSelected)
        self.actionSave_Data.triggered.connect(self.saveData)
        
    # Slots
    def searchByTitle(self):
        title_key = self.lineEdit_title.text()
        # sql = "select id, title, eventtype, abstract from papers where title like '%"+title_key+"%'"
        sql = "select id"
        
        if self.checkBox_title.isChecked():
            sql = sql + ",title"
        if self.checkBox_type.isChecked():
            sql = sql + ",eventtype"
        if self.checkBox_abstract.isChecked():
            sql = sql + ",abstract"    
        if self.checkBox_text.isChecked():
            sql = sql + ", papertext"
        
        sql = sql + " from papers where title like '%"+title_key+"%'"
        with self.conn:
            self.rows = SQLExecute(self, sql)
            if len(self.rows) > 0: 
                ToTableView(self, self.rows)
    
    def rowSelected(self, mi):
        # print([mi.row(), mi.column()])
        if 'Abstract' in self.df.columns:
            col_list = list(self.df.columns)
        else:
            print('No Abstract from the Query')
            return
        # display Abstract on TextBrowser, then go fetch author names
        self.textBrowser_abstract.setText(self.df.iloc[mi.row(), col_list.index('Abstract')])
        show_authors(self, self.df.iloc[mi.row(), 0])

    def saveData(self):
        fname, _ = QtWidgets.QFileDialog.getSaveFileName(self, 'Save file', 
            "", "EXCEL files (*.xlsx)")
        if len(fname) != 0:
            self.df.to_excel(fname)

    def appEXIT(self):
        self.conn.close() # close database
        self.close() # close app
    
def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def SQLExecute(self, SQL):
    """
    Execute a SQL command
    :param conn: SQL command
    :return: None
    """
    self.cur = self.conn.cursor()
    self.cur.execute(SQL)
    rows = self.cur.fetchall()

    if len(rows) == 0: # nothing found
        # raise a messageBox here
        dlg = QMessageBox(self)
        dlg.setWindowTitle("SQL Information: ")
        dlg.setText("No data match the query !!!")
        dlg.setStandardButtons(QMessageBox.StandardButton.Yes)
        buttonY = dlg.button(QMessageBox.StandardButton.Yes)
        buttonY.setText('OK')
        dlg.setIcon(QMessageBox.Icon.Information)
        button = dlg.exec()
        # return
    return rows

def ToTableView(self, rows):
    """
    Display rows on the TableView in pandas format
    """
    names = [description[0] for description in self.cur.description]# extract column names
    self.df = pd.DataFrame(rows)
    self.model = TableModel(self.df)
    self.table.setModel(self.model)
    self.df.columns = names
    self.df.index = range(1, len(rows)+1)
    
def show_authors(self, paperid):
    sql = "select name from authors A, paperauthors B where B.paperid="+str(paperid)+" and A.id=B.authorid"
    with self.conn:
        self.rows = SQLExecute(self, sql)
        names =""
        for row in self.rows:
            names = names + row[0] +"; "
        self.textBrowser_authors.setText(names)

def main():
    app = QtWidgets.QApplication(sys.argv)
    main = MainWindow()
    main.show()
    sys.exit(app.exec())

if __name__ == '__main__':
    main()

專題：將 Author Name 與 title 同時做關鍵字查詢，如下圖。雙擊表格的任一格，可在右側秀出該筆資料的摘要與相關圖片。

注意事項：

本範例刻意擴充了前述範例所使用的資料庫，將表格 papers 加了一個欄位「imgfile」來紀錄與這筆資料相關的圖片檔案名稱。其實，資料庫 database.sqlite 並沒有紀錄影像圖片的欄位，更沒有相關的圖片。在此僅僅為了加入多媒體的應用，展現資料庫除了文字以外，也可以與其他媒體檔案合作。於是，在前一個單元除了下載資料庫檔案外，也附上對應的 403 張圖片檔案。下列程式碼展示如何將圖片檔案的檔名寫入資料庫。至於在 database.sqlite 的表格 papers 中，加入一個欄位 imgfile，可以在 SQLiteStudio 裡完成。
一般而言，當我們試著變更資料庫的內容前，會先複製一份資料庫來試試看。下列程式碼採用的資料庫 test.sqlite 便是這個目的。直到測試成功，才會直接針對目標資料庫。
如果想將論文的所有資料都展示出來，這裡設計的空間還是小了些，畫面必須再擴充。另一個方案是開啟一個子視窗，專職論文資料的呈現，特別是冗長的論文原文。這個問題呈現在下一個專題。

# 將整個目錄內的圖片檔名寫入表格 papers 的欄位 imgfile

import sqlite3
from sqlite3 import Error
import os

def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def fetch_paperid(conn):
    cur = conn.cursor()
    sql = "select id from papers"
    cur.execute(sql)
    rows = cur.fetchall()
    return rows
    
def update_papers(conn, params):
    
    sql = "UPDATE papers set imgfile = ? WHERE id = ?"
    cur = conn.cursor()
    cur.execute(sql, params)
    conn.commit()

def main():
    database = r"../Database/test.sqlite" # 資料庫的位置
    file_src = r"../Database/NIP2015_Images/"  # 圖片檔的位置
    picName = os.listdir(file_src)
    # create a database connection
    conn = create_connection(database)
    # fetch paper id
    paperid = fetch_paperid(conn)
    with conn:
        for i in range(len(paperid)):
            update_papers(conn, (picName[i], paperid[i][0]))

    conn.close()

if __name__ == '__main__':
    main()

專題：將 Author Name 與 title 同時做關鍵字查詢，如下圖一。雙擊表格的任一格，開啟一子視窗來呈現該論文的所有資料，如圖二。在圖二中，按《Back to Query》按鈕，變關閉子視窗，回到主視窗。

注意事項：

開啟並傳遞資料到子視窗的技術，請見 Qt Designer 設計概念及與 PyQt 結合的技術（三）範例 7 的介紹。在該範例中，示範資料的雙向傳遞，即開啟並傳遞資料到子視窗；關閉子視窗時，也同時傳送資料回主視窗。本範例較單純，子視窗關閉時，並不需要傳遞資料回主視窗。
因為一篇論文的資料很多，一般的查詢系統為顧及效率，通常在主視窗的查詢結果僅顯示每篇論文的部分資料，當使用者有興趣觀看更多細節資料時，再打開一子視窗顯示所有細節。因此，可以僅傳送 paper_id 到子視窗，到了子視窗在進行一次或多次 SQL 指令，將所有資料備齊。