全棧 - 12 資料庫 用Python操作MySQL

這是全棧資料工程師養成攻略系列教程的第十二期：12 資料庫 用Python操作MySQL。

我們已經接觸瞭如何使用Web工具phpMyAdmin和本地軟體Navicat操作MySQL，現在再來了解下如何使用Python操作MySQL。

MySQLdb

MySQLdb是Python中操作MySQL的功能包，在命令列中使用pip安裝即可。

pip install mysql-python

如果可以在Python中import成功，則表示安裝沒有問題，否則還得繼續折騰。不過安裝和配置等事情畢竟是一勞永逸的，糾結一次，受益終身，絕知此事須躬行。

import MySQLdb

建立連線

在Sublime中新建一個程式碼，首先需要import相關的包。

import MySQLdb
import MySQLdb.cursors

開啟MAMP或者WAMP並啟動Web服務，使得MySQL執行起來，可以通過訪問phpMyAdmin管理頁面以確認MySQL成功執行。然後，使用以下程式碼即可建立MySQL資料庫連線，其中host為資料庫的主機地址，可以使用127.0.0.1或localhost表示本機，user和passwd分別為資料庫的使用者名稱和密碼，db表示接下來要操作的資料庫，port和charset表示連線的埠和字符集。以上引數分別替換成實際值即可，這裡我們使用之前在本機資料庫中新建的douban資料庫，得到的cursor變數可用於執行後續資料庫操作。如果需要連線雲端伺服器的資料庫，使用相應的配置引數即可。

db = MySQLdb.connect(host='127.0.0.1', user='root', passwd='root', db='douban', port=8889, charset='utf8', cursorclass = MySQLdb.cursors.DictCursor)
db.autocommit(True)
cursor = db.cursor()

執行操作

和資料庫相關的操作無非CURD四種，即Create、Update、Read、Delete。要用到的資料可以在我的Github上找到，裡面的data資料夾中除了上次的西遊記小說，還包括這次要用到的douban_movie_clean.txt，其中包含一行表頭，之後每一行都是一條電影資料，包括id、title、url、cover、rate等15個欄位，欄位之間以^分割，主要是避免中文內容裡包括逗號導致衝突。

首先來看下如何向資料表中插入資料，以下程式碼讀取douban_movie_clean.txt中的資料並逐條插入資料表中。需要注意的是，資料表的結構應當和需要插入的欄位保持一致，即movie表應當中包含主鍵id、標題title、連結url、評分rate、時長length、簡介description六個欄位。

# 讀取資料
fr = open('douban_movie_clean.txt', 'r')

count = 0
for line in fr:
    count += 1
    # count表示當前處理到第幾行了
    print count
    # 跳過表頭
    if count == 1:
        continue

    # strip()函式可以去掉字串兩端的空白符
    # split()函式按照給定的分割符將字串分割為列表
    line = line.strip().split('^')
    # 插入資料，注意對齊欄位
    # execute()函式第一個引數為要執行的SQL命令
    # 這裡用字串格式化的方法生成一個模板
    # %s表示一個佔位符
    # 第二個引數為需要格式化的引數，傳入到模板中
    cursor.execute("insert into movie(title, url, rate, length, description) values(%s, %s, %s, %s, %s)", [line[1], line[2], line[4], line[-3], line[-1]])

# 關閉讀檔案
fr.close()

執行以上程式碼之後，在phpMyAdmin中選擇douban資料庫中的movie表，在Browse標籤頁下即可看到成功插入的資料。點選SQL標籤，輸入Select count(*) from movie並點選Go執行SQL命令，可以統計資料表中一共有多少條資料記錄。

接下來再瞭解下如何更新資料庫中的資料。SQL命令可以根據給定的條件，更新滿足條件的記錄，例如改變記錄中的某些欄位。既然每條資料都有唯一的主鍵id，不妨將id作為條件進行更新。當然也可以做一些更有意義的更新，例如新增一個“電影時長分類”欄位，然後對於每條記錄，如果時長大於100，則“電影時長分類”更新為“長電影”，否則更新為“短電影”。

# 更新需要提供條件、需要更新的欄位、更新的新值
# 以下對於id為1的記錄，將其title和length兩個欄位進行更新
cursor.execute("update movie set title=%s, length=%s where id=%s", ['全棧資料工程師養成攻略', 999, 1])

執行以上程式碼之後，在phpMyAdmin中Browse標籤頁下即可看到，id為1的記錄相應欄位確實已經得到了更新。

然後就是最常用的讀取操作，一方面是取出已有的資料進行加工和計算得到新的結果並再次儲存，另一方面是在Web專案中從後端取出資料傳遞到前端展示。讀取資料時可以僅讀取一條，也可以選擇多條；可以讀取全部欄位，也可以選擇部分欄位；還可以按某個欄位進行排序，使得讀取多條資料時的結果有序排列。

# 讀取全部資料的全部欄位
cursor.execute("select * from movie")
movies = cursor.fetchall()
# 返回元組，每一項都是一個字典
# 對應一條記錄的全部欄位和欄位值
print type(movies), len(movies), movies[0]

# 讀取一條資料的部分欄位
# 返回一個欄位，對應所選擇的部分欄位和欄位值
cursor.execute("select id, title, url from movie")
movie = cursor.fetchone()
print type(movie), len(movie), movie

# 讀取一條資料的部分欄位
# 按id降序排序，預設為升序
cursor.execute("select id, title, url from movie order by id desc")
movie = cursor.fetchone()
print type(movie), len(movie), movie

最後是從資料庫中刪除資料，刪除資料是不可恢復的，所以務必需要謹慎操作，並一定要提供刪除條件，這樣僅滿足刪除條件的記錄才會被刪除。

# 刪除資料務必要提供刪除條件
# 此處刪除id為1的記錄
cursor.execute("delete from movie where id=%s", [1])

關閉連線

使用Python操作完資料庫之後，別忘記了關閉資料庫連線。

# 關閉資料庫連線
cursor.close()
db.close()

擴充套件內容

我們會發現在使用Python運算元據庫時，主要是使用execute()函式並傳入SQL命令。以上介紹的都是最基礎的CURD操作，其實SQL命令可以融合非常多的功能並寫得更為複雜。

以下連結提供了一份更加完整的SQL教程，http://www.runoob.com/sql/sql-tutorial.html，系統地講解了SQL中的其他常用高階語法。強烈推薦學習一遍，這樣才能在用Python運算元據庫時，得心應手地寫出滿足需求的SQL命令來。

視訊連結：用Python操作MySQL