102402116蔡靖熹-数据采集-第二次博客作业

一、天气预报与股票数据采集

1. 作业代码与结果展示

作业①：中国天气网七日天气采集

实验目标： 使用 Python requests 和 BeautifulSoup，
采集北京、上海、广州、福州、成都五个城市的七日天气预报，
并存储至 SQLite 数据库。

数据来源： https://www.weather.com.cn/

实现流程：

1. 构建五个城市对应的天气页面 URL。
2. 使用 requests 请求网页并获取 HTML。
3. 使用 BeautifulSoup 解析日期、天气、温度及风向风力。
4. 创建 SQLite 数据表并写入预报数据。
5. 使用 SQL 查询各城市存储记录数。

完整 Python 代码：

"""
第二次作业 - 作业①
中国天气网七日天气爬虫
功能：采集五个城市七日天气并存储到 SQLite
"""

import re
import sqlite3
import time
from datetime import datetime, timedelta
from pathlib import Path
from zoneinfo import ZoneInfo

import requests
from bs4 import BeautifulSoup


# 城市名称及对应天气网城市代码
CITIES = {
    "北京": "101010100",
    "上海": "101020100",
    "广州": "101280101",
    "福州": "101230101",
    "成都": "101270101",
}

BASE_URL = "https://www.weather.com.cn/weather/{}.shtml"

# 基于脚本位置确定数据库路径
PROJECT_DIR = Path(__file__).resolve().parent.parent
DB_PATH = PROJECT_DIR / "data" / "weather.db"

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
                  "AppleWebKit/537.36 (KHTML, like Gecko) "
                  "Chrome/131.0.0.0 Safari/537.36",
    "Accept-Language": "zh-CN,zh;q=0.9",
    "Referer": "https://www.weather.com.cn/",
}

CHINA_TZ = ZoneInfo("Asia/Shanghai")


