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

[[265766]]

本節中，我們就來講解使用PyMySQL操作MySQL數據庫的方法。

1. 準備工作

在開始之前，請確保已經安裝好了MySQL數據庫并保證它能正常運行，而且需要安裝好PyMySQL庫。

2. 連接數據庫

這里，首先嘗試連接一下數據庫。假設當前的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連接對象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。接著，再利用這個數據庫進行后續的操作。

3. 創建表

一般來說，創建數據庫的操作只需要執行一次就好了。當然，我們也可以手動創建數據庫。以后，我們的操作都在spiders數據庫上執行。

創建數據庫后，在連接時需要額外指定一個參數db。

接下來，新創建一個數據表students，此時執行創建表的SQL語句即可。這里指定3個字段，結構如下所示。

創建該表的示例代碼如下：

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的數據表。

當然，為了演示，這里只指定了最簡單的幾個字段。實際上，在爬蟲過程中，我們會根據爬取結果設計特定的字段。

4. 插入數據

下一步就是向數據庫中插入數據了。例如，這里爬取了一個學生信息，學號為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()執行數據回滾，相當于什么都沒有發生過。

這里涉及事務的問題。事務機制可以確保數據的一致性，也就是這件事要么發生了，要么沒有發生。比如插入一條數據，不會存在插入一半的情況，要么全部插入，要么都不插入，這就是事務的原子性。另外，事務還有3個屬性——一致性、隔離性和持久性。這4個屬性通常稱為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語句和插入操作了。

5. 更新數據

數據更新操作實際上也是執行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倍。

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

6. 刪除數據

刪除操作相對簡單，直接使用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等，所以不再繼續構造復雜的判斷條件。這里直接將條件當作字符串來傳遞，以實現刪除操作。

7. 查詢數據

說完插入、修改和刪除等操作，還剩下非常重要的一個操作，那就是查詢。查詢會用到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語句的構造方法。