汪群超 Chun-Chao Wang

Dept. of Statistics, National Taipei University, Taiwan

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 的查詢指令,查詢結果顯示於下方。

圖一、SQLiteStudio 畫面

範例 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 視窗來瞧一瞧。

# 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 程式執行,並列印到終端畫面。

商學院  7F16
ccw@gm.ntpu.edu.tw
(02)8674-1111 
ext 66777

部落格統計

  • 99,322 點擊次數
%d bloggers like this: