在 Python2 中量瓜，連接 MySQL 的庫(kù)大多是使用 MySQLDB跪呈，但是此庫(kù)官方并不支持 Python3妇汗，所以在這里推薦使用的庫(kù)是 PyMySQL孩革。

我來(lái)講解一下 PyMySQL 操作 MySQL 數(shù)據(jù)庫(kù)的方法。

1. 準(zhǔn)備工作

在本節(jié)開(kāi)始之前請(qǐng)確保已經(jīng)安裝好了 MySQL 數(shù)據(jù)庫(kù)并正常運(yùn)行惶翻，而且需要安裝好 PyMySQL 庫(kù)姑蓝，如果沒(méi)有安裝，可以參考第一章的安裝說(shuō)明吕粗。

2. 連接數(shù)據(jù)庫(kù)

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

運(yùn)行結(jié)果：

Database version: ('5.6.22',)

在這里我們通過(guò) PyMySQL 的 connect() 方法聲明了一個(gè) MySQL 連接對(duì)象，需要傳入 MySQL 運(yùn)行的 host 即 IP先口，此處由于 MySQL 在本地運(yùn)行型奥，所以傳入的是 localhost瞳收，如果 MySQL 在遠(yuǎn)程運(yùn)行，則傳入其公網(wǎng) IP 地址厢汹，然后后續(xù)的參數(shù) user 即用戶名缎讼，password 即密碼，port 即端口默認(rèn) 3306坑匠。
連接成功之后，我們需要再調(diào)用 cursor() 方法獲得 MySQL 的操作游標(biāo)卧惜，利用游標(biāo)來(lái)執(zhí)行 SQL 語(yǔ)句厘灼，例如在這里我們執(zhí)行了兩句 SQL，用 execute() 方法執(zhí)行相應(yīng)的 SQL 語(yǔ)句即可咽瓷，第一句 SQL 是獲得 MySQL 當(dāng)前版本设凹，然后調(diào)用fetchone() 方法來(lái)獲得第一條數(shù)據(jù)，也就得到了版本號(hào)茅姜，另外我們還執(zhí)行了創(chuàng)建數(shù)據(jù)庫(kù)的操作闪朱，數(shù)據(jù)庫(kù)名稱叫做 spiders，默認(rèn)編碼為 utf-8钻洒，由于該語(yǔ)句不是查詢語(yǔ)句奋姿，所以直接執(zhí)行后我們就成功創(chuàng)建了一個(gè)數(shù)據(jù)庫(kù) spiders，接著我們?cè)倮眠@個(gè)數(shù)據(jù)庫(kù)進(jìn)行后續(xù)的操作素标。

3. 創(chuàng)建表

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

所以這里MySQL的連接就需要額外指定一個(gè)參數(shù) db计维。
然后接下來(lái)我們新創(chuàng)建一個(gè)數(shù)據(jù)表袜香，執(zhí)行創(chuàng)建表的 SQL 語(yǔ)句即可，創(chuàng)建一個(gè)用戶表 students鲫惶，在這里指定三個(gè)字段蜈首，結(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()

運(yùn)行之后我們便創(chuàng)建了一個(gè)名為 students 的數(shù)據(jù)表，字段即為上文列舉的三個(gè)字段剑按。
當(dāng)然在這里作為演示我們指定了最簡(jiǎn)單的幾個(gè)字段疾就，實(shí)際在爬蟲(chóng)過(guò)程中我們會(huì)根據(jù)爬取結(jié)果設(shè)計(jì)特定的字段。

4. 插入數(shù)據(jù)

我們將數(shù)據(jù)解析出來(lái)后的下一步就是向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)了艺蝴，例如在這里我們爬取了一個(gè)的學(xué)生信息猬腰，學(xué)號(hào)為 20120001，名字為 Bob猜敢，年齡為 20姑荷，那么如何將該條數(shù)據(jù)插入數(shù)據(jù)庫(kù)呢盒延，實(shí)例代碼如下：

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)造了一個(gè) SQL 語(yǔ)句，其 Value 值我們沒(méi)有用字符串拼接的方式來(lái)構(gòu)造鼠冕，如：

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

這樣的寫(xiě)法繁瑣而且不直觀添寺，所以我們選擇直接用格式化符 %s 來(lái)實(shí)現(xiàn)，有幾個(gè) Value 寫(xiě)幾個(gè) %s懈费，我們只需要在 execute() 方法的第一個(gè)參數(shù)傳入該 SQL 語(yǔ)句计露，Value 值用統(tǒng)一的元組傳過(guò)來(lái)就好了。
這樣的寫(xiě)法有既可以避免字符串拼接的麻煩憎乙，又可以避免引號(hào)沖突的問(wèn)題票罐。
之后值得注意的是，需要執(zhí)行 db 對(duì)象的 commit() 方法才可實(shí)現(xiàn)數(shù)據(jù)插入泞边，這個(gè)方法才是真正將語(yǔ)句提交到數(shù)據(jù)庫(kù)執(zhí)行的方法该押，對(duì)于數(shù)據(jù)插入、更新阵谚、刪除操作都需要調(diào)用該方法才能生效蚕礼。

