好站 : 免費線上軟體 SQLite Viewer

今天在 "Django 4 for the Impatient" 這本書裡發現一個好用工具 SQLite Viewer, 這是一個線上應用程式, 只要把 SQlite 檔案 *.sqlite3 上傳就可以直接瀏覽與操作資料庫內容 :

# https://inloop.github.io/sqlite-viewer/ 

將 .sqlite3 檔案拖曳到視窗空白區就會自動讀取資料庫, 從中間的下拉式選單點選資料表就會顯示該資料表內容 :

在下方框中輸入 SQL 指令可以進行資料表的 CRUD 操作 :

以前還要下載 DB Browser 軟體來操作 SQLite 資料庫, 現在有線上服務完全免下載, 參考 :

# Python 學習筆記 : DB Browser for SQLite

樹莓派安裝 SQLite 資料庫

SQLite 資料庫是一個廣受歡迎的嵌入式 (embedded, 非 client-server 架構) 輕量級 SQL 關聯式資料庫, 被許多軟體用來儲存系統資料 (例如 Android 平台, 瀏覽器), 它具有如下特點 :

1. 開源 (免費)
2. 無伺服器 (無須啟動, 不占用程序)
3. 單一檔案 (備份方便)
4. 零設定 (使用簡單)
5. 跨平台 (可移植性佳)
6. 自我完善 (無外部相依軟體)

參考 :

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

樹莓派 Raspbian 並未預載 SQLite, 需自行安裝.


一. 安裝 SQLite :

安裝 SQLite 指令如下 :

sudo apt-get install sqlite3 

pi@raspberrypi:~ $ sudo apt-get install sqlite3 
正在讀取套件清單... 完成
正在重建相依關係       
正在讀取狀態資料... 完成
建議套件：
  sqlite3-doc
下列【新】套件將會被安裝：
  sqlite3
升級 0 個，新安裝 1 個，移除 0 個，有 0 個未被升級。
需要下載 709 kB 的套件檔。
此操作完成之後，會多佔用 1,991 kB 的磁碟空間。
下載:1 http://mirror.ossplanet.net/raspbian/raspbian stretch/main armhf sqlite3 armhf 3.16.2-5+deb9u1 [709 kB]
取得 709 kB 用了 5s (136 kB/s)                     
選取了原先未選的套件 sqlite3。
（讀取資料庫 ... 目前共安裝了 136703 個檔案和目錄。）
Preparing to unpack .../sqlite3_3.16.2-5+deb9u1_armhf.deb ...
Unpacking sqlite3 (3.16.2-5+deb9u1) ...
設定 sqlite3 (3.16.2-5+deb9u1) ...
Processing triggers for man-db (2.7.6.1-2) ...

整個程式只有 709KB, 實在非常輕巧啊! 

二. 建立 SQLite 資料庫 :   

在命令列下 sqlite3 test.db 可建立或載入資料庫, 若 test.db 不存在則在目前目錄下建立名為 test.db 的 SQLite 資料庫, 若已存在載入資料庫, 並進入 sqlite3 的 shell 介面, 下 .help 指令會顯示使用說明, 例如 :

pi@raspberrypi:~ $ sqlite3 test.db 
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite>

資料庫操作指令除 SELECT 外需用 BEGIN; 開始, 用 COMMIT; 結束. 以下操作參考了下面文章 : 

# SQLite Database on a Raspberry Pi
# SQLite DB on Raspberry Pi
# 進階篇 - SQLite資料庫
# 樹莓派學習筆記——SQLite操作簡述

關於 SQL 指令可參考之前整理的筆記 :

# 最常用的 SQL 指令

三. 建立資料表 : 

建立資料表用 CREATE TABLE 指令, 例如 :

sqlite> BEGIN;   
sqlite> CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT); 
sqlite> COMMIT;   

上面建立了一個 dhtreadings 資料表. 用 .table 指令可列出目前資料庫已有哪些資料表, 而 .fullschema 則列出全部資料表的結構, 例如 :

sqlite> .tables   
dhtreadings
sqlite> .fullschema 
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);
/* No STAT tables available */

以下為資料表的 CRUD 操作 :


四. 新增與查詢紀錄 :

在資料表中新增資料使用 INSERT 指令, 而查詢則用 SELECT 指令, 例如 : 

sqlite> BEGIN; 
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.4, 48, date('now'), time('now'), "manual"); 
sqlite> COMMIT;     

這樣就新增了一筆紀錄到資料表 dhtreadings, 可用 SELECT 查詢資料表 :

sqlite> SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual   

可知目前整個資料表只有一筆紀錄. 再新增一筆紀錄 :

sqlite> BEGIN;   
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.5, 48.7, date('now'), time('now'), "manual"); 
sqlite> COMMIT; 

