Python爬取王者荣耀皮肤图片并生成统计图表

import requests
from bs4 import BeautifulSoup
import sqlite3
import openpyxl
from openpyxl.chart import BarChart, Reference

# 爬取皮肤图片
def get_skin_imgs():
    url = 'https://pvp.qq.com/web201605/herolist.shtml'
    r = requests.get(url)
    soup = BeautifulSoup(r.content, 'lxml')
    hero_list = soup.find_all('a', class_='herolist__icon')
    skin_imgs = []
    for hero in hero_list:
        hero_url = 'https://pvp.qq.com/web201605/' + hero['href']
        hero_r = requests.get(hero_url)
        hero_soup = BeautifulSoup(hero_r.content, 'lxml')
        hero_name = hero_soup.find('h2').text
        skin_list = hero_soup.find_all('div', class_='pic-pf')
        for skin in skin_list:
            skin_img = skin.find('img')['src']
            skin_name = skin.find('img')['alt']
            skin_imgs.append((hero_name, skin_name, skin_img))
    return skin_imgs

# 将信息保存到sqlite数据库表中
def save_to_sqlite(skin_imgs):
    conn = sqlite3.connect('skin_imgs.db')
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS skin_imgs (hero_name TEXT, skin_name TEXT, skin_img TEXT)')
    c.executemany('INSERT INTO skin_imgs VALUES (?, ?, ?)', skin_imgs)
    conn.commit()
    conn.close()

# 将信息保存到Excel表中
def save_to_excel(skin_imgs):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = '皮肤图片'
    ws['A1'] = '英雄名字'
    ws['B1'] = '皮肤名字'
    ws['C1'] = '皮肤图片链接'
    for i, skin in enumerate(skin_imgs):
        ws.cell(row=i+2, column=1, value=skin[0])
        ws.cell(row=i+2, column=2, value=skin[1])
        ws.cell(row=i+2, column=3, value=skin[2])
    chart = BarChart()
    chart.title = '皮肤数量统计图'
    chart.y_axis.title = '皮肤数量'
    chart.x_axis.title = '英雄名字'
    data = Reference(ws, min_col=2, min_row=1, max_row=len(skin_imgs)+1)
    cats = Reference(ws, min_col=1, min_row=2, max_row=len(skin_imgs)+1)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, 'E2')
    wb.save('skin_imgs.xlsx')

# 生成图表或显示处理后的信息
def generate_chart():
    conn = sqlite3.connect('skin_imgs.db')
    c = conn.cursor()
    c.execute('SELECT hero_name, COUNT(*) FROM skin_imgs GROUP BY hero_name')
    rows = c.fetchall()
    conn.close()
    wb = openpyxl.load_workbook('skin_imgs.xlsx')
    ws = wb.active
    ws2 = wb.create_sheet('皮肤数量统计')
    ws2['A1'] = '英雄名字'
    ws2['B1'] = '皮肤数量'
    for i, row in enumerate(rows):
        ws2.cell(row=i+2, column=1, value=row[0])
        ws2.cell(row=i+2, column=2, value=row[1])
    chart = BarChart()
    chart.title = '皮肤数量统计图'
    chart.y_axis.title = '皮肤数量'
    chart.x_axis.title = '英雄名字'
    data = Reference(ws2, min_col=2, min_row=1, max_row=len(rows)+1)
    cats = Reference(ws2, min_col=1, min_row=2, max_row=len(rows)+1)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws2.add_chart(chart, 'D2')
    wb.save('skin_imgs.xlsx')

if __name__ == '__main__':
    skin_imgs = get_skin_imgs()
    save_to_sqlite(skin_imgs)
    save_to_excel(skin_imgs)
    generate_chart()