Python 與SQLite 資料庫

大綱

• SQLite 基本觀念
• 資料庫連線
• SQLite 資料類型
• 建立SQLite 資料庫表單
• 增加SQLite 資料庫表單紀錄
• 查詢SQLite 資料庫表單
• 更新SQLite 資料庫表單紀錄
• 刪除SQLite 資料庫表單紀錄
• DB Browser for SQLite
• 安裝DB browser for sqlite
• 建立新的 SQLite 資料庫
• 打開舊的secret資料庫
• 將台北人口數儲存至 SQLite 資料庫

SQLite 基本觀念

在先前我們有說明 CSV、JSON...等資料格式，我們可以將資料以這些格式儲存，不過我們使用資料時，有時候只是取一個小小的部分，如果每次都要大費周章的開啟檔案處理完成在儲存檔案，其實不是很經濟的事情。

一個好的解決方式是使用輕量級的資料庫程式當作儲存媒介，未來我們可以使用資料庫語法取得資料庫的部分有用資料，這將是一個很好的想法。本文我們將介紹如何使用 Python 建立 SQLite 資料庫，同時也將說明使用 Python 插入insert、截取select、更新update、刪除delete，SQLite資料庫的內容。

Python 3.0版以上安裝完成後有內建 SQLite 資料庫，本文將以此為實例說明，在使用此 SQLite 前需要 import 導入此 SQLite :

import sqlite3

資料庫連線

執行 Python 與資料庫連線的方法如下 :

conn = sqlite3.connect(“資料庫名稱”)

上述 conn 是定義的物件名稱，讀者也可以自行定義不一樣的名稱。connect()  方法執行時，如果 connect() 內的資料庫名稱存在，就可以將 Python 程式與此資料庫名稱建立連線，然後我們可以再 Python 程式內做更進一步的操作，如果這樣庫名稱不存在就會以此為名稱建立一個新的資料庫，然後執行資料庫連線。

資料庫操作結束，我們可以在 Python 內使用下列方法結束 Python 程式與資料庫的連線。

conn.close()

範例 pythonSQLite-01.py : 建立一個新的資料庫 myData.db ，我們習慣使用的 db 當副檔名稱。

# pythonSQLite-01.py
import sqlite3
conn = sqlite3.connect("myData.db")
conn.close()

執行結果

這個程式沒有執行結果，不過可以看到資料夾內有新建的資料庫檔案 myData.db

SQLite 資料類型

SQLite資料庫內的資料可以是以下資料類型 : 

資料類型*****	描述
NULL	值是一個 NULL 值。
INTEGER	值是一個帶符號的整數，根據值的大小儲存在 1、2、3、4、6 或 8 位元組中。
REAL	值是一個浮點值，儲存為 8 位元組的 IEEE 浮點數字。
TEXT	值是一個文字字串，使用資料庫編碼（UTF-8、UTF-16BE 或 UTF-16LE）儲存。
BLOB	二進位大型物件(Binary Large Object)，值是一個 blob 資料，完全根據它的輸入儲存，例如 : 一個圖片、影片、音樂。

建立SQLite 資料庫表單

以上，我們使用 connect() 方法建立資料庫連線，這時會回傳 connect 物件，我們用 conn 變數儲存此物件，這物件可以有以下常用方法:

connect 物件的方法

• close() : 資料庫連線操作結束。
• commit() : 更新資料庫內容。
• cursor() : 建立 cursor 物件，可想像是在資料庫中移動的游標，然後執行 execute() 方法。
• execute() : 執行 SQL 資料庫指令，建立create 、插入insert、截取select、更新update、刪除delete....等資料庫紀錄 record。

範例 pythonSQLite-02.py : 建立一個新的資料庫 myDatabase.db ，此資料庫有個表單，名稱為 "students"。

# pythonSQLite-02.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")   # 資料庫連線
cursor = conn.cursor()
sql = '''Create table students(  
        id int,
        name text,
        gender text)'''