用 SELECT 查詢已有兩筆紀錄 :

sqlite> SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual
2|22.5|48.7|2019-04-10|03:00:40|manual


五. 更新紀錄 : 

更新紀錄使用 UPDATE 指令, 可同時更新一個以上欄位, 每個欄位用逗號隔開, 例如更改上面第二筆紀錄之溫濕度 :

sqlite&glt; BEGIN; 
sqlite&glt; UPDATE dhtreadings SET temperature='33', humidity='66' WHERE id='2'; 
sqlite&glt; COMMIT; 
sqlite&glt; SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual
2|33|66|2019-04-10|03:00:40|manual 

可見 id=2 的溫溼度都被修改了.


六. 刪除紀錄 :

刪除紀錄使用 DELETE 指令, 必須用 WHERE 限定刪除對象, 否則資料表內的紀錄會全部被刪除, 例如刪除上面第一筆紀錄 :

sqlite&glt; BEGIN;
sqlite&glt; DELETE FROM dhtreadings WHERE id='1'; 
sqlite&glt; COMMIT; 
sqlite&glt; SELECT * FROM dhtreadings; 
2|33|66|2019-04-10|03:00:40|manual

可見第一筆資料已被刪除.


七. 刪除資料表 : 

刪除資料表用 DROP TABLE 指令, 與 SELECT 一樣不需要用 BEGIN 與 COMMIT, 例如 :

sqlite&glt; .tables 
dhtreadings 
sqlite&glt; DROP TABLE dhtreadings;   
sqlite&glt; .tables 
sqlite&glt;

可見資料表 dhtreadings 已經被刪除了. 但如果資料表不存在會出現錯誤訊息, DROP TABLE 指令最好加上 IF EXISTS, 例如上面已刪除資料表 dhtreadings, 若再刪除一次就會報錯 :

sqlite&glt; DROP TABLE dhtreadings; 
Error: no such table: dhtreadings   
sqlite&glt; DROP TABLE IF EXISTS dhtreadings;   
sqlite&glt;


八.  跳出 SQLite Shell : 

在 SQLite Shell 輸入 .quit 或 .exit 均可跳出 Shell 回到命令列 :

sqlite&glt; .quit   
pi@raspberrypi:~ $


以上是用 SQLite Shell 直接操作資料庫, 亦可用 Python, Java 等程式語言存取 SQLite 資料庫, 參考 :

# Python 學習筆記 : 資料庫存取測試 (一) SQLite
# Java使用JDBC操作SQLite
# SQLite Java

Python 學習筆記 : DB Browser for SQLite

之前在測試 Python 的 SQLite 存取時曾使用一個 SQLite 圖形化資料庫管理工具 SQLite Manager, 它是一個 Firefox 擴充元件 (add-on), 必須先安裝 Firefox 瀏覽器再安裝此元件才能使用, 參考 :

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

其實還有一個 DB Browser for SQLite 也很好用, 這款是應用程式, 不是瀏覽器插件, 有安裝版也有提供免安裝版, 參考 :

# https://sqlitebrowser.org/

教學文件參考 :

# https://github.com/sqlitebrowser/sqlitebrowser/wiki
# 透過 Python 將資料存入 SQLite 教學


1. 下載免安裝版

我下載的是 v3.10.1 免安裝版, 此為 .exe 壓縮檔, 經 VirusTotal 掃毒 OK (不要從中國網站下載免安裝版, 經掃描有加料), 執行 .exe 檔解壓縮會產生一個 SQLiteDatabaseBrowserPortable 目錄存放解壓縮資料 :

點按其中的 SQLiteDatabaseBrowserPortable.exe 執行畫面如下 :

2. 建立資料庫與資料表 : 

按 "新建資料庫" 鈕輸入檔名按存檔後會立刻彈出資料表設定視窗 :

這樣便建立了含有一個資料表 users 的資料庫 test.db 了.


3. 新增與刪除紀錄 : 

切到 'Browse Data' 頁籤, 在 'Table' 選擇要操作的資料表, 按 '新增紀錄' 鈕, 底下便會出現新紀錄的欄位, 在儲存格中輸入資料即可 :

或者也可以切到 'Execute SQL' 視窗, 直接輸入 SQL 指令來操作資料表 :

INSERT INTO users(name,email)
VALUES('武大郎','woodalan@gmail.com')

按上方三角形的 Play 鈕即執行 SQL 指令矣, 切回 'Browse Data' 頁籤即可檢視新增的紀錄. 點選每筆紀錄最前面的序號即鎖定該筆紀錄, 按上方 '刪除紀錄' 鈕即自該資料表中刪除選定之紀錄.

Python 學習筆記 : 資料庫存取測試 (二) MySQL

