数据采集第二次作业

数据采集第二次作业

102402132　邱福铭

运行环境：Windows 11、Python 3.14.7、requests 2.34.2、beautifulsoup4 4.15.0
运行日期：2026 年 11 月 8 日
数据库：data_collection.db（SQLite）

Gitee 作业2 文件夹：https://gitee.com/qiu-fuming/2026shujucaijiyuronghejishu/tree/master/作业2

一、作业①②：天气预报与股票行情采集

1）实验实现

作业①：中国气象网 5 个城市 7 日天气预报

目标页面是 https://www.weather.com.cn/weather/城市代码.shtml。7 天数据都在 ul.t.clearfix 下的 7 个 li 里，分别取 h1 日期、p.wea 天气、p.tem 气温、p.win 风向和风力。第一天的气温只显示当前温度，没有最高/最低温，所以数据库保留原始 temp_text，同时把 temp_high、temp_low 设为可空。

本次选择北京、上海、广州、成都、哈尔滨 5 个城市，城市代码分别是 101010100、101020100、101280101、101270101、101050101。

"""作业①：爬取中国气象网 5 个城市的 7 日天气预报并存入 SQLite。"""

import re
import sqlite3
import unicodedata
from datetime import date, timedelta
from pathlib import Path

import requests
from bs4 import BeautifulSoup
from requests.exceptions import RequestException

DB_PATH = Path(__file__).resolve().parent / "data_collection.db"
HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/131.0.0.0 Safari/537.36"
    ),
    "Referer": "http://www.weather.com.cn/",
}
CITIES = {
    "北京": "101010100",
    "上海": "101020100",
    "广州": "101280101",
    "成都": "101270101",
    "哈尔滨": "101050101",
}


def init_db(connection: sqlite3.Connection) -> None:
    """创建天气预报表；同一城市同一天只保留一条记录。"""
    connection.execute(
        """
        CREATE TABLE IF NOT EXISTS weather (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT NOT NULL,
            city_code TEXT NOT NULL,
            forecast_date TEXT NOT NULL,
            date_label TEXT,
            weather TEXT,
            temp_text TEXT,
            temp_high INTEGER,
            temp_low INTEGER,
            wind TEXT,
            wind_level TEXT,
            collected_at TEXT DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(city_code, forecast_date)
        )
        """
    )
    connection.commit()


def fetch_html(city_code: str) -> str:
    """请求指定城市的 7 日天气预报页面。"""
    url = f"https://www.weather.com.cn/weather/{city_code}.shtml"
    try:
        response = requests.get(url, headers=HEADERS, timeout=20)
        response.raise_for_status()
        response.encoding = response.apparent_encoding
        return response.text
    except RequestException as exc:
        raise RuntimeError(f"{url} 请求失败：{exc}") from exc


def parse_forecast(html: str) -> list[dict[str, object]]:
    """解析页面中的 7 个 li，返回每天的天气、气温和风力。"""
    soup = BeautifulSoup(html, "html.parser")
    items = soup.select("ul.t.clearfix li")[:7]
    if len(items) < 7:
        raise ValueError(f"只解析到 {len(items)} 天数据")

    forecasts: list[dict[str, object]] = []
    today = date.today()

    for index, item in enumerate(items):
        date_label = item.select_one("h1").get_text(" ", strip=True)
        weather = item.select_one("p.wea").get_text(" ", strip=True)
        temperature = item.select_one("p.tem").get_text(" ", strip=True)
        temperature = re.sub(r"\s+", "", temperature)
        numbers = [int(number) for number in re.findall(r"-?\d+", temperature)]

        # 今天只显示当前气温，没有最高/最低温；保留原始温度文本。
        if len(numbers) >= 2:
            temp_high, temp_low = numbers[0], numbers[1]
        else:
            temp_high = temp_low = None

        wind_tags = item.select("p.win em span")
        wind = "/".join(tag.get("title", "") for tag in wind_tags) or "未知"
        wind_level_tag = item.select_one("p.win i")
        wind_level = (
            wind_level_tag.get_text(" ", strip=True) if wind_level_tag else "未知"
        )

        forecasts.append(
            {
                "forecast_date": (today + timedelta(days=index)).isoformat(),
                "date_label": date_label,
                "weather": weather,
                "temp_text": temperature,
                "temp_high": temp_high,
                "temp_low": temp_low,
                "wind": wind,
                "wind_level": wind_level,
            }
        )
    return forecasts


