国家统计局->统计标准网址：http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/

流程

对统计标准的网站进行分层分级爬取

代码

import pymysql
from bs4 import BeautifulSoup
import re
import requests
import lxml
import traceback
import time
import json
from lxml import etree

def get_area(year):
    year=str(year)
    url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/"+ year +"/index.html"
    print(url)
    headers={
        "User-Agent":"Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36"
    }
    response=requests.get(url,headers)
    # print(response.text)
    response.encoding="GBK"
    page_text = response.text
    soup=BeautifulSoup(page_text,"lxml")
    # print(page_text)
    all_province=soup.find_all("tr",class_="provincetr")        #获取所有省份第一级的tr 有4个tr
    # all_province长度为4，其中第一组是从北京市到黑龙江省
    """
    格式是这样的：
    <tr class="provincetr"><td><a href="11.html">北京市<br/></a></td>
    <td><a href="12.html">天津市<br/></a></td>
    <td><a href="13.html">河北省<br/></a></td>
    <td><a href="14.html">山西省<br/></a></td>
    <td><a href="15.html">内蒙古自治区<br/></a></td>
    <td><a href="21.html">辽宁省<br/></a></td><td>
    """
    province_str=""     #为了方便处理，把省份数据变成一个字符串
    for i in range(len(all_province)):
        province_str=province_str+str(all_province[i])
    # print(province_str)
    # 开始分别获得a标签的href和text
    province={}
    province_soup=BeautifulSoup(province_str,"lxml")
    province_href=province_soup.find_all("a")    #获取所有的a标签
    for i in province_href:
        href_str=str(i)
        # print(href_str)
        #创建省份数据字典
        province.update({BeautifulSoup(href_str,"lxml").find("a").text:BeautifulSoup(href_str,"lxml").find("a")["href"]})
    # print(province)
    """
    数据provide字典
    {"北京市": "11.html", "天津市": "12.html", "河北省": "13.html", "山西省": "14.html", 
    "内蒙古自治区": "15.html", "辽宁省": "21.html", "吉林省": "22.html", "黑龙江省": "23.html", 
    "上海市": "31.html", "江苏省": "32.html", "浙江省": "33.html", "安徽省": "34.html", 
    "福建省": "35.html", "江西省": "36.html", "山东省": "37.html", "河南省": "41.html", 
    "湖北省": "42.html", "湖南省": "43.html", "广东省": "44.html", "广西壮族自治区": "45.html",
    "海南省": "46.html", "重庆市": "50.html", "四川省": "51.html", "贵州省": "52.html", "云南省": "53.html",
    "西藏自治区": "54.html", "陕西省": "61.html", "甘肃省": "62.html", "青海省": "63.html", 
    "宁夏回族自治区": "64.html", "新疆维吾尔自治区": "65.html"}
    """
    # 根据身份数据字典继续爬取下一级的市级数据，创建市级数据字典
    city=[]
    city_url=""
    city_tr=[]
    temp_list=[]
    for item in province.items():
        # print(value)
        city_url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/"+year+"/"+item[1]
        city_html=requests.get(city_url,headers)
        city_html.encoding="GBK"
        city_text=city_html.text
        city_tr.append(BeautifulSoup(city_text,"lxml").find_all("tr",class_="citytr"))
        # 获得所有的市区tr city_tr列表长度是31 对应31个省或直辖市
        # 下面开始建立市区的字典{"名字":"链接"}
    #存放省名字列表
    province_key=[]
    for key in province.keys():
        province_key.append(key)
    num=0
    for i in city_tr:
        for j in i:
            # j:<tr class="citytr"><td><a href="11/1101.html">110100000000</a></td><td><a href="11/1101.html">市辖区</a></td></tr>
            # print(j)
            etree_ = etree.HTML(str(j))
            temp_list.append({
                etree_.xpath("//tr/td[2]/a/text()")[0]:
                etree_.xpath("//tr/td[2]/a/@href")[0]
            })
            # print(temp_list)
        city.append({province_key[num]:temp_list})
        num=num+1
        temp_list=[]
    print(len(city))

    """
    city[11]
    {"安徽省": [{"合肥市": "34/3401.html"}, {"芜湖市": "34/3402.html"}, {"蚌埠市": "34/3403.html"}, 
    {"淮南市": "34/3404.html"}, {"马鞍山市": "34/3405.html"}, {"淮北市": "34/3406.html"}, {"铜陵市": "34/3407.html"}, 
    {"安庆市": "34/3408.html"}, {"黄山市": "34/3410.html"}, {"滁州市": "34/3411.html"}, {"阜阳市": "34/3412.html"}, 
    {"宿州市": "34/3413.html"}, {"六安市": "34/3415.html"}, {"亳州市": "34/3416.html"}, {"池州市": "34/3417.html"}, 
    {"宣城市": "34/3418.html"}]}
    """

    # 搞定市级字典，下面开始最后一步，area
    province_name=""
    city_name=""
    area_name=""
    area_tr=[]
    area_list=[]
    temp_area_list=[]

    for item1 in city:
        for k1,v1 in item1.items():
            province_name=k1
            if(province_name in ["北京","天津","上海","重庆"]):
                province_name=province_name+"市"
            if(province_name =="宁夏"):
                province_name=province_name+"回族自治区"
            if(province_name in["西藏","内蒙古"]):
                province_name=province_name+"自治区"
            if(province_name == "新疆"):
                province_name=province_name+"维吾尔自治区"
            if (province_name == "广西"):
                province_name = province_name + "壮族自治区"
            if(province_name=="黑龙江"):
                province_name=province_name+"省"
            if(len(province_name)==2 and province_name not in ["西藏","宁夏","新疆","广西","北京","天津","上海","重庆"]):
                province_name = province_name+"省"
            for item2 in v1:
                for k2,v2 in item2.items():
                    city_name=k2
                    # print(city_name)
                    area_url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/"+ year +"/"+ v2
                    print(area_url)
                    area_response=requests.get(area_url,headers)
                    area_response.encoding="GBK"
                    area_text=area_response.text
                    area_soup=BeautifulSoup(area_text,"lxml")
                    area_tr=area_soup.find_all("tr",class_="countytr")
                    for i in range(len(area_tr)):
                        etree_area = etree.HTML(str(area_tr[i]))
                        try:
                            area_name=etree_area.xpath("//tr/td[2]/a/text()")[0]
                        except:
                            area_name = etree_area.xpath("//tr/td[2]/text()")[0]
                        # print(area_name)
                        # print(str(area_tr[i]))
                        try:
                            temp_area_list.append({
                                etree_area.xpath("//tr/td[1]/a/text()")[0][0:6]: province_name+"·"+city_name+"·"+area_name
                            })
                        except:
                            temp_area_list.append({
                                etree_area.xpath("//tr/td[1]/text()")[0][0:6]: province_name+"·"+city_name+"·"+area_name
                            })
                        area_list.append(temp_area_list)
                        temp_area_list=[]
        time.sleep(1)
    return area_list

def into_mysql(year):
    year=str(year)
    SQL=""
    conn,cursor=get_mysql_conn()
    res=get_area(year)
    try:
        for item in res:
            for k,v in item[0].items():
                print(k)
                print(v)
                SQL="insert into std_area (year,area_code, area_name) values (""+year+"",""+k+"",""+v+"")"
                print(SQL)
                cursor.execute(SQL)
                conn.commit()
    except:
        print("出现错误")
    conn,cursor.close()
    return None

def query(sql,*args):
    """
    通用封装查询
    :param sql:
    :param args:
    :return:返回查询结果 （（），（））
    """
    conn , cursor= get_mysql_conn()
    print(sql)
    cursor.execute(sql)
    res = cursor.fetchall()
    close_conn(conn , cursor)
    return res
"""
------------------------------------------------------------------------------------
"""
def get_mysql_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                    user="root",
                    password="000429",
                    db="data_cleaning",
                    charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor

def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()
if __name__ == "__main__":
    # res=get_area()
    into_mysql("2009")