# Python 進階語法使用
###### tags: `Python/SQL商業資料分析` , `Python`
[TOC]
# Python SQL資料庫串接與檔案操作
## 建立範例環境設定
將以下 SQL 內容複製貼上到 phpMyAdmin SQL 輸入框執行，即可建立學習使用的資料表和資料集（若已經有建立 demo_shop 資料庫和資料表可以沿用，不用重複新增）。
預計新增資料表：
- users：使用者資訊
- products：商品資訊
- orders：訂單資訊
- order_details：訂單詳細資訊
1. **使用 XAMPP 啟動資料庫**
2. **開啟[phpMyAdmin](http://localhost/phpmyadmin)**
3. **建立資料庫**
4. **在 phpMyAdmin SQL 輸入框中新增資料表和資料集**（若已建立可以沿用）：
```sql
/* PRIMARY KEY 加入 AUTO_INCREMENT 可以在新增時自動累加 id 數值，就不用手動輸入 */
CREATE table users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
email VARCHAR(120) NOT NULL,
age INT NOT NULL,
created_at DATETIME
);
/* INSERT INTO 語法加入指定要插入的屬性欄位，就可以明確指出要插入的欄位，這樣就可以忽略自動產生或有預設值的欄位 */
INSERT INTO users (name, email, age, created_at)
VALUES
('Jack Hung', 'jackh32@gmail.com', 20, '2019-07-24 17:11:01'),
('Tony Liu', 'tonykk@gmail.com', 62, '2020-06-03 17:11:01'),
('Amy Chang', 'amychang@gmail.com', 32, '2020-05-11 17:11:01');
CREATE table products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
price INT NOT NULL,
weight DECIMAL(5, 2),
category VARCHAR(20),
created_at DATETIME
);
/*
INSERT INTO 資料表 (欄位) VALUES` 後面可以透過 , 分隔多筆資料，就可以一次新增多筆資料
*/
INSERT INTO products (name, price, weight, category, created_at)
VALUES
('華速 intel i3 筆電', 20000, 2.12, 'NB', '2020-05-11 17:11:01'),
('Mac Pro 筆電', 62000, 1.4, 'NB', '2020-05-11 17:11:01'),
('微興電競筆電', 32000, 3.00, 'NB', '2020-05-11 17:11:01'),
('戈林冰箱', 22000, 13.78, '3C', '2020-05-11 17:11:01'),
('三力冰箱', 52000, 23.18, '3C', '2020-05-11 17:11:01'),
('C 語言入門', 420, 0.31, 'Book', '2020-05-11 17:11:01'),
('python3 實戰', 580, 0.28, 'Book', '2020-05-11 17:11:01'),
('JavaScript 英雄', 1000, 0.12, 'Book', '2020-05-11 17:11:01'),
('Java 資料分析', 340, -1, 'Book', '2020-05-11 17:11:01'),
('python 資料分析', 640, -0.43, 'Book', '2020-05-11 17:11:01');
CREATE table orders (
id INT PRIMARY KEY AUTO_INCREMENT,
amount INT NOT NULL,
-- 先宣告欄位，再設定外鍵 FOREIGN KEY REFERENCES
customer_id INT,
created_at DATETIME,
FOREIGN KEY (customer_id) REFERENCES users(id)
);
INSERT INTO orders (amount, customer_id, created_at)
VALUES
(188420, 1, '2020-05-11 17:11:01'),
(54000, 3, '2019-04-13 17:11:01'),
(104420, 2, '2020-06-21 17:11:01'),
(52420, 2, '2020-05-01 17:11:01'),
(104000, 2, '2020-07-11 17:11:01'),
(32420, 2, '2019-03-24 17:11:01');
CREATE table order_details (
id INT PRIMARY KEY AUTO_INCREMENT,
-- 先宣告欄位，再設定外鍵 FOREIGN KEY REFERENCES
order_id INT,
product_id INT,
created_at DATETIME,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO order_details (order_id, product_id, created_at)
VALUES
(1, 1, '2020-05-11 17:11:01'),
(1, 2, '2020-05-11 17:11:01'),
(1, 3, '2020-05-11 17:11:01'),
(1, 4, '2020-05-11 17:11:01'),
(1, 5, '2020-05-11 17:11:01'),
(1, 6, '2020-05-11 17:11:01'),
(2, 3, '2020-04-13 17:11:01'),
(2, 4, '2020-04-13 17:11:01'),
(3, 5, '2020-06-21 17:11:01'),
(3, 6, '2020-06-21 17:11:01'),
(3, 5, '2020-06-21 17:11:01'),
(4, 5, '2020-05-01 17:11:01'),
(4, 6, '2020-05-01 17:11:01'),
(5, 5, '2020-07-11 17:11:01'),
(5, 5, '2020-07-11 17:11:01'),
(6, 6, '2020-03-24 17:11:01'),
(6, 3, '2020-03-24 17:11:01');
```
5. 使用 `PyMySQL` 這個 Python 套件操作資料庫。首先，開啟 Anaconda Prompt 終端機安裝套件：
6. Windows/Mac 建立資料庫使用者
在啟動 XAMPP 開始操作資料庫前我們可以進入 phpmyadmin 後台新增除了 root 管理員外的資料庫使用者。
- 進入 `phpmyadmin` 管理後台
- 進入使用者管理分頁
- 新增使用者（自訂使用者名稱和密碼）
- 主機名稱設定為 `localhost`，若 Mac 則改設定為 % 因為 Mac 版本的伺服器位置不同，若設定為 localhost 會無法登入。
- 給予使用者查詢、新增、更新和刪除權限
- 執行新增使用者
- 可以使用該使用者帳號密碼登入資料庫
## 整合 MariaDB/MySQL 與 Python
完成環境設定後，開始使用 `PyMySQL` 串接我們的 `MariaDB` 資料庫（PyMySQL 可以使用在 MySQL 和 MariaDB）。PyMySQL 相關介紹可以[參考官方網站](https://github.com/PyMySQL/PyMySQL)。
打開 VS Code 編輯器建立一個資料夾放我們要使用 Python 來操作資料庫的程式，建立程式檔案 demo_db.py：
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用，建議可以另外建立資料庫用者和密碼), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
```
> 預設我們 MariaDB 若沒有特別設定帳號是 root，密碼為空值。但實務上使用建議不要使用 root 管理員權限，另外於 phpMyAdmin 或下 SQL 語法建立新的使用者帳號密碼（但一般資料庫權限不是資料分析師在管理）
透過 cursor 來操作 SQL 語法，注意到我們使用 %s 當作佔位符號（placeholder）而非直接寫死 SQL 語法。在 Python MySQL /MariaDB SQL 敘述中 %s 為佔位符號，執行時會被轉成 SQL 敘述對應值，SQL 敘述為字串，所以即便傳入值為整數，也不用更改為 %d 或其他符號。
```python
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述查詢資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'SELECT id, name FROM users WHERE email=%s'
# 語法傳入第一個參數，後面參數為 %s 內容
cursor.execute(sql, ('jackh32@gmail.com',))
# 取出第一筆結果
result = cursor.fetchone()
print(result)
finally:
# 即便程式錯誤也會執行到這行關閉資料庫連線
connection.close()
```
根據 `PyMySQL` 官方文件說明 `cursorclass` 是決定查詢回傳的資料格式，`cursor` 物件則為負責和資料庫互動的指標。預設 connect 不設定 `cursorclass` 參數回傳是 tuple 格式，另外一種是傳入 `pymysql.cursors.DictCursor `類別當作 `cursorclass` 參數，操作過程中回傳查詢資料會是 dict 格式。參考文件：
- https://pymysql.readthedocs.io/en/latest/modules/connections.html
- https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.DictCursor
若使用 `pymysql.cursors.DictCursor` 當作 `cursorclass` 參數的程式碼：
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
```
## 查詢資料
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為3306若自己有更改不同port請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述查詢資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'SELECT id, name FROM users WHERE email=%s'
cursor.execute(sql, ('jackh32@gmail.com',))
# 取出第一筆結果
result = cursor.fetchone()
print(result)
finally:
# 即便程式錯誤也會執行到這行關閉資料庫連線
connection.close()
```
## 新增資料
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述新增資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'INSERT INTO users (name, email, age, created_at) VALUES (%s, %s, %s, %s)'
cursor.execute(sql, ('Henry Chao', 'henry123@gmail.com', 28, '2020-05-11 17:11:01'))
# 有修改到資料內容需要將變更提交到資料庫才算完成操作
connection.commit()
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述查詢資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'SELECT id, name FROM users WHERE email=%s'
cursor.execute(sql, ('henry123@gmail.com',))
# 取出第一筆結果
result = cursor.fetchone()
print(result)
finally:
# 即便程式錯誤也會執行到這行關閉資料庫連線
connection.close()
```
## 更新資料
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述更新資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'UPDATE users SET name=%s WHERE email=%s'
cursor.execute(sql, ('Henry Wu', 'henry123@gmail.com',))
connection.commit()
finally:
# 即便程式錯誤也會執行到這行關閉資料庫連線
connection.close()
```
## 刪除資料
```python
# 引用 pymsql 套件
import pymysql
# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
user='root',
password='',
# 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
port=3306,
db='demo_shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
# 使用 with...as 可以讓我們程式正確執行下自動關閉資料庫連線
with connection.cursor() as cursor:
# 執行 SQL 敘述刪除資料，使用佔位符號 %s 而不是直接寫死，提升執行效率和降低資安風險
sql = 'DELETE FROM users WHERE email=%s'
cursor.execute(sql, ('henry123@gmail.com',))
connection.commit()
finally:
# 即便程式錯誤也會執行到這行關閉資料庫連線
connection.close()
```
## 檔案處理基礎
使用內建函式 `open()` 來開啟檔案並用 `close()` 來關閉檔案。使用 `open()` 會建立一個 file 物件，在 Python 中可以同時讀寫多個檔案，依靠著 file 物件來識別目前操作的檔案。
```python
# 寫入模式
file_object = open('./demo.txt', 'w')
# 讀取模式
file_object = open('./demo.txt', 'r')
```
下是模式的列表（一般分為 read / write / append 三種模式，而 +，代表是否支援檔案更新，代表支援讀寫功能）：
| mode 模式 | 當檔案已經存在 | 檔案不存在 |
|:--------- | ---------------------- | ------------ |
| w | 開啟唯讀檔案 | 發生錯誤 |
| r | 清除檔案內容後寫入 | 建立寫入檔案 |
| a | 將內容寫到檔案最後面 | 建立寫入檔案 |
| w+ | 開啟讀寫檔案 | 發生錯誤 |
| r+ | 清除檔案內容後讀寫入 | 建立讀寫檔案 |
| a+ | 將內容讀寫到檔案最後面 | 建立讀寫檔案 |
### Python 開關檔案
使用 `open('檔案路徑', '讀寫模式')` 來取得 `file object`。最後 close 關掉檔案。記得開檔案後要關掉，避免造成記憶體流失系統資源的浪費。
```python
file_object = open('./demo.txt', 'r')
file_object.close()
```
### Python 讀取檔案
在 Python 主要用來讀取檔案的方法有三個：`read([size])`、`readline()` 和 `readlines()`。
- `read([size])`：從檔案當前位置起讀取 size 大小的位元組。若無參數 size，則表示讀取到檔案結束為止（讀取整個檔案），回傳整個檔案內容。
```python
file_object = open('./demo.txt', 'r')
# 讀取全部檔案案內容變成字串
print(file_object.read())
```
- `readline([size])`：每次讀出一行內容，因此在讀取時佔用記憶體較小，比較適合大型檔案，回傳一行字串。也可選擇性傳入 size 參數
```python
file_obj = open('./data.txt', 'r')
# 每次讀取一行，_ 是一個佔位符號，當沒有使用到變數時可以使用
for _ in range(3):
print(file_obj.readline())
file_obj.close()
```
- `readlines()`：讀取整個檔案所有行，並將每行內容儲存在一個列表（list）中，每一行作為一個列表元素。但當讀取大型檔案會比較佔記憶體，不建議用於讀取大型檔案
```python
# 讀取內容成為列表，檔案一行代表一個元素，可以使用迭代方式操作
file_line_item_list = file_object.readlines()
for file_line_item in file_line_item_list:
print(file_line_item)
file_object.close()
```
### Python 寫入檔案
使用 `f.write('寫入的字串內容')` 可以將字串內容寫入檔案中（f 為檔案物件）：
```python
# 開啟檔案物件準備寫入 demo.txt 中，w 為寫入 mode
file_object = open('./demo.txt', 'w')
# 若需要換行可以於字串最後加入： \n 為換行符號
file_object.write('Java is awesome\n')
file_object.write('Python is awesome')
# 最後記得將檔案物件關閉
file_object.close()
# Java is awesome
# Python is awesome
```
### with as 檔案操作
我們可以使用 `with...as` 區塊（背後原理為 [context managers](https://book.pythontips.com/en/latest/context_managers.html)），透過 `with...as` 和縮排，可以讓我們在程式執行完畢可以自動關閉檔案。
```python
with open('./demo.txt', 'a') as file_object:
file_object.write('Python is awesom')
```
# 延伸閱讀
- [SQL 資料庫](https://hackmd.io/fQbgqRSYTDi2Ouis0fE3rg?both)
- [Python 網頁爬蟲](https://hackmd.io/JOc4g8AjSZiokS6vZckFSw?view)
- [Python 基礎語法](https://hackmd.io/4FH3w4_pQP6_dsandd45LA)
- [Python 資料分析](https://hackmd.io/C5I9OzXzQCe6wJuZhwRV3Q)
- [Python 資料科學與探索式資料分析](https://hackmd.io/qSceMWZWQcWsMIA9QiO1Nw?view)
- [營收與使用者行為資料分析專案](https://hackmd.io/i6kRZN8JQsq57uDrKeKoLQ)
- [Python專案實作 資料分析與爬蟲](https://hackmd.io/oh18KsFvSxe5Eh3ECHDJOA?view)
Sign in with Wallet
Connect another wallet