数据采集第二次作业

作业①：爬取并保存指定城市的7日天气预报

代码与展示

import requests
from bs4 import BeautifulSoup
import sqlite3
import os

def create_database(db_name):
    """
    创建SQLite数据库并建立天气预报表
    """
    db_connection = sqlite3.connect(db_name)
    db_cursor = db_connection.cursor()
    # 创建表格
    db_cursor.execute('''
        CREATE TABLE IF NOT EXISTS WeatherForecast (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT,
            date TEXT,
            weather TEXT,
            temperature TEXT
        )
    ''')
    db_connection.commit()
    return db_connection, db_cursor

def fetch_weather_data(city_code, city_name):
    """
    获取指定城市的7日天气预报
    """
    url = f"http://www.weather.com.cn/weather/{city_code}.shtml"
    headers = {
        "User-Agent": "Mozilla/5.0"
    }
    response = requests.get(url, headers=headers)
    response.encoding = 'utf-8'
    soup = BeautifulSoup(response.text, "html.parser")
    forecast_list = soup.find('ul', class_='t clearfix')
    weather_data = []
    if forecast_list:
        days = forecast_list.find_all('li')
        for day in days:
            date = day.find('h1').get_text()
            weather_desc = day.find('p', class_='wea').get_text()
            temp_high = day.find('p', class_='tem').find('span')
            temp_high = temp_high.get_text() if temp_high else ''
            temp_low = day.find('p', class_='tem').find('i').get_text()
            temperature = f"{temp_high}/{temp_low}"
            weather_data.append({
                'city': city_name,
                'date': date,
                'weather': weather_desc,
                'temperature': temperature
            })
    return weather_data

def save_weather_data(db_cursor, weather_list):
    """
    将天气数据保存到数据库中
    """
    for weather in weather_list:
        db_cursor.execute('''
            INSERT INTO WeatherForecast (city, date, weather, temperature)
            VALUES (?, ?, ?, ?)
        ''', (weather['city'], weather['date'], weather['weather'], weather['temperature']))

if __name__ == "__main__":
    database_name = 'weather_data.db'
    if not os.path.exists(database_name):
        connection, cursor = create_database(database_name)
    else:
        connection = sqlite3.connect(database_name)
        cursor = connection.cursor()
    cities = [
        {'code': '101230101', 'name': '福州'},
        # 可以在这里添加更多城市
    ]
    for city in cities:
        city_code = city['code']
        city_name = city['name']
        weather_info = fetch_weather_data(city_code, city_name)
        save_weather_data(cursor, weather_info)
        for info in weather_info:
            print(f"城市: {info['city']}, 日期: {info['date']}, 天气: {info['weather']}, 温度: {info['temperature']}")
    connection.commit()
    connection.close()

运行结果：

Gitee文件夹链接：
https://gitee.com/lan-minlong/crawl_project_2024/tree/master/作业2

作业心得

在这个作业中，我学习了如何使用 requests 和 BeautifulSoup 库从中国气象网获取指定城市的天气预报，并将数据存储到 SQLite 数据库中。

网页解析： 使用 BeautifulSoup 提取网页中的天气信息，包括日期、天气状况和温度。
数据存储： 通过 sqlite3 库创建本地数据库，实现数据的持久化存储。
代码规范： 注意了代码的模块化和函数封装，增强了代码的可读性和维护性。

作业②：爬取股票信息并存储到数据库

代码与展示

import requests
import pandas as pd
import json
import sqlite3

def get_stock_info(page_number):
    """
    爬取指定页码的股票信息
    """
    base_url = "http://44.push2.eastmoney.com/api/qt/clist/get"
    params = {
        'cb': 'jQuery112406854618710877052_1696660618066',
        'pn': page_number,
        'pz': 20,
        'po': 1,
        'np': 1,
        'ut': 'bd1d9ddb04089700cf9c27f6f7426281',
        'fltt': 2,
        'invt': 2,
        'wbp2u': '|0|0|0|web',
        'fid': 'f3',
        'fs': 'm:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23,m:0+t:81+s:2048',
        'fields': 'f2,f3,f4,f5,f6,f7,f12,f14',
        '_': '1696660618067'
    }
    response = requests.get(base_url, params=params)
    content = response.text
    start_index = content.find('(') + 1
    end_index = content.rfind(')')
    json_str = content[start_index:end_index]
    data_json = json.loads(json_str)
    stock_items = data_json['data']['diff']
    stock_list = []
    for idx, item in enumerate(stock_items):
        stock_info = [
            idx + 1,
            item['f12'],
            item['f14'],
            item['f2'],
            item['f3'],
            item['f4'],
            item['f5'],
            item['f6'],
            item['f7']
        ]
        stock_list.append(stock_info)
    return stock_list