def create_database():
    """建立天气数据库及数据表。"""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS weather (
            city TEXT NOT NULL,
            city_code TEXT NOT NULL,
            forecast_date TEXT NOT NULL,
            weather TEXT,
            high_temp INTEGER,
            low_temp INTEGER,
            wind_direction TEXT,
            wind_power TEXT,
            source_url TEXT,
            collected_at TEXT NOT NULL,
            PRIMARY KEY (city, forecast_date)
        )
    """)
    conn.commit()
    return conn


def extract_temperature(element):
    """从温度标签中解析整数温度，缺失时返回 None。"""
    if element is None:
        return None

    match = re.search(r"-?\d+", element.get_text(strip=True))
    return int(match.group()) if match else None


def fetch_weather(session, city, city_code):
    """请求并解析指定城市的七日天气预报。"""
    url = BASE_URL.format(city_code)
    print(f"\n正在采集：{city} | {url}")

    response = session.get(
        url,
        headers=HEADERS,
        timeout=20,
    )
    response.raise_for_status()
    response.encoding = response.apparent_encoding or "utf-8"

    soup = BeautifulSoup(response.text, "html.parser")

    # 中国天气网七日预报区域
    day_items = soup.select('div[id="7d"] ul.t li')

    if len(day_items) != 7:
        raise ValueError(
            f"{city} 页面解析异常：预期7天，实际{len(day_items)}天"
        )

    today = datetime.now(CHINA_TZ).date()
    collected_at = datetime.now(CHINA_TZ).isoformat(
        timespec="seconds"
    )

    records = []

    for index, item in enumerate(day_items):
        date_element = item.select_one("h1")
        weather_element = item.select_one("p.wea")

        if date_element is None or weather_element is None:
            raise ValueError(f"{city} 第{index + 1}天数据缺失")

        # 使用日期标签核对日期，避免跨月或缓存错误
        date_label = date_element.get_text(strip=True)
        match = re.search(r"(\d{1,2})日", date_label)

        forecast_date = today + timedelta(days=index)

        if not match or int(match.group(1)) != forecast_date.day:
            raise ValueError(
                f"{city} 日期不匹配：页面为{date_label}，"
                f"预期为{forecast_date}"
            )

        weather = weather_element.get_text(strip=True)

        # 最高、最低温度
        high_temp = extract_temperature(
            item.select_one("p.tem span")
        )
        low_temp = extract_temperature(
            item.select_one("p.tem i")
        )

        # 风向：提取 span 标签的 title 属性
        wind_elements = item.select("p.win em span[title]")
        directions = [
            element.get("title", "").strip()
            for element in wind_elements
        ]
        directions = list(dict.fromkeys(
            direction for direction in directions if direction
        ))
        wind_direction = "转".join(directions)

        # 风力等级
        wind_element = item.select_one("p.win i")
        wind_power = (
            wind_element.get_text(strip=True)
            if wind_element else ""
        )

        record = (
            city,
            city_code,
            forecast_date.isoformat(),
            weather,
            high_temp,
            low_temp,
            wind_direction,
            wind_power,
            url,
            collected_at,
        )

        records.append(record)

        print(
            f"{forecast_date} | {weather:<8} | "
            f"{high_temp} / {low_temp}℃ | {wind_power}"
        )

    return records


def save_weather(conn, records):
    """批量存储天气信息，重复日期执行更新。"""
    conn.executemany("""
        INSERT INTO weather (
            city, city_code, forecast_date, weather,
            high_temp, low_temp, wind_direction,
            wind_power, source_url, collected_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(city, forecast_date)
        DO UPDATE SET
            city_code = excluded.city_code,
            weather = excluded.weather,
            high_temp = excluded.high_temp,
            low_temp = excluded.low_temp,
            wind_direction = excluded.wind_direction,
            wind_power = excluded.wind_power,
            source_url = excluded.source_url,
            collected_at = excluded.collected_at
    """, records)

    conn.commit()


def main():
    """执行全部城市采集任务并统计数据库记录。"""
    print("========== 七日天气爬虫 ==========")

    success_count = 0
    failed_cities = []

    with sqlite3.connect(DB_PATH) if False else create_database() as conn:
        with requests.Session() as session:
            for city, code in CITIES.items():
                try:
                    records = fetch_weather(session, city, code)
                    save_weather(conn, records)
                    success_count += 1
                    print(f"✓ {city}：存储{len(records)}条")

                except (requests.RequestException, ValueError) as exc:
                    failed_cities.append(city)
                    print(f"✗ {city}：{exc}")

                # 控制请求频率
                time.sleep(1.5)

        print("\n========== 数据库统计 ==========")

        result = conn.execute("""
            SELECT city, COUNT(*)
            FROM weather
            GROUP BY city
            ORDER BY city
        """).fetchall()

        for city, count in result:
            print(f"{city}：{count}条")

        total = conn.execute(
            "SELECT COUNT(*) FROM weather"
        ).fetchone()[0]

        print(f"数据库总记录数：{total}")
        print(f"数据库路径：{DB_PATH}")
        print(f"本次成功城市数：{success_count}/{len(CITIES)}")

    if failed_cities:
        raise SystemExit(
            "以下城市采集失败：" + "、".join(failed_cities)
        )


if __name__ == "__main__":
    main()

实际运行结果：

• 城市数量：5
• 数据库记录数：35
• 数据库文件：data/weather.db

运行截图：

数据库截图：

作业②：东方财富股票行情数据采集

实验目标： 使用 requests 请求股票 API，
通过 json 解析返回数据，并将股票行情存储到 SQLite 数据库。

数据来源： https://www.eastmoney.com/

主要步骤：

1. 分析东方财富行情接口的请求参数。
2. 使用 requests 获取分页 JSON 数据。
3. 解析股票代码、股票名称、最新价和涨跌幅等字段。
4. 将解析结果批量写入 SQLite。
5. 查询数据库并显示股票样本。

完整 Python 代码：

"""
第二次作业 - 作业②
东方财富股票行情爬虫
使用 requests + json 解析股票数据并保存 SQLite
"""

import json
import sqlite3
import time
from datetime import datetime
from pathlib import Path
from zoneinfo import ZoneInfo

import requests


PROJECT_DIR = Path(__file__).resolve().parent.parent
DB_PATH = PROJECT_DIR / "data" / "stocks.db"
JSON_PATH = PROJECT_DIR / "data" / "stock_api_sample.json"

API_URLS = [
    "https://push2.eastmoney.com/api/qt/clist/get",
    "https://push2delay.eastmoney.com/api/qt/clist/get",
]

PAGE_SIZE = 100
MAX_PAGES = 2
CHINA_TZ = ZoneInfo("Asia/Shanghai")

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
                  "AppleWebKit/537.36 Chrome/131.0.0.0 Safari/537.36",
    "Referer": "https://quote.eastmoney.com/",
    "Accept": "application/json, text/plain, */*",
}


def to_number(value, integer=False):
    """转换行情数值，将无效数据表示为 None。"""
    if value in (None, "", "-", "--"):
        return None

    try:
        number = float(value)
        return int(number) if integer else number
    except (ValueError, TypeError):
        return None


def create_database():
    """创建股票行情数据表。"""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)

    conn.execute("""
        CREATE TABLE IF NOT EXISTS stocks (
            snapshot_date TEXT NOT NULL,
            stock_code TEXT NOT NULL,
            stock_name TEXT NOT NULL,
            market TEXT NOT NULL,
            latest_price REAL,
            change_pct REAL,
            change_amount REAL,
            volume INTEGER,
            turnover REAL,
            high_price REAL,
            low_price REAL,
            open_price REAL,
            prev_close REAL,
            collected_at TEXT NOT NULL,
            PRIMARY KEY (snapshot_date, market, stock_code)
        )
    """)

    conn.commit()
    return conn


def fetch_stock_page(session, page):
    """请求一页股票列表，并解析 JSON。"""
    params = {
        "pn": page,
        "pz": PAGE_SIZE,
        "po": 1,
        "np": 1,
        "ut": "bd1d9ddb04089700cf9c27f6f7426281",
        "fltt": 2,
        "invt": 2,
        "fid": "f12",
        "fs": "m:0 t:6,m:0 t:80,m:1 t:2,m:1 t:23",
        "fields": (
            "f2,f3,f4,f5,f6,f12,f13,f14,"
            "f15,f16,f17,f18"
        ),
    }

    errors = []

    for api_url in API_URLS:
        try:
            response = session.get(
                api_url,
                params=params,
                headers=HEADERS,
                timeout=20,
            )
            response.raise_for_status()

            # 按作业要求，使用 json 模块解析返回内容
            result = json.loads(response.text)
            data = result.get("data")

            if not isinstance(data, dict):
                raise ValueError("接口未返回有效 data")

            diff = data.get("diff")

            if isinstance(diff, dict):
                diff = list(diff.values())

            if not isinstance(diff, list) or not diff:
                raise ValueError("股票列表为空")

            if page == 1:
                JSON_PATH.write_text(
                    json.dumps(
                        result, ensure_ascii=False, indent=2
                    ),
                    encoding="utf-8",
                )

            print(f"接口：{api_url}")
            print(f"当前页：{page}，股票数：{len(diff)}")

            return diff

        except (
            requests.RequestException,
            ValueError,
            TypeError,
        ) as exc:
            errors.append(f"{api_url}: {exc}")

    raise RuntimeError(
        "所有行情接口均请求失败：\n" + "\n".join(errors)
    )


def parse_stocks(items, snapshot_date, collected_at):
    """提取股票代码、价格和成交等信息。"""
    records = []

    for item in items:
        code = str(item.get("f12") or "").strip()
        name = str(item.get("f14") or "").strip()
        market_id = item.get("f13")

        if not code or not name:
            continue

        if market_id == 1:
            market = "上海"
        elif market_id == 0:
            market = "深圳"
        else:
            market = str(market_id)

        records.append((
            snapshot_date,
            code,
            name,
            market,
            to_number(item.get("f2")),
            to_number(item.get("f3")),
            to_number(item.get("f4")),
            to_number(item.get("f5"), integer=True),
            to_number(item.get("f6")),
            to_number(item.get("f15")),
            to_number(item.get("f16")),
            to_number(item.get("f17")),
            to_number(item.get("f18")),
            collected_at,
        ))

    return records


def save_stocks(conn, records):
    """存入 SQLite，相同股票在同一天重复采集则更新。"""
    conn.executemany("""
        INSERT INTO stocks VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
        ON CONFLICT(snapshot_date, market, stock_code)
        DO UPDATE SET
            stock_name = excluded.stock_name,
            latest_price = excluded.latest_price,
            change_pct = excluded.change_pct,
            change_amount = excluded.change_amount,
            volume = excluded.volume,
            turnover = excluded.turnover,
            high_price = excluded.high_price,
            low_price = excluded.low_price,
            open_price = excluded.open_price,
            prev_close = excluded.prev_close,
            collected_at = excluded.collected_at
    """, records)
    conn.commit()


def main():
    """采集前两页沪深 A 股行情并检查数据库。"""
    print("========== 东方财富股票爬虫 ==========")

    now = datetime.now(CHINA_TZ)
    snapshot_date = now.date().isoformat()
    collected_at = now.isoformat(timespec="seconds")

    all_records = []

    with requests.Session() as session:
        for page in range(1, MAX_PAGES + 1):
            items = fetch_stock_page(session, page)
            records = parse_stocks(
                items, snapshot_date, collected_at
            )

            all_records.extend(records)
            print(f"成功解析：{len(records)} 条\n")

            time.sleep(1.5)

    if not all_records:
        raise RuntimeError("未解析到任何股票数据")

    with create_database() as conn:
        save_stocks(conn, all_records)

        count = conn.execute("""
            SELECT COUNT(*)
            FROM stocks
            WHERE snapshot_date = ?
        """, (snapshot_date,)).fetchone()[0]

        print("========== 数据库统计 ==========")
        print(f"本次解析记录：{len(all_records)}")
        print(f"今日数据库记录：{count}")
        print(f"数据库路径：{DB_PATH}")

        print("\n========== 股票样本 ==========")

        rows = conn.execute("""
            SELECT stock_code, stock_name, market,
                   latest_price, change_pct
            FROM stocks
            WHERE snapshot_date = ?
            ORDER BY stock_code
            LIMIT 10
        """, (snapshot_date,)).fetchall()

        for row in rows:
            print(row)

    print(f"\nJSON 样本保存于：{JSON_PATH}")
    print("========== 采集完成 ==========")


if __name__ == "__main__":
    main()

实际运行结果：

• 本次数据验证记录数：200
• 数据库文件：data/stocks.db
• JSON 样本：data/stock_api_sample.json

运行截图：

JSON 结构截图：

2. 作业心得

本次作业完成了网页解析和 JSON 接口解析两种数据采集方式。

天气数据采集使用 BeautifulSoup 解析 HTML，
遇到了数字开头的 ID 不能直接写为普通 CSS ID 选择器的问题。
经过修改，使用属性选择器定位七日天气数据，成功完成采集。

股票采集使用 requests 与 json 模块直接处理接口数据，
避免了从网页展示文本中逐项提取股票信息。
通过分页请求和 SQLite 批量写入，实现了多条行情数据的保存。

两项作业也让我了解了网页结构变化、请求异常、
数据缺失和数据库重复写入等实际问题。
SQLite 的主键及冲突更新机制，有助于提高数据存储的可靠性。

二、2021年中国大学排名采集

1. 作业代码与结果展示

作业③：2021年软科中国大学主榜采集

实验目标： 获取 2021 年软科中国大学主榜全部院校信息，
提取排名、学校名称、省市、类型及总分，并保存至数据库。

数据来源：

https://www.shanghairanking.cn/rankings/bcur/2021

数据接口：

/api/pub/v1/bcur?bcur_type=11&year=2021

实现流程：

1. 分析排名页面对应的数据请求。
2. 请求接口并提取 JSON 的 data.rankings 数组。
3. 解析学校排名、学校名称、省市、类型和总分。
4. 检查数据总量及学校名称唯一性。
5. 保存到 SQLite、CSV 和 JSON 文件。

完整 Python 代码：

"""
第二次作业 - 作业③
2021年软科中国大学主榜数据采集

目标：
1. 请求软科排名数据接口
2. 解析582所院校的排名、名称、省市、类型、总分
3. 保存JSON、CSV和SQLite数据库
4. 验证数据完整性
"""

import csv
import json
import sqlite3
from datetime import datetime
from pathlib import Path

import requests


PROJECT_DIR = Path(__file__).resolve().parent.parent
DATA_DIR = PROJECT_DIR / "data"

DB_PATH = DATA_DIR / "university_2021.db"
CSV_PATH = DATA_DIR / "university_2021.csv"
JSON_PATH = DATA_DIR / "university_2021_raw.json"

API_URL = "https://www.shanghairanking.cn/api/pub/v1/bcur"

PARAMS = {
    "bcur_type": 11,
    "year": 2021,
}

EXPECTED_COUNT = 582

HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
        "AppleWebKit/537.36 Chrome/131.0.0.0 Safari/537.36"
    ),
    "Referer": (
        "https://www.shanghairanking.cn/rankings/bcur/2021"
    ),
    "Accept": "application/json, text/plain, */*",
}


def fetch_rankings():
    """获取软科大学排名JSON数据。"""
    print("========== 软科2021大学排名爬虫 ==========")
    print(f"接口：{API_URL}")
    print(f"参数：{PARAMS}")

    response = requests.get(
        API_URL,
        params=PARAMS,
        headers=HEADERS,
        timeout=30,
    )
    response.raise_for_status()

    try:
        result = response.json()
    except ValueError as exc:
        raise RuntimeError(
            "接口返回内容不是有效JSON，"
            f"响应前200字符：{response.text[:200]}"
        ) from exc

    data = result.get("data")
    rankings = (
        data.get("rankings")
        if isinstance(data, dict)
        else None
    )

    if not isinstance(rankings, list):
        raise ValueError(
            "未找到data.rankings列表；"
            f"顶层字段：{list(result)}"
        )

    print(f"接口返回院校数：{len(rankings)}")

    return result, rankings


def parse_score(value):
    """解析总分，未公布的分数保存为NULL。"""
    if value in (None, "", "-", "--"):
        return None

    try:
        return float(value)
    except (ValueError, TypeError):
        return None


def parse_rankings(rankings):
    """提取符合要求的五项字段。"""
    collected_at = datetime.now().isoformat(
        timespec="seconds"
    )
    records = []

    for item in rankings:
        if not isinstance(item, dict):
            continue

        rank = (
            item.get("ranking")
            or item.get("rankOverall")
            or item.get("rank")
        )
        name = (
            item.get("univNameCn")
            or item.get("univName")
        )

        if rank is None or not name:
            continue

        record = (
            2021,
            str(rank).strip(),
            str(name).strip(),
            str(item.get("province") or "").strip(),
            str(item.get("univCategory") or "").strip(),
            parse_score(item.get("score")),
            collected_at,
        )

        records.append(record)

    print(f"成功解析：{len(records)}所高校")
    return records


def validate_records(records):
    """检查总数、重复院校及头部数据。"""
    if len(records) != EXPECTED_COUNT:
        raise ValueError(
            f"完整性检查失败：预期{EXPECTED_COUNT}条，"
            f"实际{len(records)}条"
        )

    names = [record[2] for record in records]
    if len(set(names)) != EXPECTED_COUNT:
        raise ValueError("发现重复学校名称")

    first = records[0]

    if first[1] != "1" or first[2] != "清华大学":
        raise ValueError(
            f"首条数据异常：排名={first[1]}，学校={first[2]}"
        )

    if first[5] is None or abs(first[5] - 969.2) > 0.01:
        raise ValueError(
            "清华大学2021年总分与官方数据不一致"
        )

    print("✓ 582所院校数量检查通过")
    print("✓ 学校名称唯一性检查通过")
    print("✓ 首位学校和总分检查通过")


def save_database(records):
    """创建SQLite数据表并批量写入。"""
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS university_rankings (
                year INTEGER NOT NULL,
                ranking TEXT NOT NULL,
                university_name TEXT NOT NULL,
                province TEXT,
                university_type TEXT,
                total_score REAL,
                collected_at TEXT NOT NULL,
                PRIMARY KEY (year, university_name)
            )
        """)

        # 仅替换2021年数据
        conn.execute(
            "DELETE FROM university_rankings WHERE year = ?",
            (2021,),
        )

        conn.executemany("""
            INSERT INTO university_rankings (
                year, ranking, university_name,
                province, university_type,
                total_score, collected_at
            )
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, records)

        count = conn.execute("""
            SELECT COUNT(*)
            FROM university_rankings
            WHERE year = 2021
        """).fetchone()[0]

        if count != EXPECTED_COUNT:
            raise ValueError(
                f"数据库校验失败：实际{count}条"
            )

    return count


def save_files(result, records):
    """保留原始JSON并导出CSV。"""
    JSON_PATH.write_text(
        json.dumps(result, ensure_ascii=False, indent=2),
        encoding="utf-8",
    )

    with CSV_PATH.open(
        "w", newline="", encoding="utf-8-sig"
    ) as file:
        writer = csv.writer(file)

        writer.writerow([
            "年份", "排名", "学校名称",
            "省市", "类型", "总分", "采集时间"
        ])
        writer.writerows(records)


def main():
    """主程序。"""
    DATA_DIR.mkdir(parents=True, exist_ok=True)

    result, rankings = fetch_rankings()
    records = parse_rankings(rankings)

    validate_records(records)

    count = save_database(records)
    save_files(result, records)

    print("\n========== 前20所高校 ==========")
    print("排名 | 学校名称 | 省市 | 类型 | 总分")

    for row in records[:20]:
        print(
            f"{row[1]} | {row[2]} | "
            f"{row[3]} | {row[4]} | {row[5]}"
        )

    print("\n========== 数据库统计 ==========")
    print(f"院校总数：{count}")
    print(f"数据库：{DB_PATH}")
    print(f"CSV文件：{CSV_PATH}")
    print(f"原始JSON：{JSON_PATH}")
    print("========== 采集完成 ==========")


if __name__ == "__main__":
    main()

实际运行结果：

• 成功采集院校：582 所
• 数据库文件：data/university_2021.db
• CSV 文件：data/university_2021.csv
• JSON 文件：data/university_2021_raw.json

运行截图：

F12 抓包分析 GIF：

2. 作业心得

本次作业将数据采集对象从实时天气与股票行情，
扩展到历史大学排名数据。

通过对软科数据接口的分析，我学习了如何识别网页数据请求，
以及如何从较大的 JSON 对象中提取指定字段。

与只采集网页展示的部分记录不同，
本次通过接口获得了完整的 582 所高校数据。
程序还增加了数量检查、重复学校名称检查和
排名第一学校及总分的验证，减少了数据不完整的风险。

同时，SQLite、CSV 和 JSON 三种保存方式
也让我理解了结构化存储与原始数据留存的区别。
后续可以进一步利用这些数据开展排序、统计及可视化分析。