DOCSEARCHbyAlgolia
Submit Docs

nba-sql

repository·main·Indexed 42 days ago

https://github.com/mpope9/nba-sql

An application to build comprehensive NBA databases in Postgres, MySQL/MariaDB, or SQLite by fetching data from the public NBA API. It includes a CLI tool, nba_sql.py, for initializing schemas and loading historical or current season data into tables such as player, team, game, and shot_chart_detail.

Tokens
1.2K
Snippets
6
Records
8
Agent score
3%

What's inside nba-sql

  1. Initialize a Postgres database using provided scripts

    main

    If you are using a Debian-based system and want to use the provided Postgres setup via Docker, follow these steps:

    1. Stop the local PostgreSQL service if running: sudo service postgresql stop.
    2. Start the Postgres container: docker-compose -f docker/docker-compose-postgres.yml up -d.
    3. Install Python dependencies: pip install -r requirements.txt.
    4. Run the creation script: ./scripts/create_postgres.sh.
    # Required if you're on Debian based systems
sudo service postgresql stop

docker-compose -f docker/docker-compose-postgres.yml up -d

pip install -r requirements.txt

./scripts/create_postgres.sh

  2. Configure environment variables for nba-sql

    main

    The nba-sql application requires specific environment variables to connect to your database. These variables cannot be set via command-line arguments. If you are using the provided docker-compose setup, use the following connection details:

    DB_NAME="nba"
DB_HOST="localhost"
DB_USER="nba_sql"
DB_PASSWORD="nba_sql"

  3. Initialize a MariaDB database using provided scripts

    main

    To use MariaDB with the provided Docker setup:

    1. Start the MariaDB container: docker-compose -f docker/docker-compose-mariadb.yml up -d.
    2. Run the creation script: ./scripts/create_maria.sh.
    docker-compose -f docker/docker-compose-mariadb.yml up -d

./scripts/create_maria.sh

  4. Use the nba_sql.py CLI

    main

    The main entrypoint for the application is stats/nba_sql.py. You can use this script to initialize schemas and load NBA data from the public API into Postgres, MySQL/MariaDB, or SQLite.

    Note: Building the full database can take approximately 6 hours. Large tables like play_by_play, play_by_playv3, shot_chart_detail, and pgtt can be skipped using the --skip-tables flag to save time.

    python stats/nba_sql.py --help

  5. Set up local development environment

    main

    To develop locally, create and activate a virtual environment, then install the dependencies:

    1. Create venv: python -m venv venv
    2. Activate: source venv/bin/activate (or venv\Scripts\activate on Windows)
    3. Install dependencies: pip install -r requirements.txt (or pip install -r requirements_no_gui.txt to skip GUI dependencies).

    MacOS Note: If you encounter Error: pg_config executable not found, install PostgreSQL via Homebrew: brew install postgresql.

    python -m venv venv
source venv/bin/activate
pip install -r requirements.txt

  6. Create schema and skip heavy tables

    main

    To initialize a schema in a Postgres database while skipping time-consuming tables like play_by_play and pgtt, use the following command:

    python stats/nba_sql.py --create-schema --database postgres --skip-tables play_by_play pgtt

  7. List of supported NBA SQL tables

    main

    The following tables are part of the nba-sql schema:

    • player
    • team
    • game
    • play_by_play (Detailed descriptions, but harder to correlate players)
    • play_by_playv3 (More sensible breakdown than play_by_play)
    • player_game_log (Unskippable, used for basic queries)
    • player_season
    • team_game_log
    • team_season
    • player_general_traditional_total (also known as pgtt)
    • shot_chart_detail

  8. Reference nba_sql.py CLI options

    main

    The following options are available for the stats/nba_sql.py script:

    OptionDescription
    --database {mysql,postgres,sqlite}Specifies the database protocol. Defaults to sqlite.
    --database_name DATABASE_NAMEDatabase Name (Not needed for SQLite).
    --database_host DATABASE_HOSTDatabase Hostname (Not needed for SQLite).
    --username USERNAMEDatabase Username (Not needed for SQLite).
    --create-schemaInitializes the database schema. If it exists, nothing happens.
    --time-between-requests REQUEST_GAPInjects time between API requests to prevent rate limiting.
    --batch_size BATCH_SIZEInserts rows in chunks. Ignored for sqlite.
    --sqlite-path SQLITE_PATHDefines the path for the SQLite database.
    --quietSets stdout logging level. If set, only "ok" is printed on success (applies to refreshing, not loading).
    --default-modeMode to create the database and load historic data. Use for new databases or specific season ranges.
    --current-season-modeMode to refresh the current season on an existing database.
    --password PASSWORDDatabase Password (Not needed for SQLite).
    --seasons [YYYY-YY ...]Loads specific seasons (e.g., 1997-98). Defaults to the current season.
    --skip-tables [table_name ...]Skips loading specific tables. Options include: player_season, player_game_log, play_by_play, pgtt, shot_chart_detail, game, event_message_type, team, player.