接下來(lái)我們加了一層異常處理，如果執(zhí)行失敗中姜，則調(diào)用rollback() 執(zhí)行數(shù)據(jù)回滾消玄，相當(dāng)于什么都沒(méi)有發(fā)生過(guò)一樣。
在這里就涉及一個(gè)事務(wù)的問(wèn)題丢胚，事務(wù)機(jī)制可以確保數(shù)據(jù)的一致性翩瓜，也就是這件事要么發(fā)生了，要么沒(méi)有發(fā)生携龟，比如插入一條數(shù)據(jù)兔跌，不會(huì)存在插入一半的情況，要么全部插入峡蟋，要么整個(gè)一條都不插入坟桅，這就是事務(wù)的原子性华望，另外事務(wù)還有另外三個(gè)屬性，一致性仅乓、隔離性赖舟、持久性，通常成為 ACID 特性夸楣。
歸納如下：

插入、更新忽匈、刪除操作都是對(duì)數(shù)據(jù)庫(kù)進(jìn)行更改的操作房午，更改操作都必須為一個(gè)事務(wù)，所以對(duì)于這些操作的標(biāo)準(zhǔn)寫(xiě)法就是：

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

這樣我們便可以保證數(shù)據(jù)的一致性丹允，在這里的 commit() 和 rollback() 方法就是為事務(wù)的實(shí)現(xiàn)提供了支持郭厌。
好，在上面我們了解了數(shù)據(jù)插入的操作雕蔽，是通過(guò)構(gòu)造一個(gè) SQL 語(yǔ)句來(lái)實(shí)現(xiàn)的折柠，但是很明顯，這里有一個(gè)及其不方便的地方批狐，比如又加了一個(gè)性別 gender扇售，假如突然增加了一個(gè)字段，那么我們構(gòu)造的 SQL 語(yǔ)句就需要改成：

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

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

(id, name, age, gender)

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

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

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

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涌攻。接下來(lái)我們就需要構(gòu)造一個(gè)動(dòng)態(tài)的 SQL 語(yǔ)句了欧引。
首先我們需要構(gòu)造插入的字段，id恳谎、name 和 age芝此，在這里只需要將data的鍵名拿過(guò)來(lái)，然后用逗號(hào)分隔即可因痛。所以 ', '.join(data.keys()) 的結(jié)果就是 id, name, age婚苹，然后我們需要構(gòu)造多個(gè) %s 當(dāng)作占位符，有幾個(gè)字段構(gòu)造幾個(gè)鸵膏，比如在這里有兩個(gè)字段膊升，就需要構(gòu)造 %s, %s, %s ，所以在這里首先定義了長(zhǎng)度為 1 的數(shù)組 ['%s'] 谭企，然后用乘法將其擴(kuò)充為 ['%s', '%s', '%s']廓译，再調(diào)用 join() 方法，最終變成 %s, %s, %s债查。所以我們?cè)倮米址?format() 方法將表名非区，字段名，占位符構(gòu)造出來(lái)盹廷，最終sql語(yǔ)句就被動(dòng)態(tài)構(gòu)造成了：

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

最后再 execute() 方法的第一個(gè)參數(shù)傳入 sql 變量征绸，第二個(gè)參數(shù)傳入 data 的鍵值構(gòu)造的元組，就可以成功插入數(shù)據(jù)了俄占。
如此以來(lái)管怠，我們便實(shí)現(xiàn)了傳入一個(gè)字典來(lái)插入數(shù)據(jù)的方法，不需要再去修改 SQL 語(yǔ)句和插入操作了缸榄。

5. 更新數(shù)據(jù)

