Python網路數據採集3-數據存到CSV以及MySql

先熱熱身，下載某個頁面的所有圖片。

import requests
from bs4 import BeautifulSoup

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko)'
                  ' Chrome/52.0.2743.116 Safari/537.36 Edge/15.16193'}

start_url = 'https://www.pythonscraping.com'

r = requests.get(start_url, headers=headers)
soup = BeautifulSoup(r.text, 'lxml')
# 獲取所有img標簽
img_tags = soup.find_all('img')
for tag in img_tags:
    print(tag['src'])

https://www.pythonscraping.com/sites/default/files/lrg_0.jpg
http://pythonscraping.com/img/lrg%20(1).jpg

將網頁表格存儲到CSV文件中

以這個網址為例，有好幾個表格，我們對第一個表格進行爬取。Wiki-各種編輯器的比較

import csv
import requests
from bs4 import BeautifulSoup

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko)'
                  ' Chrome/52.0.2743.116 Safari/537.36 Edge/15.16193'}

url = 'https://en.wikipedia.org/wiki/Comparison_of_text_editors'
r = requests.get(url, headers=headers)
soup = BeautifulSoup(r.text, 'lxml')
# 只要第一個表格
rows = soup.find('table', class_='wikitable').find_all('tr')

# csv寫入時候每寫一行會有一空行被寫入，所以設置newline為空
with open('editors.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    for row in rows:
        csv_row = []
        for cell in row.find_all(['th', 'td']):
            csv_row.append(cell.text)

        writer.writerow(csv_row)

需要註意的有一點，打開文件的時候需要指定newline=''，因為寫入csv文件時，每寫入一行就會有一空行被寫入。

從網路讀取CSV文件

上面介紹了將網頁內容存到CSV文件中。如果是從網上獲取到了CSV文件呢？我們不希望下載後再從本地讀取。但是網路請求的話，返回的是字元串而非文件對象。csv.reader()需要傳入一個文件對象。故需要將獲取到的字元串轉換成文件對象。Python的內置庫，StringIO和BytesIO可以將字元串/位元組當作文件一樣來處理。對於csv模塊，要求reader迭代器返回字元串類型，所以使用StringIO，如果處理二進位數據，則用BytesIO。轉換為文件對象，就能用CSV模塊處理了。

下麵的代碼最為關鍵的就是data_file = StringIO(csv_data.text)將字元串轉換為類似文件的對象。

from io import StringIO
import csv
import requests

csv_data = requests.get('http://pythonscraping.com/files/MontyPythonAlbums.csv')
data_file = StringIO(csv_data.text)
reader = csv.reader(data_file)

for row in reader:
    print(row)

['Name', 'Year']
["Monty Python's Flying Circus", '1970']
['Another Monty Python Record', '1971']
["Monty Python's Previous Record", '1972']
['The Monty Python Matching Tie and Handkerchief', '1973']
['Monty Python Live at Drury Lane', '1974']
['An Album of the Soundtrack of the Trailer of the Film of Monty Python and the Holy Grail', '1975']
['Monty Python Live at City Center', '1977']
['The Monty Python Instant Record Collection', '1977']
["Monty Python's Life of Brian", '1979']
["Monty Python's Cotractual Obligation Album", '1980']
["Monty Python's The Meaning of Life", '1983']
['The Final Rip Off', '1987']
['Monty Python Sings', '1989']
['The Ultimate Monty Python Rip Off', '1994']
['Monty Python Sings Again', '2014']

DictReader可以像操作字典那樣獲取數據，把表的第一行（一般是標頭）作為key。可訪問每一行中那個某個key對應的數據。
每一行數據都是OrderDict，使用Key可訪問。看上面列印信息的第一行，說明由Name和Year兩個Key。也可以使用reader.fieldnames查看。

from io import StringIO
import csv
import requests

csv_data = requests.get('http://pythonscraping.com/files/MontyPythonAlbums.csv')
data_file = StringIO(csv_data.text)
reader = csv.DictReader(data_file)
# 查看Key
print(reader.fieldnames)

for row in reader:
    print(row['Year'], row['Name'], sep=': ')

['Name', 'Year']
1970: Monty Python's Flying Circus
1971: Another Monty Python Record
1972: Monty Python's Previous Record
1973: The Monty Python Matching Tie and Handkerchief
1974: Monty Python Live at Drury Lane
1975: An Album of the Soundtrack of the Trailer of the Film of Monty Python and the Holy Grail
1977: Monty Python Live at City Center
1977: The Monty Python Instant Record Collection
1979: Monty Python's Life of Brian
1980: Monty Python's Cotractual Obligation Album
1983: Monty Python's The Meaning of Life
1987: The Final Rip Off
1989: Monty Python Sings
1994: The Ultimate Monty Python Rip Off
2014: Monty Python Sings Again

寫入資料庫中

資料庫使用MySql

如果服務沒有後啟動，首先啟動服務。net start mysql57這裡57是版本號，根據自己的版本填寫。

然後mysql -u root -p輸入密碼後就可以使用了。

先來簡單複習下SQL語法。

SQL基本語法

下麵是關於資料庫的操作

• create database example;這樣創建一個叫做example的資料庫。
• drop database example;則是刪除這個資料庫。
• show databases;可以查看所有資料庫。
• use example;使用這個資料庫。select database();顯示當前正在使用的資料庫。

下麵是關於表的操作

• show tables;查看當前資料庫下的所有表。
• desc some_table;查看某個表的具體結構。
• drop table some_table;刪除某個表。
• alter table some_table add age int;加一列
• alter table some_table drop age;刪除一列

下麵是表的CURD

• insert into t_user(name, email) values('tom','[email protected]');添加一行數據，可以指定任意列的內容，剩下的要麼自己生成(如id一般自增)，要麼就是預設值。
• UPDATE t_user SET NAME='rose' WHERE id=7;更新數據，表示將id為7的數據name改為rose。
• DELETE FROM t_user WHERE NAME='God';把name是God的記錄刪除。DELETE FROM t_user;刪除整張表中所有記錄.
• select * from stu;查詢stu表裡所有數據，*是通配符匹配所有。
• select sname from stu;查詢stu的sname那列。
• select * from stu where gender='female' and age<50;條件查詢。

使用pymysql連接到MySql

Python連接MySql，這裡使用pymysql

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='admin', db='example',charset='utf8')