MySQL 是目前後端網頁設計非常廣用的資料庫, 例如 XAMPP 就是整合 Apache, PHP 以及 MySQL 等工具在一體的網站開發套件, 如果有在用 XAMPP 開發 PHP 網站的話, 就不需要另外安裝 MySQL 給 Python 用, 只要啟動 XAMPP 中的 MySQL 伺服器即可, 還可以利用裡面的 phpMyAdmin 工具來瀏覽與手動管理資料庫.

我下載的是 XAMPP 可攜版, 只要解壓縮到 D 碟即可, 升版比較方便, 參考 :

# 安裝 XAMPP PHP 架站工具包

相對於內建的 SQLite 而言, Python 的 MySQL 連接方式書上介紹得比較少, 只在下列幾本書裡有提到 :

# Learning Python (Oreilly, Mark Lutz)
# 科學運算-Python 程式理論與應用 (第 16 章)

連接 MySQL 通常使用 MySQLdb 模組來驅動, 此模組在 GitHub 上的專案名稱為 mysql-python, 不過 MySQLdb 已經很老舊了 (已 12 歲), 僅支援 Python 2.x 且年久失修 (最近更新為 9 年前), 所以有人將其 fork 出來以支援 Python 3, 改名為 mysqlclient-python, 目前還有在持續更新, 作者希望將來能合併回 MySQLdb, 但看來是遙遙無期了. 參考 :

# 用Python 連接MySQL 的幾種方式
# Python3.x的mysqlclient的安装、Python操作mysql，python连接MySQL数据库，python创建数据库表，带有事务的操作,CRUD

本系列之前的測試文章如下 :

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

使用 MySQLdb 之前要先安裝 mysqlclient 模組 :

C:\Users\user>pip3 install mysqlclient 
Collecting mysqlclient
  Downloading https://files.pythonhosted.org/packages/32/4b/a675941221b6e796efbb48c80a746b7e6fdf7a51757e8051a0bf32114471/mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl (1.3MB)
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.12

安裝完成就可以匯入 MySQLdb 來連接 MySQL 資料庫了. 注意, 驅動程式雖然是 mysqlclient, 但模組名稱仍然是 MySQLdb, 不是 mysqlclient. MySQLdb 說明文件參考 :

# MySQLdb User’s Guide
# MySQLdb User's Guide (GitHub)
# Python - MySQL Database Access (Tutorials Point)
# https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
# Python3 使用 mysqlclient 连接 MySQL / MariaDB
# Python 使用 MySQLdb 模組連接 MySQL 資料庫教學與範例
# 5.1 Connecting to MySQL Using Connector/Python


測試紀錄如下 :

1. 連線 MySQL 伺服器 :

連線 MySQL 伺服器須先匯入 MySQLdb 模組, 然後呼叫 connect() 並傳入 host (用 localhost 或 127.0.0.1 均可), user 以及 passwd 三個參數, 傳回值為一個 Conncection 連線物件 :

>>> import MySQLdb                             #匯入驅動模組
>>> conn=MySQLdb.connect(host="127.0.0.1",user="root", passwd="mysql") 

呼叫連線物件之 cursor() 方法傳回一個 Cursor 物件, 呼叫其 execute() 方法並傳入 SQL 指令 "SELECT VERSION()" 再呼叫 fetchone() 或 fetchall() 方法可查詢資料庫版本訊息 :

>>> cursor=conn.cursor()     #傳回 Cursor 物件
>>> cursor.execute("SELECT VERSION()")     #查詢資料庫版本
1
>>> print("Database version : %s " % cursor.fetchone())
Database version : 10.1.28-MariaDB   

可見我這 XAMPP 使用的是與 MySQL 相容的 MariaDB. 注意, execute() 傳回 1 表示執行 SQL 指令成功, 傳回一筆紀錄.


2. 建立資料庫 :

接著執行 "CREATE DATABASE" 指令新建一個測試用的資料庫 testdb, 指定字元集 utf8 以支援中文 :
 
>>> SQL="CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci" 
>>> cursor.execute(SQL)   
1
>>> conn.commit()              #操作結果寫入資料庫

傳回 1 表示 SQL 指令執行成功, 但所有更改資料庫的 SQL 操作結果只是實現於記憶體中, 需呼叫 Connection 物件的 commit() 方法才會真正寫入資料庫中.

按 XAMPP 控制台中, MySQL 的第二個按鈕 "Admin" 開啟 phpMyAdmin 網頁, 登入後切到 "資料庫" 頁籤即可看到多出一個新資料庫 testdb :

建好資料庫後, 可呼叫 close() 先關閉 Connection 物件與 Cursor 物件 :

>>> cursor.close()     #關閉 Cursor 物件
>>> conn.close()       #關閉 Connection 物件


3. 連接資料庫 :

