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

You can import the exercises dataset into SQLite by parsing the 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, with a formal schema definition located at 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 into memory. The file contains a top-level array of exercise objects.

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.

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.

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.

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 to exercises.db:

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:

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:

-- 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 with a schema defined in 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}