Python 學習筆記 : 資料庫存取測試 (一) SQLite

SQLite 是一個小巧簡便的輕量型關聯式資料庫 (DBMS), 係 D. Richard Hipp 於 2000 年任職 Gerneral Dynamics 公司執行美國海軍一個委託案時所設計. SQLite 由一組 C 函式庫組成, 實作了大部分的 SQL-92 標準, 可以使用 SQL 語言進行資料庫操作, 例如 CRUD (新增, 查詢, 更新, 移除) 等作業, 非常適合用在小型應用程式或原型開發 (Proto-typing). 參考 :

# https://www.sqlite.org/index.html

# https://zh.wikipedia.org/wiki/SQLite

與一般的資料庫如 MySQL 等不同的是, SQLite 並非 Client-Server 架構, 因此沒有獨立之伺服器程序, 而是嵌入整合在用戶程式中. SQLite 的資料庫與微軟 ACCESS 一樣是單一檔案型態 (即使用單一文件儲存整個資料庫, 副檔名為 .sqlite 或 .db), 不需要進行任何組態設定, 只要連接資料庫檔案就可以直接使用. SQLite 的特性整理如下 :

1. SQLite 屬於羽量級基於磁碟之資料庫管理系統
2. 不需要安裝與設定伺服器
3. 支援大部分 SQL91 標準, 但不支援外鍵限制
4. 最大支援 140TB 單一資料庫檔案, 備份方便簡易
5. 資料以 B+ 樹狀結構儲存

Python 自 2.5 版後即內建名稱為 sqlite3 的 SQLite 模組, 說明文件參考 :

# 12.6. sqlite3 — DB-API 2.0 interface for SQLite databases
# https://www.tutorialspoint.com/sqlite/index.htm
# https://www.quackit.com/sqlite/tutorial/about_sqlite.cfm
# How to Create, Open, Backup Database in SQLite

可以從 SQLite 官網下載 "Precompiled Binaries for Windows" 安裝檔, 安裝後可由命令列輸入 sqlite3 進入 SQLite3 介面進行資料庫操作 :

# https://www.sqlite.org/download.html

sqlite3 模組定義了 sqlite3.Connection 與 sqlite3.Cursor 這兩個類別來連線與操作資料庫. SQLite 資料庫的 CRUD 操作主要是利用 Cursor 物件之 execute() 方法來執行 SQL 指令. 一般資料庫操作之程序如下 :

1. 呼叫 sqlite3.connect() 連接資料庫
2. 呼叫 conn.cursor() 建立 Cursor 物件
3. 呼叫 cursor.execute() 執行 CRUD 操作
4. 呼叫 conn.close() 關閉資料庫

為了使用方便, Connection 物件也實作了 execute() 方法來執行 SQL 指令 (實際上 Connection 物件也是在背後隱密地產生一個 Cursor 物件來操作資料庫), 因此上面程序 2 與 3 可以合併為如下三步驟 :

1. 呼叫 sqlite3.connect() 連接資料庫
2. 呼叫 conn..execute() 執行 CRUD 操作
3. 呼叫 conn.close() 關閉資料庫

Connection 與 Cursor 物件的常用方法如下表 :

sqlite3 方法	說明
conn=sqlite3.connect("db.sqlite")	連接資料庫檔案 db.sqlite
conn.commit()	將之前的操作變更至資料庫中
conn.rollback()	取消最近之 commit() 變更, 回復至之前狀態
conn.close()	關閉資料庫連線
cursor=conn.execute(SQL)	執行 SQL 指令 (字串), 傳回 Cursor 物件
cursor=conn.cursor()	傳回 Cursor 物件
cursor.execute(SQL)	執行 SQL 指令 (字串)
cursor.fetchall()	讀取全部剩餘之紀錄以串列傳回, 若無紀錄傳回空串列
cursor.fetchone()	讀取目前 Cursor 物件所指之下一筆紀錄, 若無傳回 None

SQLite 資料庫操作的 SQL 語法參考官網或  TutorialsPoint :

