MySQL儲存

MySQL儲存

　　關係型資料庫是基於關係模型的資料庫，而關係模型是透過二維表來儲存的，所以它的儲存方式就是行列組成的表，每一列是一個欄位，每一行是一條記錄。表可以看作某個實體的集合，實體之間存在聯絡需要表與表之間的關聯關係來體現，如主鍵外來鍵的關聯關係。多個表組成一個資料庫，也就是關係型資料庫。

　　關係型資料庫有多種，如 SQLite、MySQL、Oracle、SQL Server、DB2 等。

　　在Python2中，連線MySQL的庫大多是MySQLdb，但是此庫的官方並不支援Python3，所以支援使用的庫是PyMySQL。

準備工作

　　安裝好 MySQL 資料庫並保證它能正常執行，而且需要安裝好 PyMySQL 庫。

連線資料庫

　　嘗試連線一下資料庫。假設當前的 MySQL 執行在本地，使用者名稱為 root，密碼為 123456，執行埠為 3306。這裡利用 PyMySQL 先連線 MySQL，然後建立一個新的資料庫，名字叫作 spiders，程式碼如下：

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
db.close()

執行結果：

Database version: ('5.7.21-log',)

　　PyMySQL 的 connect 方法宣告一個 MySQL 連線物件 db，此時需要傳入的第一個引數是 MySQL 執行的 host（即 IP）。由於 MySQL 在本地執行，所以傳入的是 localhost。如果 MySQL 在遠端執行，則傳入其公網 IP 地址。後續的引數 user （使用者名稱），password （密碼）和port （埠）（預設為 3306）。

　　連線成功後，呼叫 cursor 方法獲得 MySQL 的操作遊標，利用遊標來執行 SQL 語句。這裡執行了兩句個SQL語句，直接呼叫 execute 方法執行即可。第一句 SQL 語句用於獲得 MySQL 的當前版本，然後呼叫 fetchone 方法獲得第一條資料，即版本號。第二句 SQL 執行建立資料庫的操作，資料庫名叫作 spiders，預設編碼為 UTF-8。由於該語句不是查詢語句，所以直接執行後就成功建立了資料庫 spiders。可以利用這個資料庫進行後續的操作。

建立表

　　建立資料庫的操作只需要執行一次就好了。當然也可以手動建立資料庫。以後操作都在 spiders 資料庫上執行。建立資料庫後，在連線時需要額外指定一個引數 db。

　　接下來，新建立一個資料表 students，此時執行建立表的 SQL 語句即可。這裡指定 3 個欄位。

字　段　名	含　　義	類　　型
id	學號	varchar
name	姓名	varchar
age	年齡	int

建立該表的示例程式碼如下：

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cursor.execute(sql)
db.close()

　　執行之後，便建立了一個名為 students 的資料表。

　　為了演示這裡只指定了最簡單的幾個欄位。實際上在爬蟲過程中，要根據爬取結果設計特定的欄位。

插入資料

　　下一步就是向資料庫中插入資料了。例如，這裡爬取了一個學生資訊，學號為 20120001，名字為 Bob，年齡為 20，將將該條資料插入資料庫：

import pymysql

id = '20120001'
user = 'Bob'
age = 20

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students(id, name, age) values(% s, % s, % s)'
try:
    cursor.execute(sql, (id, user, age))
    db.commit()
except:
    db.rollback()
db.close()

　　首先構造了一個 SQL 語句，其 值沒有用字串拼接的方式來構造，如：

sql = 'INSERT INTO students(id, name, age) values(' + id + ', ' + name + ', ' + age + ')'

　　這樣的寫法煩瑣而且不直觀，所以直接用格式化符 % s 來實現。有幾個value寫幾個 % s，只需要在 execute 方法的第一個引數傳入該 SQL 語句，value 值用統一的元組傳過來就好。這樣的寫法既可以避免字串拼接的麻煩，又可以避免引號衝突的問題。

　　之後需要執行 db 物件的 commit 方法才可實現資料插入，這個方法才是真正將語句提交到資料庫執行的方法。對於資料插入、更新、刪除操作，都需要呼叫該方法才能生效。

　　接下來加了一層異常處理。如果執行失敗，則呼叫 rollback 執行資料回滾，相當於什麼都沒有發生過。

