KO | EN

GitLite

Minimalist Open Source Repository Search Engine

gitlite — search

#dsh #dsh-plugin #developer-tools #ai-agent #claude-code #codex #typescript #ai-agents #deepseek-harness #mcp #cli #rust

NCAA_MBB_Database

★ 1 Open GitHub ↗

Python + SQL Server pipeline for ingesting NCAA men’s basketball data from the balldontlie API, with historical loads and incremental game updates for analytics and dashboards.

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# NCAA College Basketball Data Pipeline

A Python and SQL Server pipeline for collecting NCAA men’s college basketball data from the `balldontlie` API and loading it into a local SQL Server database for analysis, reporting, and dashboarding.

The project includes a one-time historical ingestion workflow and an incremental database update workflow that refreshes newly completed and in-progress games.

## What This Project Does

This project pulls NCAA basketball data from the `balldontlie` API, transforms the API JSON responses into clean pandas DataFrames, and loads the results into SQL Server tables.

The pipeline currently supports:

- Conferences
- Teams
- Players
- Games
- Rankings
- Plays / play-by-play data
- Player game stats
- Team game stats

The update workflow also refreshes live or in-progress games so incomplete rows can be replaced with cleaner final data as games finish.

## Repository Contents

| File | Purpose |
|---|---|
| `Data_Ingestion.ipynb` | Initial full data pull from the API and bulk load into SQL Server. |
| `Update_Database_v2.ipynb` | Main incremental update notebook. Pulls new games, updates existing games, refreshes in-progress games, and reloads related stats/play data. |
| `Update_Database(4).ipynb` | Earlier version of the incremental update workflow. Kept for reference. |
| `get_last_post_date.sql` | Returns the most recent completed game date from the `Games` table. |
| `get_in_games.sql` | Returns games currently marked as in-progress. |
| `merge_games.sql` | SQL Server `MERGE` statement used to update or insert rows in the `Games` table. |

## Tech Stack

- Python
- pandas
- requests
- SQLAlchemy
- pyodbc
- Microsoft SQL Server
- Jupyter Notebook
- balldontlie NCAA Basketball API

## Database

The notebooks are configured to connect to a local SQL Server database:

```text
Server: Waterbored\SQLEXPRESS
Database: NCAA College Basketball
Authentication: Windows Trusted Connection
Driver: ODBC Driver 17 for SQL Server
```

Update these values in the notebooks if your SQL Server instance or database name is different.

## Main Workflow

### 1. Initial Data Ingestion

Run `Data_Ingestion.ipynb` first if the SQL Server database is empty.

This notebook:

1. Connects to the `balldontlie` NCAA basketball API.
2. Pulls reference and historical data.
3. Builds pandas DataFrames for each table.
4. Cleans data types for SQL Server compatibility.
5. Loads data into SQL Server using `DataFrame.to_sql()`.

Target tables include:

```text
Conferences
Teams
Players
Games
Rankings
Plays
Player_Stats
Team_Stats
```

### 2. Incremental Database Updates

Use `Update_Database_v2.ipynb` after the initial ingestion.

This notebook:

1. Finds the latest completed game date in the `Games` table.
2. Pulls newer games from the API.
3. Upserts new or changed game rows into SQL Server.
4. Pulls player stats, team stats, and play-by-play data for new games.
5. Updates player and team stats with `MERGE` logic.
6. Deletes and reinserts play-by-play rows for updated games.
7. Finds in-progress games and refreshes their game, stats, and play data.

## SQL Helper Files

### `get_last_post_date.sql`

Returns the most recent completed game date:

```sql
SELECT TOP 1
Games.game_date
FROM Games
WHERE Games.game_status = 'post'
ORDER BY Games.game_date DESC;
```

### `get_in_games.sql`

Returns games currently marked as in-progress:

```sql
SELECT
Games.game_id
FROM Games
WHERE Games.game_status = 'in'
ORDER BY Games.game_date DESC;
```

### `merge_games.sql`

Uses SQL Server `MERGE` to update existing `Games` rows or insert new ones based on `game_id`.

## Player Name Extraction

The notebooks include regex logic to extract player names from play-by-play descriptions.

The extraction logic attempts to identify:

- Primary player
- Secondary player
- Fouls
- Shots
- Free throws
- Rebounds
- Turnovers
- Steals
- Blocks
- Substitutions
- Jump balls

Some play types are intentionally ignored, including timeouts, end-of-period rows, unavailable plays, and dead-ball rebounds.

## Setup

### 1. Install Requirements

Create a virtual environment, then install the main Python dependencies:

```bash
pip install pandas requests sqlalchemy pyodbc jupyter
```

You also need the Microsoft ODBC Driver for SQL Server installed on your machine.

### 2. Create or Update SQL Server Database

Create a SQL Server database named:

```text
NCAA College Basketball
```

Or update the `database` variable in the notebooks to match your own database name.

### 3. Configure API Key

The notebooks currently contain a hardcoded API key. That should be changed before publishing this repo.

Recommended approach:

```python
import os

API_KEY = os.getenv("BALLDONTLIE_API_KEY")
HEADERS = {"Authorization": API_KEY}
```

Then set the environment variable locally instead of committing the key to GitHub.

Example:

```bash
set BALLDONTLIE_API_KEY=your_api_key_here
```

For PowerShell:

```powershell
$env:BALLDONTLIE_API_KEY="your_api_key_here"
```

## Usage

### Full Load

Use this when building the database from scratch:

```bash
jupyter notebook Data_Ingestion.ipynb
```

Run the notebook cells in order.

### Incremental Update

Use this after the database already has historical data:

```bash
jupyter notebook Update_Database_v2.ipynb
```

Run the notebook cells in order.

## Notes and Limitations

- The pipeline is currently notebook-based, not packaged as a command-line application.
- The SQL Server connection is configured for a local Windows trusted connection.
- The update process depends on existing completed games in the `Games` table.
- Play-by-play data is deleted and reinserted for refreshed games to avoid duplicate or stale play rows.
- API rate limits and endpoint availability depend on the `balldontlie` API.
- Before making the repo public, remove any hardcoded secrets from notebooks and commit history.

## Suggested Future Improvements

- Move API keys and database config into a `.env` file.
- Add a `requirements.txt` file.
- Convert notebooks into reusable Python scripts.
- Add database schema creation scripts.
- Add logging instead of print statements.
- Add error handling and retry logic for API calls.
- Schedule the update workflow with Windows Task Scheduler, Airflow, or another orchestration tool.
- Add tests for the play-description parsing logic.

## Project Status

Working prototype. The core ingestion and update logic is present, but the project should be cleaned up before being treated as production-ready.

NCAA_MBB_Database

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README