cursor.execute(sql)                     # 執行SQL指令
cursor.close()                          # 關閉
conn.close()                            # 關閉資料庫連線

範例 pythonSQLite-03.py : 在範例 pythonSQLite-02.py 中，我們使用 cursor() 方法建立物件，然後再啟動 execute() 方法，我們也可以省略 cursor()。

# pythonSQLite-03.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
sql = '''Create table students(  
        id int,
        name text,
        gender text)'''
conn.execute(sql)                       # 執行SQL指令
conn.close()                            # 關閉資料庫連線

範例 pythonSQLite-03-1.py : id 使用自動新增 1 方式處理，此程式會建立 student2 表單。

# pythonSQLite-03.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
sql = '''Create table student2(  
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        gender TEXT)'''
conn.execute(sql)                       # 執行SQL指令
conn.close()                            # 關閉資料庫連線

增加SQLite 資料庫表單紀錄

範例 pythonSQLite-04.py : 由螢幕輸入 student 表單，此輸入迴圈再輸入完每一筆紀錄會詢問 "繼續(y/n)?" ，按 n 結束程式，也可按 Ctrl+C 強制結束程式。

# pythonSQLite-04.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
print("請輸入myInfo資料庫students表單資料")
while True:
    new_id = int(input("請輸入id : "))  # 轉成整數
    new_name = input("請輸入name : ")
    new_gender = input("請輸入gender : ")
    x = (new_id, new_name, new_gender)
    sql = '''insert into students values(?,?,?)'''  
    conn.execute(sql,x)
    conn.commit()                       # 更新資料庫
    again = input("繼續(y/n)? ")
    if again[0].lower() == "n":
        break
conn.close()   

執行結果

請輸入myInfo資料庫students表單資料
請輸入id : 1
請輸入name : yang
請輸入gender : m
繼續(y/n)? n

上述程式可以將每筆紀錄處理成元組 ( tuple )，然後將 SQL 語法處理成字串，最後將元組與字串當作 execute() 方法的參數。

其實，在真實案例中建立表單，最重要的關鍵欄位 id 並不一定是數字，更多時候是字串。

範例 pythonSQLite-04-1.py : 使用 id 自動新增 1 方式，建立 student2 表單。

# pythonSQLite-04-1.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
print("請輸入myInfo資料庫student2表單資料")
while True:
    n_name = input("請輸入name : ")
    n_gender = input("請輸入gender : ")
    x = (n_name, n_gender)
    sql = '''insert into student2(name, gender) values(?,?)'''  
    conn.execute(sql,x)
    conn.commit()                       # 更新資料庫
    again = input("繼續(y/n)? ")
    if again[0].lower() == "n":
        break
conn.close()                            # 關閉資料庫連線

查詢SQLite 資料庫表單

查詢表單的關鍵字是 SELECT ，下列是列出所有表單的 SQL 語法。

SELECT * from students

範例 pythonSQLite-05.py : 建列出所有 students 表單內容。

# pythonSQLite-05.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
results = conn.execute("SELECT * from students")
for record in results:
    print("id = ", record[0])
    print("name = ", record[1])
    print("gender = ", record[2])
conn.close()   
conn.close()

執行結果

id =  1
name =  yang
gender =  m

sqlite3 模組內有 fetchall() 方法，可以將所獲得的資料儲存到元素是元組的串列內。

範例 pythonSQLite-06.py : 以元組元素方式列出所有 students 表單內容。

# pythonSQLite-06.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
results = conn.execute("SELECT * from students")
allstudents = results.fetchall()        # 結果轉成元素是元組的串列
print(type(allstudents))
for student in allstudents:
    print(student)
conn.close()                            # 關閉資料庫連線

執行結果

<class 'list'>
(1, 'yang', 'm')
(2, 'aron', 'm')
(5, 'linda', 'f')

範例 pythonSQLite-07.py : 只列出 students 表單中的 name 欄位內容。

# pythonSQLite-07.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
results = conn.execute("SELECT name from students")
allstudents = results.fetchall()        # 結果轉成元素是元組的串列
for student in allstudents:
    print(student)
