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

上一篇文章：Python3網(wǎng)絡(luò)爬蟲實戰(zhàn)---31、數(shù)據(jù)存儲：文件存儲
下一篇文章：Python3網(wǎng)絡(luò)爬蟲實戰(zhàn)---33、數(shù)據(jù)存儲：非關(guān)系型數(shù)據(jù)庫存儲:MongoDB

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

關(guān)系型數(shù)據(jù)庫有多種，如 SQLite、MySQL、Oracle、SQL Server、DB2等等。

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

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

本節(jié)來講解一下 PyMySQL 操作 MySQL 數(shù)據(jù)庫的方法。

在本節(jié)開始之前請確保已經(jīng)安裝好了 MySQL 數(shù)據(jù)庫并正常運行，而且需要安裝好 PyMySQL 庫，如果沒有安裝，可以參考第一章的安裝說明。

在這里我們首先嘗試連接一下數(shù)據(jù)庫，假設(shè)當(dāng)前的 MySQL運行在本地，用戶名為 root，密碼為 123456，運行端口為 3306，在這里我們利用 PyMySQL 先連接一下 MySQL 然后創(chuàng)建一個新的數(shù)據(jù)庫，名字叫做 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()

運行結(jié)果：

Database version: ("5.6.22",)

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

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

一般來說上面的創(chuàng)建數(shù)據(jù)庫操作我們只需要執(zhí)行一次就好了，當(dāng)然我們也可以手動來創(chuàng)建數(shù)據(jù)庫，以后我們的操作都是在此數(shù)據(jù)庫上操作的，所以后文介紹的 MySQL 連接會直接指定當(dāng)前數(shù)據(jù)庫 spiders，所有操作都是在 spiders 數(shù)據(jù)庫內(nèi)執(zhí)行的。

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

然后接下來我們新創(chuàng)建一個數(shù)據(jù)表，執(zhí)行創(chuàng)建表的 SQL 語句即可，創(chuàng)建一個用戶表 students，在這里指定三個字段，結(jié)構(gòu)如下：

創(chuàng)建表的示例代碼如下：

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()

運行之后我們便創(chuàng)建了一個名為 students 的數(shù)據(jù)表，字段即為上文列舉的三個字段。

當(dāng)然在這里作為演示我們指定了最簡單的幾個字段，實際在爬蟲過程中我們會根據(jù)爬取結(jié)果設(shè)計特定的字段。

我們將數(shù)據(jù)解析出來后的下一步就是向數(shù)據(jù)庫中插入數(shù)據(jù)了，例如在這里我們爬取了一個的學(xué)生信息，學(xué)號為 20120001，名字為 Bob，年齡為 20，那么如何將該條數(shù)據(jù)插入數(shù)據(jù)庫呢，實例代碼如下：

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()

在這里我們首先構(gòu)造了一個 SQL 語句，其 Value 值我們沒有用字符串拼接的方式來構(gòu)造，如：

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

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

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

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

接下來我們加了一層異常處理，如果執(zhí)行失敗，則調(diào)用rollback() 執(zhí)行數(shù)據(jù)回滾，相當(dāng)于什么都沒有發(fā)生過一樣。

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

歸納如下：

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

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

這樣我們便可以保證數(shù)據(jù)的一致性，在這里的 commit() 和 rollback() 方法就是為事務(wù)的實現(xiàn)提供了支持。

好，在上面我們了解了數(shù)據(jù)插入的操作，是通過構(gòu)造一個 SQL 語句來實現(xiàn)的，但是很明顯，這里有一個及其不方便的地方，比如又加了一個性別 gender，假如突然增加了一個字段，那么我們構(gòu)造的 SQL 語句就需要改成：

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

相應(yīng)的元組參數(shù)則需要改成：

(id, name, age, gender)

這顯然不是我們想要的，在很多情況下，我們要達到的效果是插入方法無需改動，做成一個通用方法，只需要傳入一個動態(tài)變化的字典給就好了。比如我們構(gòu)造這樣一個字典：

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

然后 SQL 語句會根據(jù)字典動態(tài)構(gòu)造，元組也動態(tài)構(gòu)造，這樣才能實現(xiàn)通用的插入方法。所以在這里我們需要將插入方法改寫一下：

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()

在這里我們傳入的數(shù)據(jù)是字典的形式，定義為 data 變量，表名也定義成變量 table。接下來我們就需要構(gòu)造一個動態(tài)的 SQL 語句了。

首先我們需要構(gòu)造插入的字段，id、name 和 age，在這里只需要將data的鍵名拿過來，然后用逗號分隔即可。所以 ", ".join(data.keys()) 的結(jié)果就是 id, name, age，然后我們需要構(gòu)造多個 %s 當(dāng)作占位符，有幾個字段構(gòu)造幾個，比如在這里有兩個字段，就需要構(gòu)造 %s, %s, %s ，所以在這里首先定義了長度為 1 的數(shù)組 ["%s"] ，然后用乘法將其擴充為 ["%s", "%s", "%s"]，再調(diào)用 join() 方法，最終變成 %s, %s, %s。所以我們再利用字符串的 format() 方法將表名，字段名，占位符構(gòu)造出來，最終sql語句就被動態(tài)構(gòu)造成了：

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