cur = conn.cursor()

try:
    # 上面填了參數這句就不是必須的
    # cur.execute('USE example')
    cur.execute('SELECT * FROM pages')
    print(cur.fetchone())
finally:
    cur.close()
    conn.close()

(1, 'Test Title', '方法', datetime.datetime(2017, 7, 15, 15, 45, 46))

連接資料庫時候，加上charset=utf8可以處理中文字元。註意不要寫成utf-8。然後就是連接和游標都要記得close。

接下來從某個wiki頁面開始，隨機獲取一個詞條訪問其頁面，並儲存詞條的標題(title)和正文第一段(content)到MySql。

建表。

create TABLE pages(id int primary key auto_increment,title varchar(200),content varchar(10000),created timestamp default current_timestamp);

上代碼

import re
import random

import pymysql
import requests
from bs4 import BeautifulSoup

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko)'
                  ' Chrome/52.0.2743.116 Safari/537.36 Edge/15.16193'}

conn = pymysql.connect(host='localhost', user='root', password='admin', db='example', charset='utf8')
cur = conn.cursor()


# 存到資料庫
def store(title, content):
    try:
        cur.execute(f"INSERT INTO pages(title, content) VALUES('{title}', '{content}');")
    except Exception as e:
        print(e)
    else:
        conn.commit()


# 獲得頁面內所有詞條的鏈接
def get_links(article_url):
    r = requests.get('https://en.wikipedia.org' + article_url, headers=headers)
    soup = BeautifulSoup(r.text, 'lxml')
    title = soup.h1.string
    content = soup.find('div', id='mw-content-text').find('p').text
    store(title, content)
    links = soup.find('div', id='bodyContent').find_all('a', href=re.compile('^/wiki/[^:/]*$'))
    return links


link_list = get_links('/wiki/Kevin_Bacon')
try:
    while len(link_list) > 0:
        new_article = random.choice(link_list).get('href')
        print(new_article)
        link_list = get_links(new_article)
finally:
    cur.close()
    conn.close()

conn.commit()註意這句，由於連接不是自動提交的，需要我們手動提交，確保數據確實改變。有些詞條的可能會導致在執行查詢語句的時候發生異常，處理一下，不讓其終止爬取。看下結果。

保存鏈接之間的聯繫

比如鏈接A，能夠在這個頁面里找到鏈接B。則可以表示為A -> B。我們就是要保存這種聯繫到資料庫。先建表：