def save_forecasts(
    connection: sqlite3.Connection, city: str, city_code: str,
    forecasts: list[dict[str, object]]
) -> None:
    """按城市和日期写入数据库，重复运行会覆盖同一天的数据。"""
    # 同一城市只保留本次抓取的 7 天，避免旧日期累计。
    connection.execute("DELETE FROM weather WHERE city_code = ?", (city_code,))
    connection.executemany(
        """
        INSERT INTO weather (
            city, city_code, forecast_date, date_label, weather,
            temp_text, temp_high, temp_low, wind, wind_level
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(city_code, forecast_date) DO UPDATE SET
            city = excluded.city,
            date_label = excluded.date_label,
            weather = excluded.weather,
            temp_text = excluded.temp_text,
            temp_high = excluded.temp_high,
            temp_low = excluded.temp_low,
            wind = excluded.wind,
            wind_level = excluded.wind_level,
            collected_at = CURRENT_TIMESTAMP
        """,
        [
            (
                city, city_code, row["forecast_date"], row["date_label"],
                row["weather"], row["temp_text"], row["temp_high"],
                row["temp_low"], row["wind"], row["wind_level"],
            )
            for row in forecasts
        ],
    )
    connection.commit()


def display_width(text: str) -> int:
    """计算中文终端显示宽度。"""
    return sum(2 if unicodedata.east_asian_width(char) in "WF" else 1
               for char in text)


def pad(text: object, width: int) -> str:
    """按显示宽度右侧补空格。"""
    value = str(text)
    return value + " " * max(1, width - display_width(value))


def main() -> None:
    """抓取 5 个城市、每城 7 天，并打印入库结果。"""
    connection = sqlite3.connect(DB_PATH)
    try:
        init_db(connection)
        for city, city_code in CITIES.items():
            html = fetch_html(city_code)
            forecasts = parse_forecast(html)
            save_forecasts(connection, city, city_code, forecasts)
            print(f"{city}（{city_code}）：已入库 {len(forecasts)} 天")

        rows = connection.execute(
            """
            SELECT city, forecast_date, weather, temp_text, wind, wind_level
            FROM weather
            ORDER BY id
            """
        ).fetchall()

        print(f"\n数据库共 {len(rows)} 条天气预报记录：")
        print(pad("城市", 8) + pad("日期", 13) + pad("天气", 10)
              + pad("气温", 15) + pad("风向", 24) + "风力")
        print("-" * 90)
        for city, forecast_date, weather, temp_text, wind, wind_level in rows:
            print(pad(city, 8) + pad(forecast_date, 13) + pad(weather, 10)
                  + pad(temp_text, 15) + pad(wind, 24) + wind_level)
    finally:
        connection.close()


if __name__ == "__main__":
    main()

图 1　VSCode 终端运行天气爬虫，weather 表 35 条（上半）

图 2　VSCode 终端天气结果（下半），含成都、哈尔滨和总数

Gitee 作业①：https://gitee.com/qiu-fuming/2026shujucaijiyuronghejishu/tree/master/作业2

作业②：东方财富沪深 A 股行情

在东方财富行情页按 F12，Network 面板里可以找到列表接口：

https://push2.eastmoney.com/api/qt/clist/get

关键参数：

参数	含义
pn / pz	页码 / 每页条数，本次每页 100 条
fs	市场范围，包含沪深主板、创业板、科创板
fid=f3	按涨跌幅排序
fields	指定返回字段，减少无用数据

字段对应关系：f12 股票代码、f14 名称、f2 最新价、f3 涨跌幅、f4 涨跌额、f5 成交量、f6 成交额、f7 振幅、f15 最高、f16 最低、f17 今开、f18 昨收。接口返回 data.total = 5563，程序按 100 条一页循环抓完。

"""作业②：调用东方财富行情接口，抓取沪深 A 股列表并存入 SQLite。"""

import sqlite3
import time
import unicodedata
from pathlib import Path

import requests
from requests.exceptions import RequestException

