?[[227352]]?

在Python 2中，連接MySQL的庫(kù)大多是使用MySQLdb，但是此庫(kù)的官方并不支持Python 3，所以這里推薦使用的庫(kù)是PyMySQL。

本節(jié)中，我們就來講解使用PyMySQL操作MySQL數(shù)據(jù)庫(kù)的方法。

1. 準(zhǔn)備工作

在開始之前，請(qǐng)確保已經(jīng)安裝好了MySQL數(shù)據(jù)庫(kù)并保證它能正常運(yùn)行，而且需要安裝好PyMySQL庫(kù)。

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

這里通過PyMySQL的connect()方法聲明一個(gè)MySQL連接對(duì)象db，此時(shí)需要傳入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)來執(zhí)行SQL語(yǔ)句。這里我們執(zhí)行了兩句SQL，直接用execute()方法執(zhí)行即可。***句SQL用于獲得MySQL的當(dāng)前版本，然后調(diào)用fetchone()方法獲得***條數(shù)據(jù)，也就得到了版本號(hào)。第二句SQL執(zhí)行創(chuàng)建數(shù)據(jù)庫(kù)的操作，數(shù)據(jù)庫(kù)名叫作spiders，默認(rèn)編碼為UTF-8。由于該語(yǔ)句不是查詢語(yǔ)句，所以直接執(zhí)行后就成功創(chuàng)建了數(shù)據(jù)庫(kù)spiders。接著，再利用這個(gè)數(shù)據(jù)庫(kù)進(jìn)行后續(xù)的操作。

3. 創(chuàng)建表

一般來說，創(chuàng)建數(shù)據(jù)庫(kù)的操作只需要執(zhí)行一次就好了。當(dāng)然，我們也可以手動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)。以后，我們的操作都在spiders數(shù)據(jù)庫(kù)上執(zhí)行。

創(chuàng)建數(shù)據(jù)庫(kù)后，在連接時(shí)需要額外指定一個(gè)參數(shù)db。

接下來，新創(chuàng)建一個(gè)數(shù)據(jù)表students，此時(shí)執(zhí)行創(chuàng)建表的SQL語(yǔ)句即可。這里指定3個(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ù)表。

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

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

下一步就是向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)了。例如，這里爬取了一個(gè)學(xué)生信息，學(xué)號(hào)為20120001，名字為Bob，年齡為20，那么如何將該條數(shù)據(jù)插入數(shù)據(jù)庫(kù)呢？示例代碼如下：

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值沒有用字符串拼接的方式來構(gòu)造，如：

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

這樣的寫法煩瑣而且不直觀，所以我們選擇直接用格式化符%s來實(shí)現(xiàn)。有幾個(gè)Value寫幾個(gè)%s，我們只需要在execute()方法的***個(gè)參數(shù)傳入該SQL語(yǔ)句，Value值用統(tǒng)一的元組傳過來就好了。這樣的寫法既可以避免字符串拼接的麻煩，又可以避免引號(hào)沖突的問題。

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

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

這里涉及事務(wù)的問題。事務(wù)機(jī)制可以確保數(shù)據(jù)的一致性，也就是這件事要么發(fā)生了，要么沒有發(fā)生。比如插入一條數(shù)據(jù)，不會(huì)存在插入一半的情況，要么全部插入，要么都不插入，這就是事務(wù)的原子性。另外，事務(wù)還有3個(gè)屬性——一致性、隔離性和持久性。這4個(gè)屬性通常稱為ACID特性，具體如下表所示。

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

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

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

上面數(shù)據(jù)插入的操作是通過構(gòu)造SQL語(yǔ)句實(shí)現(xiàn)的，但是很明顯，這有一個(gè)極其不方便的地方，比如突然增加了性別字段gender，此時(shí)SQL語(yǔ)句就需要改成：

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

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

(id, name, age, gender)

