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

安裝：SQLite Studio（資料庫管理的 App）：https://github.com/pawelsalawa/sqlitestudio/releases
下載資料庫樣本（學習 SQL）：SQLite Sample Database;  
參考網站：SQLite Python
資料庫來源：NIPS 2015 papers

Objective:

1. 學習 SQL 語言。
2. 學習基本的資料庫管理。
3. 學習從 Python 連接到 SQLite 資料庫進行資料存取操作。

Prerequisite:

1. Python 基本程式設計能力。

範例 1： 使用 SQLite Studio 學習基本的 SQL 語言。先熟習查詢（QUERY）的語法：SELECT。

參考：本範例資料庫來自樣本資料庫的 database.sqlite 檔，該資料庫的語法（schema）如下的表格。而 SQL 語法則從 SOLiteStudio （圖一）操作下列練習題。 練習題：

1. 查詢表格內容：查詢資料庫內每一個表格內的資料。

2. 計算數量：計算每個表格內資料的總數。

3. 數量與內容：計算 Papers 表格內有幾種 eventtype 的資料型態。

4. 條件式查詢：

   • 查詢有幾篇屬於 poster 類型（eventtype）的論文（Papers）。

   • 列出論文 id 介於 5636 ~ 5670 之間的論文 id 與標題（title）。

   • 列出論文標題（title）含 neural 的所有論文 id, title 與 eventtype。

   • 列出作者姓名之中有 smith 的所有作者全名（name）。

   • 列出論文 id 為 5633, 5634, 5642, 5648 的論文 id 與標題。

5. Joint Query：

   • 列出作者 id = 8387 的所有論文的 id, title 與 papertext。

   • 列出論文標題（title）含 deep 的所有論文的作者 id、論文 title 與 eventtype。

   • 列出作者姓名中有 smith 的所有作者姓名及其所有論文的 title 與 eventtype。（可以先分兩段 SQL：第一個 SQL 根據作者姓名有 smith 查出所有的 paperid；第二個 SQL 根據前面得到的 paperid 查詢論文 title, eventtype）

注意事項：

1. 學習 SQL 語言可以從表格少、結構簡單的資料庫樣本下手。譬如，上面「下載資料庫樣本」內的 SQLite 資料庫檔案：database.sqlite。該資料庫僅含三個表格：Authors, PaperAuthors, Papers。

2. SQLite Studio 算是個功能完整的小軟體，不但可以練習資料庫管理，也能練習 SQL 語言。如下圖所示。首先連結到名為 database.sqlite 的資料庫，便可以看到裡面的三個表格名稱（圖左）。點擊表格名稱便能看到表格內容，即欄位名稱與屬性等資料。下圖右上開啟了一個執行 SQL 指令的小視窗並執行一個 SELECT 的查詢指令，查詢結果顯示於下方。

範例 2：從 Python 連接到 SQLite 資料庫檔案、執行 SQL 指令並將回傳結果列印出來。

將範例 1 在 SQLite Studio 練習的 SQL 指令搬到 Python 程式裡，練習 Python 指令的一系列的標準程序，進行 (1) 如何連接資料庫(2) 準備與執行 SQL 指令(3) 取得執行結果 (4) 列印執行結果。 請將範例 1 所練習的 SQL 搬到程式來試試。

參考來源: https://www.sqlitetutorial.net/sqlite-python/

注意事項：

1. 下列程式碼呈現資料庫連線（connection）的基本動作。連線指令 sqlite3.connect(db_file) 掛在 try … except … 的架構裡。這是標準寫法，簡潔地做資料庫連線失敗的處裡。當然也可以在連線指令後面，再以 if … else … 處理。

2. 下列程式碼的 main() 開啟了資料庫連線，接著執行資料庫查詢的工作。這裡使用了 with 指令，目的是簡潔地處理資料庫連線的結束，不需要再使用 conn.close() 來結束連線，釋放資料庫連線所佔據的系統資料。

