# How to Import the Exercises Dataset into SQLite: Step-by-Step Guide

> Import the exercises dataset into SQLite easily. Learn to parse JSON data and use the JSON1 extension for powerful queries. Get started with this step-by-step guide.

- Repository: [Hasan Emir Yıldırım/exercises-dataset](https://github.com/hasaneyldrm/exercises-dataset)
- Tags: how-to-guide
- Published: 2026-07-31

---

**You can import the exercises dataset into SQLite by parsing the [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) file from the hasaneyldrm/exercises-dataset repository and inserting records into a relational table that stores nested arrays as JSON text, enabling complex queries via SQLite's built-in JSON1 extension.**

The hasaneyldrm/exercises-dataset repository provides a comprehensive collection of fitness exercises stored in a single JSON file. To leverage this data in applications that require fast filtering, full-text search, or relational joins, converting it into a SQLite database offers a lightweight, serverless solution that requires no external dependencies beyond Python's standard library.

## Understanding the Dataset Structure

Before importing, examine the source files to understand the data model. The repository stores its canonical data in [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json), with a formal schema definition located at [`data/exercises.schema.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.schema.json).

The dataset contains **13 core fields** per exercise record:

- `id` – Unique identifier (text)
- `name` – Exercise name (text)
- `category` – Body region classification (text)
- `body_part` – Specific anatomical focus (text)
- `equipment` – Required gear (text)
- `instructions` – Nested object with multilingual text (JSON object)
- `instruction_steps` – Ordered array of steps (JSON array)
- `muscle_group` – Primary muscle classification (text)
- `secondary_muscles` – Supporting muscle groups (JSON array)
- `target` – Specific muscle target (text)
- `image` – Static image URL (text)
- `gif_url` – Animated demonstration URL (text)
- `media_id` – External media reference (text)

Because fields like `instructions`, `instruction_steps`, and `secondary_muscles` contain nested structures, they require special handling during import to preserve their queryability.

## Step-by-Step Import Process

### 1. Parse the JSON Source File

Use Python's built-in `json` module to load [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) into memory. The file contains a top-level array of exercise objects.

```python
import json
from pathlib import Path

json_path = Path("data/exercises.json")
with json_path.open(encoding="utf-8") as f:
    exercises = json.load(f)  # Returns a list of dictionaries

```

### 2. Design the SQLite Schema

Create a table that maps JSON fields to SQLite columns. Store nested objects as `TEXT` columns containing serialized JSON. This approach allows you to use SQLite's **JSON1 extension** (available in SQLite 3.9.0+) to query inside these columns later.

```sql
CREATE TABLE IF NOT EXISTS exercises (
    id               TEXT PRIMARY KEY,
    name             TEXT,
    category         TEXT,
    body_part        TEXT,
    equipment        TEXT,
    instructions     TEXT,   -- JSON object stored as text
    instruction_steps TEXT,  -- JSON array stored as text
    muscle_group     TEXT,
    secondary_muscles TEXT,  -- JSON array stored as text
    target           TEXT,
    image            TEXT,
    gif_url          TEXT,
    media_id         TEXT
);

```

### 3. Insert Records with Type Conversion

Iterate through the parsed exercises and insert them using parameterized queries. Convert Python dictionaries and lists back to JSON strings using `json.dumps()` before insertion.

```python
import sqlite3

conn = sqlite3.connect("exercises.db")
cur = conn.cursor()

insert_sql = """
    INSERT OR REPLACE INTO exercises (
        id, name, category, body_part, equipment,
        instructions, instruction_steps,
        muscle_group, secondary_muscles, target,
        image, gif_url, media_id
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);
"""

for ex in exercises:
    cur.execute(insert_sql, (
        ex.get("id"),
        ex.get("name"),
        ex.get("category"),
        ex.get("body_part"),
        ex.get("equipment"),
        json.dumps(ex.get("instructions")),
        json.dumps(ex.get("instruction_steps")),
        ex.get("muscle_group"),
        json.dumps(ex.get("secondary_muscles")),
        ex.get("target"),
        ex.get("image"),
        ex.get("gif_url"),
        ex.get("media_id"),
    ))

conn.commit()

```

### 4. Create Indexes for Performance

After bulk insertion, create indexes on frequently filtered columns to optimize query performance. Based on the schema, `category`, `muscle_group`, and `equipment` are high-value indexing candidates.

```sql
CREATE INDEX idx_category ON exercises(category);
CREATE INDEX idx_muscle_group ON exercises(muscle_group);
CREATE INDEX idx_equipment ON exercises(equipment);

```

## Complete Python Import Script

Combine all steps into a single, production-ready script that handles the entire import process from [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) to `exercises.db`:

```python
import json
import sqlite3
from pathlib import Path

# Configuration

PROJECT_ROOT = Path(__file__).parent
JSON_PATH = PROJECT_ROOT / "data" / "exercises.json"
DB_PATH = PROJECT_ROOT / "exercises.db"

def import_exercises():
    # Load source data

    with JSON_PATH.open(encoding="utf-8") as f:
        exercises = json.load(f)
    
    # Initialize database

    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys = ON")
    cur = conn.cursor()
    
    # Create table

    cur.execute("""
        CREATE TABLE IF NOT EXISTS exercises (
            id               TEXT PRIMARY KEY,
            name             TEXT,
            category         TEXT,
            body_part        TEXT,
            equipment        TEXT,
            instructions     TEXT,
            instruction_steps TEXT,
            muscle_group     TEXT,
            secondary_muscles TEXT,
            target           TEXT,
            image            TEXT,
            gif_url          TEXT,
            media_id         TEXT
        );
    """)
    
    # Insert data

    insert_sql = """
        INSERT OR REPLACE INTO exercises (
            id, name, category, body_part, equipment,
            instructions, instruction_steps,
            muscle_group, secondary_muscles, target,
            image, gif_url, media_id
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);
    """
    
    for ex in exercises:
        cur.execute(insert_sql, (
            ex.get("id"),
            ex.get("name"),
            ex.get("category"),
            ex.get("body_part"),
            ex.get("equipment"),
            json.dumps(ex.get("instructions")),
            json.dumps(ex.get("instruction_steps")),
            ex.get("muscle_group"),
            json.dumps(ex.get("secondary_muscles")),
            ex.get("target"),
            ex.get("image"),
            ex.get("gif_url"),
            ex.get("media_id"),
        ))
    
    # Create indexes

    cur.execute("CREATE INDEX IF NOT EXISTS idx_category ON exercises(category);")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_muscle_group ON exercises(muscle_group);")
    
    conn.commit()
    conn.close()
    print(f"✅ Imported {len(exercises)} exercises into {DB_PATH}")

if __name__ == "__main__":
    import_exercises()

```

## Querying the Imported Data

Once imported, you can query the exercises using standard SQL or leverage the **JSON1 extension** to search inside nested fields.

Standard relational queries filter on the scalar columns:

```sql
SELECT name, target 
FROM exercises 
WHERE category = 'waist' 
  AND equipment = 'body weight';

```

JSON1 functions enable searching inside the stored JSON text. Extract specific language instructions or check array contents:

```sql
-- Extract English instructions from nested JSON object
SELECT id, name,
       json_extract(instructions, '$.en') as instruction_en
FROM exercises
WHERE json_extract(instructions, '$.en') LIKE '% crunch%';

-- Find exercises targeting specific secondary muscles
SELECT name 
FROM exercises 
WHERE json_each.value = 'biceps'
  AND json_each.type = 'text'
  AND json_each.json = secondary_muscles;

```

## Performance Considerations

- **Bulk Operations**: The import script uses a single transaction (committed after all inserts), which is significantly faster than committing after each row.
- **JSON Storage Overhead**: Storing nested data as JSON text increases storage size by approximately 10-15% compared to normalized relational tables, but eliminates the need for complex join operations.
- **Indexing Strategy**: The suggested indexes on `category` and `muscle_group` cover the most common filter patterns; add additional indexes only if query patterns require them, as writes become slower with each added index.

## Summary

- The **hasaneyldrm/exercises-dataset** stores exercise data in [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) with a schema defined in [`data/exercises.schema.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.schema.json).
- Import the dataset into SQLite by creating a table with `TEXT` columns for nested JSON fields (`instructions`, `instruction_steps`, `secondary_muscles`).
- Use Python's `json` module to serialize complex objects before insertion, and `sqlite3` for database operations.
- Leverage SQLite's **JSON1 extension** to query inside stored JSON columns without parsing them in application code.
- Create indexes on `category`, `muscle_group`, and `equipment` after import to optimize read performance.

## Frequently Asked Questions

### Can I import the exercises dataset into SQLite without using Python?

Yes, you can use command-line tools like `jq` combined with `sqlite3` imports, or languages like Node.js, Ruby, or Go. However, Python is recommended because it handles the UTF-8 encoding and JSON serialization requirements natively, and the `sqlite3` module ships with the standard library.

### How do I handle updates when the dataset changes?

Run the import script with `INSERT OR REPLACE` semantics (as shown in the code examples). This upsert pattern updates existing records matched by the `id` primary key and inserts new records, ensuring your database stays synchronized with [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) without creating duplicates.

### Why store nested arrays as JSON text instead of separate tables?

Storing `instruction_steps` and `secondary_muscles` as JSON text in SQLite provides faster read performance for the common use case of retrieving a complete exercise record. Normalizing these into separate tables would require JOIN operations that slow down simple lookups, though normalization might be preferable if you frequently need to query relationships across exercises.

### Does SQLite support full-text search on the exercise descriptions?

SQLite supports full-text search via the FTS5 extension. To enable FTS on exercise instructions, create a virtual table that mirrors the `exercises` table and populate it with the text content extracted from the `instructions` JSON column using `json_extract()`, then query it with MATCH operators for advanced text search capabilities.