　　這裡涉及事務的問題。事務機制可以確保資料的一致性，也就是這件事要麼發生了，要麼沒有發生。比如插入一條資料，不會存在插入一半的情況，要麼全部插入，要麼都不插入，這就是事務的原子性。另外，事務還有 3 個屬性 —— 一致性、隔離性和永續性。這 4 個屬性通常稱為 ACID 特性。

屬　　性	解　　釋
原子性（atomicity）	事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做
一致性（consistency）	事務必須使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的
隔離性（isolation）	一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾
永續性（durability）	持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響

　　插入、更新和刪除操作都是對資料庫進行更改的操作，而更改操作都必須為一個事務，所以這些操作的標準寫法就是：

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

　　這樣便可以保證資料的一致性。這裡的 commit 和 rollback 方法就為事務的實現提供了支援。

　　上面資料插入的操作是透過構造 SQL 語句實現的，但是很明顯，這有一個極其不方便的地方，比如突然增加了性別欄位 gender，此時 SQL 語句就需要改成：

INSERT INTO students(id, name, age, gender) values(% s, % s, % s, % s)

　　相應的元組引數則需要改成：

(id, name, age, gender)

　　在很多情況下，要達到的效果是插入方法無需改動，做成一個通用方法，只需要傳入一個動態變化的字典就好了。比如，構造這樣一個字典：

{
    'id': '20120001',
    'name': 'Bob',
    'age': 20
}

　　 SQL 語句會根據字典動態構造，元組也動態構造，這樣才能實現通用的插入方法。所以，這裡我們需要改寫一下插入方法：

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
cursor = db.cursor()
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
    if cursor.execute(sql, tuple(data.values())):
        print('Successful')
        db.commit()
except Exception as e:
    print('Failed', e)
    db.rollback()
db.close()

　　傳入的資料是字典，並將其定義為 data 變數。表名也定義成變數 table。接下來，就需要構造一個動態的 SQL 語句了。

　　首先構造插入的欄位： id、name 和 age。這裡只需要將 data 的鍵名拿過來，然後用逗號分隔即可。所以 ', '.join(data.keys()) 的結果就是 id, name, age，然後需要構造多個 % s 當作佔位符，有幾個欄位構造幾個即可。比如，這裡有三個欄位，就需要構造 % s, % s, % s。這裡首先定義了長度為 1 的陣列 ['% s']，然後用乘法將其擴充為 ['% s', '% s', '% s']，再呼叫 join 方法，最終變成 % s, % s, % s。最後，我們再利用字串的 format 方法將表名、欄位名和佔位符構造出來。最終的 SQL 語句就被動態構造成了：

INSERT INTO students(id, name, age) VALUES (% s, % s, % s)

最後，為 execute 方法的第一個引數傳入 sql 變數，第二個引數傳入 data 的鍵值構造的元組，就可以成功插入資料了。

如此以來，我們便實現了傳入一個字典來插入資料的方法，不需要再去修改 SQL 語句和插入操作了。

更新資料

資料更新操作實際上也是執行 SQL 語句，最簡單的方式就是構造一個 SQL 語句，然後執行：

sql = 'UPDATE students SET age = % s WHERE name = % s'
try:
   cursor.execute(sql, (25, 'Bob'))
   db.commit()
except:
   db.rollback()
db.close()

這裡同樣用佔位符的方式構造 SQL，然後執行 execute 方法，傳入元組形式的引數，同樣執行 commit 方法執行操作。如果要做簡單的資料更新的話，完全可以使用此方法。

但是在實際的資料抓取過程中，大部分情況下需要插入資料，但是我們關心的是會不會出現重複資料，如果出現了，我們希望更新資料而不是重複儲存一次。另外，就像前面所說的動態構造 SQL 的問題，所以這裡可以再實現一種去重的方法，如果資料存在，則更新資料；如果資料不存在，則插入資料。另外，這種做法支援靈活的字典傳值。示例如下：

data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 21
}

table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['% s'] * len(data))

sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join(["{key} = % s".format(key=key) for key in data])
sql += update
try:
    if cursor.execute(sql, tuple(data.values())*2):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

