爬蟲系列:使用 MySQL 儲存資料

爬蟲程式大魔王發表於2021-12-09

上一篇文章我們講解了爬蟲如何儲存 CSV 檔案,這篇文章,我們講解如何將採集到的資料儲存到 MySQL 資料庫中。

MySQL 是目前最受歡迎的開源關係型資料庫管理系統。一個開源專案具有如此之競爭力實在是令人意外,它的流行程度正在不斷地接近兩外兩個閉源的商業資料庫系統:微軟的 SQL Server 和甲骨文的 Oracle 資料庫(MySQL 在2010年被甲骨文收購)。

它的流行程度名符其實。對於大多數應用來說,MySQL 都是不二選擇。他是一種非常靈活、穩定、功能齊全的 DBMS,許多頂級的網站都在使用它:Youtube、Twitter 和 Facebook 等。

因為它受眾廣泛,免費,開箱即用,所以它是網路資料採集專案中常用的資料庫,這篇文章我們介紹如何通過 MySQL 儲存採集到的資料。

安裝 MySQL

如果你第一次接觸 MySQL,可能會覺得有點麻煩。其實,安裝方法和安裝其他軟體一樣簡單。歸根結底,MySQL 就是由一系列資料檔案構成的,儲存在你遠端伺服器或者本地電腦上,裡面包含了資料庫儲存的所有資訊。

Windows 安裝 MySQL、Ubuntu 安裝 MySQL、MAC 安裝 MySQL 具體步驟在此:全平臺安裝 MySQL

在此不做過多說明,按照視訊操作就可以了。

基本命令

MySQL 伺服器啟動之後,有很多種方法可以與資料庫伺服器互動。因為有很多工具是圖形介面,所以你可以不用 MySQL 的命令列(或是很少用命令列)也能管理資料庫。像 phpMyAdmin 和 MySQL Workbench 這類工具可以很容易地實現資料庫檢視、排序和新建等工作。但是,掌握命令列運算元據庫還是很重要的。

除了使用者自定義變數名,MySQL 是不區分大小寫的。例如,SELECT 和 select 是一樣的,不過習慣上寫 MySQL 語句的時候所有的 MySQL 關鍵詞都用大寫。大多數開發者還喜歡用小寫字母表示資料庫和資料表的名稱。

首先登入 MySQL 資料庫的時候,裡面是沒有資料庫存放資料的。我們需要建立一個資料庫:

CREATE DATABASE scraping_article DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

因為每個 MySQL 例項可以有多個資料庫,所以使用某個資料庫之前需要指定資料庫的名稱:

USE scraping_article

從現在開始(直到關閉 MySQL 連結或切換到另一個資料庫之前),所有的命令都執行在這個新的“scraping_article”資料庫裡面。

所有操作都看起來非常簡單。那麼在資料庫裡面新建表的操作方法也應該類似吧?我們在庫裡面新建一張表來儲存採集的網頁文章資料:

CREATE TABLE articles;

結果顯示錯誤:

ERROR 1113 (42000): A table must have at least 1 column

和資料庫不同,MySQL 資料表必須有一列,否則不能建立。為了在 MySQL 裡定義欄位(資料列),我們還必須在 CREATE TABLE 語句後面,把欄位定義放進一個帶括號的、內部由逗號分隔的列表中:

create table articles
(
    id                   int auto_increment
        primary key,
    title                varchar(64)                        null,
    body                 text                               null,
    summary              varchar(256)                       null,
    body_html            text                               null,
    create_time          datetime default CURRENT_TIMESTAMP null,
    time_updated         datetime                           null,
    link_text            varchar(128)                       null
);

每個欄位定義由三部分組成:

  • 名稱(id、title、body 等)

  • 資料型別(INT、VARCHAR、TEXT)

  • 其他可選屬性(NOT NULL AUTO_INCREMENT)

在欄位定義列表的最後,還要定義一個“主鍵”(key)。MySQL 用這個主鍵來組織表的內容,便於後面快速查詢。在以後的文章中,我將介紹如果通過這些主鍵以提高資料庫的查詢速度,但是現在,我們使用表的 id 列作為主鍵就可以。

語句執行之後,我們可以使用 DESCRIBE 檢視資料表的結構:

+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| title        | varchar(64)  | YES  |     | NULL              |                |
| body         | text         | YES  |     | NULL              |                |
| summary      | varchar(256) | YES  |     | NULL              |                |
| body_html    | text         | YES  |     | NULL              |                |
| create_time  | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| time_updated | datetime     | YES  |     | NULL              |                |
| link_text    | varchar(128) | YES  |     | NULL              |                |
+--------------+--------------+------+-----+-------------------+----------------+
8 rows in set (0.03 sec)