# SQL As Understood By SQLite
# TutorialsPoint : SQLite Tutorial
# Python Library Reference

SQLite 的 Schema (表格結構) 相當簡潔, 其資料欄位只有如下 5 種資料型態 :

sqlite3 資料類型	說明
NULL	無值或值為 NULL
INTEGER	有號整數
REAL	8 Bytes 的 IEEE 浮點數
TEXT	UTF-8, UTF-16BE 或 UTF-16LE 編碼之字串
BLOB	原始輸入類型

SQLite 並無布林型態欄位, 但可用 INTEGER 的 0 (False) 與 1 (True) 代用. 另外 SQLite 也沒有 DATE/TIME 型態欄位, 但可以透過 Date And Time Functions 日期時間函數的協助利用 REAL, INTEGER, 或 TEXT 型態來儲存日期時間資料. 參考 :

# Datatypes In SQLite Version 3

除了資料型態外, 欄位還可以加上一些修飾詞, 例如欄位必須有值要用 NOT NULL; 值不可重複用 UNIQUE; 主鍵欄位使用 PRIMARY KEY, 整數自動增量 AUTOINCREMENT, 此常用來作為當作紀錄的索引. 可自動增量的 id 欄位, 其欄位型態可定義為 :

INTEGER PRIMARY KEY AUTOINCREMENT

注意, AUTOINCREMENT 只能用在 INTEGER, 而且若與 PRIMARY KEY 同時存在時必須放在 PRIMARY KEY 後面.

在測試之前還要安裝一個 FireFox 瀏覽器上好用的 SQLite 圖形化資料庫管理工具, 它是 FireFox 的一個附加元件, 必須經過安裝才能使用. 首先開啟 FireFox, 搜尋 "SQLite Manager", 點擊超連結 :

# https://addons.mozilla.org/zh-TW/firefox/addon/sqlite-manager-webext/?src=search

按 "+ 新增至 FireFox" 鈕再按彈出視窗之 "安裝" 鈕, 完成後點 FireFox 右上角之設定鈕, 在彈出視窗中按 "+自訂" 鈕開啟 "其他工具與功能" 頁面 :

將其中的 "SQLite Manager" 拖曳到設定視窗中, 按 "結束自訂模式" 即完成設定 :

再次按設定頁籤開啟 SQLite Manager 頁面, 介面如下 :

以下是 sqlite3 模組之 CRUD 操作測試紀錄, 本測試參考了下列書籍 :

1.  Python 程式設計實務 (博碩, 何敏煌)
2.  Python 初學特訓班 (碁峰, 文淵閣工作室)
3.  Python 入門邁向高手之路-王者歸來 (深石, 洪錦魁)
4.  Python Pocket Reference (Oreilly, Mark Lutz)
5.  Python Cookbook(Oreilly, David Beazly)
6.  Learning Python (Oreilly, Mark Lutz) 

本系列之前的測試紀錄參考 :

# Python 學習筆記 : 安裝執行環境與 IDLE 基本操作
# Python 學習筆記 : 檔案處理
# Python 學習筆記 : 日誌 (logging) 模組測試


1. 連接資料庫 :

使用 import sqlite3 匯入模組後即可呼叫 sqlite3.connect() 方法連接資料庫檔案, 它會傳回一個 sqlite3.Connection 物件, 呼叫 Connection 物件之 cursor() 則會傳回一個 sqlite3.Cursor 物件, 這個 Cursor 物件便是操作資料庫的主要工具 :

D:\Python\test>python
Python 3.6.1 (v3.6.1:69c0db5, Mar 21 2017, 18:41:36) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3                                        #匯入 sqlite3 模組
>>> conn=sqlite3.connect("db.sqlite")       #連接資料庫檔案
>>> type(conn)                                              #傳回 Connection 物件
<class 'sqlite3.Connection'> 
>>> cursor=conn.cursor()                            #建立 Cursor 物件
>>> type(cursor) 
<class 'sqlite3.Cursor'> 

