把热榜存下来：用 SQLAlchemy + PostgreSQL 设计 A股/美股/基金榜单数据模型

引言：为什么要把热榜落库

前几篇我们已经解决了「怎么抓」——用 httpx 监听百度金融 sapi/v1/ranks 接口，拿到了 A股、美股、基金三类实时热榜。但只把数据打印到控制台或存成 CSV，价值其实非常有限：

• 无法回溯历史：热榜的精髓在于「变化」。今天涨得最猛的股票和三天前的可能完全不同，而 CSV 平铺之后，时间序列分析几乎无从下手。
• 无法去重与对账：同一支标的在一次抓取里可能重复出现，多个榜单之间还有交集，需要一张维度表来归一。
• 查询低效：想查「某基金过去 7 天进入热榜 Top10 的次数」，CSV 要全量扫描，而 PostgreSQL 一个索引就能秒回。

所以本篇的核心目标只有一句话：设计一套能长期、可追溯、易查询的榜单数据模型，把每次抓取变成一个「快照」，把榜单里的每一项变成快照下的「记录」。

顺带一提：落库的前提是「抓得到、抓得稳」。下面第 3 节我会先讲如何接入亿牛云代理 IP，把采集的稳定性问题前置解决，否则后面再漂亮的表结构也只是无米之炊。

一、技术选型：为什么是 SQLAlchemy + PostgreSQL

维度	选择	理由
ORM	SQLAlchemy 2.0	类型注解友好（Mapped/mapped_column），生态成熟，迁移工具 Alembic 开箱即用
数据库	PostgreSQL 15+	原生 JSONB 兜底半结构化字段、丰富索引（BRIN 适合时序）、CTE 写分析查询很顺手
驱动	psycopg (psycopg3)	SQLAlchemy 2.0 官方推荐，异步支持好
重试	tenacity	为代理请求叠加指数退避重试，提升采集健壮性

为什么不用 MySQL？榜单附带大量行情快照字段（价格、涨跌幅、成交额），后续还会追加不固定的扩展字段，PostgreSQL 的 JSONB 能优雅兜底，避免频繁 ALTER TABLE。

依赖（放到 pyproject.toml）：

dependencies = [
    "sqlalchemy>=2.0",
    "psycopg[binary]>=3.1",
    "pydantic>=2.0",       # 校验接口返回的每条记录
    "tenacity>=8.0",       # 代理请求重试
]

二、理解 ranks 接口的数据结构

动手建表前，先看清接口到底返回什么。百度金融 sapi/v1/ranks 的核心字段（已简化）大致是：

{
  "data": {
    "list": [
      {
        "code": "SH600519",
        "name": "贵州茅台",
        "cur_price": "1680.00",
        "change_rate": "+2.31%",
        "board_rank": 1,
        "turnover_ratio": "0.42%"
      }
    ]
  }
}

关键洞察：一次请求 = 一个市场的一个榜单 = 一个快照。A股、美股、基金分别请求，所以「市场（market）」和「榜单类型（board）」是天然的维度。另外注意，价格、涨跌幅都是字符串（百度的老坑），落库前必须清洗成数值。

三、采集稳定性：接入代理 IP

热榜要长期采集，最大的敌人是「IP 被限频/封禁」。一旦出口 IP 被百度金融拉黑，后面整张表都无从填充。这里我用亿牛云隧道代理来兜底——它最大的好处是「每次请求自动切换出口 IP」，天然规避单 IP 的频率限制，对爬虫极其友好。

亿牛云的隧道代理是一个固定的转发入口，认证信息（用户名/密码）由订单分配，建议从环境变量读取，切勿硬编码进代码库：

import os

import httpx
from tenacity import retry, stop_after_attempt, wait_exponential

# 亿牛云隧道代理入口：认证信息从环境变量读取，避免密钥泄露
YINIU_TUNNEL = (
    f"http://{os.getenv('YINIU_USER')}:{os.getenv('YINIU_PASS')}"
    "@tunnel.ip3366.net:9020"  # 端口以你购买的套餐为准
)


