Python操作MySQL儲存

標籤：返回   公網ip地址   cursor   failed   儲存   python 2   commit   樣本   table   

在Python 2中，串連MySQL的庫大多是使用MySQLdb，但是此庫的官方並不支援Python 3，所以這裡推薦使用的庫是PyMySQL。本節中，我們就來講解使用PyMySQL操作MySQL資料庫的方法。1. 準備工作在開始之前，請確保已經安裝好了MySQL資料庫並保證它能正常運行，而且需要安裝好PyMySQL庫。2. 串連資料庫這裡，首先嘗試串連一下資料庫。假設當前的MySQL運行在本地，使用者名稱為root，密碼為123456，運行連接埠為3306。這裡利用PyMySQL先串連MySQL，然後建立一個新的資料庫，名字叫作spiders，代碼如下：import pymysqldb = 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個欄位，結構如下所示。欄位名含義類型id學號varcharname姓名varcharage年齡int建立該表的範例程式碼如下：import pymysqldb = 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 pymysqlid = ‘20120001‘user = ‘Bob‘age = 20db = 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特性，具體如下表所示。屬性解釋原子性（atomicity）事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做一致性（consistency）事務必須使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的隔離性（isolation）一個事務的執行不能被其他事務幹擾，即一個事務內部的操作及使用的資料對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾持久性（durability）持久性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他動作或故障不應該對其有任何影響插入、更新和刪除操作都是對資料庫變更的操作，而更改操作都必須為一個事務，所以這些操作的標準寫法就是：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 += updatetry:    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: 4One: (‘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‘)這樣每迴圈一次，指標就會位移一條資料，隨用隨取，簡單高效。

　　

Python操作MySQL儲存