def store_data_to_db(stock_data):
    """
    将股票数据存储到SQLite数据库
    """
    connection = sqlite3.connect('stock_info.db')
    cursor = connection.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS stock_data (
            序号 INTEGER,
            代码 TEXT,
            名称 TEXT,
            最新价 REAL,
            涨跌幅 REAL,
            涨跌额 REAL,
            成交量 INTEGER,
            成交额 REAL,
            振幅 REAL
        )
    ''')
    cursor.executemany('INSERT INTO stock_data VALUES (?,?,?,?,?,?,?,?,?)', stock_data)
    connection.commit()
    connection.close()

def main():
    """
    主函数，执行数据获取和存储操作
    """
    total_stock_data = []
    page_input = int(input("请输入要爬取的页数："))
    for page in range(1, page_input + 1):
        page_stock_data = get_stock_info(page)
        total_stock_data.extend(page_stock_data)
    store_data_to_db(total_stock_data)
    df = pd.DataFrame(total_stock_data, columns=['序号','代码','名称','最新价','涨跌幅','涨跌额','成交量','成交额','振幅'])
    print(df)

if __name__ == "__main__":
    main()

运行结果：

Gitee文件夹链接：
https://gitee.com/lan-minlong/crawl_project_2024/tree/master/作业2

作业心得

通过这个作业，我学会了如何使用 requests 库结合浏览器的开发者工具来分析网络请求，从而获取动态加载的数据。

接口分析： 利用浏览器的 F12 开发者工具，抓取网络请求，找到数据接口。
数据解析： 处理返回的 JSONP 格式数据，提取出有效的 JSON 内容。
数据存储： 使用 sqlite3 将获取的股票信息保存到本地数据库中，方便后续的数据分析。

作业③：爬取中国大学2021主榜并存储到数据库

代码与展示

import requests
from bs4 import BeautifulSoup
import sqlite3
import re

def get_html_content(url):
    """
    获取指定URL的HTML内容
    """
    response = requests.get(url)
    response.raise_for_status()
    response.encoding = response.apparent_encoding
    return response.text

def parse_university_info(html_text):
    """
    解析HTML内容，提取大学信息列表
    """
    soup = BeautifulSoup(html_text, "html.parser")
    university_list = []
    tbody = soup.find('tbody')
    rows = tbody.find_all('tr')
    for row in rows:
        columns = row.find_all('td')
        rank = columns[0].text.strip()
        name_tag = columns[1].find('a')
        if name_tag:
            name = name_tag.text.strip()
        else:
            name = columns[1].text.strip()
        labels_to_remove = ['双一流', '985工程', '211工程', '985', '211']
        for label in labels_to_remove:
            name = name.replace(label, '')
        name = re.sub(r'[A-Za-z]', '', name)
        name = re.sub(r'[^\u4e00-\u9fa5]', '', name)
        province = columns[2].text.strip()
        category = columns[3].text.strip()
        score = columns[4].text.strip()
        university_list.append([rank, name, province, category, score])
    return university_list

def save_to_database(data_list):
    """
    将大学信息存储到SQLite数据库中
    """
    conn = sqlite3.connect('university_rankings.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS UniversityRanking (
            Rank TEXT,
            Name TEXT,
            Province TEXT,
            Category TEXT,
            Score TEXT
        )
    ''')
    cursor.executemany('INSERT INTO UniversityRanking VALUES (?,?,?,?,?)', data_list)
    conn.commit()
    conn.close()

def display_university_list(universities, count):
    """
    打印大学信息列表
    """
    template = "{0:^6}\t{1:{5}<20}\t{2:^6}\t{3:^8}\t{4:^6}"
    print(template.format("排名", "学校", "省份", "类型", "总分", chr(12288)))
    for i in range(count):
        uni = universities[i]
        print(template.format(uni[0], uni[1], uni[2], uni[3], uni[4], chr(12288)))

def main():
    """
    主函数，执行数据获取、解析、存储和显示操作
    """
    url = "https://www.shanghairanking.cn/rankings/bcur/2021"
    html_content = get_html_content(url)
    university_data = parse_university_info(html_content)
    save_to_database(university_data)
    display_university_list(university_data, 10)

if __name__ == '__main__':
    main()

F12调试分析过程：