接下來要再連線 MySQL 伺服器, 並傳入 db 與 charset 參數連接 testdb 資料庫, 利用傳回之 Connection 物件呼叫 cursor() 方法取得 Cursor 物件來操作資料庫 :

>>> conn=MySQLdb.connect(host="localhost",user="root", passwd="mysql", db="testdb", charset="utf8")                   #連線資料庫
>>> cursor=conn.cursor()           #傳回 Cursor 物件
 

4. 新增資料表 :

呼叫已指定資料庫之 Connection 物件之 execute() 方法執行 "CREATE TABLE" 即可新建資料表, 此處我們要建立一個名為 users 的資料表來儲存使用者資料, 包含 id, user_name, age, gender, password  等五個欄位 :

>>> SQL="CREATE TABLE IF NOT EXISTS users(id INT(5) \ 
... PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(20), \   
... age TINYINT(3), gender CHAR(1),email VARCHAR(80), \
... password VARCHAR(20))"   
>>> cursor.execute(SQL) 
0
>>> conn.commit()              #操作結果寫入資料庫

傳回 0 表示新增成功 (但沒有傳回任何紀錄), 否則會出現錯誤訊息. 在 phpMyAdmin 頁面顯示 users 資料表結構如下 :

MySQL 資料庫常用的欄位與其屬性如下 :

欄位型態	說明
VARCHAR(20)	可變長度字元 255 bytes (文字)
CHAR(4)	固定長度字元 255 bytes (文字)
TINYTEXT	255 Bytes (文字)
TEXT	65535 bytes (文字)
MEDIUMTEXT	16777215 bytes (文字)
LONGTEXT	4294967295 bytes (文字)
TINYBLOB	255 bytes (文字)
BLOB	65535 bytes (文字,分大小寫)
MEDIUMBLOB	16777215 bytes (文字,分大小寫)
LONGBLOB	4294967295 bytes (文字,分大小寫)
TINYINT(M)	1 bytes (最大顯示寬度 M<=255)
SMALLINT(M)	2 bytes (最大顯示寬度 M<=255)
MEDIUMINT(M)	3 bytes (最大顯示寬度 M<=255)
INT(M),INTEGER(M)	4 bytes (最大顯示寬度 M<=255)
BIGINT(M)	8 bytes (總位數 M<=65, 小數位數 D<=30&M-2)
FLOAT(M,D)	4 bytes (總位數 M<=65, 小數位數 D<=30&M-2)
DOUBLE(M,D)	8 bytes (總位數 M<=65, 小數位數 D<=30&M-2)
DECIMAL(M,D)	? bytes (總位數 M<=65, 小數位數 D<=30&M-2)
DATE	3 bytes (YY-MM-DD)
DATETIME	8 bytes (YY-MM-DD HH:MM::SS)
TIMESTAMP	4 bytes (1970-01-01 00:00:00)
TIME	3 bytes (HH:MM:SS)
YEAR(2|4)	1 byte (預設 4)
ENUM	1~2 bytes (儲存單選 radio)
SET	1~8 bytes (儲存多選 checkbox)

而屬性是放在類型後面的限制, 如下表所示 :

屬性	說明
SIGNED,UNSIGNED	是否有負值 (數值)
AUTO_INCREMENT	自動增量編號 (數值)
BINARY	字元有大小寫之分 (文字)
NULL,NOT NULL	是否允許不填入資料 (全部)
DEFAULT	預設值
PRIMARY KEY	資料表之唯一主鍵

5. 新增與查詢紀錄 : 

新增紀錄之 SQL 指令為 "INSERT INTO", 可呼叫 Cursor 物件之 execute() 與 executemany() 分別新增一筆或多筆紀錄, 例如 :

>>> SQL="INSERT INTO users(user_name,age,gender,email,password) VALUES('愛咪','12','女','amy@gmail.com','123')"     #新增紀錄
>>> cursor.execute(SQL)   
1
>>> conn.commit()                     #操作結果寫入資料庫

傳回 1 表示插入一筆紀錄成功, 可用 "SELECT" 指令查詢資料表, 傳回之紀錄集可用 Cursor 物件之 fetchone(), fetchall(), 或 fetchmany(n) 等方法以串列型態傳回 :

>>> SQL="SELECT * FROM users"       #查詢資料表
>>> cursor.execute(SQL)   
1
>>> print(cursor.fetchone())                       #擷取紀錄集
(1, '愛咪', 12, '女', 'amy@gmail.com', '123') 
>>> print(cursor.fetchone())                       #擷取紀錄集
()

每呼叫一次 fetchone() 游標就指向下一個紀錄集, 因為目前 users 內只有一筆紀錄, 因此第二次呼叫時傳回空的 tuple. 呼叫 executemany() 可一次插入多筆紀錄, 例如 :