現在這張表是一張空表,我們插入一下資料看看,如下所示:

INSERT INTO articles(title,body,summary,body_html,link_text) VALUES ("Test page title","Test page body.","Test page summary.","<p>Test page body.</p>","test-page");

這裡需要我們注意,雖然 articles 表有8個欄位(id,title,body,summary,body_html,create_time,time_update,link_text),但實際上我們這裡只插入5個欄位(title,body,summary,body_html,link_text)的資料即可。因為 id 欄位是自動遞增的(每次插入資料時 MySQL 預設增加1),通常不用處理。另外 create_time 欄位的型別是 current_timestamp,預設插入的是時間戳。

當然我們也可以自定義欄位內容插入資料:

INSERT INTO articles(id,title,body,summary,body_html,create_time,link_text) VALUES (4,"Test page title","Test page body.","Test page summary.","<p>Test page body.</p>","2021-11-20 15:51:45","test-page");

只要你定義的整數在資料表的 id 欄位裡沒有,他就可以插入到資料表。但是,這麼做非常不好;除非萬不得已(比如程式中漏了一行資料),否則讓 MySQL 自己處理 id 和 timestamp 欄位。

現在表裡面有一些資料了,我們可以通過很多方法查詢這些資料。下面是幾個 SELECT 語句的示例:

SELECT * FROM articles WHERE id=1;

這條語句告訴 MySQL,“從 articles 表中把所有 id 等於 2 的資料全部挑選出來”。這個星號(*)是萬用字元,表示所有欄位,這行語句會把滿足條件(where id=1)的所有欄位內容都顯示出來。如果 id 這裡沒有任何一行等於1,就會返回一個空集。例如,下面這個不區分大小寫的查詢,會返回 title 欄位裡包含 “test”的所有行(%符號表示 MySQL 字串萬用字元)的所有欄位:

SELECT * FROM articles WHERE title LIKE "%test%"; 

但是如果你有很多欄位,而你只想返回部分欄位怎麼辦?你可以不要用星號,而是使用下面的方式:

SELECT title, body FROM articles WHERE body LIKE "%test%";

這樣就只會返回 body 內容包含 “test” 所有行的 title 和 body 兩個欄位了。

DELETE 語句語法與 SELECT 語句類似:

DELETE FROM articles WHERE id=1;

由於資料庫的資料刪除不能恢復,所以在執行 DELETE 語句之前,建議使用 SELECT 確認一下需要刪除的資料(上面的刪除語句可以使用 SELECT * FROM articles WHERE id=1; 檢視),然後把 SELECT * 換成 DELETE 就可以了,這會是一個好習慣。很多程式設計師都有過一些 DELETE 誤操作的傷心往事,還有一些恐怖的故事就是有人慌亂中忘了在語句中放 WHERE,結果把所有的客戶資料都刪除了。別讓這事情發生在你身上!

還有一個需要介紹的是 UPDATE 語句:

UPDATE articles SET title="A new title", body="Some new body." WHERE id=4;

以上只是使用了最基本的 MySQL 語句,做一些簡單的資料查詢、建立和更新等工作。

與 Python 整合

Python 沒有內建的 MySQL 支援工具。不過,有很多開源的可以用來與 MySQL 做互動,Python 2.x 和 Python 3.x 版本都支援。最有名的一個就是 PyMySQL

我們可以使用 pip 安裝,執行如下命令:

python3 -m pip install PyMySQL

安裝完成我們就可以使用 PyMySQL 包了。如果你的 MySQL 伺服器處於執行狀態,應該就可以成功地執行下面命令:

import pymysql
import os
from dotenv import load_dotenv


class DataSaveToMySQL(object):
    def __init__(self):
        # loading env config file
        dotenv_path = os.path.join(os.getcwd(), '.env')
        if os.path.exists(dotenv_path):
            load_dotenv(dotenv_path)

    conn = pymysql.connect(host=os.environ.get('MYSQL_HOST'), port=os.environ.get('MYSQL_PORT'),
                           user=os.environ.get('MYSQL_USER'), password=os.environ.get('MYSQL_PASSWORD'),
                           db=os.environ.get('MYSQL_DATABASES'))
    cur = conn.cursor()
    cur.execute("SELECT * FROM articles WHERE id=4;")
    print(cur.fetchone())
    cur.close()
    conn.close()

這段程式有兩個物件:連線物件(conn)和遊標物件(cur)。

