数据采集第二次作业
第二次线上作业
本次作业完成三个定向采集任务并全部入库,运行环境 Python 3.14 + requests + BeautifulSoup + sqlite3。
一、天气预报与股票行情的定向采集(作业①、作业②)
1)实验代码与运行结果
作业① 中国气象网 7 日天气预报
"""作业①:爬取中国气象网给定城市集未来 7 天天气预报,并保存到 SQLite 数据库。
运行环境:Python 3.10+,依赖 requests、BeautifulSoup4。
数据来源:中国气象网城市天气页 http://www.weather.com.cn/weather/<城市编码>.shtml
"""
from __future__ import annotations
import sqlite3
from datetime import datetime
from pathlib import Path
import requests
from bs4 import BeautifulSoup
# 城市集:城市名 -> 中国气象网城市编码(编码可从 http://www.weather.com.cn 搜索城市后从地址栏获得)
CITY_CODES = {
"北京": "101010100",
"上海": "101020100",
"广州": "101280101",
"福州": "101230101",
}
URL_TEMPLATE = "http://www.weather.com.cn/weather/{code}.shtml"
HEADERS = {
"User-Agent": (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
),
"Accept-Language": "zh-CN,zh;q=0.9",
}
DB_PATH = Path(__file__).resolve().parent / "data" / "weather.db"
def fetch_page(city_code: str) -> str:
"""下载指定城市的天气页面 HTML 文本。"""
url = URL_TEMPLATE.format(code=city_code)
response = requests.get(url, headers=HEADERS, timeout=20)
response.raise_for_status()
# 中国气象网页面为 UTF-8 编码,显式指定可避免中文乱码
response.encoding = "utf-8"
return response.text
def parse_forecast(html_text: str, city: str) -> list[dict]:
"""解析页面中 ul.t 下的 7 个 li,返回每天的天气记录。"""
soup = BeautifulSoup(html_text, "html.parser")
forecast_list = soup.select("ul.t li")
if not forecast_list:
raise RuntimeError(f"未在 {city} 的页面中解析到预报列表")
records = []
for item in forecast_list[:7]:
date_text = item.select_one("h1").get_text(strip=True)
weather = item.select_one("p.wea").get_text(strip=True)
# tem 节点中 span 是最高温、i 是最低温;第一天的 li 只有 i(当天已过最高温时段)
high_node = item.select_one("p.tem span")
low_node = item.select_one("p.tem i")
temp_high = int(high_node.get_text(strip=True).replace("℃", "")) if high_node else None
temp_low = int(low_node.get_text(strip=True).replace("℃", "")) if low_node else None
wind_nodes = item.select("p.win em span")
wind = "、".join(node.get("title", "") for node in wind_nodes if node.get("title"))
wind_level = item.select_one("p.win i").get_text(strip=True)
records.append({
"city": city,
"date_text": date_text,
"weather": weather,
"temp_high": temp_high,
"temp_low": temp_low,
"wind": wind,
"wind_level": wind_level,
})
return records
def save_to_db(records: list[dict]) -> int:
"""把预报记录写入 SQLite,返回写入条数。"""
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
with sqlite3.connect(DB_PATH) as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS weather_7day (
id INTEGER PRIMARY KEY AUTOINCREMENT,
city TEXT NOT NULL,
date_text TEXT NOT NULL,
weather TEXT,
temp_high INTEGER,
temp_low INTEGER,
wind TEXT,
wind_level TEXT,
fetch_time TEXT NOT NULL,
UNIQUE (city, date_text)
)
"""
)
fetch_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
rows = [
(r["city"], r["date_text"], r["weather"], r["temp_high"],
r["temp_low"], r["wind"], r["wind_level"], fetch_time)
for r in records
]
# 同一城市同一天重复采集时用 REPLACE 覆盖,保证表内是最新一次结果
conn.executemany(
"""
INSERT OR REPLACE INTO weather_7day
(city, date_text, weather, temp_high, temp_low, wind, wind_level, fetch_time)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""",
rows,
)
conn.commit()
return len(rows)
def show_from_db() -> None:
"""从数据库中读回数据并打印,验证入库结果。"""
with sqlite3.connect(DB_PATH) as conn:
cursor = conn.execute(
"""
SELECT city, date_text, weather, temp_high, temp_low, wind, wind_level
FROM weather_7day ORDER BY city, id
"""
)
print(f"\n数据库 data/{DB_PATH.name} 中 weather_7day 表的内容:")
print(f"{'城市':<5} | {'日期':<9} | {'天气':<7} | {'最高':>5} | {'最低':>5} | {'风向':<13} | 风力")
for city, date_text, weather, high, low, wind, level in cursor:
high_text = "—" if high is None else str(high)
low_text = "—" if low is None else str(low)
print(f"{city:<5} | {date_text:<9} | {weather:<7} | {high_text:>5} | "
f"{low_text:>5} | {wind:<13} | {level}")
def main() -> None:
all_records = []
print("=" * 78)
print("作业① 中国气象网 7 日天气预报采集")
print(f"城市集:{'、'.join(CITY_CODES)} 数据库:data/weather.db")
print("=" * 78)
for city, code in CITY_CODES.items():
html_text = fetch_page(code)
records = parse_forecast(html_text, city)
all_records.extend(records)
print(f"\n【{city}】{URL_TEMPLATE.format(code=code)} 共解析 {len(records)} 天")
for r in records:
high = r["temp_high"] if r["temp_high"] is not None else "—"
print(f" {r['date_text']:<12}{r['weather']:<8}"
f"{high!s:>4}/{r['temp_low']!s:<4}℃ {r['wind']} {r['wind_level']}")
count = save_to_db(all_records)
print("\n" + "-" * 78)
print(f"已写入数据库 data/{DB_PATH.name},本次写入 {count} 条记录")
show_from_db()
if __name__ == "__main__":
main()


作业② 沪深 A 股行情(requests + json)
"""作业②:用 requests + json 解析新浪股票行情接口,并保存到 SQLite 数据库。
选题说明:作业给出的候选网站是东方财富网和新浪股票。东方财富的行情接口
push2.eastmoney.com/api/qt/clist/get 在部分网络环境下会被限制(返回 502 或
ProxyError),因此改用新浪股票的行情列表接口,同样满足“requests 请求 +
json 解析 + 入库”的要求。
接口:http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/
Market_Center.getHQNodeData?page=1&num=100&sort=changepercent&asc=0&node=hs_a
"""
from __future__ import annotations
import json
import sqlite3
import time
from datetime import datetime
from pathlib import Path
import requests
API_URL = (
"http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/"
"Market_Center.getHQNodeData"
)
HEADERS = {
"User-Agent": (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
),
"Referer": "http://finance.sina.com.cn/stock/",
}
DB_PATH = Path(__file__).resolve().parent / "data" / "stock.db"
PAGES = 3 # 抓取前 3 页,每页 100 条,合计约 300 只股票
PAGE_SIZE = 100
# 接口字段 -> 数据库列名(字段含义见新浪行情接口说明)
FIELD_MAP = {
"code": "code",
"name": "name",
"trade": "price", # 最新价
"changepercent": "change_pct", # 涨跌幅 %
"pricechange": "change", # 涨跌额
"open": "open",
"high": "high",
"low": "low",
"settlement": "prev_close", # 昨收
"volume": "volume", # 成交量(股)
"amount": "amount", # 成交额(元)
"turnoverratio": "turnover", # 换手率 %
"per": "pe", # 市盈率
"pb": "pb",
"mktcap": "market_cap", # 总市值(万元)
}
def fetch_page(page: int) -> list[dict]:
"""请求一页行情数据,返回股票记录列表。"""
params = {
"page": page,
"num": PAGE_SIZE,
"sort": "changepercent",
"asc": 0,
"node": "hs_a", # 沪深 A 股
"symbol": "",
"_s_r_a": "page",
}
response = None
last_error = None
for _ in range(3):
try:
response = requests.get(API_URL, params=params, headers=HEADERS, timeout=20)
response.raise_for_status()
break
except requests.RequestException as error:
last_error = error
time.sleep(1)
if response is None:
raise RuntimeError(f"第 {page} 页请求失败:{last_error}")
# 接口返回的是 JSON 数组,个别页码可能返回 null,需要先做判断
payload = json.loads(response.text)
if not payload:
return []
records = []
for item in payload:
record = {}
for field, column in FIELD_MAP.items():
value = item.get(field)
if column in ("code", "name"):
record[column] = value or ""
else:
try:
record[column] = float(value) if value not in (None, "") else None
except (TypeError, ValueError):
record[column] = None
records.append(record)
return records
def save_to_db(stocks: list[dict]) -> int:
"""写入 SQLite,股票代码为主键,重复采集时更新为最新行情。"""
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
with sqlite3.connect(DB_PATH) as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS stock_quote (
code TEXT PRIMARY KEY,
name TEXT NOT NULL,
price REAL, change_pct REAL, change REAL,
open REAL, high REAL, low REAL, prev_close REAL,
volume REAL, amount REAL, turnover REAL,
pe REAL, pb REAL, market_cap REAL,
trade_time TEXT NOT NULL
)
"""
)
trade_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
rows = [
(s["code"], s["name"], s["price"], s["change_pct"], s["change"],
s["open"], s["high"], s["low"], s["prev_close"], s["volume"],
s["amount"], s["turnover"], s["pe"], s["pb"], s["market_cap"], trade_time)
for s in stocks
]
conn.executemany(
"""
INSERT OR REPLACE INTO stock_quote
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
rows,
)
conn.commit()
return len(rows)
def show_from_db(limit: int = 10) -> None:
"""按涨跌幅倒序读回数据库,验证入库结果。"""
with sqlite3.connect(DB_PATH) as conn:
total = conn.execute("SELECT COUNT(*) FROM stock_quote").fetchone()[0]
print(f"\n数据库 data/{DB_PATH.name} 中 stock_quote 表共 {total} 条记录")
print(f"{'代码':<7} | {'名称':<9} | {'最新价':>8} | {'涨跌幅%':>8} | "
f"{'成交额(亿)':>9} | {'换手%':>7}")
for code, name, price, change_pct, amount, turnover in conn.execute(
"""
SELECT code, name, price, change_pct, amount, turnover
FROM stock_quote ORDER BY change_pct DESC LIMIT ?
""",
(limit,),
):
amount_yi = f"{amount / 1e8:.2f}" if amount else "-"
price_text = f"{price:.2f}" if price is not None else "-"
pct_text = f"{change_pct:.2f}" if change_pct is not None else "-"
turnover_text = f"{turnover:.2f}" if turnover is not None else "-"
print(f"{code:<7} | {name:<9} | {price_text:>8} | {pct_text:>8} | "
f"{amount_yi:>9} | {turnover_text:>7}")
def main() -> None:
print("=" * 78)
print("作业② 新浪股票沪深 A 股行情采集(requests + json 解析)")
print(f"接口:{API_URL}")
print(f"参数:node=hs_a sort=changepercent asc=0 num={PAGE_SIZE} 共 {PAGES} 页")
print("=" * 78)
all_stocks: dict[str, dict] = {}
for page in range(1, PAGES + 1):
stocks = fetch_page(page)
print(f"第 {page} 页:解析到 {len(stocks)} 条记录")
for item in stocks:
all_stocks[item["code"]] = item
stocks = list(all_stocks.values())
print(f"\n三页汇总去重后共 {len(stocks)} 只股票,按涨跌幅排序前 10 名:")
for item in stocks[:10]:
amount_yi = item["amount"] / 1e8 if item["amount"] else 0
print(f" {item['code']:<7}{item['name']:<9}"
f"{item['price']:>8.2f} {item['change_pct']:>7.2f}%"
f" 成交额 {amount_yi:>7.2f} 亿")
count = save_to_db(stocks)
print("\n" + "-" * 78)
print(f"已写入数据库 data/{DB_PATH.name},本次写入 {count} 条记录")
show_from_db()
if __name__ == "__main__":
main()


2)作业心得
先看清数据在哪,再动手写解析;接口不稳定时把取数与入库解耦;空值、字符串数值、唯一键这些细节决定数据质量。
二、软科 2021 中国大学排名全量采集(作业③)
1)实验代码、F12 抓包过程与运行结果


"""作业③:爬取软科中国大学排名 2021 主榜全部院校,并保存到 SQLite 数据库。
抓包思路:排名页 https://www.shanghairanking.cn/rankings/bcur/2021 的表格由
JavaScript 渲染,直接在 HTML 源码里看不到数据。按 F12 打开 Network 面板,
切换到 XHR/JS 分类并刷新页面,可以看到一条
https://www.shanghairanking.cn/api/pub/v1/bcur?bcur_type=11&year=2021
接口请求,响应 JSON 的 data.rankings 就是全部院校记录。
"""
from __future__ import annotations
import json
import sqlite3
import time
from pathlib import Path
import requests
API_URL = "https://www.shanghairanking.cn/api/pub/v1/bcur"
PARAMS = {"bcur_type": "11", "year": "2021"} # bcur_type=11 为主榜
HEADERS = {
"User-Agent": (
"Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
"AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"
),
"Referer": "https://www.shanghairanking.cn/rankings/bcur/2021",
}
DB_PATH = Path(__file__).resolve().parent / "data" / "ranking.db"
def fetch_rankings() -> list[dict]:
"""请求排名接口,返回按名次排好的院校记录列表。"""
response = None
last_error = None
for _ in range(3):
try:
response = requests.get(API_URL, params=PARAMS, headers=HEADERS, timeout=30)
response.raise_for_status()
break
except requests.RequestException as error:
last_error = error
time.sleep(1)
if response is None:
raise RuntimeError(f"接口请求失败:{last_error}")
payload = json.loads(response.text)
if payload.get("code") != 200:
raise RuntimeError(f"接口返回异常:{payload.get('code')} {payload.get('msg')}")
rankings = payload["data"]["rankings"]
records = []
for item in rankings:
records.append({
"ranking": item.get("ranking", ""),
"name_cn": item.get("univNameCn", ""),
"name_en": item.get("univNameEn", ""),
"province": item.get("province", ""),
"category": item.get("univCategory", ""),
"score": item.get("score"),
"tags": "、".join(item.get("univTags") or []),
})
# 接口按名次升序返回,这里按名次数值再排一次,兼容并列名次的情况
records.sort(key=lambda r: (int(r["ranking"]) if str(r["ranking"]).isdigit() else 9999))
return records
def save_to_db(records: list[dict]) -> int:
"""把全部院校写入 SQLite,名次 + 学校作为唯一键。"""
DB_PATH.parent.mkdir(parents=True, exist_ok=True)
with sqlite3.connect(DB_PATH) as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS university_2021 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ranking TEXT NOT NULL,
name_cn TEXT NOT NULL,
name_en TEXT,
province TEXT,
category TEXT,
score REAL,
tags TEXT,
UNIQUE (ranking, name_cn)
)
"""
)
conn.executemany(
"""
INSERT OR REPLACE INTO university_2021
(ranking, name_cn, name_en, province, category, score, tags)
VALUES (?, ?, ?, ?, ?, ?, ?)
""",
[(r["ranking"], r["name_cn"], r["name_en"], r["province"],
r["category"], r["score"], r["tags"]) for r in records],
)
conn.commit()
return len(records)
def show_from_db() -> None:
"""从数据库中读回前 10 名和统计信息,验证入库结果。"""
with sqlite3.connect(DB_PATH) as conn:
total = conn.execute("SELECT COUNT(*) FROM university_2021").fetchone()[0]
print(f"\n数据库 {DB_PATH.name} 中 university_2021 表共 {total} 条记录")
print(f"{'排名':<6}{'学校':<16}{'省市':<8}{'类型':<8}{'总分':>8}")
for ranking, name, province, category, score in conn.execute(
"""
SELECT ranking, name_cn, province, category, score
FROM university_2021 ORDER BY CAST(ranking AS INTEGER) LIMIT 10
"""
):
print(f"{ranking:<6}{name:<16}{province:<8}{category:<8}{score!s:>8}")
def main() -> None:
print("=" * 78)
print("作业③ 软科中国大学排名 2021 主榜全量采集")
print(f"页面:https://www.shanghairanking.cn/rankings/bcur/2021")
print(f"接口:{API_URL}?bcur_type=11&year=2021")
print("=" * 78)
records = fetch_rankings()
print(f"\n接口返回院校 {len(records)} 所,其中前 10 名:")
for r in records[:10]:
print(f" {r['ranking']:<5}{r['name_cn']:<14}{r['province']:<6}"
f"{r['category']:<6}{r['score']}")
count = save_to_db(records)
print("\n" + "-" * 78)
print(f"已写入数据库 data/ranking.db,本次写入 {count} 条记录")
show_from_db()
if __name__ == "__main__":
main()


2)作业心得
能抓接口就不硬解析页面;Network 面板要会过滤 Fetch/XHR;入库后做数量对账;抓包 GIF 记录过程。

浙公网安备 33010602011771号