2018年5月4日 星期五

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')])

沒有留言 :