這裡構造的 SQL 語句其實是插入語句，但是我們在後面加了 ON DUPLICATE KEY UPDATE。這行程式碼的意思是如果主鍵已經存在，就執行更新操作。比如，我們傳入的資料 id 仍然為 20120001，但是年齡有所變化，由 20 變成了 21，此時這條資料不會被插入，而是直接更新 id 為 20120001 的資料。完整的 SQL 構造出來是這樣的：

INSERT INTO students(id, name, age) VALUES (% s, % s, % s) ON DUPLICATE KEY UPDATE id = % s, name = % s, age = % s

這裡就變成了 6 個 % s。所以在後面的 execute 方法的第二個引數元組就需要乘以 2 變成原來的 2 倍。

如此一來，我們就可以實現主鍵不存在便插入資料，存在則更新資料的功能了。

刪除資料

刪除操作相對簡單，直接使用 DELETE 語句即可，只是需要指定要刪除的目標表名和刪除條件，而且仍然需要使用 db 的 commit 方法才能生效。示例如下：

table = 'students'
condition = 'age > 20'

sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condition)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

因為刪除條件有多種多樣，運算子有大於、小於、等於、LIKE 等，條件連線符有 AND、OR 等，所以不再繼續構造複雜的判斷條件。這裡直接將條件當作字串來傳遞，以實現刪除操作。

查詢資料

說完插入、修改和刪除等操作，還剩下非常重要的一個操作，那就是查詢。查詢會用到 SELECT 語句，示例如下：

sql = 'SELECT * FROM students WHERE age >= 20'

try:
    cursor.execute(sql)
    print('Count:', cursor.rowcount)
    one = cursor.fetchone()
    print('One:', one)
    results = cursor.fetchall()
    print('Results:', results)
    print('Results Type:', type(results))
    for row in results:
        print(row)
except:
    print('Error')

執行結果如下：

Count: 4
One: ('20120001', 'Bob', 25)
Results: (('20120011', 'Mary', 21), ('20120012', 'Mike', 20), ('20120013', 'James', 22))
Results Type: <class 'tuple'>
('20120011', 'Mary', 21)
('20120012', 'Mike', 20)
('20120013', 'James', 22)

這裡我們構造了一條 SQL 語句，將年齡 20 歲及以上的學生查詢出來，然後將其傳給 execute 方法。注意，這裡不再需要 db 的 commit 方法。接著，呼叫 cursor 的 rowcount 屬性獲取查詢結果的條數，當前示例中是 4 條。

然後我們呼叫了 fetchone 方法，這個方法可以獲取結果的第一條資料，返回結果是元組形式，元組的元素順序跟欄位一一對應，即第一個元素就是第一個欄位 id，第二個元素就是第二個欄位 name，以此類推。隨後，我們又呼叫了 fetchall 方法，它可以得到結果的所有資料。然後將其結果和型別列印出來，它是二重元組，每個元素都是一條記錄，我們將其遍歷輸出出來。

但是這裡需要注意一個問題，這裡顯示的是 3 條資料而不是 4 條，fetchall 方法不是獲取所有資料嗎？這是因為它的內部實現有一個偏移指標用來指向查詢結果，最開始偏移指標指向第一條資料，取一次之後，指標偏移到下一條資料，這樣再取的話，就會取到下一條資料了。我們最初呼叫了一次 fetchone 方法，這樣結果的偏移指標就指向下一條資料，fetchall 方法返回的是偏移指標指向的資料一直到結束的所有資料，所以該方法獲取的結果就只剩 3 個了。

此外，我們還可以用 while 迴圈加 fetchone 方法來獲取所有資料，而不是用 fetchall 全部一起獲取出來。fetchall 會將結果以元組形式全部返回，如果資料量很大，那麼佔用的開銷會非常高。因此，推薦使用如下方法來逐條取資料：

sql = 'SELECT * FROM students WHERE age >= 20'
try:
    cursor.execute(sql)
    print('Count:', cursor.rowcount)
    row = cursor.fetchone()
    while row:
        print('Row:', row)
        row = cursor.fetchone()
except:
    print('Error')

這樣每迴圈一次，指標就會偏移一條資料，隨用隨取，簡單高效。

本節中，我們介紹瞭如何使用 PyMySQL 操作 MySQL 資料庫以及一些 SQL 語句的構造方法，後面會在實戰案例中應用這些操作來儲存資料。