最后再 execute() 方法的第一個參數(shù)傳入 sql 變量，第二個參數(shù)傳入 data 的鍵值構(gòu)造的元組，就可以成功插入數(shù)據(jù)了。

如此以來，我們便實現(xiàn)了傳入一個字典來插入數(shù)據(jù)的方法，不需要再去修改 SQL 語句和插入操作了。

數(shù)據(jù)更新操作實際上也是執(zhí)行 SQL 語句，最簡單的方式就是構(gòu)造一個 SQL 語句然后執(zhí)行：

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

在這里同樣是用占位符的方式構(gòu)造 SQL，然后執(zhí)行 excute() 方法，傳入元組形式的參數(shù)，同樣執(zhí)行 commit() 方法執(zhí)行操作。

如果要做簡單的數(shù)據(jù)更新的話，使用此方法是完全可以的。

但是在實際數(shù)據(jù)抓取過程中，在大部分情況下是需要插入數(shù)據(jù)的，但是我們關(guān)心的是會不會出現(xiàn)重復(fù)數(shù)據(jù)，如果出現(xiàn)了重復(fù)數(shù)據(jù)，我們更希望的做法一般是更新數(shù)據(jù)而不是重復(fù)保存一次，另外就是像上文所說的動態(tài)構(gòu)造 SQL 的問題，所以在這里我們在這里重新實現(xiàn)一種可以做到去重的做法，如果重復(fù)則更新數(shù)據(jù)，如果數(shù)據(jù)不存在則插入數(shù)據(jù)，另外支持靈活的字典傳值。

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()

在這里構(gòu)造的 SQL 語句其實是插入語句，但是在后面加了 ON DUPLICATE KEY UPDATE，這個的意思是如果主鍵已經(jīng)存在了，那就執(zhí)行更新操作，比如在這里我們傳入的數(shù)據(jù) id 仍然為 20120001，但是年齡有所變化，由 20 變成了 21，但在這條數(shù)據(jù)不會被插入，而是將 id 為 20120001 的數(shù)據(jù)更新。

在這里完整的 SQL 構(gòu)造出來是這樣的：

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

相比上面介紹的插入操作的 SQL，后面多了一部分內(nèi)容，那就是更新的字段，ON DUPLICATE KEY UPDATE 使得主鍵已存在的數(shù)據(jù)進行更新，后面跟的是更新的字段內(nèi)容。所以這里就變成了 6 個 %s。所以在后面的 execute() 方法的第二個參數(shù)元組就需要乘以 2 變成原來的 2 倍。

如此一來，我們就可以實現(xiàn)主鍵不存在便插入數(shù)據(jù)，存在則更新數(shù)據(jù)的功能了。

刪除操作相對簡單，使用 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 等等，所以不再繼續(xù)構(gòu)造復(fù)雜的判斷條件，在這里直接將條件當(dāng)作字符串來傳遞，以實現(xiàn)刪除操作。

說完插入、修改、刪除等操作，還剩下非常重要的一個操作，那就是查詢。

在這里查詢用到 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")

運行結(jié)果：

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)

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

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

但是這里注意到一個問題，顯示的是4條數(shù)據(jù)，fetall() 方法不是獲取所有數(shù)據(jù)嗎？為什么只有3條？這是因為它的內(nèi)部實現(xiàn)是有一個偏移指針來指向查詢結(jié)果的，最開始偏移指針指向第一條數(shù)據(jù)，取一次之后，指針偏移到下一條數(shù)據(jù)，這樣再取的話就會取到下一條數(shù)據(jù)了。所以我們最初調(diào)用了一次 fetchone() 方法，這樣結(jié)果的偏移指針就指向了下一條數(shù)據(jù)，fetchall() 方法返回的是偏移指針指向的數(shù)據(jù)一直到結(jié)束的所有數(shù)據(jù)，所以 fetchall() 方法獲取的結(jié)果就只剩 3 個了，所以在這里要理解偏移指針的概念。

所以我們還可以用 while 循環(huán)加 fetchone() 的方法來獲取所有數(shù)據(jù)，而不是用 fetchall() 全部一起獲取出來，fetchall() 會將結(jié)果以元組形式全部返回，如果數(shù)據(jù)量很大，那么占用的開銷會非常高。所以推薦使用如下的方法來逐條取數(shù)據(jù)：

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")

這樣每循環(huán)一次，指針就會偏移一條數(shù)據(jù)，隨用隨取，簡單高效。

本節(jié)我們介紹了 PyMySQL 操作 MySQL 數(shù)據(jù)庫以及一些SQL語句的構(gòu)造方法，在后文我們會在實戰(zhàn)案例中應(yīng)用這些操作進行數(shù)據(jù)存儲。

上一篇文章：Python3網(wǎng)絡(luò)爬蟲實戰(zhàn)---31、數(shù)據(jù)存儲：文件存儲
下一篇文章：Python3網(wǎng)絡(luò)爬蟲實戰(zhàn)---33、數(shù)據(jù)存儲：非關(guān)系型數(shù)據(jù)庫存儲:MongoDB