>>> SQL="INSERT INTO users(user_name,age,gender,email,password) VALUES(%s, %s, %s, %s, %s)"
>>> cursor.executemany(SQL, [('彼得',14,'男','peter@gmail.com','456'),\
... ('凱莉',16,'女','kelly@gmail.com','789')])   
2
>>> conn.commit()              #操作結果寫入資料庫

傳回 2 表示插入 2 筆紀錄成功. 此處 SQL 指令的 VALUES 部分以 %s 格式代表要插入的各欄位值, 注意, 不管是數值或字串都用 %s, 數值若用 %d 會報錯. 多筆紀錄以串列型態傳入 executemany() 的第二參數中, MySQLdb 模組會自動抽出每一筆紀錄插入 SQL 指令的 %s 格式中.

>>> SQL="SELECT * FROM users"        #查詢資料表
>>> cursor.execute(SQL) 
3
>>> cursor.fetchall()                                      #擷取紀錄集 (全部)
((1, '愛咪', 12, '女', 'amy@gmail.com', '123'), (2, '彼得', 14, '男', 'peter@gmail.com', '456'), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789')) 

可見 fetchall() 可擷取目前 users 內全部 3 筆紀錄. 還可用 fetchmany(n) 傳入要擷取的紀錄筆數, 但須再查詢一次 :

>>> cursor.execute(SQL)                              #重新查詢資料表 
3
>>> cursor.fetchmany(2)                              #擷取 2 筆紀錄
((1, '愛咪', 12, '女', 'amy@gmail.com', '123'), (2, '彼得', 14, '男', 'peter@gmail.com', '456'))
>>> cursor.fetchmany(3)                              #擷取 3 筆紀錄
((3, '凱莉', 16, '女', 'kelly@gmail.com', '789'),)


6. 更新紀錄 : 

更新紀錄使用 "UPDATE" 指令, 在此之前我們先插入一筆資料不全的紀錄 ;

>>> SQL="INSERT INTO users(user_name) VALUES('東尼')" 
>>> cursor.execute(SQL)                            #新增紀錄
1
>>> conn.commit()                                      #操作結果寫入資料庫
>>> SQL="SELECT * FROM users"      #查詢資料表
>>> cursor.execute(SQL)   
4
>>> 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))

可見資料不全的欄位值均為 None. 使用 "UPDATE" 指令來補全這筆紀錄闕漏之欄位 :

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

可見欄位資料已補全.

7. 刪除紀錄 :

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

可見最後一筆已被刪除剩下 3 筆.


8. 更改資料欄位 :

更改資料欄位使用 "ALTER TABLE" 指令, 包括新增欄位與更改欄位型態. 與 SQLite 一樣必須一個一個欄位執行, 例如新增 telephone 與 city 兩個欄位 :

>>> SQL="ALTER TABLE users ADD telephone CHAR(20)"   #新增欄位
>>> cursor.execute(SQL) 
4
>>> SQL="ALTER TABLE users ADD city CHAR(20)"             #新增欄位
>>> cursor.execute(SQL) 
4
>>> conn.commit() 
>>> SQL="INSERT INTO users(user_name) VALUES('潔西卡')"   #新增紀錄
>>> cursor.execute(SQL)
1
>>> SQL="SELECT * FROM users"     #查詢全部紀錄
>>> cursor.execute(SQL) 
5
>>> cursor.fetchall()
((1, '愛咪', 12, '女', 'amy@gmail.com', '123', None, None), (2, '彼得', 14, '男', 'peter@gmail.com', '456', None, None), (3, '凱莉', 16, '女', 'kelly@gmail.com', '789', None, None), (4, '東尼', 48, '男', 'tony@gmail.com', 'abc', None, None), (5, '潔西卡', None, None, None, None, None, None))

可見新增的欄位值均為 None.

更改欄位型態需用 "ALTER TABLE table MODIFY field type" 指令, 例如要將 email 欄位從原先的 VARCHAR(80) 加上 NOT NULL 屬性 (即新增紀錄時一定要給值), 其 SQL 指令為 :

SQL="ALTER TABLE users MODIFY email VARCHAR(80) NOT NULL"

注意, 因為 NOT NULL 只是屬性, 必須伴隨類型 VARCHAR(80) 才能修改, 例如 :

>>> SQL="ALTER TABLE users MODIFY email CHAR(80) NOT NULL" 
>>> cursor.execute(SQL) 
__main__:1: Warning: (1265, "Data truncated for column 'email' at row 5")
5
>>> conn.commit() 

進入 phpMyAdmin 查詢 testdb 資料表結構可知 email 欄位的 NULL 已經不見了 :

如果只改欄位類型, 例如將 VARCHAR(80) 改為 VARCHAR(100) :

