Build a Golden Boot Database Engine

Transform flat CSV files into a normalized SQLite relational database.

Introduction

30 Second Summary

Ever noticed how spreadsheets get messy when the same information appears in dozens of rows? A player's name, their team, their league. All repeated over and over across multiple files.

In this project, you will transform your 3 Golden Boot CSV files into a clean, normalized relational database using Python and SQLite. You will design a proper schema, build an ETL pipeline, and write SQL queries that pull cross-season insights.

What You'll Build

A Python-powered database engine that reads flat CSV files and loads them into a normalized SQLite database with 4 interlinked tables.

By the end of this project, you'll have:

  • A create_db.py script that defines your database schema and loads all CSV data into 4 normalized tables linked by foreign keys.
  • A golden_boot.db SQLite database file with proper foreign key relationships enforcing data integrity across players, teams, leagues, and season stats.
  • A queries.py script with SQL JOIN queries that pull cross-season player comparisons and team-level analytics.
  • Secret Mission: Create a database VIEW that outputs an ML-ready flat feature table your models can consume directly.

Are there any prerequisites?

You need Python and VS Code installed on your machine. You also need 3 Golden Boot CSV files from the previous project in this series (with headers like Player, Nation, Pos, Squad, Comp, and stat columns).

No additional packages are required. The sqlite3 and csv modules both ship with Python's standard library.

Set Up Your Project Structure

You have three seasons of Golden Boot data sitting in flat CSV files. That is fine for browsing, but querying across seasons or linking players to teams becomes painful fast.

In this step, you will create the project folder, verify your tools are ready, and organize the files so your database engine has a clean starting point.

In this step, get ready to:
  • Create the golden-boot-db project folder and verify Python and sqlite3 are available.
  • Organize your 3 season CSV files into a data/ subfolder.
  • Create the Python scripts you will build in later steps.
Create your project folder and verify your tools

Your database engine needs a dedicated folder on your Desktop. You will also confirm that Python's built-in SQLite module is accessible before writing any code.

  • In VS Code, open the integrated terminal by pressing Cmd+` (macOS) or Ctrl+` (Windows).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir golden-boot-db
cd golden-boot-db
  • Confirm the folder was created by running:
ls

You should see an empty output since the folder is brand new.

  • Open this folder in VS Code by clicking File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the golden-boot-db folder, and click Open.

VS Code reloads with golden-boot-db as the workspace root. The Explorer panel on the left shows your empty project.

  • Open a new terminal by pressing Cmd+` (macOS) or Ctrl+` (Windows).

This terminal opens inside golden-boot-db automatically.

  • Confirm Python is installed by running:
python3 --version

You should see a version number like Python 3.x.x printed in your terminal.

Windows users

If python3 is not recognized, try python --version instead. On Windows, the command is often just python. Use whichever works for all commands in this project.

  • Confirm sqlite3 is available by running:
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

You should see a SQLite version number like 3.x.x printed in your terminal.

What does this confirm?

The sqlite3 module is part of Python's standard library. It ships with every Python installation, so there is nothing extra to install.

This command proves Python can talk to SQLite and shows which version of the SQLite engine is bundled in.

Seeing an error?

  • If you see "command not found" for python3, try python (Windows) or check that Python is in your system PATH.
  • If the import fails, your Python installation may be incomplete. Reinstall Python from the official site.

Help me troubleshoot my Python or sqlite3 setup

Organize your CSV files

Your 3 season CSV files from the previous project need a dedicated home. A data/ subfolder keeps raw data separate from your Python scripts.

  • Create the data subfolder by running:
mkdir data
  • Open Finder (macOS) or File Explorer (Windows).
  • Locate your 3 season CSV files from the previous project: season_2022_23.csv, season_2023_24.csv, and season_2024_25.csv.
  • Copy all 3 files into the data/ folder inside golden-boot-db on your Desktop.

Drag and drop in VS Code

You can also drag the CSV files directly into the data/ folder in VS Code's Explorer panel on the left. This copies them into your project.

  • Verify your files are in place by running:
ls data/

You should see season_2022_23.csv, season_2023_24.csv, and season_2024_25.csv listed in the output.

What headers should the CSVs have?

Each CSV file should have this header row as the first line:

Player,Nation,Pos,Squad,Comp,Age,MP,Starts,Min,90s,Gls,Ast,G+A,G-PK,PK,PKatt,xG,npxG,xAG

You can check by opening any of the CSV files in VS Code and looking at the first line. These headers are what the database loader will use to map columns.

Files not showing up?

  • Make sure you copied the files into golden-boot-db/data/ and not into golden-boot-db/ directly.
  • Check that the file names match exactly, including underscores. Spaces or capital letters in file names will cause issues later.