數(shù)據(jù)更新操作實(shí)際上也是執(zhí)行 SQL 語(yǔ)句排惨，最簡(jiǎn)單的方式就是構(gòu)造一個(gè) SQL 語(yǔ)句然后執(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í)行操作。
如果要做簡(jiǎn)單的數(shù)據(jù)更新的話欲低，使用此方法是完全可以的辕宏。
但是在實(shí)際數(shù)據(jù)抓取過(guò)程中，在大部分情況下是需要插入數(shù)據(jù)的砾莱，但是我們關(guān)心的是會(huì)不會(huì)出現(xiàn)重復(fù)數(shù)據(jù)瑞筐，如果出現(xiàn)了重復(fù)數(shù)據(jù)，我們更希望的做法一般是更新數(shù)據(jù)而不是重復(fù)保存一次腊瑟，另外就是像上文所說(shuō)的動(dòng)態(tài)構(gòu)造 SQL 的問(wèn)題聚假，所以在這里我們?cè)谶@里重新實(shí)現(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 語(yǔ)句其實(shí)是插入語(yǔ)句，但是在后面加了 ON DUPLICATE KEY UPDATE瘪贱，這個(gè)的意思是如果主鍵已經(jīng)存在了纱控，那就執(zhí)行更新操作，比如在這里我們傳入的數(shù)據(jù) id 仍然為 20120001菜秦，但是年齡有所變化甜害，由 20 變成了 21，但在這條數(shù)據(jù)不會(huì)被插入球昨，而是將 id 為 20120001 的數(shù)據(jù)更新尔店。

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

  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ù)進(jìn)行更新，后面跟的是更新的字段內(nèi)容河哑。所以這里就變成了 6 個(gè) %s。所以在后面的 execute() 方法的第二個(gè)參數(shù)元組就需要乘以 2 變成原來(lái)的 2 倍龟虎。
如此一來(lái)璃谨，我們就可以實(shí)現(xiàn)主鍵不存在便插入數(shù)據(jù)，存在則更新數(shù)據(jù)的功能了鲤妥。

6. 刪除數(shù)據(jù)

刪除操作相對(duì)簡(jiǎn)單佳吞，使用 DELETE 語(yǔ)句即可，需要指定要?jiǎng)h除的目標(biāo)表名和刪除條件棉安，而且仍然需要使用 db 的 commit() 方法才能生效底扳，實(shí)例如下：

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

7. 查詢數(shù)據(jù)

說(shuō)完插入、修改贡耽、刪除等操作衷模，還剩下非常重要的一個(gè)操作，那就是查詢蒲赂。

在這里查詢用到 SELECT 語(yǔ)句阱冶，我們先用一個(gè)實(shí)例來(lái)感受一下：

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

運(yùn)行結(jié)果：

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)

在這里我們構(gòu)造了一條 SQL 語(yǔ)句，將年齡 20 歲及以上的學(xué)生查詢出來(lái)滥嘴，然后將其傳給 execute() 方法即可木蹬，注意在這里不再需要 db 的 commit() 方法。然后我們可以調(diào)用 cursor 的 rowcount 屬性獲取查詢結(jié)果的條數(shù)若皱，當(dāng)前示例中獲取的結(jié)果條數(shù)是 4 條镊叁。
然后我們調(diào)用了 fetchone() 方法尘颓，這個(gè)方法可以獲取結(jié)果的第一條數(shù)據(jù)，返回結(jié)果是元組形式晦譬，元組的元素順序跟字段一一對(duì)應(yīng)疤苹，也就是第一個(gè)元素就是第一個(gè)字段 id，第二個(gè)元素就是第二個(gè)字段 name蛔添，以此類推痰催。隨后我們又調(diào)用了fetchall() 方法，它可以得到結(jié)果的所有數(shù)據(jù)迎瞧，然后將其結(jié)果和類型打印出來(lái)夸溶，它是二重元組，每個(gè)元素都是一條記錄凶硅。我們將其遍歷輸出缝裁，將其逐個(gè)輸出出來(lái)。
但是這里注意到一個(gè)問(wèn)題足绅，顯示的是4條數(shù)據(jù)捷绑，fetall() 方法不是獲取所有數(shù)據(jù)嗎？為什么只有3條氢妈？這是因?yàn)樗膬?nèi)部實(shí)現(xiàn)是有一個(gè)偏移指針來(lái)指向查詢結(jié)果的粹污，最開(kāi)始偏移指針指向第一條數(shù)據(jù)，取一次之后首量，指針偏移到下一條數(shù)據(jù)壮吩，這樣再取的話就會(huì)取到下一條數(shù)據(jù)了。所以我們最初調(diào)用了一次 fetchone() 方法加缘，這樣結(jié)果的偏移指針就指向了下一條數(shù)據(jù)鸭叙，fetchall() 方法返回的是偏移指針指向的數(shù)據(jù)一直到結(jié)束的所有數(shù)據(jù)，所以 fetchall() 方法獲取的結(jié)果就只剩 3 個(gè)了拣宏，所以在這里要理解偏移指針的概念沈贝。
所以我們還可以用 while 循環(huán)加 fetchone() 的方法來(lái)獲取所有數(shù)據(jù)，而不是用 fetchall() 全部一起獲取出來(lái)勋乾，fetchall() 會(huì)將結(jié)果以元組形式全部返回宋下，如果數(shù)據(jù)量很大，那么占用的開(kāi)銷會(huì)非常高辑莫。所以推薦使用如下的方法來(lái)逐條取數(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)一次杨凑，指針就會(huì)偏移一條數(shù)據(jù)，隨用隨取摆昧，簡(jiǎn)單高效撩满。

8. 結(jié)語(yǔ)

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