摘要：關系型數據庫有多種，如等等。一致性與原子性是密切相關的。持久性持續性也稱永久性，指一個事務一旦提交，它對數據庫中數據的改變就應該是永久性的。如此一來，我們就可以實現主鍵不存在便插入數據，存在則更新數據的功能了。

上一篇文章：Python3網絡爬蟲實戰---31、數據存儲：文件存儲
下一篇文章：Python3網絡爬蟲實戰---33、數據存儲：非關系型數據庫存儲:MongoDB

關系型數據庫基于關系模型的數據庫，而關系模型是通過二維表來保存的，所以它的存儲方式就是行列組成的表，每一列是一個字段，每一行是一條記錄。表可以看作是某個實體的集合，而實體之間存在聯系，這就需要表與表之間的關聯關系來體現，如主鍵外鍵的關聯關系，多個表組成一個數據庫，也就是關系型數據庫。

關系型數據庫有多種，如 SQLite、MySQL、Oracle、SQL Server、DB2等等。

在本節我們主要介紹 Python3 下 MySQL 的存儲。

在 Python2 中，連接 MySQL 的庫大多是使用 MySQLDB，但是此庫官方并不支持 Python3，所以在這里推薦使用的庫是 PyMySQL。

本節來講解一下 PyMySQL 操作 MySQL 數據庫的方法。

在本節開始之前請確保已經安裝好了 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.6.22",)

在這里我們通過 PyMySQL 的 connect() 方法聲明了一個 MySQL 連接對象，需要傳入 MySQL 運行的 host 即 IP，此處由于 MySQL 在本地運行，所以傳入的是 localhost，如果 MySQL 在遠程運行，則傳入其公網 IP 地址，然后后續的參數 user 即用戶名，password 即密碼，port 即端口默認 3306。

連接成功之后，我們需要再調用 cursor() 方法獲得 MySQL 的操作游標，利用游標來執行 SQL 語句，例如在這里我們執行了兩句 SQL，用 execute() 方法執行相應的 SQL 語句即可，第一句 SQL 是獲得 MySQL 當前版本，然后調用fetchone() 方法來獲得第一條數據，也就得到了版本號，另外我們還執行了創建數據庫的操作，數據庫名稱叫做 spiders，默認編碼為 utf-8，由于該語句不是查詢語句，所以直接執行后我們就成功創建了一個數據庫 spiders，接著我們再利用這個數據庫進行后續的操作。

一般來說上面的創建數據庫操作我們只需要執行一次就好了，當然我們也可以手動來創建數據庫，以后我們的操作都是在此數據庫上操作的，所以后文介紹的 MySQL 連接會直接指定當前數據庫 spiders，所有操作都是在 spiders 數據庫內執行的。

所以這里MySQL的連接就需要額外指定一個參數 db。

然后接下來我們新創建一個數據表，執行創建表的 SQL 語句即可，創建一個用戶表 students，在這里指定三個字段，結構如下：

創建表的示例代碼如下：

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 語句，其 Value 值我們沒有用字符串拼接的方式來構造，如：

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

這樣的寫法繁瑣而且不直觀，所以我們選擇直接用格式化符 %s 來實現，有幾個 Value 寫幾個 %s，我們只需要在 execute() 方法的第一個參數傳入該 SQL 語句，Value 值用統一的元組傳過來就好了。

這樣的寫法有既可以避免字符串拼接的麻煩，又可以避免引號沖突的問題。

之后值得注意的是，需要執行 db 對象的 commit() 方法才可實現數據插入，這個方法才是真正將語句提交到數據庫執行的方法，對于數據插入、更新、刪除操作都需要調用該方法才能生效。

接下來我們加了一層異常處理，如果執行失敗，則調用rollback() 執行數據回滾，相當于什么都沒有發生過一樣。

在這里就涉及一個事務的問題，事務機制可以確保數據的一致性，也就是這件事要么發生了，要么沒有發生，比如插入一條數據，不會存在插入一半的情況，要么全部插入，要么整個一條都不插入，這就是事務的原子性，另外事務還有另外三個屬性，一致性、隔離性、持久性，通常成為 ACID 特性。

歸納如下：

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

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 語句會根據字典動態構造，元組也動態構造，這樣才能實現通用的插入方法。所以在這里我們需要將插入方法改寫一下：

data = {
    "id": "20120001",
    "name": "Bob",
    "age": 20
}
table = "students"
keys = ", ".join(data.keys())
values = ", ".join(["%s"] * len(data))
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:
    print("Failed")
    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，然后執行 excute() 方法，傳入元組形式的參數，同樣執行 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

相比上面介紹的插入操作的 SQL，后面多了一部分內容，那就是更新的字段，ON DUPLICATE KEY UPDATE 使得主鍵已存在的數據進行更新，后面跟的是更新的字段內容。所以這里就變成了 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: 
("20120011", "Mary", 21)
("20120012", "Mike", 20)
("20120013", "James", 22)

在這里我們構造了一條 SQL 語句，將年齡 20 歲及以上的學生查詢出來，然后將其傳給 execute() 方法即可，注意在這里不再需要 db 的 commit() 方法。然后我們可以調用 cursor 的 rowcount 屬性獲取查詢結果的條數，當前示例中獲取的結果條數是 4 條。

然后我們調用了 fetchone() 方法，這個方法可以獲取結果的第一條數據，返回結果是元組形式，元組的元素順序跟字段一一對應，也就是第一個元素就是第一個字段 id，第二個元素就是第二個字段 name，以此類推。隨后我們又調用了fetchall() 方法，它可以得到結果的所有數據，然后將其結果和類型打印出來，它是二重元組，每個元素都是一條記錄。我們將其遍歷輸出，將其逐個輸出出來。

但是這里注意到一個問題，顯示的是4條數據，fetall() 方法不是獲取所有數據嗎？為什么只有3條？這是因為它的內部實現是有一個偏移指針來指向查詢結果的，最開始偏移指針指向第一條數據，取一次之后，指針偏移到下一條數據，這樣再取的話就會取到下一條數據了。所以我們最初調用了一次 fetchone() 方法，這樣結果的偏移指針就指向了下一條數據，fetchall() 方法返回的是偏移指針指向的數據一直到結束的所有數據，所以 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語句的構造方法，在后文我們會在實戰案例中應用這些操作進行數據存儲。

上一篇文章：Python3網絡爬蟲實戰---31、數據存儲：文件存儲
下一篇文章：Python3網絡爬蟲實戰---33、數據存儲：非關系型數據庫存儲:MongoDB