注意, 若檔案不存在, SQLite 會自動建立空白的資料庫檔案. 關於 Cursor 物件, 參考 :

# https://docs.python.org/2.5/lib/sqlite3-Cursor-Objects.html

2. 新增資料表 : 

新增資料表使用 CREATE TABLE 指令, 若要避免重複新增相同名稱資料表造成錯誤, 可用 CREATE TABLE IF NOT EXISTS, 這樣當同名資料表已經存在時就不會執行此 CREATE TABLE 指令了. SQLite 說明文件參考 :

# https://www.sqlite.org/lang_createtable.html

在下面的測試中, 我參考了之前測試 Java 連接 ACCESS 資料庫的範例 :

# Java 資料庫存取 : 使用 ACCESS

改寫如下 :

>>> import sqlite3 
>>> conn=sqlite3.connect("db.sqlite") 
>>> SQL='CREATE TABLE IF NOT EXISTS users(id INTEGER \ 
... PRIMARY KEY AUTOINCREMENT NOT NULL,user_name TEXT, \   
... age NUMBER, gender TEXT,email TEXT, password TEXT)' 
>>> cursor=conn.execute(SQL)     //傳回 Cursor 物件
>>> type(cursor)   
<class 'sqlite3.Cursor'>   

這樣便在資料庫 db.sqlite 中建立了一個 users 資料表, 裡面含有 id, user_name, age, gender, email, password 六個欄位. 注意, 有加上 NOT NULL 限制的欄位在新增紀錄時必須要給值, 否則會出現 "NOT NULL constraint failed" 錯誤訊息. 欄位 id 因為有 AUTOINCREMENT 屬性會自動給值, 因此加 NOT NULL 是多此一舉,

在 FireFox 的 SQLite manager 中執行 Database/Connect Database, 點選目前目錄下的 db.sqlite 檔案開啟資料庫, 再打開 users 資料表即可看到其欄位結構 :

在已建立 users 資料表情況下, 若將 IF NOT EXISTS 拿掉, 再次執行 CREATE TABLE 的話就會出現 "table users already exists" 的錯誤訊息 :

>>> SQL='CREATE TABLE users(id INTEGER \ 
...      PRIMARY KEY AUTOINCREMENT NOT NULL,user_name TEXT, \ 
...      age NUMBER, gender TEXT,email TEXT,password TEXT)'
>>> cursor=conn.execute(SQL)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: table users already exists     #資料表已存在

所以在使用 CREATE TABLE 時最好伴隨 IF NOT EXISTS.

另外, SQL 指令中的自訂名稱, 例如資料表名稱與欄位名稱亦可用引號括起來, 但要注意雙引號與單引號交錯出現原則, 即若名稱用雙引號, 則 SQL 語句就用單引號, 例如 :

SQL='CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER \
     PRIMARY KEY AUTOINCREMENT NOT NULL, "user_name" TEXT, "age" NUMBER, \
     "gender" TEXT, "email" TEXT, "password" TEXT)'

若名稱用單引號, 則 SQL 語句就用雙引號, 例如 :

SQL="CREATE TABLE IF NOT EXISTS 'users' ('id' INTEGER \
     PRIMARY KEY AUTOINCREMENT NOT NULL, 'user_name' TEXT, 'age' NUMBER, \
     'gender' TEXT, 'email' TEXT, 'password' TEXT)"


3. 新增與查詢紀錄 : 

新增紀錄之 SQL 指令格式 :

INSERT INTO table_name(field1,field2,...,fieldn) VALUES(val1,val2,...,valn)   

注意, 欄位名稱必須與 VALUES 中列舉之值一一對應, 數目若不一致將導致執行錯誤. 這裡的欄位名稱不需列舉該資料表之全部欄位, 缺漏的欄位將被填入 NULL 值, 但若缺漏的欄位型態為 NOT NULL 將產生執行錯誤. 說明文件參考 :

# https://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm

查詢紀錄之 SQL 指令格式 :

