本文详细阐述如何利用Python和SQLite数据库，根据用户输入的球队名称，从两个关联表（球队表现数据和球员深度名单）中高效地查询并组合数据。核心在于通过自定义行工厂增强数据可读性，并根据一支球队的表现记录，动态匹配另一支指定球队中相同位置的球员，最终生成一份整合了球队表现与对手球员信息的报告。
引言
在数据分析和应用开发中，我们常遇到需要从多个关联表中提取信息，并根据用户动态输入进行复杂条件筛选的场景。本教程将以一个NBA数据分析为例，展示如何使用Python的sqlite3模块，结合SQL查询，实现从两个不同的数据表中（一个存储球队表现数据，另一个存储球员名单）根据用户指定的两支球队，获取一支球队的详细表现，并关联查询另一支球队中对应位置的球员信息。
数据库结构概览
我们假设有两个SQLite数据库表：Cheatsheet 和 Teamdepth。
-
Cheatsheet 表：存储球队的各项表现数据。
- Teamname (TEXT): 球队名称，可能包含缩写。
- threeRPA (TEXT): 表现类型（例如：Points, Rebounds）。
- Value (REAL): 表现数值。
- Pos (TEXT): 球员位置（例如：Center (C), Power Forward (PF)）。
-
Teamdepth 表：存储球队的球员名单及其位置。
- Teamname (TEXT): 球队名称。
- Player (TEXT): 球员姓名。
- Pos (TEXT): 球员位置。
- Con (TEXT): 其他信息（此处不使用）。
为了方便演示，我们首先创建这些表并插入示例数据：
-- 创建 Cheatsheet 表
CREATE TABLE IF NOT EXISTS Cheatsheet (
Teamname TEXT,
threeRPA TEXT,
Value REAL,
Pos TEXT
);
-- 插入 Cheatsheet 示例数据
INSERT INTO Cheatsheet (Teamname, threeRPA, Value, Pos) VALUES
('Washington Wizards (WAS)', 'Points', 27.9, 'Center (C)'),
('San Antonio Spurs (SA)', 'Points', 25.4, 'Center (C)'),
('Charlotte Hornets (CHA)', 'Points', 25.3, 'Center (C)'),
('Philadelphia 76ers (PHI)', 'Points', 24.1, 'Center (C)'),
('Detroit Pistons (DET)', 'Points', 23.8, 'Center (C)'),
('Chicago Bulls (CHI)', 'Points', 23.4, 'Center (C)'),
('Dallas Mavericks (DAL)', 'Points', 23.4, 'Center (C)'),
('Washington Wizards (WAS)', 'Rebounds', 18.0, 'Center (C)'),
('Chicago Bulls (CHI)', 'Rebounds', 12.0, 'Power Forward (PF)'),
('Portland Trail Blazers (POR)', 'Rebounds', 11.4, 'Power Forward (PF)'),
('Oklahoma City Thunder (OKC)', 'Rebounds', 11.3, 'Power Forward (PF)'),
('Washington Wizards (WAS)', 'Rebounds', 11.3, 'Power Forward (PF)'),
('Atlanta Hawks (ATL)', 'Rebounds', 11.0, 'Power Forward (PF)'),
('Denver Nuggets (DEN)', 'Rebounds', 10.8, 'Power Forward (PF)'),
('Charlotte Hornets (CHA)', 'Rebounds', 10.8, 'Power Forward (PF)');
-- 创建 Teamdepth 表
CREATE TABLE IF NOT EXISTS Teamdepth (
Teamname TEXT,
Player TEXT,
Pos TEXT,
Con TEXT
);
-- 插入 Teamdepth 示例数据
INSERT INTO Teamdepth (Teamname, Player, Pos, Con) VALUES
('Atlanta Hawks (ATL)', 'Trae Young', 'Point Guard (PG)', ''),
('Atlanta Hawks (ATL)', 'Dejounte Murray', 'Shooting Guard (SG)', ''),
('Atlanta Hawks (ATL)', 'Saddiq Bey', 'Small Forward (SF)', ''),
('Atlanta Hawks (ATL)', 'Jalen Johnson', 'Power Forward (PF)', ''),
('Atlanta Hawks (ATL)', 'Clint Capela', 'Center (C)', ''),
('Denver Nuggets (DEN)', 'Jamal Murray', 'Point Guard (PG)', ''),
('Dallas Mavericks (DAL)', 'Derrick Jones', 'Power Forward (PF)', ''),
('Denver Nuggets (DEN)', 'Nikola Jokic', 'Center (C)', ''),
('Atlanta Hawks (ATL)', 'Onyeka Okongwu', 'Center (C)', ''),
('Washington Wizards (WAS)', 'Kyle Kuzma', 'Power Forward (PF)', '');
核心逻辑与实现
我们的目标是：
- 用户输入两支球队的名称（可以是全名或缩写）。
- 对于第一支球队的每条表现记录（例如，华盛顿奇才队中锋位置的得分），找到第二支球队中所有担任相同位置的球员。
- 反之亦然，对于第二支球队的每条表现记录，找到第一支球队中所有担任相同位置的球员。
- 将这些信息整合输出。
为了提高代码的可读性和可维护性，我们将采用collections.namedtuple来定义数据结构，并利用sqlite3的row_factory功能将查询结果自动映射到这些具名元组。
1. 定义数据结构
使用namedtuple为Cheatsheet、Teamdepth以及最终输出结果定义清晰的数据模型。
import collections
import sqlite3
# 定义具名元组，对应数据库表结构和最终输出结构
Cheatsheet = collections.namedtuple("Cheatsheet", "teamname,threerpa,value,pos")
Teamdepth = collections.namedtuple("Teamdepth", "teamname,player,pos")
Output = collections.namedtuple("Output", "teamname,threerpa,value,pos,players")
2. 自定义行工厂 custom_row_factory
sqlite3默认返回的查询结果是元组。通过自定义row_factory，我们可以让cursor返回具名元组，从而可以通过属性名而不是索引来访问数据，极大地提升代码可读性。
def custom_row_factory(cursor: sqlite3.Cursor, row: tuple):
"""
自定义行工厂，根据查询的字段名返回对应的具名元组。
"""
field_names = tuple(x[0] for x in cursor.description)
if field_names == Cheatsheet._fields:
return Cheatsheet(*row)
elif field_names == Teamdepth._fields:
return Teamdepth(*row)
elif len(row) == 1: # 针对单列查询（如获取球员列表）
return row[0]
return row
3. 查询函数 show_team
这个函数负责查询指定球队的表现数据，并根据其位置信息，从对手球队中查找对应的球员。
def show_team(conn: sqlite3.Connection, this_team_abbr: str, opposition_team_abbr: str):
"""
查询指定球队的表现数据，并关联查询对手球队中相同位置的球员。
Args:
conn: SQLite数据库连接对象。
this_team_abbr: 当前查询球队的缩写（如 'WAS'）。
opposition_team_abbr: 对手球队的缩写（如 'ATL'）。
Returns:
一个包含 Output 具名元组的列表。
"""
# 查询当前球队的 Cheatsheet 数据
# 使用 LIKE '%{team_abbr}%' 来匹配包含缩写或完整名称的球队
cheat_data = conn.execute(
"SELECT Teamname, threeRPA, Value, Pos FROM Cheatsheet WHERE teamname LIKE ?",
(f"%{this_team_abbr}%",),
)
rows = []
for row in cheat_data:
# 对于每条表现记录，查询对手球队中相同位置的球员
players_cursor = conn.execute(
"SELECT Player FROM Teamdepth WHERE teamname LIKE ? AND Pos = ?",
(f"%{opposition_team_abbr}%", row.pos),
)
players = players_cursor.fetchall() # fetchall() 返回的是一个列表的列表，需要处理
# 将球员姓名用逗号连接起来
player_names = ",".join(players) if players else "N/A"
# 构建 Output 具名元组并添加到结果列表
rows.append(Output(row.teamname, row.threerpa, row.value, row.pos, player_names))
return rows
4. 主执行函数 main
main函数将连接数据库，设置行工厂，获取用户输入，并调用show_team函数来处理两支球队的数据。
def main():
"""程序入口点"""
# 连接到数据库，使用 with 语句确保连接正确关闭
with sqlite3.connect("NBA.db") as conn:
# 设置自定义行工厂
conn.row_factory = custom_row_factory
cursor = conn.cursor()
# 确保表存在并包含数据，仅在第一次运行时需要
# 为了教程的完整性，这里再次执行创建和插入数据的SQL
# 实际应用中，这些操作通常在数据库初始化脚本中完成
cursor.executescript("""
CREATE TABLE IF NOT EXISTS Cheatsheet (Teamname TEXT, threeRPA TEXT, Value REAL, Pos TEXT);
CREATE TABLE IF NOT EXISTS Teamdepth (Teamname TEXT, Player TEXT, Pos TEXT, Con TEXT);
INSERT OR IGNORE INTO Cheatsheet (Teamname, threeRPA, Value, Pos) VALUES
('Washington Wizards (WAS)', 'Points', 27.9, 'Center (C)'),
('San Antonio Spurs (SA)', 'Points', 25.4, 'Center (C)'),
('Charlotte Hornets (CHA)', 'Points', 25.3, 'Center (C)'),
('Philadelphia 76ers (PHI)', 'Points', 24.1, 'Center (C)'),
('Detroit Pistons (DET)', 'Points', 23.8, 'Center (C)'),
('Chicago Bulls (CHI)', 'Points', 23.4, 'Center (C)'),
('Dallas Mavericks (DAL)', 'Points', 23.4, 'Center (C)'),
('Washington Wizards (WAS)', 'Rebounds', 18.0, 'Center (C)'),
('Chicago Bulls (CHI)', 'Rebounds', 12.0, 'Power Forward (PF)'),
('Portland Trail Blazers (POR)', 'Rebounds', 11.4, 'Power Forward (PF)'),
('Oklahoma City Thunder (OKC)', 'Rebounds', 11.3, 'Power Forward (PF)'),
('Washington Wizards (WAS)', 'Rebounds', 11.3, 'Power Forward (PF)'),
('Atlanta Hawks (ATL)', 'Rebounds', 11.0, 'Power Forward (PF)'),
('Denver Nuggets (DEN)', 'Rebounds', 10.8, 'Power Forward (PF)'),
('Charlotte Hornets (CHA)', 'Rebounds', 10.8, 'Power Forward (PF)');
INSERT OR IGNORE INTO Teamdepth (Teamname, Player, Pos, Con) VALUES
('Atlanta Hawks (ATL)', 'Trae Young', 'Point Guard (PG)', ''),
('Atlanta Hawks (ATL)', 'Dejounte Murray', 'Shooting Guard (SG)', ''),
('Atlanta Hawks (ATL)', 'Saddiq Bey', 'Small Forward (SF)', ''),
('Atlanta Hawks (ATL)', 'Jalen Johnson', 'Power Forward (PF)', ''),
('Atlanta Hawks (ATL)', 'Clint Capela', 'Center (C)', ''),
('Denver Nuggets (DEN)', 'Jamal Murray', 'Point Guard (PG)', ''),
('Dallas Mavericks (DAL)', 'Derrick Jones', 'Power Forward (PF)', ''),
('Denver Nuggets (DEN)', 'Nikola Jokic', 'Center (C)', ''),
('Atlanta Hawks (ATL)', 'Onyeka Okongwu', 'Center (C)', ''),
('Washington Wizards (WAS)', 'Kyle Kuzma', 'Power Forward (PF)', '');
""")
conn.commit() # 提交更改
# 获取用户输入
team1_input = input("请输入第一支球队的缩写（例如 WAS）：").strip().upper()
team2_input = input("请输入第二支球队的缩写（例如 ATL）：").strip().upper()
# 定义输出格式
fmt = "%-30s %-10s %6.1f %-20s %s"
print(fmt % ("Teamname", "Stat", "Value", "Position", "Opponent Players"))
print("-" * 90)
# 显示第一支球队对阵第二支球队的数据
print(f"\n--- {team1_input} 的表现 vs. {team2_input} 的球员 ---")
for row in show_team(conn, team1_input, team2_input):
print(fmt % row)
# 显示第二支球队对阵第一支球队的数据
print(f"\n--- {team2_input} 的表现 vs. {team1_input} 的球员 ---")
for row in show_team(conn, team2_input, team1_input):
print(fmt % row)
if __name__ == "__main__":
main()
运行与输出示例
当您运行上述Python代码时，程序会提示您输入两支球队的缩写。 假设输入 WAS 和 ATL：
请输入第一支球队的缩写（例如 WAS）：WAS 请输入第二支球队的缩写（例如 ATL）：ATL Teamname Stat Value Position Opponent Players ------------------------------------------------------------------------------------------ --- WAS 的表现 vs. ATL 的球员 --- Washington Wizards (WAS) Points 27.9 Center (C) Clint Capela,Onyeka Okongwu Washington Wizards (WAS) Rebounds 18.0 Center (C) Clint Capela,Onyeka Okongwu Washington Wizards (WAS) Rebounds 11.3 Power Forward (PF) Jalen Johnson --- ATL 的表现 vs. WAS 的球员 --- Atlanta Hawks (ATL) Rebounds 11.0 Power Forward (PF) Kyle Kuzma
注意事项与总结
- 团队名称匹配：在SQL查询中使用了 LIKE '%{team_abbr}%'，这允许用户输入球队缩写（如WAS）就能匹配到完整的球队名称（如Washington Wizards (WAS)）。这增加了用户输入的灵活性。
- namedtuple 的优势：通过collections.namedtuple和custom_row_factory，我们使得从数据库获取的数据可以直接通过有意义的属性名（如row.teamname, row.pos）访问，而不是通过索引（如row[0], row[3]），极大地提高了代码的可读性和可维护性。
- 循环查询的效率：本方案采用循环（先查询表现数据，再对每条表现记录查询对手球员）而非复杂的SQL JOIN。对于数据量不是特别巨大的情况，这种方式清晰易懂且性能可接受。如果数据量非常庞大，可能需要考虑更复杂的SQL JOIN语句来优化查询效率，但会牺牲部分代码可读性。
- 错误处理：示例代码中对于没有找到对应球员的情况，简单地输出"N/A"。在实际应用中，可以根据需求增加更详细的错误处理或用户反馈机制。
- 数据库初始化：为了使教程完整可复现，main函数中包含了创建表和插入数据的SQL。在实际项目中，这些通常是独立的数据库初始化脚本，而非每次运行Python程序时都执行。INSERT OR IGNORE语句确保数据不会重复插入。
通过本教程，您应该掌握了如何使用Python的sqlite3模块，结合自定义行工厂和SQL查询，灵活地从多个关联表中提取和整合数据，以满足复杂的业务需求，特别是涉及到用户动态输入和多条件匹配的场景。
Python免费学习笔记（深入）：立即使用
在学习笔记中，你将探索 Python 的核心概念和高级技巧！