這顯然不是我們想要的。在很多情況下，我們要達(dá)到的效果是插入方法無需改動(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)通用的插入方法。所以，這里我們需要改寫一下插入方法：

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)造一個(gè)動(dòng)態(tài)的SQL語(yǔ)句了。

首先，需要構(gòu)造插入的字段id、name和age。這里只需要將data的鍵名拿過來，然后用逗號(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è)倮米址膄ormat()方法將表名、字段名和占位符構(gòu)造出來。最終的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ù)了。

如此以來，我們便實(shí)現(xiàn)了傳入一個(gè)字典來插入數(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í)行execute()方法，傳入元組形式的參數(shù)，同樣執(zhí)行commit()方法執(zhí)行操作。如果要做簡(jiǎn)單的數(shù)據(jù)更新的話，完全可以使用此方法。

但是在實(shí)際的數(shù)據(jù)抓取過程中，大部分情況下需要插入數(shù)據(jù)，但是我們關(guān)心的是會(huì)不會(huì)出現(xiàn)重復(fù)數(shù)據(jù)，如果出現(xiàn)了，我們希望更新數(shù)據(jù)而不是重復(fù)保存一次。另外，就像前面所說的動(dòng)態(tài)構(gòu)造SQL的問題，所以這里可以再實(shí)現(xiàn)一種去重的方法，如果數(shù)據(jù)存在，則更新數(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ǔ)句，但是我們?cè)诤竺婕恿薕N DUPLICATE KEY UPDATE。這行代碼的意思是如果主鍵已經(jīng)存在，就執(zhí)行更新操作。比如，我們傳入的數(shù)據(jù)id仍然為20120001，但是年齡有所變化，由20變成了21，此時(shí)這條數(shù)據(jù)不會(huì)被插入，而是直接更新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

這里就變成了6個(gè)%s。所以在后面的execute()方法的第二個(gè)參數(shù)元組就需要乘以2變成原來的2倍。

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

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

刪除操作相對(duì)簡(jiǎn)單，直接使用DELETE語(yǔ)句即可，只是需要指定要?jiǎng)h除的目標(biāo)表名和刪除條件，而且仍然需要使用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()

因?yàn)閯h除條件有多種多樣，運(yùn)算符有大于、小于、等于、LIKE等，條件連接符有AND、OR等，所以不再繼續(xù)構(gòu)造復(fù)雜的判斷條件。這里直接將條件當(dāng)作字符串來傳遞，以實(shí)現(xiàn)刪除操作。

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

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

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é)生查詢出來，然后將其傳給execute()方法。注意，這里不再需要db的commit()方法。接著，調(diào)用cursor的rowcount屬性獲取查詢結(jié)果的條數(shù)，當(dāng)前示例中是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é)果和類型打印出來，它是二重元組，每個(gè)元素都是一條記錄，我們將其遍歷輸出出來。

但是這里需要注意一個(gè)問題，這里顯示的是3條數(shù)據(jù)而不是4條，fetchall()方法不是獲取所有數(shù)據(jù)嗎？這是因?yàn)樗膬?nèi)部實(shí)現(xiàn)有一個(gè)偏移指針用來指向查詢結(jié)果，最開始偏移指針指向***條數(shù)據(jù)，取一次之后，指針偏移到下一條數(shù)據(jù)，這樣再取的話，就會(huì)取到下一條數(shù)據(jù)了。我們最初調(diào)用了一次fetchone()方法，這樣結(jié)果的偏移指針就指向下一條數(shù)據(jù)，fetchall()方法返回的是偏移指針指向的數(shù)據(jù)一直到結(jié)束的所有數(shù)據(jù)，所以該方法獲取的結(jié)果就只剩3個(gè)了。

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

本節(jié)中，我們介紹了如何使用PyMySQL操作MySQL數(shù)據(jù)庫(kù)以及一些SQL語(yǔ)句的構(gòu)造方法。