>>> SQL="ALTER TABLE users MODIFY email CHAR(100)"
>>> cursor.execute(SQL)   
5
>>> conn.commit() 

這樣字串長度雖然放寬至 100, 但上面添加的 NOT NULL 會消失不見 :

因此在使用 MODIFY 修改欄位型態與屬性時, 還要繼續保持之屬性一定要列入, 否則會被刪除.

更改欄位名稱要用 "ALTER TABLE table CHANGE COLUMN" 指令, 例如將 email 欄位名稱改為開頭大寫的 Email :

>>> SQL="ALTER TABLE users CHANGE COLUMN email Email VARCHAR(70)" 
>>> cursor.execute(SQL) 
4

注意, 雖然只是要改欄名, 但欄位的定義不可省略, 否則會報錯. 更多 ALTER TABLE 指令參考 :

# https://dev.mysql.com/doc/refman/8.0/en/alter-table.html


參考 :

# [MySQL]Python連結MySQL---查詢篇
# GitHub 上 Fork、Watch、Star 是什麼意思？
# 5.1 Connecting to MySQL Using Connector/Python
# mysql-connector-python-8.0.11-py3.6-windows-x86-64bit.msi
# https://dev.mysql.com/downloads/file/?id=477196
# Download MySQL connector for Windows 64-bit
# 5.1 Connecting to MySQL Using Connector/Python


2018-05-06 補充 :

為了重複測試方便, 不用再手動一行一行輸入, 我將資料表 users 輸出為 .sql 檔備份, 內容如下 :

-- phpMyAdmin SQL Dump
-- version 4.8.0
-- https://www.phpmyadmin.net/
--
-- 主機: 127.0.0.1
-- 產生時間： 2018-05-06 01:34:50
-- 伺服器版本: 10.1.31-MariaDB
-- PHP 版本： 7.2.4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 資料庫： `testdb`
--

-- --------------------------------------------------------

--
-- 資料表結構 `users`
--