SELECT * FROM table [WHERE field='value' [AND field='value']] 

利用 cursor.execute() 或 conn.execute() 執行 SELECT 語句後會傳回 Cursor 物件, 此物件有兩個擷取符合查詢條件之紀錄的方法 :

Cursor 物件方法	說明
fetchone()	以 tuple 傳回符合查詢條件之下一筆紀錄, 若無傳回 None
fetchall()	以 list 傳回符合查詢條件之全部紀錄, 若無傳回 None

其中 fetchone() 傳回的是表示紀錄的 tuple; 而 fetchall() 傳回的是 tuple 組成之 list (表示多筆紀錄), fetchall() 即使只查詢到一筆紀錄也是傳回 list.

說明文件參考 :

# https://www.tutorialspoint.com/sqlite/sqlite_select_query.htm

首先新增第一筆紀錄到上面建立的 users 資料表內, 然後用 SELECT 指令查詢單筆紀錄 :

>>> SQL="INSERT INTO users(user_name,age,gender,email,password) \ 
...      VALUES('愛咪','12','女','amy@gmail.com','123')"   
>>> conn.execute(SQL)                             #新增第一筆紀錄
>>> SQL="SELECT * FROM users"     #查詢所有紀錄
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchone())                     #fetchone() 傳回 tuple
(1, '愛咪', 12, '女', 'amy@gmail.com', '123') 
>>> SQL="SELECT * FROM users"     #查詢所有紀錄
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchall())                        #fetchall() 傳回 list
[(1, '愛咪', 12, '女', 'amy@gmail.com', '123')] 

可見 fetchone() 傳回的是一個表示紀錄的 tuple; 而 fetchall() 傳回的則是可表示多筆紀錄的 list (事實上是 tuple's list), 即使只有一筆紀錄也是傳回 list. 注意, 雖然可查詢到這筆紀錄, 但事實上它還放在記憶體中並未寫回資料庫裡, 因為還沒有呼叫 conn.commit(). 這時到 FireFox 的 SQLite Manager 裡面是看不到這筆紀錄的.

接著寫入第二筆與第三筆紀錄 :

>>> SQL="INSERT INTO users(user_name,age,gender,email,password) \   
...      VALUES('彼得',14,'男','peter@gmail.com','456')" 
>>> conn.execute(SQL)                            #新增第二筆紀錄
>>> SQL="INSERT INTO users(user_name,age,gender,email,password) \ 
...      VALUES('凱莉',16,'女','kelly@gmail.com','789')" 
>>> conn.execute(SQL)                            #新增第三筆紀錄     
>>> SQL="SELECT * FROM users"    #查詢所有紀錄
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchone())                     #擷取下一筆
(1, '愛咪', 12, '女', 'amy@gmail.com', '123') 
>>> print(cursor.fetchone())                     #擷取下一筆
(2, '彼得', 14, '男', 'peter@gmail.com', '456') 
>>> print(cursor.fetchone()) 
(3, '凱莉', 16, '女', 'kelly@gmail.com', '789')              #擷取下一筆
>>> print(cursor.fetchone()) 
None 
>>> print(cursor.fetchall())                        #擷取全部
[] 

可見 Cursor 物件的作用如同指向查詢所得紀錄集的指標, 呼叫 fetchone() 就由頭指向下一筆紀錄, 到尾時就傳回 None, 此時呼叫 fetchall() 就傳回空串列了.

以上新增的紀錄每一筆都有完整的欄位資料, 事實上新增時可以只填入部分欄位資料 (但欄位定義中有 NOT NULL 者必須填入), 未填欄位會被填入 None, 例如下面填入第四筆紀錄時只填入 user_name 欄位, 其餘欄位未填, 最後並呼叫 conn.commit() 將目前已寫入記憶體中的紀錄寫回資料庫檔案中 :