Help me find or organize my CSV files

Create your Python files

You need two Python scripts. One will define the database schema and load data. The other will run analytical queries against the finished database.

  • Create both files by running:

macOS

touch create_db.py queries.py

Windows

New-Item create_db.py, queries.py
  • Confirm the files exist by running:
ls

You should see create_db.py, data, and queries.py in the output.

What are these files for?

  • create_db.py will define your database schema (the 4 tables) and load all CSV data into them.
  • queries.py will hold analytical SQL queries that pull insights from the finished database.

Touch not recognized on Windows?

The touch command does not exist in Windows PowerShell. Use New-Item instead, as shown in the Windows tab above.

Help me create empty Python files in my project

Your project structure is ready. Next up, you will design the database schema and create 4 normalized tables that link players, teams, leagues, and season stats together.

Design and Create the Database Schema

Your project folder is ready with empty Python files and three CSV files waiting to be processed.

Right now, all your player data lives in flat CSV files where every row repeats the team name, league name, and player details. That repetition makes it hard to answer questions like "which teams has Haaland played for?" without manually scanning every row across every file. A relational database solves this by splitting the data into linked tables, each with a single responsibility.

In this step, get ready to:
  • Design a 4-table schema that normalizes your CSV data into related tables.
  • Write Python code that creates the database tables with primary keys and foreign keys.
  • Run the script and verify all 4 tables exist in your new database file.
Understand the schema design

Your CSV files have one row per player per season, repeating the team and league names on every single row. Normalization is the process of splitting that repeated data into separate tables, each storing one type of thing.

You will create 4 tables that work together:

  • leagues holds unique competition names (e.g., Premier League).
  • teams holds unique squad names.
  • players holds unique player identifiers with their nation and position.
  • season_stats is the fact table that links a player to a team and league for a specific season, with all the stat columns.

What is a foreign key?

A foreign key is a column in one table that points to the primary key of another table. It enforces that every season_stats row must reference a valid player, team, and league.

If you try to insert a stat row for a player that does not exist in the players table, the database will reject it. This keeps your data consistent.

Write the schema creation code in create_db.py

You will build create_db.py in chunks, starting with the imports and configuration at the top of the file.

  • In VS Code, open create_db.py from the Explorer sidebar.
  • Add the following imports and configuration at the top of the file:
import sqlite3
import csv
import os

# Database file name and data directory
DB_NAME = "golden_boot.db"
DATA_DIR = "data"

# Map each season label to its CSV filename
SEASON_FILES = {
    "2022-23": "season_2022_23.csv",
    "2023-24": "season_2023_24.csv",
    "2024-25": "season_2024_25.csv",
}

What does this code do?

  • sqlite3 and csv are Python standard library modules. No install required.
  • DB_NAME sets where the database file will be created.
  • SEASON_FILES maps human-readable season labels to the actual CSV filenames in your data/ folder.
  • Below the SEASON_FILES dictionary, add the start of the create_schema() function with the two simple lookup tables:

