实战：欧洲职业足球数据库分析

第2章综合实战：欧洲职业足球数据库分析与 JSON 导出

小象实战讲义 · Python数据分析实战

在掌握了 TXT、JSON、CSV、Excel 以及 SQLite 关系型数据库的多表关联等核心技术之后，本节我们将迎来第 2 章的压轴综合大实战——《欧洲职业足球数据库（European Soccer Database）多维分析》。我们将使用经典的 SQLite 足球数据库文件，综合运用 SQLite 连接、复杂多表 JOIN 查询、Pandas 统计计算以及结构化 JSON 导出，完整走通工业界本地数据采集、探索分析与交付成果的全流程。

💡 核心导读

• 真实工业级数据库结构探索：了解包含国家、联赛、球队、比赛和球员等 7 张核心表的数据库 Schema。
• 数据库元信息动态嗅探：掌握通过 sqlite_master 自动扫描数据库中所有表名与建表 DDL 的方法。
• 多表关联高阶分析：通过 INNER JOIN 关联球员表与球员属性表，提取球员综合能力与身体素质。
• 结构化结果持久化交付：将清洗计算后的高潜力球员画像以美化格式导出为 players_profile.json。
• 端到端综合实战全景代码：编写高内聚的 Python 分析脚本，实现自动化数据提取与报表落地。

1. 实战业务背景与数据库 Schema 架构

欧洲职业足球数据库是数据科学领域最经典的真实关系型数据集之一，记录了欧洲多个顶级联赛多年的海量数据。该数据库主要包含以下核心实体表：

┌─────────────────────────────────────────────────────────────┐
│               欧洲职业足球数据库 (soccer.db) 架构            │
├─────────────────────────────────────────────────────────────┤
│ 1. League (联赛表)        : id, country_id, name            │
│ 2. Team (球队表)          : id, team_api_id, team_long_name │
│ 3. Match (比赛记录表)     : id, league_id, season, stage... │
│ 4. Player (球员基本信息)  : id, player_api_id, name, height │
│ 5. Player_Attributes (属性): id, player_api_id, rating...   │
└─────────────────────────────────────────────────────────────┘

核心实战任务清单：

1. 数据库元信息勘探：查询数据库中存在哪些表，并了解其字段定义；
2. 球员身体特征与综合能力关联合并：将 Player 表（基础生理数据：身高、体重、生日）与 Player_Attributes 表（竞技数据：综合评分 overall_rating、潜力值 potential）进行外键多表连接；
3. 数据清洗与分组统计：计算欧洲职业球员的平均身高、平均体重以及各评分梯队的球员数量；
4. 交付成果导出：筛选出评分优秀的高潜力球员名单，整理为结构化嵌套 JSON 文件并保存至本地。

2. Python 代码实战：构建测试环境与多表综合分析

为了让每位学员都能无门槛、零依赖地直接在本地运行，我们首先编写一段 Python 脚本，在内存 SQLite 数据库中还原出微型版本的欧洲足球数据库表结构并录入真实风格的测试样本。

# 示例 1：构建欧洲职业足球微型测试数据库并写入样本数据

import sqlite3
import pandas as pd
import json
import os

# 建立数据库连接
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# 1. 创建球员基础信息表 (Player)
cursor.execute("""
CREATE TABLE Player (
    id INTEGER PRIMARY KEY,
    player_api_id INTEGER UNIQUE,
    player_name TEXT NOT NULL,
    birthday TEXT,
    height REAL,
    weight REAL
);
""")

# 2. 创建球员竞技属性表 (Player_Attributes)
cursor.execute("""
CREATE TABLE Player_Attributes (
    id INTEGER PRIMARY KEY,
    player_api_id INTEGER,
    overall_rating INTEGER,
    potential INTEGER,
    preferred_foot TEXT,
    FOREIGN KEY (player_api_id) REFERENCES Player(player_api_id)
);
""")

# 插入球员基础信息数据 (身高单位: cm, 体重单位: 磅)
players_sample = [
    (1, 1001, "莱奥·梅西 (Lionel Messi)", "1987-06-24", 170.0, 159.0),
    (2, 1002, "克里斯蒂亚诺·罗纳尔多 (Cristiano Ronaldo)", "1985-02-05", 187.0, 183.0),
    (3, 1003, "凯文·德布劳内 (Kevin De Bruyne)", "1991-06-28", 181.0, 154.0),
    (4, 1004, "埃尔林·哈兰德 (Erling Haaland)", "2000-07-21", 194.0, 194.0),
    (5, 1005, "卢卡·莫德里奇 (Luka Modric)", "1985-09-09", 172.0, 146.0),
    (6, 1006, "青年新秀球员 (Sample Rookie)", "2005-03-15", 178.0, 160.0)
]
cursor.executemany("INSERT INTO Player VALUES (?, ?, ?, ?, ?, ?);", players_sample)

# 插入球员能力属性数据
attributes_sample = [
    (1, 1001, 94, 94, "left"),
    (2, 1002, 93, 93, "right"),
    (3, 1003, 91, 92, "right"),
    (4, 1004, 89, 94, "left"),
    (5, 1005, 88, 88, "right"),
    (6, 1006, 76, 88, "right")
]
cursor.executemany("INSERT INTO Player_Attributes VALUES (?, ?, ?, ?, ?);", attributes_sample)
conn.commit()
print("✅ 欧洲职业足球微型数据库初始化成功！")

