近期由於在忙一個Web APP，會需要用到爬蟲，並且要存到資料庫裡(剛好這個APP在雲端的資料庫是MySQL)，所以就開始研究怎麼爬蟲、怎麼存資料、怎麼操作MySQL…，東西一多就雜、變得難記，因此就把一些瑣碎的事情寫在這吧！
要準備什麼？
（假設你已經有安裝python3和pip3了）
1. 安裝MySQL
1 | [~]$ sudo apt-get install mysql-common mysql-client libmysqlclient-dev mysql-server |
ps. 安裝期間會要求輸入密碼
2. 安裝mysqlclient
1 | [~]$ pip3 install mysqlclient |
3. 學會SQL！！！
之前都是用Rails內建的Active Record來操作資料的，要我透過Rails存資料、拿資料、關聯都可以，但給我一筆資料要直接存入資料庫，沒有經過Rails我就掛了，就是因為不會SQL。那為什麼不在Rails中做爬蟲然後就可以透過Rails存資料呢？因為查了查資料，發現Ruby雖然可以爬，可是資源實在是很少，要學爬蟲還是用Python比較實在！
簡單介紹SQL
SQL並不是一種API或是一種協定，而是一種宣告式語言，是關聯資料庫的通用語言。
這是我在歐萊禮出版的精通Python內所看到的，個人覺得這本書很不錯，網路上的評價也很好。
SQL陳述式可以分為兩類：
- DDL(Data Definition Language)：負責處理建構與定義資料庫結構與管理資料庫權限，主要由CREATE、DROP、ALTER3個語法所組成
- DML(Data Manipulation Language)：資料的CRUD
DDL基本指令
| 動作 | SQL | 範例 |
|---|---|---|
| 建立資料庫 | CREATE DATABASE dbname | CREATE DATABASE my_app |
| 選擇資料庫 | USE dbname | Use my_app |
| 刪除資料庫與其資料表 | DROP DATABASE dbname | DROP DATABASE my_app |
| 建立資料表 | CREATE TABLE tbname (column data-type) | CREATE TABLE users (id INT, name VARCHAR(20), birthday DATE) |
| 刪除資料表與其資料 | DROP TABLE tbname | DROP TABLE users |
| 清空資料內容(不會刪掉資料表) | TRUNCATE TABLE tbname | TRUNCATE TABLE users |
DML基本指令
| 動作 | SQL | 範例 |
|---|---|---|
| 加入資料列 | INSERT INTO tbname VALUES(data) | INSERT INTO users VALUES(1, ‘Qaz’, ‘1974-10-12’) |
| 選取資料 | SELECT column FROM tbname WHERE condition | SELECT id,name FROM users WHERE id=1 |
| 更新資料 | UPDATE tbname SET colume=value WHERE condition | UPDATE users name=’Wsx’ WHERE id>3 |
| 刪除資料 | DELETE FROM tbname WHERE condition | DELETE FROM users WHERE name!=’Qaz’ |
然後其實上面的指令可以不用大寫沒關係，但我比較喜歡他大寫的樣子，比較好區分哪些是指令、哪些是我們要帶的
好了啦再來呢？
使用Python的DB-API有幾個通用的函式，讓我們可以存取各種關聯資料庫：
- connect()： 連結資料庫，可以傳入帳號、密碼、伺服器等參數
- cursor(): 建立一個cursor物件來處理SQL指令
- execute()與executemany()： 對資料庫執行一或多個SQL指令，是cursor物件的方法
- fetchone()、fetchmany()、fetchall()： 取得execute的結果
操作流程[1]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21# .py
import MySQLdb #因為MySQLdb不支援python3，所以有開發者開發了python3能使用的mysqlclient，在import的時候仍然以MySQLdb為名，且操作和MySQLdb都相同
# 連接MySQL資料庫
db = MySQLdb.connect(host='localhost', user='db_user', passwd='db_passwd', db='db_name', charset='utf8') # 最後一項若沒設定則取回的資料將無法以中文字顯示
curs = db.cursor()
# 執行MySQL指令
curs.execute("放入MySQL指令")
# 取得結果
curs.fetchall()
# 取得資料總筆數
curs.rowcount
# 若有修改資料記得要做這個動作
db.commit()
# 結束與資料庫的連結
db.close()
所以我說MySQL指令呢？
在上面是python透過mysqlclient操作MySQL的作法，操作中最重要的就是在execute裡輸入MySQL指令，而目前我們只知道一部份的SQL指令，對於MySQL指令是什麼並不知道。
MySQL是一個資料庫管理系統(DBMS)，除了支援SQL以外，還能透過某些指令進行資料庫操作權限的管理，像是設定使用者、密碼、管理使用者權限範圍等等。先不管權限的問題，就對資料的CRUD而言，MySQL的指令幾乎就和SQL一樣，因此就不贅述。以下就列出除了CRUD外，我認為重要且常用的指令[2]。
| 動作 | MySQL |
|---|---|
| 以root身份進入MySQL console | mysql -u root -p |
| 更新資料表名稱 | ALTER TABLE tbname1 RENAME tbname2; |
| 新增欄位 | ALTER TABLE tbname ADD column INT; |
| 修改欄位的資料型態 | ALTER TABLE tbname MODIFY column INT; |
| 修改欄位名稱與資料型態 | ALTER TABLE tbname CHANGE column1 column2 INT; |
| 刪除欄位 | ALTER TABLE tbname DROP column; |
| 顯示所有資料庫 | SHOW DATABASES; |
| 顯示該資料庫的所有資料表 | SHOW TABLES; |
| 顯示欄位資訊 | DESCRIBE tbname; |
還有幾個對資料庫設定也很重要的操作
設定Primary Key
- 可以在一開始建立資料表時就設定：
CREATE TALBE tbname (id INT AUTO_INCREMENT, PRIMARY KEY (id)); - 也可以在建立資料表後修改：
ALTER TABLE tbname ADD PRIMARY KEY (id);
設定AUOT_INCREMENT
預設會是從1開始遞增(2,3,4…)，但如果想更改起始數字的話可以ALTER TABLE tbname AUTO_INCREMENT=start_number;
設定default與是否允許空值
CREATE TABLE tbname (name NOT NULL DEFAULT "Hi!");
設定欄位資料不重複
CREATE TABLE tbname (id UNIQUE);
設定建立時間
ALTER TABLE tbname ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
設定存入資料的條件
照理說可以用ALTER TABLE tbname ADD CHECK (age>=18);來設定age至少要18才能存入資料庫，但我在MySQL試過了，即使設定好條件，小於18的還是能存入；上網查了一下，Stack Overflow說MySQL是不支援CHECK的囧…
意外發現TRUNCATE TABLE tbname與DELETE FROM tbname的小差異
雖然同樣是把資料表清空，但TRUNCATE是把所有相關設定都刪除，而DELETE只是把資料拿掉而已；舉例來說，TRUNCATE後再新增資料，資料的PK會從1開始；而若是使用DELETE，則資料的PK會接續之前刪掉的繼續下去，並不會從頭開始。