pages表只保存鏈接url。

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

links表保存鏈接的fromId和toId，這兩個id和pages裡面的id是一致的。如1 -> 2就是pages里id為1的url頁面里可以訪問到id為2的url的意思。

CREATE TABLE `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fromId` int(11) DEFAULT NULL,
  `toId` int(11) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)

上面的建表語句看起來有點臃腫，我是先用可視化工具建表後，再用show create table pages這樣的語句查看的。

import re

import pymysql
import requests
from bs4 import BeautifulSoup

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko)'
                  ' Chrome/52.0.2743.116 Safari/537.36 Edge/15.16193'}

conn = pymysql.connect(host='localhost', user='root', password='admin', db='wiki', charset='utf8')
cur = conn.cursor()


def insert_page_if_not_exists(url):
    cur.execute(f"SELECT * FROM pages WHERE url='{url}';")
    # 這條url沒有插入的話
    if cur.rowcount == 0:
        # 那就插入
        cur.execute(f"INSERT INTO pages(url) VALUES('{url}');")
        conn.commit()
        # 剛插入數據的id
        return cur.lastrowid
    # 否則已經存在這條數據，因為url一般是唯一的，所以獲取一個就行，取腳標0是獲得id
    else:
        return cur.fetchone()[0]


def insert_link(from_page, to_page):
    print(from_page, ' -> ', to_page)
    cur.execute(f"SELECT * FROM links WHERE fromId={from_page} AND toId={to_page};")
    # 如果查詢不到數據，則插入，插入需要兩個pages的id，即兩個url
    if cur.rowcount == 0:
        cur.execute(f"INSERT INTO links(fromId, toId) VALUES({from_page}, {to_page});")
        conn.commit()


# 鏈接去重
pages = set()


# 得到所有鏈接
def get_links(page_url, recursion_level):
    global pages
    if recursion_level == 0:
        return
    # 這是剛插入的鏈接
    page_id = insert_page_if_not_exists(page_url)
    r = requests.get('https://en.wikipedia.org' + page_url, headers=headers)
    soup = BeautifulSoup(r.text, 'lxml')
    link_tags = soup.find_all('a', href=re.compile('^/wiki/[^:/]*$'))
    for link_tag in link_tags:
        # page_id是剛插入的url，參數里再次調用了insert_page...方法，獲得了剛插入的url里能去往的url列表
        # 由此形成聯繫，比如剛插入的id為1，id為1的url里能去往的id有2、3、4...，則形成1 -> 2, 1 -> 3這樣的聯繫
        insert_link(page_id, insert_page_if_not_exists(link_tag['href']))

        if link_tag['href'] not in pages:
            new_page = link_tag['href']
            pages.add(new_page)
            # 遞歸查找, 只能遞歸recursion_level次
            get_links(new_page, recursion_level - 1)


if __name__ == '__main__':
    try:
        get_links('/wiki/Kevin_Bacon', 5)
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

1  ->  2
2  ->  1
1  ->  2
1  ->  3
3  ->  4
4  ->  5
4  ->  6
4  ->  7
4  ->  8
4  ->  4
4  ->  4
4  ->  9
4  ->  9
3  ->  10
10  ->  11
10  ->  12
10  ->  13
10  ->  14
10  ->  15
10  ->  16
10  ->  17
10  ->  18
10  ->  19
10  ->  20
10  ->  21
...

看列印的信息，一目瞭然。看前兩行列印，pages表裡id為1的url可以訪問id為2的url，同時pages表裡id為2的url可以訪問id為1的url...依次類推。

首先需要使用insert_page_if_not_exists(page_url)獲得鏈接的id，然後使用insert_link(fromId, toId)形成聯繫。fromId是當前頁面的url，toId則是從當前頁面能夠去往的url的id，這些能去往的url用bs4找到以列表形式返回。當前所處的url即page_id，所以需要在insert_link的第二個參數中，再次調用insert_page_if_not_exists(link)以獲得列表中每個url的id。由此形成了聯繫。比如剛插入的id為1，id為1的url里能去往的id有2、3、4...，則形成1 -> 2, 1 -> 3這樣的聯繫。

看下資料庫。下麵是pages表，每一個id都對應一個url。

然後下麵是links表，fromId和toId就是pages中的id。當然和列印的數據是一樣的咯，不過列印了看看就過去了，存下來的話哪天需要分析這些數據就大有用處了。

by @sunhaiyu

2017.7.15