接下来，我们编写 SQL 多表连接查询，将两张表的数据完整关联，使用 Pandas 进行统计分析，并将顶级球星画像导出为美化的 JSON 文件：

# 示例 2：执行跨表 JOIN 分析、计算身体指标分布并导出结构化 JSON

# 1. 动态嗅探数据库中的所有表清单
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print("\n=== 数据库包含的数据表清单 ===")
for tbl in cursor.fetchall():
    print(f"• 表名: {tbl[0]}")

# 2. 执行 INNER JOIN 关联查询，提取球员综合能力与身材数据
sql_join_players = """
SELECT 
    p.player_api_id,
    p.player_name,
    p.birthday,
    p.height AS height_cm,
    ROUND(p.weight * 0.453592, 1) AS weight_kg, -- 将磅转换为公斤
    a.overall_rating,
    a.potential,
    a.preferred_foot
FROM Player AS p
INNER JOIN Player_Attributes AS a ON p.player_api_id = a.player_api_id
ORDER BY a.overall_rating DESC;
"""

df_players = pd.read_sql_query(sql_join_players, conn)
print("\n=== 欧洲职业球员多表连接综合信息大表 ===")
print(df_players[["player_name", "height_cm", "weight_kg", "overall_rating", "potential", "preferred_foot"]])

# 3. 统计球员核心体能与能力指标
avg_height = df_players["height_cm"].mean()
avg_weight = df_players["weight_kg"].mean()
avg_rating = df_players["overall_rating"].mean()

print(f"\n[全员生理与竞技指标均值]")
print(f"• 平均身高: {avg_height:.1f} cm | 平均体重: {avg_weight:.1f} kg | 平均能力评分: {avg_rating:.1f} 分")

# 4. 筛选顶级球员 (评分 >= 90) 并构建嵌套 JSON 报表导出
top_stars = df_players[df_players["overall_rating"] >= 90]
json_export_data = {
    "report_name": "欧洲足坛顶级巨星档案",
    "generated_by": "小象数据教研组",
    "player_count": len(top_stars),
    "superstars": top_stars.to_dict(orient="records")
}

out_json_path = "european_superstars.json"
with open(out_json_path, "w", encoding="utf-8") as f:
    json.dump(json_export_data, f, ensure_ascii=False, indent=2)

print(f"\n✅ 成功将 {len(top_stars)} 名顶级球星画像导出至本地 JSON: {out_json_path}")

# 5. 回读验证导出的 JSON 文件
with open(out_json_path, "r", encoding="utf-8") as f:
    verify_json = json.load(f)
print(f"回读检验: 报表共收录 {len(verify_json['superstars'])} 名球员，榜首为【{verify_json['superstars'][0]['player_name']}】")

conn.close()
if os.path.exists(out_json_path):
    os.remove(out_json_path)

📝 动手练一练

1. 业务拓展思考题：在欧洲足球数据库中，如果还存在一张 Match 比赛表（包含 home_team_api_id 主队 ID、away_team_api_id 客队 ID、home_team_goal 主队进球数、away_team_goal 客队进球数），如果要统计所有主场作战的比赛中，“主队获胜”的场次占比（主场胜率），应该如何设计分析逻辑？

   👉 点击查看参考答案

   参考答案： ① 查询总比赛场次：SELECT COUNT(*) FROM Match; ② 筛选主队获胜场次：SELECT COUNT(*) FROM Match WHERE home_team_goal > away_team_goal; ③ 或者直接在 SQL 中使用 AVG(CASE WHEN home_team_goal > away_team_goal THEN 1.0 ELSE 0.0 END) 一步计算出主场胜率百分比。

2. 综合编程练习：假设你有一个球员身价字典列表 players = [{"name": "梅西", "goals": 30}, {"name": "德布劳内", "goals": 12}, {"name": "哈兰德", "goals": 35}]。请编写 Python 代码，使用列表推导式筛选出进球数大于 20 的射手，并用 json.dumps() 格式化打印出输出结果。

   👉 点击查看参考答案

   参考答案：

   import json
   
   players = [{"name": "梅西", "goals": 30}, {"name": "德布劳内", "goals": 12}, {"name": "哈兰德", "goals": 35}]
   
   top_scorers = [p for p in players if p["goals"] > 20]
   print("=== 进球数 > 20 的顶级射手榜 (JSON 格式) ===")
   print(json.dumps(top_scorers, ensure_ascii=False, indent=2))

本章小结

在本节中，我们圆满完成了第 2 章《本地数据的采集与操作》的综合压轴实战：

• 完整回顾并串联了 TXT、JSON、CSV、Excel 与 SQLite 数据库的综合应用；
• 运用 SQL 多表连接（INNER JOIN）成功实现了跨表格复杂特征的挖掘与抽取；
• 借助 Pandas 与内置 json 模块，高效完成了工业级数据清洗、统计分析与结构化文件交付。

📋 行动清单

• 复习第 2 章全部 9 节内容，确保对各类本地文件格式与 SQL 语法做到胸有成竹。
• 做好准备，进入第 3 章《网络数据的获取与表示》，开启 Python 网络爬虫与接口解析之旅！

—— 小象教研组