CREATE TABLE `users` (
  `id` int(5) NOT NULL,
  `user_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` tinyint(3) DEFAULT NULL,
  `gender` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- 資料表的匯出資料 `users`
--

INSERT INTO `users` (`id`, `user_name`, `age`, `gender`, `email`, `password`) VALUES
(1, '愛咪', 12, '女', 'amy@gmail.com', '123'),
(2, '彼得', 14, '男', 'peter@gmail.com', '456'),
(3, '凱莉', 16, '女', 'kelly@gmail.com', '789'),
(4, '東尼', 48, '男', 'tony@gmail.com', 'abc');

--
-- 已匯出資料表的索引
--

--
-- 資料表索引 `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- 在匯出的資料表使用 AUTO_INCREMENT
--

--
-- 使用資料表 AUTO_INCREMENT `users`
--
ALTER TABLE `users`
  MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

以下是手動執行指令 :

SQL="CREATE TABLE IF NOT EXISTS users(id INT(5) PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(20), age TINYINT(3), gender CHAR(1),email VARCHAR(80),  password VARCHAR(20))"

cursor.execute(SQL)

SQL="INSERT INTO users(user_name,age,gender,email,password) VALUES(%s, %s, %s, %s, %s)"

cursor.executemany(SQL, [('愛咪','12','女','amy@gmail.com','123'),('彼得',14,'男','peter@gmail.com','456'), ('凱莉',16,'女','kelly@gmail.com','789'), ('東尼', 48, '男', 'tony@gmail.com', 'abc')])

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" 錯誤.

從 IP 查來源國家 (二)

在上一篇文章中使用了檔案查詢的方式從訪客 IP 找出其國名, 其中存在兩個問題, 一是資料似乎有點舊, 有些 IP 找不到所屬國家 (特別是香港); 其二是檔案處理要使用迴圈, 查詢速度似乎較慢.

我找到 ip2nation 這個網站, 不但可以線上查詢 IP 所屬國家, 還慷慨地提供資料庫讓我們下載 (點左方導覽列的 download), 方便整合到自己的應用服務之中. 還可以在底下的框框輸入 email, 當資料庫有更新時會通知我們下載 :

# http://www.ip2nation.com/

解壓縮所下載的 ip2nation.zip 會得到一個 ip2nation.sql 資料庫檔, 裡面建立了 ip2nation 與 ip2nationCountries 這兩個資料表, 前者儲存 IP 的上限與國碼簡碼, 如下所示 :

DROP TABLE IF EXISTS ip2nation;

CREATE TABLE ip2nation (
  ip int(11) unsigned NOT NULL default '0',
  country char(2) NOT NULL default '',
  KEY ip (ip)
);

DROP TABLE IF EXISTS countries;
   
CREATE TABLE countries (
  code varchar(4) NOT NULL default '',
  iso_code_2 varchar(2) NOT NULL default '',
  iso_code_3 varchar(3) default '',
  iso_country varchar(255) NOT NULL default '',
  country varchar(255) NOT NULL default '',
  country_zhtw varchar(255) NOT NULL default '',
  lat float NOT NULL default '0',
  lon float NOT NULL default '0',
  PRIMARY KEY  (code),
  KEY code (code)
);
INSERT INTO ip2nation (ip, country) VALUES(0, 'us');
INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za');
INSERT INTO ip2nation (ip, country) VALUES(689963008, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(691011584, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691617792, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691621888, 'lr');
INSERT INTO ip2nation (ip, country) VALUES(691625984, 'ke');
INSERT INTO ip2nation (ip, country) VALUES(691630080, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691631104, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691632128, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691633152, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691634176, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691650560, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691666944, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691732480, 'tz');
INSERT INTO ip2nation (ip, country) VALUES(691798016, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(691863552, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691994624, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(692011008, 'za');
.....

後者儲存國碼簡碼, 英文全名, 經緯度等資訊 :

INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ad', 'AD', 'AND', 'Andorra', 'Andorra', '', 42.3, 1.3);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ae', 'AE', 'ARE', 'United Arab Emirates', 'United Arab Emirates', 24, 54);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('af', 'AF', 'AFG', 'Afghanistan', 'Afghanistan', 33, 65);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ag', 'AG', 'ATG', 'Antigua and Barbuda', 'Antigua and Barbuda', 17.03, -61.48);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ai', 'AI', 'AIA', 'Anguilla', 'Anguilla', 18.15, -63.1);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('al', 'AL', 'ALB', 'Albania', 'Albania', 41, 20);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('am', 'AM', 'ARM', 'Armenia', 'Armenia', 40, 45);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('an', 'AN', 'ANT', 'Netherlands Antilles', 'Netherlands Antilles', 12.15, -68.45);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ao', 'AO', 'AGO', 'Angola', 'Angola', -12.3, 18.3);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('aq', 'AQ', 'ATA', 'Antarctica', 'Antarctica', -90, 0);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw,  lat, lon) VALUES('ar', 'AR', 'ARG', 'Argentina', 'Argentina', -34, -64);
.....

在 phpmyadmin 的輸入上傳這個 ip2nation.sql 就會在系統產生 ip2nation 與 ip2nationCountries 這兩個資料表 :

但是這樣只能查詢英文國名, 為了要查中文國名, 我另外準備了一個 nation 資料表, 它只有 code 與 name 兩個欄位, code 就是上面 ip2nationCountries 資料表的國名簡碼, 而 name 是其繁體中文國名, 此表的可由下列連結下載 :

# 下載國名簡碼與中文國名對照表 nation.sql
# 下載國名簡碼與中文國名對照表 nation.txt

然後參考 ip2nation.com 網站在 Sample scripts 所提供的範例程式碼, 修改 sys.php 中的 visitors 與 list_visitors 這兩個模組, 在 visitors 模組中我將中英國名分在兩欄呈現 :

    $('#sys_visitors').datagrid({
      columns:[[
        {field:'id',title:'id',sortable:true},
        {field:'visit_time',title:'到訪時間',sortable:true},
        {field:'remote_addr',title:'遠端位址',sortable:true},
        {field:'remote_port',title:'遠端埠號',sortable:true},
        {field:'country',title:'Country',sortable:false},
        {field:'name',title:'國家',sortable:false},
        {field:'user_agent',title:'使用者代理',sortable:true}
        ]],
      url:"sys.php",
      queryParams:{op:"list_visitors"},
      fitColumns:true,
      singleSelect:true,
      pagination:true,
      pageSize:10,
      rownumbers:true
      });

注意, 因為 country 與 name 並非 visitors 資料表內的欄位, 所以這裡 sortable 要設為 false.

而 list_visitors 模組則改為如下 :

  case "list_visitors" : {
    $page=isset($_REQUEST['page']) ? intval($_REQUEST['page']) : 1;
    $rows=isset($_REQUEST['rows']) ? intval($_REQUEST['rows']) : 10;
    $sort=isset($_REQUEST['sort']) ? $_REQUEST['sort'] : 'id';
    $order=isset($_REQUEST['order']) ? $_REQUEST['order'] : 'desc';
    if (isset($_REQUEST['search_field'])) { //有 search
      $where="WHERE ".$_REQUEST['search_field']." LIKE '%".
             $_REQUEST['search_what']."%'";
      }
    else {$where="";} //無 search
    $start=($page-1) * $rows;  //本頁第一個列索引 (0 起始)
    $SQL="SELECT COUNT(*) FROM `sys_visitors`";
    $RS=run_sql($SQL);
    $total=$RS[0][0]; //紀錄總筆數
    $SQL="SELECT * FROM sys_visitors ".$where." ORDER BY ".
         $sort." ".$order." LIMIT ".$start.",".$rows;
    $RS=run_sql($SQL);
    $visitors=Array();
    if (is_array($RS)) {
      for ($i=0; $i<count($RS); $i++) {
        //查詢 IP 來源國名
        $SQL='SELECT c.country,c.code FROM ip2nationCountries c,ip2nation i '.
             'WHERE i.ip < INET_ATON("'.$RS[$i]["remote_addr"].'") AND '.
             'c.code=i.country ORDER BY i.ip DESC LIMIT 0,1';
        $RS1=run_sql($SQL);
        if (is_array($RS1)) { //
          $code=trim($RS1[0]["code"]); //Country code
          $country=$RS1[0]["country"]; //Country name
          $RS1=search("nation", "code", $code); //根據 code 查中文國名
          if (is_array($RS1)) {$name=$RS1[0]["name"];}
          else {$name="";}
          }
        else {
          $country="";
          $name="";
          }
        $visitors[$i]=Array("id" => $RS[$i]["id"],
                            "visit_time" => $RS[$i]["visit_time"],
                            "remote_addr" => $RS[$i]["remote_addr"],
                            "remote_port" => $RS[$i]["remote_port"],
                            "country" => $country,
                            "name" => $name,
                            "user_agent" => $RS[$i]["user_agent"]
                            );
        }
      }
    $arr=array("total" => $total, "rows" => $visitors);
    echo json_encode($arr);
    break;
    }

主要就是利用從 ip2nationCountries 資料表查得的國名簡碼 code, 再去 nation 這張表去查中文國名而已. 這樣就能順利顯示訪客來自哪個國家了 :

以後若發現有些 IP 沒顯示國名, 表示資料需要更新了, 只要再去 ip2nation.com 下載最新的 sql 檔匯入資料庫即可, 而 nation 資料表除非有新的獨立國家, 否則幾乎都不用更新. 有了這三張資料表, 上一篇文章所用的檔案可以丟掉了, 而且 file.php 函式庫中新增的 get_country_by_ip() 也可以拿掉了.

如果能顯示 IP 的 DNS 更好, 可以大致知道訪客來自哪個公司或機關, 但可惜沒有這樣的資料庫可用, 有的話相信也很龐大, 會佔據 MySQL 很大份量. 這可以到下列網站查詢 :

# http://ping.eu/nslookup/

最後我修改了 EasyuiCMS 的系統安裝檔 install.php, 加入上面三個資料表的建立語法 :

    //建立 ip2nation 資料表 (訪客紀錄用:DUMMY:避免錯誤)
    $data_array["ip"]="int(11)";         //IP 上限
    $data_array["country"]="char(2)";    //國名簡碼
    $result=create_table("ip2nation",$data_array);
    if ($result) {$msg .= "建立資料表 ip2nation ... 完成!<br>";}
    $data_array=NULL;

    //建立 countries 資料表 (訪客紀錄用:DUMMY:避免錯誤)
    $data_array["code"]="varchar(4)";           //國名簡碼
    $data_array["iso_code_2"]="varchar(2)";     //國名簡碼
    $data_array["iso_code_3"]="varchar(3)";     //國名簡碼
    $data_array["iso_country"]="varchar(255)";
    $data_array["country"]="varchar(255)";
    $data_array["lat"]="float";
    $data_array["lon"]="float";
    $result=create_table("countries",$data_array);
    if ($result) {$msg .= "建立資料表 countries ... 完成!<br>";}
    $data_array=NULL;

    //建立 nation 資料表 (訪客紀錄)
    $data_array["code"]="varchar(4) PRIMARY KEY";
    $data_array["name"]="varchar(255)";       //中文國名
    $result=create_table("nation",$data_array);
    if ($result) {$msg .= "建立資料表 nation ... 完成!<br>";}
    $data_array=NULL;
    //從 data 下讀取 nation.txt 寫入 nation 資料表
    $file=read_file("./data/nation.txt");
    $lines=explode("\n", $file);
    foreach($lines as $line) {
      if (strlen($line)) {
        $arr=explode(',', trim($line));
        //插入 nation 資料表
        $data_array["code"]=$arr[0];
        $data_array["name"]=$arr[1];
        $result=insert("nation", $data_array);
        $data_array=NULL;
        }
      }

這裡主要是建立 nation 這張資料表, 並從 /data 下讀取 nation.txt 填入資料表中, 而 ip2nation 與 countries 這裡只是建立空資料表, 避免尚未匯入從 ip2nation.com 下載的 ip2nation.sql 時, 程式讀取它們可能產生的錯誤.

參考 :

# INET_ATON() and INET_NTOA() in PHP?