conn.close()                            # 關閉資料庫連線

執行結果

('yang',)
('aron',)
('linda',)

範例 pythonSQLite-08.py : 查詢所有男生紀錄( where gender = "m" )，只列出 students 表單中的 name、gender 欄位內容。

# pythonSQLite-08.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
sql = '''SELECT name, gender
        from students
        where gender = "m"'''
results = conn.execute(sql)
allstudents = results.fetchall()        # 結果轉成元素是元組的串列
for student in allstudents:
    print(student)
conn.close()                            # 關閉資料庫連線

執行結果

('yang', 'm')
('aron', 'm')

更新SQLite 資料庫表單紀錄

更新表單紀錄 record 的關鍵字是 UPDATE，語法如下 :

UPDATE 表單 set 欄位 新內容 where 指名哪一筆紀錄

範例 pythonSQLite-09.py : 將 id = 1 的紀錄 name 名字改為 "Tomy"。

# pythonSQLite-09.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
sql = '''UPDATE students
        set name = "Tomy"
        where id = 1'''
results = conn.execute(sql)
conn.commit()                           # 更新資料庫
results = conn.execute("SELECT name from students")
allstudents = results.fetchall()        # 結果轉成元素是元組的串列
for student in allstudents:
    print(student)
conn.close()                            # 關閉資料庫連線

執行結果

('Tomy',)
('aron',)
('linda',)

刪除SQLite 資料庫表單紀錄

刪除表單紀錄 record 的關鍵字是 DELETE，語法如下 :

DELETE from 表單 where 指名哪一筆紀錄

上述指令完成後，需要 commit() 更新資料庫。

範例 pythonSQLite-10.py : 將 id = 2 的紀錄刪除。

# pythonSQLite-10.py
import sqlite3
conn = sqlite3.connect("myDatabase.db")     # 資料庫連線
sql = '''DELETE
        from students
        where id = 2'''
results = conn.execute(sql)
conn.commit()                           # 更新資料庫
results = conn.execute("SELECT name from students")
allstudents = results.fetchall()        # 結果轉成元素是元組的串列
for student in allstudents:
    print(student)
conn.close()                            # 關閉資料庫連線

執行結果

('Tomy',)
('linda',)

DB Browser for SQLite

SQLite 盡管好用，如何使用 Python Shell 視窗方式處理每一筆的記錄  Record 輸入是一件麻煩的事，SQLite 沒有提供圖形介面處理這方面的問題，不過目前市面上有免費的 DB Browser for SQLite，可以讓我們很輕鬆地管理 SQLite。

安裝DB browser for sqlite

進入DB browser for sqlite網址 ，然後點選 Download

接著可以依照自己的電腦環境點選適當的DB browser安裝，為了方便日後執行，請記住在桌面上建立捷徑方便日後使用。

下列是安裝完成，啟動 DB Browser for SQLite 後的畫面。

建立新的 SQLite 資料庫

點選 "新建資料庫" 標籤後 => 請選擇資料庫所要存放的資料夾 => 然後輸入資料庫檔案名稱，這裡我們建立的資料庫檔案名稱是 testDatabase => 資料庫建立完成後，接下來需要分別建立資料表 table、欄位field、資料表記錄 record，最後儲存可以。參考下列說明 :

上述請按 "存檔鈕"，可以看到 "編輯資料表定義" 視窗，請在資料表欄位輸入所要建立的資料表名稱，這裡請輸入students。

接著要建立資料表欄位名稱，請按 "欄位" "Add"，以下是加入 id 和 name 欄位的畫面，同時我們在欄位屬性中勾選 U屬性，代表 id 的值必須是唯一的。

欄位建立完成後可以按 "Browse Data" 標籤，然後按 "新建紀錄圖示鈕"，輸入紀錄。

輸入紀錄完成可以按 Write Change 標籤，這樣就完成建立資料庫的目的了。

打開舊的 SQLite 資料庫

可以開啟 DB Browser ，請按 "打開資料庫" 標籤，選擇適當的資料夾，再選擇欲開啟的資料庫檔案即可。

