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

weixin_34370347發表於2017-03-14

這是全棧資料工程師養成攻略系列教程的第十二期: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.1localhost表示本機,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

相關文章