def create_schema(cursor):
    """Create the 4 normalized tables."""
    # Leagues table - stores unique competition names
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS leagues (
            league_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

    # Teams table - stores unique squad names
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS teams (
            team_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

What does this code do?

  • create_schema() accepts a database cursor and uses it to execute SQL statements.
  • INTEGER PRIMARY KEY AUTOINCREMENT makes the database assign a unique ID to each row automatically.
  • TEXT NOT NULL UNIQUE ensures every league and team name is present and never duplicated.
  • CREATE TABLE IF NOT EXISTS prevents errors if the table already exists.
  • Still inside the create_schema() function, add the players table below the teams table:
    # Players table - stores unique player identifiers
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS players (
            player_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            nation TEXT,
            position TEXT,
            UNIQUE(name, nation)
        )
    """)

Why UNIQUE(name, nation)?

Different players can share the same name. Adding nation as part of the uniqueness constraint provides enough disambiguation for this dataset without over-engineering the schema.

  • Finally, add the season_stats fact table to close out the create_schema() function:
    # Season stats - the fact table linking players, teams, and leagues
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS season_stats (
            stat_id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL,
            team_id INTEGER NOT NULL,
            league_id INTEGER NOT NULL,
            season TEXT NOT NULL,
            age INTEGER,
            mp INTEGER,
            starts INTEGER,
            min INTEGER,
            nineties REAL,
            gls INTEGER,
            ast INTEGER,
            g_plus_a INTEGER,
            g_minus_pk INTEGER,
            pk INTEGER,
            pk_att INTEGER,
            xg REAL,
            npxg REAL,
            xag REAL,
            FOREIGN KEY (player_id) REFERENCES players (player_id),
            FOREIGN KEY (team_id) REFERENCES teams (team_id),
            FOREIGN KEY (league_id) REFERENCES leagues (league_id)
        )
    """)

What does this table do?

This is the heart of the database. Each row records one player's stats for one season, linked to the parent tables by foreign keys.

  • FOREIGN KEY (player_id) REFERENCES players (player_id) ensures every stat row points to a real player.
  • Column names like g_plus_a and g_minus_pk are SQL-safe versions of the CSV headers G+A and G-PK.
  • nineties stores the CSV's 90s column (column names cannot start with a number in SQL).
  • Below the create_schema() function, add the main() function that ties everything together:

def main():
    # Remove existing database to start fresh
    if os.path.exists(DB_NAME):
        os.remove(DB_NAME)

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # Enable foreign key enforcement
    cursor.execute("PRAGMA foreign_keys = ON")

    # Create tables
    create_schema(cursor)
    print("Schema created successfully.")

    # Verify tables exist
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    print(f"Tables: {[t[0] for t in tables]}")

    conn.close()


if __name__ == "__main__":
    main()

What does this code do?

  • os.remove(DB_NAME) deletes any existing database so you always start fresh when developing.
  • sqlite3.connect() creates the database file and returns a connection object.
  • PRAGMA foreign_keys = ON tells SQLite to actually enforce foreign key constraints. This is OFF by default and must be set on every new connection.
  • The query against sqlite_master lists all tables that exist in the database, confirming your schema was created.

Why enable PRAGMA foreign_keys every time?

SQLite has foreign key enforcement turned OFF by default. The PRAGMA foreign_keys = ON setting is not saved in the database file. You must enable it on every new connection before running any INSERT statements.

  • Save create_db.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete create_db.py file so far:

import sqlite3
import csv
import os

# Database file name and data directory
DB_NAME = "golden_boot.db"
DATA_DIR = "data"

# Map each season label to its CSV filename
SEASON_FILES = {
    "2022-23": "season_2022_23.csv",
    "2023-24": "season_2023_24.csv",
    "2024-25": "season_2024_25.csv",
}


def create_schema(cursor):
    """Create the 4 normalized tables."""
    # Leagues table - stores unique competition names
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS leagues (
            league_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

    # Teams table - stores unique squad names
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS teams (
            team_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

    # Players table - stores unique player identifiers
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS players (
            player_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            nation TEXT,
            position TEXT,
            UNIQUE(name, nation)
        )
    """)

    # Season stats - the fact table linking players, teams, and leagues
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS season_stats (
            stat_id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL,
            team_id INTEGER NOT NULL,
            league_id INTEGER NOT NULL,
            season TEXT NOT NULL,
            age INTEGER,
            mp INTEGER,
            starts INTEGER,
            min INTEGER,
            nineties REAL,
            gls INTEGER,
            ast INTEGER,
            g_plus_a INTEGER,
            g_minus_pk INTEGER,
            pk INTEGER,
            pk_att INTEGER,
            xg REAL,
            npxg REAL,
            xag REAL,
            FOREIGN KEY (player_id) REFERENCES players (player_id),
            FOREIGN KEY (team_id) REFERENCES teams (team_id),
            FOREIGN KEY (league_id) REFERENCES leagues (league_id)
        )
    """)


def main():
    # Remove existing database to start fresh
    if os.path.exists(DB_NAME):
        os.remove(DB_NAME)

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # Enable foreign key enforcement
    cursor.execute("PRAGMA foreign_keys = ON")

    # Create tables
    create_schema(cursor)
    print("Schema created successfully.")

    # Verify tables exist
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    print(f"Tables: {[t[0] for t in tables]}")

    conn.close()


if __name__ == "__main__":
    main()
Run the script and verify the schema was created

Time to run your script and confirm that all 4 tables exist in the new database file.

  • In your terminal, run the script by entering this command:
python3 create_db.py
  • You should see this output in the terminal:
Schema created successfully.
Tables: ['leagues', 'teams', 'players', 'season_stats']
  • Confirm the database file was created by running:
ls golden_boot.db
  • You should see golden_boot.db listed in the output.

Not seeing the expected output?

  • If you see a SyntaxError, check that your triple-quoted SQL strings are properly closed with matching """) on each table definition.
  • If you see IndentationError, make sure all the cursor.execute() calls inside create_schema() are indented one level (4 spaces).
  • If the file runs but shows no output, make sure you have the if __name__ == "__main__": block at the bottom calling main().

Help me debug my create_db.py script

Your database schema is locked in. You have 4 empty tables with proper constraints ready to enforce data integrity. Next up, you will build the ETL pipeline that reads your CSV files and loads the data into these tables.

Build the CSV Loader (ETL Pipeline)

Your database schema is in place with 4 empty tables ready to receive data. Now you need a way to get your CSV data into those tables.

In this step, you will build the ETL pipeline. ETL stands for Extract (read from CSV), Transform (normalize into separate tables), and Load (insert into SQLite). The loader reads each row from your flat CSV files, splits the data into the correct parent tables, and inserts the stats into the child table with proper foreign key links.

In this step, get ready to:
  • Write extraction logic that reads CSV rows and inserts unique values into parent tables.
  • Write the loading logic that inserts season stats with foreign key references.
  • Run the full loader across all 3 season files and verify row counts.
Write the extraction and normalization logic

Each row in your CSV contains denormalized data. The same team name and league name repeat across hundreds of rows. Your job is to extract unique values from those repeated columns and insert them into the parent tables once.

What is INSERT OR IGNORE?

When you try to insert a value that already exists in a UNIQUE column, SQLite would normally throw an error. INSERT OR IGNORE tells SQLite to silently skip the insert if there is a conflict.

After the insert (or skip), you run a SELECT to retrieve the row's ID. This two-step pattern handles duplicates across multiple CSV files without needing to pre-check existence.

  • In create_db.py, add the following load_csv function below create_schema but above main():
def load_csv(cursor, season, filename):
    """Load a single CSV file into the database."""
    filepath = os.path.join(DATA_DIR, filename)

    with open(filepath, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)

        for row in reader:
            # Insert league (ignore if already exists)
            cursor.execute(
                "INSERT OR IGNORE INTO leagues (name) VALUES (?)",
                (row["Comp"],)
            )
            cursor.execute(
                "SELECT league_id FROM leagues WHERE name = ?",
                (row["Comp"],)
            )
            league_id = cursor.fetchone()[0]

            # Insert team (ignore if already exists)
            cursor.execute(
                "INSERT OR IGNORE INTO teams (name) VALUES (?)",
                (row["Squad"],)
            )
            cursor.execute(
                "SELECT team_id FROM teams WHERE name = ?",
                (row["Squad"],)
            )
            team_id = cursor.fetchone()[0]

            # Insert player (ignore if already exists)
            cursor.execute(
                "INSERT OR IGNORE INTO players (name, nation, position) VALUES (?, ?, ?)",
                (row["Player"], row["Nation"], row["Pos"])
            )
            cursor.execute(
                "SELECT player_id FROM players WHERE name = ? AND nation = ?",
                (row["Player"], row["Nation"])
            )
            player_id = cursor.fetchone()[0]

What does this code do?

  • csv.DictReader(f) reads the CSV and maps each row to a dictionary using the header row as keys. You access values like row["Comp"] or row["Squad"].
  • For each parent table (leagues, teams, players), the pattern is: insert the value (skip if it already exists), then SELECT to get back the row's ID.
  • The ? placeholders protect against SQL injection by letting SQLite safely bind Python values into the query.
  • The players table uses UNIQUE(name, nation) because two players from different countries could share the same name.

You won't see a change yet. The next chunk completes the function by inserting into season_stats.

Write the loading logic with parameterized inserts

Now that you have the IDs for the player, team, and league, you can insert the actual stats row. This is the child table that references all three parents via foreign keys.

  • Still inside the for row in reader: loop (directly after the player_id = cursor.fetchone()[0] line), add the following season stats insert:
            # Insert season stats
            cursor.execute("""
                INSERT INTO season_stats (
                    player_id, team_id, league_id, season, age,
                    mp, starts, min, nineties, gls, ast, g_plus_a,
                    g_minus_pk, pk, pk_att, xg, npxg, xag
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                player_id, team_id, league_id, season,
                int(row["Age"]) if row["Age"] else None,
                int(row["MP"]) if row["MP"] else None,
                int(row["Starts"]) if row["Starts"] else None,
                int(row["Min"]) if row["Min"] else None,
                float(row["90s"]) if row["90s"] else None,
                int(row["Gls"]) if row["Gls"] else None,
                int(row["Ast"]) if row["Ast"] else None,
                int(row["G+A"]) if row["G+A"] else None,
                int(row["G-PK"]) if row["G-PK"] else None,
                int(row["PK"]) if row["PK"] else None,
                int(row["PKatt"]) if row["PKatt"] else None,
                float(row["xG"]) if row["xG"] else None,
                float(row["npxG"]) if row["npxG"] else None,
                float(row["xAG"]) if row["xAG"] else None,
            ))

What does this code do?

  • This INSERT uses the player_id, team_id, and league_id you fetched above as foreign keys. SQLite rejects the insert if any of those IDs do not exist in their parent tables.
  • The int(row["Age"]) if row["Age"] else None pattern handles empty fields in the CSV. If the cell is blank, it stores None (which becomes NULL in SQLite).
  • Whole-number columns (goals, assists, matches) get int() while decimal columns (xG, npxG, 90s) get float().

Why does the CSV use different column names than the database?

Your CSV headers use characters like G+A, G-PK, and 90s. These are fine as dictionary keys in Python, but SQL column names should avoid special characters.

The database maps them to safe names: g_plus_a, g_minus_pk, and nineties. The transformation happens here in the loader.

You won't see a change yet. The next substep updates main() to call this function and run the full load.

Run the loader and verify row counts

Your load_csv function is complete. Now you need to call it from main() for each of your 3 season files and verify the data landed correctly.

  • In create_db.py, replace your entire main() function (from def main(): down to conn.close()) with this updated version:
def main():
    # Remove existing database to start fresh
    if os.path.exists(DB_NAME):
        os.remove(DB_NAME)

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # Enable foreign key enforcement
    cursor.execute("PRAGMA foreign_keys = ON")

    # Create tables
    create_schema(cursor)
    print("Schema created successfully.")

    # Verify tables exist
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    print(f"Tables: {[t[0] for t in tables]}")

    # Load data from all CSV files
    for season, filename in SEASON_FILES.items():
        print(f"Loading {filename} ({season})...")
        load_csv(cursor, season, filename)

    conn.commit()

    # Print row counts
    for table in ["leagues", "teams", "players", "season_stats"]:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        count = cursor.fetchone()[0]
        print(f"  {table}: {count} rows")

    conn.close()
    print("Done! Database saved as golden_boot.db")


if __name__ == "__main__":
    main()

What does this code do?

  • The loop iterates over the SEASON_FILES dictionary you defined at the top of the file. Each key is a season label (like "2022-23") and each value is the corresponding CSV filename.
  • conn.commit() is called once after all 3 files are loaded. This wraps the full load in a single transaction, which is faster and ensures all-or-nothing consistency.
  • The row count loop queries each table and prints its total, giving you a quick sanity check that data landed correctly.

Why does main() delete the database first?

The os.remove(DB_NAME) line at the top of main() deletes the old database file before recreating it. This means you can safely re-run the script during development without getting duplicate rows or stale schema.

  • Save create_db.py.
  • Run the script by entering this command in your terminal:
python3 create_db.py

You should see output showing schema creation, table names, loading progress for each file, and row counts for all 4 tables.

What should I see?

Your terminal prints something like:

  • Schema created successfully.
  • A list of your 4 table names.
  • Loading lines for each CSV file.
  • Row counts for each table. The season_stats count should roughly equal the total rows across your 3 CSV files. The players count will be lower because players who appear in multiple seasons are stored only once.

Seeing a KeyError?

  • A KeyError means the CSV header does not match what the code expects. Open one of your CSV files and confirm the first line matches exactly: Player,Nation,Pos,Squad,Comp,Age,MP,Starts,Min,90s,Gls,Ast,G+A,G-PK,PK,PKatt,xG,npxG,xAG.
  • Check for extra spaces or invisible characters in the header row. Some spreadsheet exports add a BOM character at the start of the file.
  • Make sure your CSV files are inside the data/ folder and the filenames match SEASON_FILES exactly (season_2022_23.csv, season_2023_24.csv, season_2024_25.csv).

Help me debug a KeyError in my CSV loader

✔️ Awesome, I've got everything!

Great. Double check you have saved create_db.py and your terminal shows row counts for all 4 tables.

ⓧ I'd like to double check the full code

Here is the complete create_db.py file at this point:

import sqlite3
import csv
import os

DB_NAME = "golden_boot.db"
DATA_DIR = "data"

SEASON_FILES = {
    "2022-23": "season_2022_23.csv",
    "2023-24": "season_2023_24.csv",
    "2024-25": "season_2024_25.csv",
}


def create_schema(cursor):
    """Create the 4 normalized tables."""
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS leagues (
            league_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS teams (
            team_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS players (
            player_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            nation TEXT,
            position TEXT,
            UNIQUE(name, nation)
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS season_stats (
            stat_id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL,
            team_id INTEGER NOT NULL,
            league_id INTEGER NOT NULL,
            season TEXT NOT NULL,
            age INTEGER,
            mp INTEGER,
            starts INTEGER,
            min INTEGER,
            nineties REAL,
            gls INTEGER,
            ast INTEGER,
            g_plus_a INTEGER,
            g_minus_pk INTEGER,
            pk INTEGER,
            pk_att INTEGER,
            xg REAL,
            npxg REAL,
            xag REAL,
            FOREIGN KEY (player_id) REFERENCES players (player_id),
            FOREIGN KEY (team_id) REFERENCES teams (team_id),
            FOREIGN KEY (league_id) REFERENCES leagues (league_id)
        )
    """)


def load_csv(cursor, season, filename):
    """Load a single CSV file into the database."""
    filepath = os.path.join(DATA_DIR, filename)

    with open(filepath, newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)

        for row in reader:
            cursor.execute(
                "INSERT OR IGNORE INTO leagues (name) VALUES (?)",
                (row["Comp"],)
            )
            cursor.execute(
                "SELECT league_id FROM leagues WHERE name = ?",
                (row["Comp"],)
            )
            league_id = cursor.fetchone()[0]

            cursor.execute(
                "INSERT OR IGNORE INTO teams (name) VALUES (?)",
                (row["Squad"],)
            )
            cursor.execute(
                "SELECT team_id FROM teams WHERE name = ?",
                (row["Squad"],)
            )
            team_id = cursor.fetchone()[0]

            cursor.execute(
                "INSERT OR IGNORE INTO players (name, nation, position) VALUES (?, ?, ?)",
                (row["Player"], row["Nation"], row["Pos"])
            )
            cursor.execute(
                "SELECT player_id FROM players WHERE name = ? AND nation = ?",
                (row["Player"], row["Nation"])
            )
            player_id = cursor.fetchone()[0]

            cursor.execute("""
                INSERT INTO season_stats (
                    player_id, team_id, league_id, season, age,
                    mp, starts, min, nineties, gls, ast, g_plus_a,
                    g_minus_pk, pk, pk_att, xg, npxg, xag
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                player_id, team_id, league_id, season,
                int(row["Age"]) if row["Age"] else None,
                int(row["MP"]) if row["MP"] else None,
                int(row["Starts"]) if row["Starts"] else None,
                int(row["Min"]) if row["Min"] else None,
                float(row["90s"]) if row["90s"] else None,
                int(row["Gls"]) if row["Gls"] else None,
                int(row["Ast"]) if row["Ast"] else None,
                int(row["G+A"]) if row["G+A"] else None,
                int(row["G-PK"]) if row["G-PK"] else None,
                int(row["PK"]) if row["PK"] else None,
                int(row["PKatt"]) if row["PKatt"] else None,
                float(row["xG"]) if row["xG"] else None,
                float(row["npxG"]) if row["npxG"] else None,
                float(row["xAG"]) if row["xAG"] else None,
            ))


def main():
    # Remove existing database to start fresh
    if os.path.exists(DB_NAME):
        os.remove(DB_NAME)

    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # Enable foreign key enforcement
    cursor.execute("PRAGMA foreign_keys = ON")

    # Create tables
    create_schema(cursor)
    print("Schema created successfully.")

    # Verify tables exist
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    print(f"Tables: {[t[0] for t in tables]}")

    # Load data from all CSV files
    for season, filename in SEASON_FILES.items():
        print(f"Loading {filename} ({season})...")
        load_csv(cursor, season, filename)

    conn.commit()

    # Print row counts
    for table in ["leagues", "teams", "players", "season_stats"]:
        cursor.execute(f"SELECT COUNT(*) FROM {table}")
        count = cursor.fetchone()[0]
        print(f"  {table}: {count} rows")

    conn.close()
    print("Done! Database saved as golden_boot.db")


if __name__ == "__main__":
    main()

Your database is now fully loaded with 3 seasons of Golden Boot data. Next up, you will write SQL JOIN queries that pull cross-season insights from this relational structure.

Query Your Data with SQL JOINs

Your database is loaded and all 4 tables are linked by foreign keys. Now it is time to prove that the relational structure actually works by pulling insights that would be impossible with flat CSV files.

In this step, you will write 3 analytical SQL queries in queries.py. Each query uses a JOIN to combine data from multiple tables into a single result set. This is the payoff for normalizing your data.

In this step, get ready to:
  • Write a top-scorers query using JOIN and GROUP BY.
  • Write a cross-season player comparison query.
  • Write a team-level aggregation query with ranked output.
Write a query to find the top 5 scorers across all seasons

A SQL JOIN lets you combine rows from two or more tables based on a related column. Your season_stats table stores player IDs, but not player names. To get human-readable output, you need to join it with the players table.

  • Switch back to queries.py in VS Code and add the following code:
import sqlite3

DB_NAME = "golden_boot.db"


def top_scorers(cursor):
    """Find the top 5 scorers across all seasons."""
    print("\n--- Top 5 Scorers (All Seasons Combined) ---")
    cursor.execute("""
        SELECT p.name, SUM(s.gls) as total_goals, COUNT(s.season) as seasons_played
        FROM season_stats s
        JOIN players p ON s.player_id = p.player_id
        GROUP BY p.player_id
        ORDER BY total_goals DESC
        LIMIT 5
    """)
    results = cursor.fetchall()
    for rank, (name, goals, seasons) in enumerate(results, 1):
        print(f"  {rank}. {name} - {goals} goals ({seasons} seasons)")

What does this query do?

The JOIN players p ON s.player_id = p.player_id line connects each stat row to the player it belongs to. This is how you translate numeric IDs into readable names.

GROUP BY p.player_id collapses multiple season rows for the same player into one result. SUM(s.gls) then adds up their goals across all grouped rows. The ORDER BY total_goals DESC LIMIT 5 gives you only the top 5.

  • Save queries.py.
  • You will not be able to run this yet. The next substeps add the remaining functions and the main() that ties everything together.
Write a cross-season comparison query

This is where normalization truly shines. Because each player has one row in players but multiple rows in season_stats, you can pull all of a single player's seasons side by side with one query.

  • Add the following function below top_scorers in queries.py:
def player_across_seasons(cursor, player_name):
    """Show a single player's stats across all seasons."""
    print(f"\n--- {player_name}: Season-by-Season ---")
    cursor.execute("""
        SELECT s.season, t.name as team, s.gls, s.ast, s.xg, s.npxg
        FROM season_stats s
        JOIN players p ON s.player_id = p.player_id
        JOIN teams t ON s.team_id = t.team_id
        WHERE p.name LIKE ?
        ORDER BY s.season
    """, (f"%{player_name}%",))
    results = cursor.fetchall()
    if not results:
        print(f"  No data found for '{player_name}'")
        return
    print(f"  {'Season':<10} {'Team':<20} {'Gls':<5} {'Ast':<5} {'xG':<6} {'npxG':<6}")
    for season, team, gls, ast, xg, npxg in results:
        print(f"  {season:<10} {team:<20} {gls:<5} {ast:<5} {xg:<6.1f} {npxg:<6.1f}")

What does this query do?

This query uses TWO JOINs: one to get the player's name for filtering, and another to get the team name for display. The WHERE p.name LIKE ? clause uses a wildcard match so partial names work (e.g., "Haaland" matches "Erling Haaland").

The ? placeholder keeps the query safe from SQL injection. Python's sqlite3 module substitutes the actual value at execution time.

  • Save queries.py.
  • You will run all 3 queries together once the final function and main() are in place.
Write a team-level aggregation query

Roll-up analytics like "total goals per team per season" are exactly the kind of question that flat CSVs struggle with. With your normalized tables, it is a single GROUP BY away.

  • Add the following function below player_across_seasons in queries.py:
def team_goals_by_season(cursor):
    """Total goals per team per season."""
    print("\n--- Team Goals by Season (Top 5 per Season) ---")
    cursor.execute("""
        SELECT s.season, t.name as team, SUM(s.gls) as total_goals
        FROM season_stats s
        JOIN teams t ON s.team_id = t.team_id
        GROUP BY s.season, t.team_id
        ORDER BY s.season, total_goals DESC
    """)
    results = cursor.fetchall()
    current_season = None
    count = 0
    for season, team, goals in results:
        if season != current_season:
            current_season = season
            count = 0
            print(f"\n  {season}:")
        count += 1
        if count <= 5:
            print(f"    {count}. {team} - {goals} goals")

What does this query do?

GROUP BY s.season, t.team_id creates one result row per team per season. SUM(s.gls) adds up all goals scored by that team's players in that season.

The Python loop tracks which season is currently printing and resets a counter each time the season changes. This gives you a ranked "top 5 teams" breakdown for each season.

  • Now add the main() function at the bottom of queries.py to connect to the database and call all 3 query functions:
def main():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")

    top_scorers(cursor)
    player_across_seasons(cursor, "Haaland")
    team_goals_by_season(cursor)

    conn.close()


if __name__ == "__main__":
    main()

Why enable PRAGMA foreign_keys here too?

PRAGMA foreign_keys = ON must be set on every connection. It is not stored in the database file. Even though you are only reading data here, it is good practice to always enable it so any accidental writes still respect your foreign key constraints.

  • Save queries.py.
  • Run your queries script by entering this command in the terminal:
python3 queries.py
  • You should see 3 result sets printed: the top 5 scorers with goal totals, Haaland's season-by-season stats with xG and npxG, and a ranked list of teams by goals for each season.

Seeing an error?

  • If you see no such table: season_stats, make sure you ran create_db.py successfully in the previous step. The golden_boot.db file must exist in the same folder as queries.py.
  • If you see No data found for 'Haaland', check that your CSV files contain that player. You can swap "Haaland" for any player name that appears in your data.
  • If you see a SyntaxError, check that your triple-quoted SQL strings are closed properly with matching """ on both ends.

Why aren't my SQL queries returning results?

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.py and that all 3 result sets print without errors.

ⓧ I'd like to double check the full code

import sqlite3

DB_NAME = "golden_boot.db"


def top_scorers(cursor):
    """Find the top 5 scorers across all seasons."""
    print("\n--- Top 5 Scorers (All Seasons Combined) ---")
    cursor.execute("""
        SELECT p.name, SUM(s.gls) as total_goals, COUNT(s.season) as seasons_played
        FROM season_stats s
        JOIN players p ON s.player_id = p.player_id
        GROUP BY p.player_id
        ORDER BY total_goals DESC
        LIMIT 5
    """)
    results = cursor.fetchall()
    for rank, (name, goals, seasons) in enumerate(results, 1):
        print(f"  {rank}. {name} - {goals} goals ({seasons} seasons)")


def player_across_seasons(cursor, player_name):
    """Show a single player's stats across all seasons."""
    print(f"\n--- {player_name}: Season-by-Season ---")
    cursor.execute("""
        SELECT s.season, t.name as team, s.gls, s.ast, s.xg, s.npxg
        FROM season_stats s
        JOIN players p ON s.player_id = p.player_id
        JOIN teams t ON s.team_id = t.team_id
        WHERE p.name LIKE ?
        ORDER BY s.season
    """, (f"%{player_name}%",))
    results = cursor.fetchall()
    if not results:
        print(f"  No data found for '{player_name}'")
        return
    print(f"  {'Season':<10} {'Team':<20} {'Gls':<5} {'Ast':<5} {'xG':<6} {'npxG':<6}")
    for season, team, gls, ast, xg, npxg in results:
        print(f"  {season:<10} {team:<20} {gls:<5} {ast:<5} {xg:<6.1f} {npxg:<6.1f}")


def team_goals_by_season(cursor):
    """Total goals per team per season."""
    print("\n--- Team Goals by Season (Top 5 per Season) ---")
    cursor.execute("""
        SELECT s.season, t.name as team, SUM(s.gls) as total_goals
        FROM season_stats s
        JOIN teams t ON s.team_id = t.team_id
        GROUP BY s.season, t.team_id
        ORDER BY s.season, total_goals DESC
    """)
    results = cursor.fetchall()
    current_season = None
    count = 0
    for season, team, goals in results:
        if season != current_season:
            current_season = season
            count = 0
            print(f"\n  {season}:")
        count += 1
        if count <= 5:
            print(f"    {count}. {team} - {goals} goals")


def main():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")

    top_scorers(cursor)
    player_across_seasons(cursor, "Haaland")
    team_goals_by_season(cursor)

    conn.close()


if __name__ == "__main__":
    main()

Secret mission

Create an ML-Ready Database VIEW

Your normalized database is great for data integrity, but machine learning models want flat tables. Can you create a SQL VIEW that flattens your 4 tables back into a single ML-ready feature table that your prediction models can query directly?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • The golden-boot-db project folder (contains create_db.py, queries.py, and the data/ subfolder with your CSV files).
  • The golden_boot.db SQLite database file (inside your project folder).

Keep everything running

No action needed. Choose this if you are still actively building or plan to use this database in the next phase of your Golden Boot prediction pipeline.

  • Your golden-boot-db folder stays on your Desktop with all files intact.
  • The golden_boot.db file is self-contained. You can reopen and query it anytime without rerunning the loader.
  • Python and VS Code remain installed on your machine for future projects.

Pause - I'll come back to this later

Since everything is local files, there is nothing to shut down or pause. Your project is already in a paused state whenever you close VS Code.

  • Close VS Code. Your files remain saved on disk.
  • When you return, reopen the golden-boot-db folder in VS Code to pick up where you left off.
  • The database file does not require any running process. It is ready to query the moment you reconnect.

Delete - I don't want to use this again

Remove all project files from your machine. This permanently deletes your database and scripts.

  • Close VS Code.
  • Delete the entire project folder by running the command for your operating system below.

macOS

rm -rf ~/Desktop/golden-boot-db

Windows

rmdir /s /q %USERPROFILE%\Desktop\golden-boot-db

This removes create_db.py, queries.py, the data/ folder with your CSVs, and the golden_boot.db database file.

Nice Work!

Nice Work!

Nice work! You just transformed flat CSV files into a professional, normalized relational database powered by Python and SQLite.

You've learned how to:

  • Build a normalized relational database with 4 tables linked by foreign keys, enforcing referential integrity with PRAGMA foreign_keys = ON.
  • Write a Python ETL pipeline that extracts CSV data, transforms it into normalized rows, and loads it into SQLite using parameterized inserts.
  • Query cross-season player data using SQL JOINs and GROUP BY aggregations that would be impossible with flat CSV files.
  • Secret Mission: Created an ML-ready database VIEW that flattens normalized tables into a single feature table your prediction models can query directly.

Ready to quiz yourself?