將台北人口數儲存至 SQLite 資料庫

資料夾中有 "Taipei_Population.csv" 檔案，這個檔案是取自台北市政府民政局，這個檔案有台北市各行政區人口統計相關資訊。

在這裡的專案中我們將截取下列資料 :

• A欄位 :行政區名稱。
• H 欄位 : 男性人口數。
• I 欄位 : 女性人口數。
• G 欄位 : 總計人口數。

範例 pythonSQLite-11.py : 除了在 Python 視窗列出上述各行政區域男性女性人口數，也將列出總人口數資訊，同時我們也將建立 SQLite 的 population.db 資料庫檔案，這個檔案中有population 表單，這個表單欄位資訊如下 :

• area TEXT : 行政區名稱
• male int :  男性人數
• female int :  女性人數
• total int : 總人數

所有人口資訊也將儲存到 population 表單 。

# pythonSQLite-11.py
import sqlite3
import csv
import matplotlib.pyplot as plt

conn = sqlite3.connect("populations.db")    # 資料庫連線
sql = '''Create table population( 
        area TEXT,
        male int,                     
        female int,
        total int)'''
conn.execute(sql)                           # 執行SQL指令

fn = 'Taipei_Population.csv'
with open(fn) as csvFile:                   # 儲存在SQLite
    csvReader = csv.reader(csvFile)
    listCsv = list(csvReader)               # 轉成串列
    csvData = listCsv[4:]                   # 切片刪除前4 rows
    for row in csvData:
        area = row[0]                       # 區名稱
        male = int(row[7])                  # 男性人數
        female = int(row[8])                # 女性人數
        total = int(row[6])                 # 總人數
        x = (area, male, female, total)
        sql = '''insert into population values(?,?,?,?)'''
        conn.execute(sql,x)
        conn.commit()

results = conn.execute("SELECT * from population")
for record in results:
    print("區域       = ", record[0])
    print("男性人口數 = ", record[1])
    print("女性人口數 = ", record[2])
    print("總計人口數 = ", record[3])
       
conn.close()      # 關閉資料庫連線

執行結果

區域       =    松山區
男性人口數 =  96357
女性人口數 =  109276
總計人口數 =  205633
區域       =    信義區
男性人口數 =  106330
女性人口數 =  116783
總計人口數 =  223113
區域       =    大安區
男性人口數 =  143905
女性人口數 =  164781
總計人口數 =  308686
........部分省略.....

範例 pythonSQLite-12.py : 讀取 population.db 資料庫檔案，列出 population 表單 台北市 2019年 男性、女性與總計人口數，用折線圖表達。

# pythonSQLite-12.py
import sqlite3
import matplotlib.pyplot as plt

conn = sqlite3.connect("populations.db")    # 資料庫連線
results = conn.execute("SELECT * from population")

area, male, female, total = [], [], [], []
for record in results:                      # 將人口資料放入串列
    area.append(record[0])
    male.append(record[1])
    female.append(record[2])
    total.append(record[3])       
conn.close()                                # 關閉資料庫連線

plt.rcParams['font.sans-serif'] = ['Microsoft JhengHei']        # 使用黑體
seq = area
linemale, = plt.plot(seq, male, '-*', label='男性人口數')
linefemale, = plt.plot(seq, female, '-o', label='女性人口數')
linetotal, = plt.plot(seq, total, '-^', label='總計人口數')

plt.legend(handles=[linemale, linefemale, linetotal], loc='best')
plt.title(u"台北市", fontsize=24)
plt.xlabel("2019年", fontsize=14)
plt.ylabel("人口數", fontsize=14)
plt.show()

執行結果

參考資料

• DB Browser for SQLite
• sqlite3 --- SQLite 數據庫 DB-API 2.0 接口模組(外連結)

特色、摘要，Feature、Summary:

關鍵字、標籤，Keyword、Tag:

• Web-Crawler,Data-Mining,Data-Science,Python,BackEnd,SQLite