連線/遊標模式是資料庫程式設計中常用的模式,在剛剛接觸資料庫的時候,有的時候很難區分這兩種模式的不同。連線模式除了連線資料庫之外,還要傳送資料庫資訊,處理回滾操作(當一個查詢或一組查詢被中斷時,資料庫就需要回到初始狀態,一般使用事務實現狀態回滾),建立新的遊標等等。

而一個連線可以有很多個遊標,一個遊標追蹤一種狀態(state)資訊,比如追蹤資料庫的使用狀態。如果有多個資料庫,且需要向所有資料庫寫內容,就需要多個遊標來處理。遊標還可以包含最後一次查詢執行結果。通過呼叫遊標函式,比如cur.fetchone(),可以獲取查詢結果。

用完連線和遊標之後千萬記得關閉它們。如果不關閉就會導致連線洩露(connection leak),造成一種關閉連線現象,即連線已經不再使用,但資料庫卻不能關閉,因為資料庫不確定你還要不要繼續使用它。這種現象一直會耗費資料庫資源,所以用完資料庫之後記得關閉連線!

剛開始的時候,你想做的事情就是把採集的資料儲存到資料庫。我們繼續採集部落格文章的例子來演示如何實現資料儲存。

import pymysql
import os
from dotenv import load_dotenv

from config import logger_config
from utils import connection_util


class DataSaveToMySQL(object):
    def __init__(self):
        # loading env config file
        dotenv_path = os.path.join(os.getcwd(), '.env')
        if os.path.exists(dotenv_path):
            load_dotenv(dotenv_path)
        # MySQL config
        self._host = os.environ.get('MYSQL_HOST')
        self._port = int(os.environ.get('MYSQL_PORT'))
        self._user = os.environ.get('MYSQL_USER')
        self._password = os.environ.get('MYSQL_PASSWORD')
        self._db = os.environ.get('MYSQL_DATABASES')

        self._target_url = 'https://www.scrapingbee.com/blog/'
        self._baseUrl = 'https://www.scrapingbee.com'
        self._init_connection = connection_util.ProcessConnection()
        logging_name = 'store_mysql'
        init_logging = logger_config.LoggingConfig()
        self._logging = init_logging.init_logging(logging_name)

    def scrape_data(self):
        get_content = self._init_connection.init_connection(self._target_url)
        if get_content:
            parent = get_content.findAll("section", {"class": "section-sm"})[0]
            get_row = parent.findAll("div", {"class": "col-lg-12 mb-5 mb-lg-0"})[0]
            get_child_item = get_row.findAll("div", {"class": "col-md-4 mb-4"})
            for item in get_child_item:
                # 獲取標題文字
                get_title = item.find("a", {"class": "h5 d-block mb-3 post-title"}).get_text()
                # 獲取釋出時間
                get_release_date = item.find("div", {"class": "mb-3 mt-2"}).findAll("span")[1].get_text()
                # 獲取文章描述
                get_description = item.find("p", {"class": "card-text post-description"}).get_text()
                self.article_save_mysql(title=get_title, description=get_description, release_date=get_release_date)
        else:
            self._logging.warning('未獲取到文章任何內容,請檢查!')


    def article_save_mysql(self, title, description, release_date):
        connection = pymysql.connect(host=self._host, port=self._port, user=self._user, password=self._password,
                                     db=self._db, charset='utf-8')
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO articles (title,summary,create_time) VALUES (%s,%s,%s);"
            cursor.execute(sql, (title, description, release_date))

        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()

這裡有幾點需要注意:首先,charset='utf-8' 要增加到連線字串裡。這是讓 conn 把所有傳送資料庫的資訊都當成 utf-8 編碼格式(當然,前提是資料庫預設編碼設定成 UTF-8)。

然後需要注意的是 article_save_mysql 函式。它有3個引數:title、description和release_date,並把這兩個引數加入到一個 INSERT 語句中並用遊標執行,然後使用遊標進行確認。這是一個讓遊標與連線分離的好例子;當遊標裡儲存了一些資料庫與資料庫上下文(context)的資訊時,需要通過連線確認將資訊傳進資料庫,再將資訊插入資料庫。

上面程式碼沒有使用 try...finally 語句來關閉資料庫,而是使用的 with() 來關閉資料庫連線,上一期中我們也是使用的 with() 來關閉 CSV 檔案。

雖然 PyMySQL 規模並不大,但是裡面有一些非常實用的函式,本篇文章中並沒有演示,具體可以參考 Python 的 DBAPI 標準文件

以上是關於將採集的內容儲存到 MySQL 的內容,本例項的所有程式碼託管於 github。

github: https://github.com/sycct/Scrape_1_1.git

如果有任何問題,歡迎在 github issue。

相關文章