简易爬取网页数据,存入本地mysql数据库

一, 创建mysql本地数据库和表

1,登录

➜  ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.7.24 MySQL Community Server (GPL)

2, 创建数据库和表

mysql> create database py_db default character set utf8;

mysql> use py_db;

mysql> CREATE TABLE `poetry_t` (
-> `id` varchar(50) NOT NULL,
-> `title` varchar(50) NOT NULL,
-> `content` text NOT NULL,
-> `author` varchar(20) NOT NULL,
-> `des` varchar(100) DEFAULT NULL,
->  PRIMARY KEY (`id`)
-> );

二, python代码

mydb.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import mysql.connector

db = 'py_db'
table = 'poetry_t'
user = 'root'
psd = '1001'

def insertInto(params):
    conn = mysql.connector.connect(user=user, password=psd, database=db)
    cursor = conn.cursor()
    sql = 'INSERT INTO poetry_t (id, title, content, author) VALUES(%s,%s,%s,%s)'
    cursor.executemany(sql,params)
    conn.commit()
    cursor.close()
    conn.close()

分析布局和需要的内容 来源链接

mydata.py

需要安装的库: requests, bs4

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import requests
from bs4 import BeautifulSoup as bs
from mydb import insertInto
from concurrent.futures import ThreadPoolExecutor
import sched,time

base_url = 'https://so.gushiwen.org/type.aspx?p={page_num}&x=%E8%AF%97'  # 请求地址

def get_page(url):
    try:
        response = requests.get(url)
        if response.status_code == 200:
           return response.text
        else:
           return None
    except Exception:
        return None


def caculate(response):
    soup = bs(response, 'html.parser')

    contents = soup.select(".contson") #标签选择 选择类名为.contson标签
    titles = soup.select('a[href^="/shiwenv_"]')  #href属性匹配
    authors = soup.select('a[href^="/search.aspx?value="]')

    pList = []
    for i in range(len(contents)):
        try:
            contentVal = contents[i]
            id = contentVal["id"]
            content = contentVal.get_text()
            title = titles[i].get_text()
            author = authors[i].get_text()
            pList.append([id, title, content, author])
        except:
            # print("error...")
            continue
        return pList


def start(location,length):
    for i in range(length):
        time.sleep(2)   #防止频繁请求失败 间隔2s
        url = base_url.format(page_num=i + location)

        response = get_page(url)
        if (response == None):
            print(url)
            continue
        else:
            pList = caculate(response)
        try:
            insertInto(pList)
        except Exception as e:
            print(e)
            continue #插入失败继续执行
        else:
            pass
        finally:
            pass

start(1,50) #开始 

查看有没有存入

mysql> select * from poetry_t;

或者安装数据库客户端链登录本地mysql, 我用的是MysqlWorkBench