#!/usr/bin/env python """ 将数据从CSV文件导入到Django模型的脚本 """ import os import sys import django import csv import json import re from pathlib import Path from datetime import datetime # 添加项目根目录到Python路径 project_root = Path(__file__).resolve().parent sys.path.insert(0, str(project_root)) # 设置Django环境 os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'api.project_config.settings') django.setup() from api.models import Country, Team, Player, PlayerStats, MatchResult, LeagueStanding # 定义路径 BASE_DIR = Path(__file__).resolve().parent DATA_DIR = BASE_DIR / 'data' def load_team_name_mapping(): """加载球队名称映射""" with open(BASE_DIR / 'team_name_standardization.json', 'r', encoding='utf-8') as f: data = json.load(f) # 创建一个从别名到标准名称的映射 alias_to_standard = {} for team in data['teams']: standard_name = team['standard_name'] alias_to_standard[standard_name] = standard_name # 标准名称映射到自身 for alias in team.get('aliases', []): alias_to_standard[alias] = standard_name return alias_to_standard def parse_date_of_birth(date_str): """解析出生日期字符串""" if not date_str: return None # 匹配 "27 years old (08-10-1997)" 格式 match = re.search(r'\((\d{2}-\d{2}-\d{4})\)', date_str) if match: date_part = match.group(1) try: # 将 "08-10-1997" 转换为 "1997-10-08" day, month, year = date_part.split('-') return f"{year}-{month}-{day}" except: return None # 匹配标准日期格式 try: datetime.strptime(date_str, '%Y-%m-%d') return date_str except: pass return None def parse_age(age_str): """解析年龄字符串""" if not age_str: return None # 匹配 "27 years old (08-10-1997)" 格式，提取年龄 match = re.search(r'(\d+)', age_str) if match: return int(match.group(1)) # 匹配纯数字 if age_str.isdigit(): return int(age_str) return None def parse_height(height_str): """解析身高字符串""" if not height_str: return None # 匹配 "186cm" 格式 match = re.search(r'(\d+)', height_str) if match: return int(match.group(1)) # 匹配纯数字 if height_str.isdigit(): return int(height_str) return None def import_countries(): """导入国家数据""" print("导入国家数据...") # 定义国家和代码 countries_data = [ {"name": "England", "code": "ENG", "league_name": "Premier League"}, {"name": "Germany", "code": "GER", "league_name": "Bundesliga"}, {"name": "Spain", "code": "ESP", "league_name": "La Liga"} ] for country_data in countries_data: country, created = Country.objects.get_or_create( name=country_data["name"], defaults={ 'code': country_data["code"], 'league_name': country_data["league_name"] } ) if created: print(f" 创建国家: {country.name}") print("国家数据导入完成") def import_teams(): """导入球队数据""" print("导入球队数据...") # 获取国家对象 england = Country.objects.get(name="England") # 从team_name_standardization.json获取球队列表 alias_to_standard = load_team_name_mapping() standard_teams = list(set(alias_to_standard.values())) # 去重后的标准名称 for team_name in standard_teams: team, created = Team.objects.get_or_create( name=team_name, defaults={ 'country': england } ) if created: print(f" 创建球队: {team.name}") print(f"球队数据导入完成，共 {len(standard_teams)} 支球队") def import_player_data(): """导入球员数据""" print("导入球员数据...") player_data_dir = DATA_DIR / 'player_data' if not player_data_dir.exists(): print(f" 球员数据目录不存在: {player_data_dir}") return # 获取所有球队对象 teams = {team.name: team for team in Team.objects.all()} player_count = 0 stats_count = 0 # 遍历所有球员文件 for player_file in player_data_dir.glob("*.csv"): # 从文件名提取球队和球员名称 filename = player_file.name parts = filename.replace(".csv", "").split("_") if len(parts) >= 2: team_name = parts[0] player_name = "_".join(parts[1:]) # 标准化球队名称 alias_to_standard = load_team_name_mapping() standard_team_name = alias_to_standard.get(team_name, team_name) # 查找球队 team = teams.get(standard_team_name) if not team: print(f" 警告: 未找到球队 {standard_team_name}，跳过文件 {filename}") continue # 读取球员数据 with open(player_file, 'r', encoding='utf-8') as f: reader = csv.reader(f) header = next(reader) # 跳过表头 # 读取第一行获取球员基本信息 first_row = next(reader, None) if first_row and len(first_row) >= 14: # 解析球员基本信息 age = parse_age(first_row[3]) # 年龄在第4列 date_of_birth = parse_date_of_birth(first_row[3]) # 生日在年龄字段中 height = parse_height(first_row[4]) # 身高在第5列 nationality = first_row[5] if first_row[5] else None # 国籍在第6列 positions = first_row[6] if first_row[6] else None # 位置在第7列 # 创建或更新球员 player, player_created = Player.objects.get_or_create( name=player_name, defaults={ 'age': age, 'date_of_birth': date_of_birth, 'height': height, 'nationality': nationality, 'positions': positions } ) if player_created: player_count += 1 print(f" 创建球员: {player_name}") # 重新定位到第一行以处理统计数据 f.seek(0) reader = csv.reader(f) next(reader) # 跳过表头 # 处理所有统计数据行 for row in reader: if len(row) >= 19: # 解析统计数据 tournament = row[7] if row[7] else "Premier League" appearances = row[8] if row[8] else None minutes_played = int(row[9]) if row[9].isdigit() else None goals = int(row[10]) if row[10].isdigit() else None assists = int(row[11]) if row[11].isdigit() else None tackles = float(row[12]) if row[12].replace('.', '', 1).isdigit() else None interceptions = float(row[13]) if row[13].replace('.', '', 1).isdigit() else None fouls_committed = float(row[14]) if row[14].replace('.', '', 1).isdigit() else None pass_success_rate = float(row[15]) if row[15].replace('.', '', 1).isdigit() else None shots_per_game = float(row[16]) if row[16].replace('.', '', 1).isdigit() else None dribbles_per_game = float(row[17]) if row[17].replace('.', '', 1).isdigit() else None rating = float(row[18]) if row[18].replace('.', '', 1).isdigit() else None # 创建球员统计数据 stats, stats_created = PlayerStats.objects.get_or_create( player=player, team=team, tournament=tournament, defaults={ 'appearances': appearances, 'minutes_played': minutes_played, 'goals': goals, 'assists': assists, 'tackles': tackles, 'interceptions': interceptions, 'fouls_committed': fouls_committed, 'pass_success_rate': pass_success_rate, 'shots_per_game': shots_per_game, 'dribbles_per_game': dribbles_per_game, 'rating': rating } ) if stats_created: stats_count += 1 print(f"球员数据导入完成，新增 {player_count} 名球员，新增 {stats_count} 条统计数据") def main(): """主函数""" print("开始导入数据到Django模型...") try: import_countries() import_teams() import_player_data() print("所有数据导入完成!") # 显示导入结果 print("\n导入结果统计:") print(f" 国家数量: {Country.objects.count()}") print(f" 球队数量: {Team.objects.count()}") print(f" 球员数量: {Player.objects.count()}") print(f" 球员统计数据数量: {PlayerStats.objects.count()}") except Exception as e: print(f"导入数据时出错: {e}") import traceback traceback.print_exc() if __name__ == "__main__": main()