>>> SQL="INSERT INTO users(user_name) VALUES('東尼')" 
>>> cursor=conn.execute(SQL)                 #新增第四筆紀錄
>>> SQL="SELECT * FROM users" 
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchall())                         #擷取全部
[(1, '愛咪', 12, '女', 'amy@gmail.com', '123'), (2, '彼得', 14, '男', 'peter@gmail.com', '456'), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789'), (4, '東尼', None, None, None, None)]
>>> conn.commit()                                       #寫回資料庫

可見 fetchall() 傳回含有四個 tuple 的串列. 呼叫 commit() 之後再去 SQLite Manager 按 Refresh 鈕即可看到這四筆紀錄 :

可見值為 None 的欄位在 SQLite Manager 中顯示為空白.


4. 更新與刪除紀錄 : 

更新紀錄的 SQL 指令格式如下 :

UPDATE table SET field1='value1' [, field2='value2', ... fieldn='valuen'] [WHERE field=value] 

以上面新增的第四筆紀錄為例, 由於只填入了 user_name 欄位, 此處可用 UPDATE 指令來補足其餘欄位資料, 例如 :

>>> SQL="UPDATE users SET age='48',gender='男',email='tony@gmail.co', \ 
...      password='abc' WHERE user_name='東尼'" 
>>> cursor=conn.execute(SQL)                     #更新紀錄
>>> SQL="SELECT * FROM users"          #查詢全部紀錄
>>> cursor=conn.execute(SQL)                   
>>> print(cursor.fetchall())                             #擷取全部紀錄集
[(1, '愛咪', 12, '女', 'amy@gmail.com', '123'), (2, '彼得', 14, '男', 'peter@gmail.com', '456'), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789'), (4, '東尼', 48, '男', 'tony@gmail.co', 'abc')] 
>>> conn.commit()                                           #寫回資料庫

可見執行 UPDATE 指令後, 第四筆紀錄缺漏的欄位資料都補足了. 寫回資料庫後將 SQLite Manager 按 Refresh 鈕更新即可看到更新的結果. 注意, 如果沒有設定 WHERE 限制條件, 則每一筆紀錄都會被 UPDATE 操作, 使得被更新的欄位值都變成相同.

SQL 指令也可以用格式化指令 format() 搭配 {} 運算子來對應填值, 上面的 UPDATE 指令可用下列指令取代 :

SQL="UPDATE users SET age='{}',gender='{}',email='{}', \
     password='{}' WHERE user_name='東尼'".format('48',\
     '男','tony@gmail.co','abc')

刪除紀錄的 SQL 指令格式如下 :

DELETE FROM table [WHERE field='value']

注意, 刪除紀錄若沒有加上 WHERE 條件的話會刪除整個資料表內的全部記錄.

以刪除上面第四筆紀錄為例 :

>>> SQL="DELETE FROM users WHERE id='4'"   #刪除第四筆資料
>>> cursor=conn.execute(SQL)                                   
>>> SQL="SELECT * FROM users"                            #查詢全部紀錄
>>> cursor=conn.execute(SQL)                                     
>>> print(cursor.fetchall())                                               #擷取全部紀錄集
[(1, '愛咪', 12, '女', 'amy@gmail.com', '123'), (2, '彼得', 14, '男', 'peter@gmail.com', '456'), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789')]
>>> conn.commit()                                                             #寫回資料庫

可見只剩下三筆資料, 第四筆已經被刪除. 注意, 此處 id 用 4 或 '4' 均可, SQLite 會自動轉態. WHERE 限制條件可以使用 AND 或 OR 來組合多重條件, 例如若要刪除女性紀錄, 但年紀小於 15 歲者, 其 SQL 指令如下 :

>>> SQL="DELETE FROM users WHERE gender='女' AND age < 15" 
>>> cursor=conn.execute(SQL)               #刪除第一筆紀錄
>>> SQL="SELECT * FROM users" 
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchall()) 
[(2, '彼得', 14, '男', 'peter@gmail.com', '456'), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789')] 

可見符合女性且年齡小於 15 者只有第一筆紀錄被刪除. 下面測試


5. 更改資料欄位 : 

更改資料欄位的  SQL 指令為 ALTER TABLE, 能用的只有新增欄位以及變更資料表名稱兩個功能, 新增欄位指令格式如下 :

ALTER TABLE table ADD field type

SQLite 一次只能新增一個欄位, 且資料格式不能用上面的簡約格式如 TEXT, INTEGER 等, 而是要用如 CHAR(), VARCHAR(), INT(20) 等詳盡格式, 例如 :

>>> SQL="ALTER TABLE users ADD telephone CHAR(20)"   #新增 telephone 欄位
>>> cursor=conn.execute(SQL)
>>> SQL="ALTER TABLE users ADD city CHAR(20)"             #新增 city 欄位
>>> cursor=conn.execute(SQL) 
>>> conn.commit() 
>>> SQL="INSERT INTO users(user_name) VALUES('潔西卡')"   
>>> cursor=conn.execute(SQL)   
>>> SQL="SELECT * FROM users" 
>>> cursor=conn.execute(SQL) 
>>> print(cursor.fetchall()) 
[(2, '彼得', 14, '男', 'peter@gmail.com', '456', None, None), (3, '凱莉', 16, ' 女', 'kelly@gmail.com', '789', None, None), (5, '潔西卡', None, None, None, None, None, None)]
>>> conn.commit() 

可見每一筆紀錄後面都新增了兩個欄位, 更新 SQLite Manager 後檢視資料表結構可知已加入 telephone 與 city 兩欄位 :

變更資料表名稱指令格式如下 :

ALTER TABLE table RENAME TO  new_table 

其中 new_table 為資料表的新名稱 :

>>> SQL="ALTER TABLE users RENAME TO users_new"
>>> cursor=conn.execute(SQL)
>>> SQL="SELECT * FROM users"
>>> cursor=conn.execute(SQL)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: users

可見更名為 users_new 之後再去查詢 users 就會出現 "no such table" 的錯誤, 應該改為查詢 users_new 資料表才對 :

>>> SQL="SELECT * FROM users_new"     
>>> cursor=conn.execute(SQL)   
>>> print(cursor.fetchall())   
[(2, '彼得', 14, '男', 'peter@gmail.com', '456', None, None), (3, '凱莉', 16, ' 女', 'kelly@gmail.com', '789', None, None), (5, '潔西卡', None, None, None, None, None, None)]


6. 刪除資料表 : 

刪除資料表指令格式 :

DROP TABLE IF EXISTS table

要將上面建立的 users 資料表刪除之指令為 DROP TABLE users, 但在刪除之前可先用 SQLite Manager 將資料表匯出儲存, 以便刪除後若後悔還可從匯出檔再匯入 :

點選左邊欄位之資料表 users, 按右欄中的 "EXPORT" 鈕 :

勾選 "Include CREATE TABLE statement" :

按底下的 "OK" 鈕即可將資料表 users 存為 users.sql 檔, 內容如下 :

DROP TABLE IF EXISTS "users";
CREATE TABLE users(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,user_name TEXT,age NUMBER, gender TEXT,email TEXT NOT NULL,password TEXT);
INSERT INTO "users" VALUES(1,'愛咪','12','女','amy@gmail.com','123');
INSERT INTO "users" VALUES(2,'彼得',14,'男','peter@gmail.com','456');
INSERT INTO "users" VALUES(3,'凱莉',16,'女','kelly@gmail.com','789');
INSERT INTO "users" VALUES(4,'東尼',48,'男','tony@gmail.co','789');

以後可利用此備份檔重建資料表.

這樣就可以放心刪除資料表 users 了 :

>>> SQL="DROP TABLE IF EXISTS users"   
>>> cursor=conn.execute(SQL) 
>>> SQL="SELECT * FROM users" 
>>> cursor=conn.execute(SQL) 
Traceback (most recent call last):   
  File "<stdin>", line 1, in <module> 
sqlite3.OperationalError: no such table: users   

可見 users 資料表已被刪除, 查詢紀錄顯示 "no such table" 錯誤.