DB_PATH = Path(__file__).resolve().parent / "data_collection.db"
API_URL = "https://push2.eastmoney.com/api/qt/clist/get"
HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/131.0.0.0 Safari/537.36"
    ),
    "Referer": "https://quote.eastmoney.com/",
}
PARAMS = {
    "pn": "1",
    "pz": "100",
    "po": "1",
    "np": "1",
    "fltt": "2",
    "invt": "2",
    "fid": "f3",
    "fs": "m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23",
    "fields": "f12,f14,f2,f3,f4,f5,f6,f7,f15,f16,f17,f18",
}


def init_db(connection: sqlite3.Connection) -> None:
    """创建股票表，以股票代码作为唯一标识。"""
    connection.execute(
        """
        CREATE TABLE IF NOT EXISTS stocks (
            code TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            price REAL,
            change_pct REAL,
            change_amount REAL,
            volume REAL,
            turnover REAL,
            amplitude REAL,
            high REAL,
            low REAL,
            open REAL,
            prev_close REAL,
            collected_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    connection.commit()


def to_number(value: object) -> float | None:
    """接口里的停牌字段可能是 '-'，统一转成 None。"""
    if value in (None, "", "-"):
        return None
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def fetch_page(session: requests.Session, page: int) -> tuple[int, list[dict]]:
    """请求一页行情，返回（股票总数，本页数据）。"""
    params = dict(PARAMS, pn=str(page))
    try:
        response = session.get(API_URL, params=params, timeout=20)
        response.raise_for_status()
        payload = response.json()
    except (RequestException, ValueError) as exc:
        raise RuntimeError(f"第 {page} 页请求或 JSON 解析失败：{exc}") from exc

    data = payload.get("data") or {}
    return int(data.get("total") or 0), data.get("diff") or []


def parse_stock(item: dict) -> tuple:
    """把接口字段映射成数据库列。"""
    return (
        str(item.get("f12", "")),
        str(item.get("f14", "")),
        to_number(item.get("f2")),
        to_number(item.get("f3")),
        to_number(item.get("f4")),
        to_number(item.get("f5")),
        to_number(item.get("f6")),
        to_number(item.get("f7")),
        to_number(item.get("f15")),
        to_number(item.get("f16")),
        to_number(item.get("f17")),
        to_number(item.get("f18")),
    )


def crawl_all() -> tuple[int, int]:
    """逐页抓取全部沪深 A 股，返回（接口总数，实际入库数）。"""
    connection = sqlite3.connect(DB_PATH)
    session = requests.Session()
    session.headers.update(HEADERS)
    seen_codes: set[str] = set()

    try:
        init_db(connection)
        total = 0
        page = 1
        while page <= 100:
            total, rows = fetch_page(session, page)
            if not rows:
                break
            records = [parse_stock(item) for item in rows]
            records = [record for record in records if record[0]]
            connection.executemany(
                """
                INSERT INTO stocks (
                    code, name, price, change_pct, change_amount, volume,
                    turnover, amplitude, high, low, open, prev_close
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(code) DO UPDATE SET
                    name = excluded.name,
                    price = excluded.price,
                    change_pct = excluded.change_pct,
                    change_amount = excluded.change_amount,
                    volume = excluded.volume,
                    turnover = excluded.turnover,
                    amplitude = excluded.amplitude,
                    high = excluded.high,
                    low = excluded.low,
                    open = excluded.open,
                    prev_close = excluded.prev_close,
                    collected_at = CURRENT_TIMESTAMP
                """,
                records,
            )
            connection.commit()
            seen_codes.update(record[0] for record in records)
            if page % 10 == 0 or len(seen_codes) >= total:
                print(f"进度：第 {page:02d} 页，累计 {len(seen_codes)}/{total} 条")
            if len(seen_codes) >= total:
                break
            page += 1
            time.sleep(0.15)
        return total, len(seen_codes)
    finally:
        session.close()
        connection.close()


def display_width(text: str) -> int:
    """计算中文终端显示宽度。"""
    return sum(2 if unicodedata.east_asian_width(char) in "WF" else 1
               for char in text)


def pad(text: object, width: int) -> str:
    """按显示宽度右侧补空格。"""
    value = str(text)
    return value + " " * max(1, width - display_width(value))


def print_top(limit: int = 20) -> int:
    """打印涨幅榜前若干名，并返回数据库总记录数。"""
    connection = sqlite3.connect(DB_PATH)
    try:
        count = connection.execute("SELECT COUNT(*) FROM stocks").fetchone()[0]
        rows = connection.execute(
            """
            SELECT code, name, price, change_pct, volume, turnover
            FROM stocks
            ORDER BY COALESCE(change_pct, -999) DESC, code
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
    finally:
        connection.close()

    print(f"\n股票表共 {count} 条记录，涨幅榜前 {len(rows)} 名：")
    print(pad("序号", 6) + pad("代码", 9) + pad("名称", 14)
          + pad("最新价", 10) + pad("涨跌幅", 11) + "成交额/亿")
    print("-" * 68)
    for index, (code, name, price, change_pct, volume, turnover) in enumerate(
        rows, start=1
    ):
        price_text = f"{price:.2f}" if price is not None else "-"
        change_text = f"{change_pct:.2f}%" if change_pct is not None else "-"
        turnover_text = f"{turnover / 100000000:.2f}" if turnover else "-"
        print(pad(index, 6) + pad(code, 9) + pad(name, 14)
              + pad(price_text, 10) + pad(change_text, 11) + turnover_text)
    return count


def main() -> None:
    """抓取全部股票并输出涨幅榜。"""
    total, stored = crawl_all()
    print(f"\n接口返回总数：{total}；实际入库：{stored}")
    print(f"数据库：{DB_PATH}")
    print_top()


if __name__ == "__main__":
    main()

图 3　VSCode 终端运行股票爬虫，stocks 表 5563 条

Gitee 作业②：https://gitee.com/qiu-fuming/2026shujucaijiyuronghejishu/tree/master/作业2

2）心得体会

天气这题不难在解析，难在第一天的数据格式和其他六天不一样：其他天是“最高温/最低温”，第一天只有一个当前温度。最开始想直接拆成两个数字，结果第一天的值会被误当成最低温；后来保留原始温度文本，最高/最低温允许为空，数据含义才准确。顺便也确认了响应头没有给字符集，必须自己按 UTF-8 解码。

股票这题主要是对着接口字段做映射。f1、f2 这些名字在 JSON 里看不出含义，只能一条条对照页面字段；分页时还要注意 pz=100 和总数 data.total，不然会少抓。接口对请求头有要求，UA 和 Referer 都按浏览器请求补上后才稳定。全量 5563 条写进 SQLite 后，用 ON CONFLICT 更新，重复运行也不会产生重复记录。

二、作业③：2021 软科中国大学排名全量采集

1）实验实现

先打开榜单页，按 F12 → Network → Fetch/XHR，刷新页面后可以看到榜单数据不是直接写在 HTML 表格里，而是由一个接口返回：

https://www.shanghairanking.cn/api/pub/v1/bcur?bcur_type=11&year=2021

接口返回 JSON，院校数组在 data.rankings，每一条包含 ranking、univNameCn、province、univCategory、score 等字段。2021 主榜共 582 所院校，第一条是清华大学，总分 969.2。请求时需要带 Referer，否则可能返回拦截页。

"""作业③：调用软科 2021 榜单接口，抓取所有院校并存入 SQLite。"""

import sqlite3
import unicodedata
from pathlib import Path

import requests
from requests.exceptions import RequestException

DB_PATH = Path(__file__).resolve().parent / "data_collection.db"
API_URL = "https://www.shanghairanking.cn/api/pub/v1/bcur"
HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/131.0.0.0 Safari/537.36"
    ),
    "Referer": "https://www.shanghairanking.cn/rankings/bcur/2021",
}