@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1))
def fetch_ranks(market: str, board: str) -> dict:
    """经亿牛云隧道代理请求百度金融 ranks 接口。

    隧道代理会在每次请求时自动轮换出口 IP，从根上规避单 IP 频率限制；
    配合 tenacity 的指数退避，偶发网络抖动也能自愈。

    Args:
        market: 市场代码 ('A'/'US'/'FUND')
        board: 榜单类型
    Returns:
        接口返回的 JSON（已 parsed）
    Raises:
        httpx.HTTPStatusError: 重试耗尽后仍为非 2xx
    """
    with httpx.Client(
        proxies={"http://": YINIU_TUNNEL, "https://": YINIU_TUNNEL},
        timeout=10.0,
        headers={"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"},
    ) as client:
        resp = client.get(
            "https://finance-guoxin-api.baidu.com/sapi/v1/ranks",
            params={"market": market, "board": board},
        )
        resp.raise_for_status()
        return resp.json()

几个工程要点：

• 隧道 vs 动态转发：隧道代理适合「每次请求换 IP」的高频场景；若你买的是短效独享 IP（IP:PORT 形式），则需自己维护一个 IP 池并定时刷新，复杂度更高。新手直接用隧道代理最省心。
• 代理要覆盖 http 和 https：百度金融接口是 HTTPS，但 proxies 字典里两个协议都配上才稳妥，避免某些 httpx 版本走直连。
• 超时 + 重试缺一不可：代理链路比直连多一跳，10s 超时配合 3 次指数退避，能过滤掉绝大多数瞬时失败。

四、数据模型设计（核心）

我们采用**「维度表 + 快照表 + 记录表」**三层结构，这是时序榜单数据最稳的范式：

security (维度表: 标的主数据)
   │ 1
   │ ──── N
rank_snapshot (快照表: 每次抓取)
   │ 1
   │ ──── N
rank_record (记录表: 快照中的每一项)

4.1 三张表职责

• security：(symbol, name, market, security_type)。一支标的只存一行，靠 symbol 去重，解决跨榜单重复问题。
• rank_snapshot：(market, board, fetched_at, source)。代表某次抓取动作，是所有记录的根。
• rank_record：(snapshot_id, security_id, board_rank, price, change_pct, extra)。每条热榜项一行，extra 用 JSONB 兜底。

4.2 SQLAlchemy 模型代码

from datetime import datetime, timezone

from sqlalchemy import (
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    UniqueConstraint,
    Index,
    func,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
)


class Base(DeclarativeBase):
    """所有模型的基类，统一元数据。"""


class Security(Base):
    """标的主数据维度表：A股/美股/基金去重后的唯一身份。"""

    __tablename__ = "security"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    symbol: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    name: Mapped[str] = mapped_column(String(64), index=True)
    market: Mapped[str] = mapped_column(String(16))  # 'A' / 'US' / 'FUND'
    security_type: Mapped[str] = mapped_column(String(16))

    __table_args__ = (Index("ix_security_market_type", "market", "security_type"),)


class RankSnapshot(Base):
    """榜单快照：每次对 ranks 接口的完整抓取。"""

    __tablename__ = "rank_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    market: Mapped[str] = mapped_column(String(16), index=True)
    board: Mapped[str] = mapped_column(String(32), index=True)
    # 用服务端时间，带时区，保证跨机器一致
    fetched_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True
    )
    source: Mapped[str] = mapped_column(String(64), default="baidu_finance")

    records: Mapped[list["RankRecord"]] = relationship(back_populates="snapshot")


class RankRecord(Base):
    """快照中的单条榜单记录。"""

    __tablename__ = "rank_record"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    snapshot_id: Mapped[int] = mapped_column(
        ForeignKey("rank_snapshot.id", ondelete="CASCADE"), index=True
    )
    security_id: Mapped[int] = mapped_column(
        ForeignKey("security.id"), index=True
    )
    board_rank: Mapped[int] = mapped_column(Integer)        # 榜单内名次
    price: Mapped[float | None] = mapped_column(Float)       # 清洗后的数值价
    change_pct: Mapped[float | None] = mapped_column(Float)  # 涨跌幅，单位 %
    extra: Mapped[dict] = mapped_column(JSONB, default=dict)  # 兜底字段

    snapshot: Mapped["RankSnapshot"] = relationship(back_populates="records")

    __table_args__ = (
        UniqueConstraint("snapshot_id", "security_id", name="uq_snap_security"),
    )

五、落库逻辑：快照 + 增量写入

写入必须幂等：同一批次重复跑不能插出重复记录。策略是「先 upsert security，再建 snapshot，最后 bulk 插入 record」。

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession


