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 資料庫常用的欄位與其屬性如下 :
而屬性是放在類型後面的限制, 如下表所示 :
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)
我下載的是 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
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')])