def init_db(connection: sqlite3.Connection) -> None:
    """创建 2021 排名表，同一学校只保留一条记录。"""
    connection.execute(
        """
        CREATE TABLE IF NOT EXISTS rankings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            year INTEGER NOT NULL,
            rank INTEGER NOT NULL,
            school TEXT NOT NULL,
            province TEXT,
            category TEXT,
            score REAL,
            collected_at TEXT DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(year, school)
        )
        """
    )
    connection.commit()


def fetch_rankings(year: int = 2021) -> list[dict]:
    """请求榜单 API，返回 data.rankings 列表。"""
    params = {"bcur_type": "11", "year": str(year)}
    try:
        response = requests.get(API_URL, params=params, headers=HEADERS, timeout=30)
        response.raise_for_status()
        payload = response.json()
    except (RequestException, ValueError) as exc:
        raise RuntimeError(f"榜单接口请求或 JSON 解析失败：{exc}") from exc

    if payload.get("code") != 200:
        raise RuntimeError(f"接口返回异常：{payload.get('msg')}")
    return payload["data"]["rankings"]


def save_rankings(
    connection: sqlite3.Connection, year: int, rankings: list[dict]
) -> int:
    """把院校写入数据库，返回写入条数。"""
    records = []
    for item in rankings:
        rank_text = str(item.get("ranking", "")).strip()
        score = item.get("score")
        records.append(
            (
                year,
                int(rank_text) if rank_text.isdigit() else 0,
                item.get("univNameCn", ""),
                item.get("province", ""),
                item.get("univCategory", ""),
                float(score) if score not in (None, "", "-") else None,
            )
        )

    connection.executemany(
        """
        INSERT INTO rankings (year, rank, school, province, category, score)
        VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT(year, school) DO UPDATE SET
            rank = excluded.rank,
            province = excluded.province,
            category = excluded.category,
            score = excluded.score,
            collected_at = CURRENT_TIMESTAMP
        """,
        records,
    )
    connection.commit()
    return len(records)


