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

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

在学习了本地数据的各种采集与操作方法后，我们通过一个实战项目来巩固所学。本节我们将对一个欧洲职业足球数据库进行分析，目标是提取球员的基本信息，并整合其生涯平均评分，最终将处理好的数据保存为JSON格式，为后续的数据应用做好准备。

💡 核心导读

• 项目目标：从SQLite数据库中提取并整合球员信息，输出为结构化的JSON文件。
• 关键技术：使用Python标准库sqlite3连接数据库，执行SQL查询，进行多表关联与数据计算。
• 代码组织：学习如何通过模块化设计（主程序+工具函数）使代码更清晰、易维护。
• 数据流转：体验从原始数据库（.db）到中间处理（Python对象），再到最终数据文件（.json）的完整流程。

项目背景与数据

本次实战使用的数据来源于一个欧洲职业足球数据库soccer.db。该数据库使用SQLite创建，包含了国家、比赛、球员、球队等多个维度的信息。

我们主要关注其中两张表：

• Player表：存储球员的基本信息，如ID、姓名、生日、身高、体重等，约有1万多条记录。
• Player_Attributes表：存储球员在不同时间点的属性评分，如综合评分（overall_rating）、潜力等，约有18万多条记录。

我们的核心任务是：为每位球员计算其整个职业生涯的平均综合评分，并将其与基本信息（姓名、年龄、身高、体重）一同保存到一个JSON文件中。

代码结构设计

为了保持代码的清晰和可维护性，我们将项目代码分为两个文件：

• main.py：主程序文件，负责程序的主要流程控制，如连接数据库、调用处理函数、保存结果。
• utils.py：工具函数文件，存放可复用的辅助函数，如根据生日计算年龄、计算平均评分等。

这种模块化的设计避免了将所有代码堆砌在一个文件中，使得主程序逻辑清晰，工具函数也易于管理和测试。

核心代码实现

下面，我们通过一个简化的、可独立运行的示例来演示核心逻辑。请注意，为了演示的完整性，我们将工具函数直接内联在主代码块中。

# 示例：足球球员数据处理核心逻辑演示
import sqlite3
import json
import datetime

# 注意：此为演示逻辑的简化代码。
# 实际项目中，数据库文件路径应为 './database/soccer.db'
# 此处我们模拟数据并演示流程。

def calculate_age(birthday_str):
    """根据生日字符串计算年龄（粗略计算）"""
    # 生日格式如 '1989-12-15 00:00:00'
    born_year = int(birthday_str.split('-')[0])  # 提取出生年份
    current_year = 2026  # 演示用固定年份（实际项目中应为 datetime.datetime.now().date().year）
    return current_year - born_year

def calculate_average_rating(cur, player_api_id):
    """根据球员API ID查询其所有历史评分并计算平均值"""
    # 构建查询语句，查找该球员的所有综合评分
    sql = f"SELECT overall_rating FROM Player_Attributes WHERE player_api_id = {player_api_id};"
    rows = cur.execute(sql).fetchall()
    
    # 处理查询结果：过滤空值，并转换为浮点数列表
    ratings = [float(row[0]) for row in rows if row[0] is not None]
    
    # 计算平均分，避免除零错误
    if ratings:
        mean_rating = sum(ratings) / len(ratings)
    else:
        mean_rating = 0.0  # 若无评分数据，则记为0
    return mean_rating

# 模拟从Player表查询到的数据行
# 真实库 Player 表的列顺序为: (id, player_api_id, player_name, player_fifa_api_id, birthday, height, weight)
# 此处模拟时省略了 player_fifa_api_id 字段（本例用不到它）
simulated_player_rows = [
    (1, 12345, 'Lionel Messi', '1987-06-24 00:00:00', 170, 70),
    (2, 67890, 'Cristiano Ronaldo', '1985-02-05 00:00:00', 187, 83)
]

player_list = []
for row in simulated_player_rows:
    player = {}
    player['name'] = row[2]  # 姓名
    player['age'] = calculate_age(row[3])  # 年龄
    player['height'] = row[4]  # 身高（真实库中 height 列在 weight 之前）
    player['weight'] = row[5]  # 体重
    # 注意：此处无法真实连接数据库，因此模拟一个平均评分
    player['average_rating'] = 93.5 if row[2] == 'Lionel Messi' else 92.0
    
    player_list.append(player)

# 将处理结果转换为JSON格式字符串并打印，模拟保存到文件
result_json = json.dumps(player_list, indent=2)
print("处理后的球员数据（JSON格式）:")
print(result_json)

