Flask第九课-爬虫-AI

让豆包完成ICode成绩的查询，缓存数据到本地数据库，提供页面查询。

https://service.icode.org.cn/rank/queryUserLeaderboard?pageStart=1&pageSize=10&edition=1&gameId=26127222&areaId=411500

https://service.icode.org.cn/rank/queryUserLeaderboard

127.0.0.1/api/refresh 加载数据

127.0.0.1/ 访问页面

from flask import Flask, request, jsonify, render_template
import sqlite3
import requests

app = Flask(__name__)

EXTERNAL_API = "https://service.icode.org.cn/rank/queryUserLeaderboard"
DEFAULT_PAGE_SIZE = 10
EDITION = 1
GAME_ID = 26127222
DB_FILE = "rank_cache.db"
ALLOW_PAGE_SIZE = [10,20,50,100]


def init_db():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()
    c.execute('''
    CREATE TABLE IF NOT EXISTS user_leaderboard (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        area_id INTEGER,
        first_name TEXT,
        rank INTEGER,
        rownum INTEGER,
        school_cn_name TEXT,
        total_score INTEGER,
        user_grade INTEGER,
        edition INTEGER,
        game_id INTEGER,
        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    c.execute("CREATE INDEX IF NOT EXISTS idx_name ON user_leaderboard(first_name)")
    c.execute("CREATE INDEX IF NOT EXISTS idx_school ON user_leaderboard(school_cn_name)")
    conn.commit()
    conn.close()


def get_db_conn():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn


def refresh_rank_cache():
    conn = get_db_conn()
    cur = conn.cursor()
    cur.execute("DELETE FROM user_leaderboard WHERE edition=? AND game_id=?", (EDITION, GAME_ID))
    conn.commit()

    page_start = 1
    while True:
        resp = requests.get(EXTERNAL_API, params={
            "pageStart": page_start,
            "pageSize": 10,
            "edition": EDITION,
            "gameId": GAME_ID
        }, timeout=15)
        res_json = resp.json()
        if res_json.get("code") != 0:
            break
        data_list = res_json.get("data", [])
        if not data_list:
            break
        for item in data_list:
            cur.execute("""
                INSERT INTO user_leaderboard
                (area_id, first_name, rank, rownum, school_cn_name, total_score, user_grade, edition, game_id)
                VALUES (?,?,?,?,?,?,?,?,?)
            """, (
                item["areaId"],
                item["firstName"],
                item["rank"],
                item["rownum"],
                item["schoolCnName"],
                item["totalScore"],
                item["userGrade"],
                EDITION,
                GAME_ID
            ))
        conn.commit()
        total_page = res_json["totalPage"]
        if page_start >= total_page:
            break
        page_start += 1
    cur.close()
    conn.close()
    return True


@app.route("/api/refresh")
def api_refresh():
    try:
        refresh_rank_cache()
        return jsonify({"code": 0, "msg": "缓存刷新成功"})
    except Exception as e:
        return jsonify({"code": 500, "msg": str(e)})


@app.route("/api/rank")
def api_rank():
    page = int(request.args.get("page", 1))
    keyword = request.args.get("keyword", "").strip()
    page_size = int(request.args.get("pageSize", DEFAULT_PAGE_SIZE))
    # 限制只允许指定的每页条数
    if page_size not in ALLOW_PAGE_SIZE:
        page_size = DEFAULT_PAGE_SIZE

    offset = (page - 1) * page_size

    conn = get_db_conn()
    cur = conn.cursor()

    where_parts = ["edition=?", "game_id=?"]
    params = [EDITION, GAME_ID]
    if keyword:
        where_parts.append("(first_name LIKE ? OR school_cn_name LIKE ?)")
        params.append(f"%{keyword}%")
        params.append(f"%{keyword}%")
    where_sql = " WHERE " + " AND ".join(where_parts)

    count_sql = f"SELECT COUNT(*) as cnt FROM user_leaderboard {where_sql}"
    cur.execute(count_sql, params)
    total_rows = cur.fetchone()["cnt"]
    total_page = (total_rows + page_size - 1) // page_size

    data_sql = f"""
        SELECT area_id,first_name,rank,rownum,school_cn_name,total_score,user_grade
        FROM user_leaderboard {where_sql}
        ORDER BY `rank` ASC LIMIT ? OFFSET ?
    """
    params.extend([page_size, offset])
    cur.execute(data_sql, params)
    rows = cur.fetchall()
    data = [dict(r) for r in rows]
    conn.close()

    return jsonify({
        "code": 0,
        "data": data,
        "total": total_rows,
        "totalPage": total_page,
        "currentPage": page
    })


@app.route("/")
def index():
    return render_template("paihang.html")


if __name__ == "__main__":
    init_db()
    app.run(debug=True, host="0.0.0.0", port=80)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>排行榜</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        body {
            font-family: "Microsoft Yahei", sans-serif;
            background-color: #d9f5dd;
            position: relative;
            min-height: 100vh;
            padding: 40px 15px;
        }
        /* 大水印文字 */
        body::before {
            content: "怂样喜欢绿色";
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            z-index: -1;
            color: #70c978;
            font-size: 72px;
            font-weight:bold;
            opacity: 0.22;
        }
        .container {
            max-width: 1100px;
            margin: 0 auto;
        }
        h1 {
            text-align: center;
            color: #14702c;
            margin-bottom: 20px;
            font-size:32px;
        }
        .search-box{
            display:flex;
            gap:10px;
            justify-content:center;
            margin-bottom:24px;
            flex-wrap: wrap;
            align-items:center;
        }
        .search-box input{
            padding:10px 14px;
            width:320px;
            border:2px solid #21a844;
            border-radius:6px;
            font-size:16px;
        }
        .search-box select{
            padding:10px 12px;
            border:2px solid #21a844;
            border-radius:6px;
            font-size:16px;
        }
        .search-box button{
            padding:10px 20px;
            border:none;
            background-color:#21a844;
            color:white;
            border-radius:6px;
            cursor:pointer;
            font-size:16px;
        }
        .rank-table {
            width: 100%;
            border-collapse: collapse;
            background-color: #ffffff;
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 3px 14px #98d8a8;
        }
        .rank-table th {
            background-color: #21a844;
            color: white;
            padding:16px 10px;
            text-align: center;
            font-size:16px;
        }
        .rank-table td {
            padding:14px 10px;
            text-align: center;
            border-bottom: 1px solid #c6e9cf;
            color: #222;
            font-size:15px;
        }
        .rank-table tr:nth-child(even) {
            background-color: #edfaf1;
        }
        .pagination-wrap {
            margin-top: 32px;
            display: flex;
            flex-wrap: wrap;
            align-items: center;
            justify-content: center;
            gap:8px;
        }
        .pagination-wrap button {
            padding:9px 16px;
            border:none;
            background-color:#21a844;
            color:#fff;
            border-radius:6px;
            cursor:pointer;
            font-size:15px;
            min-width:44px;
        }
        .pagination-wrap button:disabled {
            background-color:#86c796;
            cursor: not-allowed;
        }
        .page-info {
            color:#14702c;
            font-size:15px;
            margin:0 10px;
        }
        .page-num-btn.active{
            background-color:#14702c;
        }
        .loading {
            text-align:center;
            padding:60px;
            color:#21a844;
            font-size:19px;
        }
    </style>
</head>
<body>
<div class="container">
    <h1>用户排行榜</h1>
    <div class="search-box">
        <select id="pageSizeSelect">
            <option value="10">每页10条</option>
            <option value="20">每页20条</option>
            <option value="50">每页50条</option>
            <option value="100">每页100条</option>
        </select>
        <input type="text" id="searchInput" placeholder="输入姓名/学校模糊查询">
        <button onclick="doSearch()">搜索</button>
        <button onclick="resetSearch()">重置</button>
    </div>
    <div id="loading" class="loading">加载中...</div>
    <table class="rank-table" id="rankTable" style="display:none;">
        <thead>
            <tr>
                <th>排名</th>
                <th>姓名</th>
                <th>地区ID</th>
                <th>学校</th>
                <th>年级</th>
                <th>总分数</th>
            </tr>
        </thead>
        <tbody id="tableBody">
        </tbody>
    </table>
    <div class="pagination-wrap" id="pagination" style="display:none;">
        <button id="firstBtn">首页</button>
        <button id="prevBtn">上一页</button>
        <div id="pageNumberBox"></div>
        <button id="nextBtn">下一页</button>
        <button id="lastBtn">尾页</button>
        <div class="page-info">
            当前第 <span id="currentPage">1</span> 页 / 共 <span id="totalPage">0</span> 页
        </div>
    </div>
</div>

<script>
    let pageSize = 10;
    let currentPage = 1;
    let totalPage = 0;
    let keyword = "";

    const loadingEl = document.getElementById('loading');
    const rankTable = document.getElementById('rankTable');
    const tableBody = document.getElementById('tableBody');
    const paginationEl = document.getElementById('pagination');
    const currentPageEl = document.getElementById('currentPage');
    const totalPageEl = document.getElementById('totalPage');
    const prevBtn = document.getElementById('prevBtn');
    const nextBtn = document.getElementById('nextBtn');
    const firstBtn = document.getElementById('firstBtn');
    const lastBtn = document.getElementById('lastBtn');
    const pageNumberBox = document.getElementById('pageNumberBox');
    const searchInput = document.getElementById("searchInput");
    const pageSizeSelect = document.getElementById("pageSizeSelect");

    //切换每页条数，回到第一页
    pageSizeSelect.addEventListener('change',function(){
        pageSize = parseInt(this.value);
        loadRank(1);
    })

    async function loadRank(pageNum) {
        loadingEl.style.display = 'block';
        rankTable.style.display = 'none';
        paginationEl.style.display = 'none';
        try {
            let url = `/api/rank?page=${pageNum}&pageSize=${pageSize}`;
            if(keyword){
                url += `&keyword=${encodeURIComponent(keyword)}`
            }
            const res = await fetch(url);
            const json = await res.json();
            if(json.code === 0){
                totalPage = json.totalPage;
                renderList(json.data);
                renderPagination(pageNum, totalPage);
            }else{
                alert("获取排行榜失败");
            }
        } catch(err) {
            console.error(err);
            alert("请求异常");
        } finally {
            loadingEl.style.display = 'none';
        }
    }

    function renderList(list) {
        tableBody.innerHTML = '';
        if(list.length === 0){
            tableBody.innerHTML = `<tr><td colspan="6">没有匹配的数据</td></tr>`
        }
        list.forEach(item => {
            const tr = document.createElement('tr');
            tr.innerHTML = `
                <td>${item.rank}</td>
                <td>${item.first_name}</td>
                <td>${item.area_id}</td>
                <td>${item.school_cn_name}</td>
                <td>${item.user_grade}</td>
                <td>${item.total_score}</td>
            `;
            tableBody.appendChild(tr);
        });
        rankTable.style.display = 'table';
    }

    function renderPagination(page, total) {
        currentPage = page;
        currentPageEl.innerText = page;
        totalPageEl.innerText = total;
        paginationEl.style.display = 'flex';

        firstBtn.disabled = page <=1;
        prevBtn.disabled = page <=1;
        nextBtn.disabled = page >= total;
        lastBtn.disabled = page >= total;

        pageNumberBox.innerHTML = "";
        let start = Math.max(1, page -2);
        let end = Math.min(total, page +2);
        for(let i = start; i <= end; i++){
            const numBtn = document.createElement("button");
            numBtn.className = "page-num-btn" + (i===page?" active":"");
            numBtn.innerText = i;
            numBtn.onclick = ()=> loadRank(i);
            pageNumberBox.appendChild(numBtn);
        }
    }

    function doSearch(){
        keyword = searchInput.value.trim();
        loadRank(1);
    }
    function resetSearch(){
        keyword = "";
        searchInput.value = "";
        loadRank(1);
    }

    firstBtn.addEventListener('click', ()=> loadRank(1));
    prevBtn.addEventListener('click', ()=>{
        if(currentPage > 1){
            loadRank(currentPage - 1);
        }
    })
    nextBtn.addEventListener('click', ()=>{
        if(currentPage < totalPage){
            loadRank(currentPage + 1);
        }
    })
    lastBtn.addEventListener('click', ()=> loadRank(totalPage));

    searchInput.addEventListener("keydown",function(e){
        if(e.key === 'Enter') doSearch();
    })

    loadRank(1);
</script>
</body>