async def save_snapshot(
    session: AsyncSession,
    market: str,
    board: str,
    items: list[dict],
) -> None:
    """把一次抓取结果落库。items 为清洗后的榜单项列表。

    Args:
        session: 异步数据库会话
        market: 市场代码 ('A'/'US'/'FUND')
        board: 榜单类型
        items: [{'symbol','name','price','change_pct','board_rank',**extra}]
    """
    # 1. upsert 标的维度，保证 security 唯一
    for it in items:
        stmt = select(Security).where(Security.symbol == it["symbol"])
        sec = (await session.execute(stmt)).scalar_one_or_none()
        if sec is None:
            sec = Security(
                symbol=it["symbol"],
                name=it["name"],
                market=market,
                security_type=board,
            )
            session.add(sec)
    await session.flush()  # 拿到 security.id

    # 2. 新建快照
    snap = RankSnapshot(market=market, board=board)
    session.add(snap)
    await session.flush()

    # 3. 批量插入记录
    records = [
        RankRecord(
            snapshot_id=snap.id,
            security_id=sec.id,
            board_rank=it["board_rank"],
            price=it["price"],
            change_pct=it["change_pct"],
            extra=it.get("extra", {}),
        )
        for it, sec in zip(items, _resolve_secs(session, items))
    ]
    session.add_all(records)
    await session.commit()

工程提示：_resolve_secs 用缓存（如 dict 映射 symbol→id）替换上面的逐条查询，能把 N+1 查询压成 1 次 IN 查询，抓取量大时必做。

六、常用查询示例

查某基金近 7 天进 Top10 的次数：

from sqlalchemy import func as f, select
from datetime import datetime, timedelta, timezone

since = datetime.now(timezone.utc) - timedelta(days=7)
stmt = (
    select(Security.symbol, Security.name, f.count().label("top10_cnt"))
    .join(RankRecord, RankRecord.security_id == Security.id)
    .join(RankSnapshot, RankSnapshot.id == RankRecord.snapshot_id)
    .where(
        RankSnapshot.market == "FUND",
        RankRecord.board_rank <= 10,
        RankSnapshot.fetched_at >= since,
    )
    .group_by(Security.symbol, Security.name)
    .order_by(f.count().desc())
)

查某标的历次榜单名次的走势（时序）：

stmt = (
    select(RankSnapshot.fetched_at, RankRecord.board_rank, RankRecord.price)
    .join(RankRecord, RankRecord.snapshot_id == RankSnapshot.id)
    .join(Security, Security.id == RankRecord.security_id)
    .where(Security.symbol == "SH600519")
    .order_by(RankSnapshot.fetched_at)
)

七、踩坑与优化建议

1. 价格/涨跌幅是字符串：百度返回 "+2.31%"，必须正则提取数字并转 float，否则排序全错。
2. 代理是采集的命门：直连极易被限频，生产务必走亿牛云这类隧道代理，并把认证信息放环境变量，密钥绝不入库。
3. **快照时间用服务端 **now()：爬虫机器时钟不可信，统一用 PG 的 server_default，分析时才对齐。
4. BRIN 索引救大表：rank_record 会极速膨胀，对 snapshot_id 建 BRIN 索引，比 B-tree 省空间且时序范围查询更快。
5. 软删除 + 分区：按月对 rank_record 做声明式分区，冷数据可快速 detach 归档。
6. 别把 extra 当主字段用：JSONB 只兜底，高频查询字段一定要提升为独立列并加索引。

八、小结

本篇给出了百度金融热榜的完整落库链路：先用亿牛云隧道代理保证采集稳定，再以 security 维度表做去重、rank_snapshot 记抓取动作、rank_record 存明细，配合 SQLAlchemy 2.0 的类型安全模型与 PostgreSQL 的 JSONB/BRIN 能力。这套结构既能支撑简单的历史回溯，也能平滑演进到跨市场热度分析。下一篇我们会用 schedule + Typer 把它变成每天自动跑的定时任务。

运行环境：Python 3.11+ / SQLAlchemy 2.0 / PostgreSQL 15+。建表用 Base.metadata.create_all(engine) 或 Alembic 迁移，生产环境强烈建议后者。亿牛云代理需在 .env 中配置 YINIU_USER 与 YINIU_PASS。