处理后的球员数据（JSON格式）:
[
  {
    "name": "Lionel Messi",
    "age": 39,
    "height": 170,
    "weight": 70,
    "average_rating": 93.5
  },
  {
    "name": "Cristiano Ronaldo",
    "age": 41,
    "height": 187,
    "weight": 83,
    "average_rating": 92.0
  }
]

主程序流程解析

结合原项目代码，主程序 main.py 的执行流程如下：

1. 连接数据库：使用 sqlite3.connect() 建立与 soccer.db 数据库的连接，并获取游标（cursor）用于执行SQL。
2. 查询球员基本信息：在 get_players_info 函数中，首先从 Player 表查询指定数量或全部球员的基本记录。
3. 循环处理每条记录：对查询到的每一条球员记录：
   • 提取姓名、体重、身高。
   • 调用 utils.get_age() 函数，根据生日字符串计算年龄。
   • 提取 player_api_id，调用 utils.get_overall_rating() 函数，查询 Player_Attributes 表并计算该球员的平均综合评分。
   • 将以上信息构造成一个字典，添加到球员列表中。
4. 保存为JSON文件：使用 json.dump() 将整个球员列表写入到 player.json 文件中。
5. 关闭数据库连接：处理完成后，关闭数据库连接以释放资源。

关键SQL查询示例：

• 限制查询条数：SELECT * FROM Player LIMIT 50;
• 关联查询评分：SELECT overall_rating FROM Player_Attributes WHERE player_api_id = ?;

查看与验证结果

运行项目后，会生成 player.json 文件。你可以用文本编辑器打开查看，也可以使用在线的JSON查看器（如 jsonviewer.stack.hu）来更清晰地浏览其树状结构。文件中每条记录都包含了我们处理后的球员信息。

[
  {
    "name": "Aaron Cresswell",
    "age": 34,
    "weight": 66,
    "height": 170,
    "average rating": 63.666666666666664
  },
  {
    "name": "Aaron Doran",
    "age": 33,
    "weight": 68,
    "height": 170,
    "average rating": 58.75
  }
]

口径说明：上面这份是原项目 main.py 的真实输出样例，其键名为 average rating（含空格，沿用原项目的字典写法）；而本节演示代码用的是蛇形命名的 average_rating。两者都是合法的 JSON 键名——键名由写代码的人定义，Python 中带空格的键需要用字符串形式书写。学习时以「演示代码（average_rating）」为准即可，原样保留旧输出是为了忠实呈现原项目的产物。

📝 动手练一练

1. 概念辨析：在本次实战的代码结构中，为什么要把 get_age 和 get_overall_rating 函数单独放在 utils.py 文件中，而不是直接写在 main.py 里？这样做有什么好处？

2. 代码修改：在原项目的 get_overall_rating 函数中，我们使用了列表推导式 [float(row[0]) for row in rows if row[0] is not None] 来过滤空值。如果要求不使用列表推导式，改用传统的 for 循环来实现相同的功能，代码应该怎么写？

def get_overall_rating(cur, player_api_id):
    """
    获取球员平均评分 (使用for循环版本)
    """
    sql = f"SELECT overall_rating FROM Player_Attributes WHERE player_api_id = {player_api_id};"
    rows = cur.execute(sql).fetchall()
    
    ratings = []  # 初始化一个空列表来存储有效的评分
    for row in rows:
        rating_value = row[0]  # 取出评分
        if rating_value is not None:  # 检查评分是否不为空
            ratings.append(float(rating_value))  # 转换为浮点数并加入列表
    
    if ratings:  # 检查列表是否非空，避免除以零
        mean_rating = sum(ratings) / len(ratings)
    else:
        mean_rating = 0.0  # 如果没有有效评分，返回0.0
    return mean_rating

本章小结

通过这个实战项目，我们完整地演练了从本地数据库获取数据、进行多表关联查询、数据计算与清洗，并最终将结果序列化为通用数据格式（JSON）的整个流程。这是数据分析工作中非常典型的一个环节。

📋 行动清单

• 运行项目代码：尝试在本地配置环境，运行提供的项目代码，亲自体验从数据库到JSON文件的完整生成过程。
• 尝试修改查询：修改 main.py 中的 n_players 参数，分别获取前10名和全部球员的数据，观察生成文件的大小和内容差异。
• 思考扩展：如果还想在JSON中加入球员的“国籍”信息，你需要查看数据库中的哪些表？如何修改SQL查询和代码逻辑？

—— 小象教研组