3. 在程式裡的資料庫的每一個查詢，通常會用一個函數來處哩，因為每個查詢的 SQL 指令不同，對取得資料的處裡也不同。

4. 資料庫查詢的基本指令有二：第一、 execute(sql)，第二、fetchall()。顧名思義，便是執行 SQL 與取得所有執行後的資料。一般而言，Python 程式的重頭戲是從取得資料開始，那才是程式真正的目的：處理資料。在此示範程式僅簡單的列印出 10 筆資料到 terminal 視窗來瞧一瞧。

5. 整個來說，在程式中進行資料庫查詢，需要做四件事：

   • 連接到一個指定資料庫並取得一個連線的物件（token, variable），譬如程式中的 conn，之後藉 conn 與資料庫溝通。

   • 從 conn 的連線中建立一個 cursor 作為取用資料的暫存區。

   • 透過 cursor 執行 SQL 指令。

   • 最後透過 cursor 取得 SQL 指令所需要的資料。

# SELECT data from a table

import sqlite3
from sqlite3 import Error

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(conn, tbname):
    """
    Query table data
    :param conn: the Connection object
    :param tbname: table name
    :return:
    """
    cur = conn.cursor()
    sql = "select * from " + tbname
    cur.execute(sql)
    rows = cur.fetchall()

    for row in rows[0:10]:
        print(row)

def main():
    database = r"../Database/database.sqlite"

    # create a database connection
    conn = create_connection(database)
    with conn: # with can take care conn.close()
        print("Connection successful")
        select_table(conn, "paperauthors") # authors, papers
    
    # with create_connection(database) as conn:
    #     print("Connection successful")
    #     select_table(conn, "paperauthors") # authors, papers

if __name__ == '__main__':
    main()

範例 3：接續範例 2，進一步執行稍複雜一點的任務：列印出論文共有幾種 eventtype？分別有幾篇？

特點：連續利用兩個 SQL 指令。

注意事項：

1. 想從資料庫查詢的資料不一定能用一條 SQL 指令取得，必要時可以分兩個 SQL 指令，即第二個 SQL 指令需要用到前一個 SQL 執行的結果。

2. 從執行 SQL 得到的結果，包裝在一個 tuple array 裡面，譬如 [(?,?,?), (?,?,?), (?,?,?)] 代表選取 3 種項目且得到 3 筆資料。下列程式碼的列印部分，幫助釐清這些資料型態。

3. 下列程式碼示範另一種組合完整 SQL 指令的方式，也就是在執行時 cur.execute(sql, rows[i]) 才在第二個參數放入條件值。該條件值對應到 SQL 指令的問號。

def count_eventtype(conn):
    """
    論文有哪機種型態(eventtype)?各有幾篇?
    """
    cur = conn.cursor()
    sql = "select distinct eventtype from papers"
    cur.execute(sql)
    rows = cur.fetchall()
    sql = "select count(*) from papers where eventtype = ?"
    
    for i in range(len(rows)):
        cur.execute(sql, rows[i])
        cnt = cur.fetchall()
        print(rows[i][0] +":" + str(cnt[0][0]))

範例 4：接續範例 3，建構 SQL 指令時，置入兩個或以上的問號。

查詢：自 papers 表格中查詢論文 id 介於 “5636” 與 “5670” 之間所有論文的 id 與 title。

注意事項：

1. 下列程式示範置入兩個問號時，execute 的第二個參數以 tuple 的方式結合對應的值。

2. 資料庫的應用程式不外乎處理兩件事：（一）包裝正確的 SQL 指令，（二）將執行的結果表達出來。

def paper_between(conn, from_id, to_id):
    cur = conn.cursor()
    sql = "select id, title from papers where id>=? and id <=?"
    cur.execute(sql,(from_id, to_id))
    rows = cur.fetchall()
    for row in rows:
        print(row)

練習：將範例一的所有 SQL 指令都放置在 Python 程式執行，並將 SQL 指令選取的資料都列印到終端畫面。