def display_width(text: str) -> int:
    """计算中文终端显示宽度。"""
    return sum(2 if unicodedata.east_asian_width(char) in "WF" else 1
               for char in text)


def pad(text: object, width: int) -> str:
    """按显示宽度右侧补空格。"""
    value = str(text)
    return value + " " * max(1, width - display_width(value))


def print_rankings(limit: int = 20) -> tuple[int, list[tuple]]:
    """打印榜单前若干名，返回（总条数，打印行）。"""
    connection = sqlite3.connect(DB_PATH)
    try:
        count = connection.execute(
            "SELECT COUNT(*) FROM rankings WHERE year = 2021"
        ).fetchone()[0]
        rows = connection.execute(
            """
            SELECT rank, school, province, category, score
            FROM rankings
            WHERE year = 2021
            ORDER BY rank, id
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
    finally:
        connection.close()

    print(f"\n2021 主榜共 {count} 所院校，前 {len(rows)} 名：")
    print(pad("排名", 7) + pad("学校", 18) + pad("省市", 10)
          + pad("类型", 10) + "总分")
    print("-" * 60)
    for rank, school, province, category, score in rows:
        score_text = f"{score:.1f}" if score is not None else "-"
        print(pad(rank, 7) + pad(school, 18) + pad(province, 10)
              + pad(category, 10) + score_text)
    return count, rows


def main() -> None:
    """抓取 2021 主榜全部院校，写入数据库并打印前 20 名。"""
    rankings = fetch_rankings(2021)
    connection = sqlite3.connect(DB_PATH)
    try:
        init_db(connection)
        saved = save_rankings(connection, 2021, rankings)
    finally:
        connection.close()

    print(f"接口返回：{len(rankings)} 所院校；写入/更新：{saved} 条")
    print(f"数据库：{DB_PATH}")
    print_rankings(20)


if __name__ == "__main__":
    main()

图 4　VSCode 终端运行榜单爬虫，rankings 表 582 所院校

图 5　F12 分析 2021 榜单接口，返回 582 条院校

Gitee 作业③：https://gitee.com/qiu-fuming/2026shujucaijiyuronghejishu/tree/master/作业2

2）心得体会

这题让我第一次认真用了 F12 的 Network 面板。榜单页 HTML 首屏只有 30 条，一开始用 BeautifulSoup 只能抓到 30 条；在 Fetch/XHR 里找到 bcur 接口后，一次就能拿到 582 条。接口里的 ranking 有并列名次，不能拿它当唯一键，所以表里用 (year, school) 做唯一约束，rank 只作为普通字段保存。

写入时用了 ON CONFLICT(year, school) DO UPDATE，这样重复运行不会重复插入。相比第一题解析 HTML，接口返回的 JSON 更稳定，但字段名和请求头需要先分析清楚；如果接口改版，代码也要跟着改。最后把 F12 过程录成 GIF，博客里能直接看到从刷新页面到查看 Response 的步骤。