How to Import the Exercises Dataset into SQL Databases: SQLite, PostgreSQL & MySQL Examples

Yes, you can import the exercises-dataset into any SQL database by parsing the data/exercises.json file and inserting the resulting records into appropriately structured tables.

The hasaneyldrm/exercises-dataset repository stores its core data as a single JSON file at data/exercises.json. This file contains an array of exercise objects that map cleanly to relational rows, making SQL import straightforward using standard JSON parsing tools or native database JSON features.

---

Understanding the Dataset Structure

The repository follows a minimal layout optimized for easy consumption:

• data/exercises.json — Contains an array of objects, each representing an exercise with fields like id, title, description, tags, and difficulty
• README.md — Provides usage notes and licensing information
• LICENSE — MIT license governing reuse

No predefined SQL schema is included, so you define table structures based on the JSON keys present in the source file. According to the repository's source code, each exercise object follows a consistent flat structure that translates directly to relational columns.

---

Importing into SQLite (Python + sqlite3)

SQLite's lightweight, file-based architecture makes it ideal for local development and testing. Python's standard library sqlite3 module handles the entire import without external dependencies.

import json
import sqlite3

# Load the JSON data from the repository

with open("data/exercises.json", "r", encoding="utf-8") as f:
    exercises = json.load(f)

# Create or connect to database file

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

# Define table matching JSON structure observed in source

cur.execute("""
CREATE TABLE IF NOT EXISTS exercises (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    difficulty TEXT,
    tags TEXT  -- JSON array stored as string
)
""")

# Insert records with parameterized queries

for ex in exercises:
    cur.execute(
        """INSERT OR REPLACE INTO exercises 
           (id, title, description, difficulty, tags) 
           VALUES (?, ?, ?, ?, ?)""",
        (
            ex.get("id"),
            ex.get("title"),
            ex.get("description"),
            ex.get("difficulty"),
            json.dumps(ex.get("tags", [])),
        )
    )

conn.commit()
conn.close()

Performance tip: For large datasets, wrap inserts in a transaction (as shown) rather than committing per row. This reduces I/O overhead by orders of magnitude.

---

Importing into PostgreSQL (Python + psycopg2 + JSONB)

PostgreSQL's native JSONB type provides efficient storage and indexing of semi-structured data. The import process follows the same parse-then-insert pattern, leveraging PostgreSQL's superior JSON handling.

import json
import psycopg2

# Load source data from hasaneyldrm/exercises-dataset

with open("data/exercises.json", "r", encoding="utf-8") as f:
    exercises = json.load(f)

# Establish database connection

conn = psycopg2.connect("dbname=yourdb user=youruser password=yourpwd")
cur = conn.cursor()

# Create table with JSONB column for flexible storage

cur.execute("""
CREATE TABLE IF NOT EXISTS exercises (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
)
""")

# Build optional GIN index for JSONB queries

cur.execute("CREATE INDEX IF NOT EXISTS idx_exercises_data ON exercises USING GIN (data)")

# Insert complete JSON objects

for ex in exercises:
    cur.execute(
        "INSERT INTO exercises (data) VALUES (%s::jsonb)",
        (json.dumps(ex),)
    )

conn.commit()
conn.close()

Query advantages: With JSONB, you can query nested fields directly: SELECT data->>'title' FROM exercises WHERE data->>'difficulty' = 'hard'.

---

Importing into MySQL (Python + pymysql + JSON Column)

MySQL 5.7+ supports a dedicated JSON column type with validation and binary storage. The import approach mirrors PostgreSQL but uses MySQL-specific connection handling.

import json
import pymysql

# Parse exercises-dataset source file

with open("data/exercises.json", "r", encoding="utf-8") as f:
    exercises = json.load(f)

# Connect to MySQL server

conn = pymysql.connect(
    host="localhost",
    user="youruser",
    password="yourpwd",
    database="yourdb",
    charset="utf8mb4"
)
cur = conn.cursor()

# Create table with JSON column

cur.execute("""
CREATE TABLE IF NOT EXISTS exercises (
    id INT PRIMARY KEY,
    json_data JSON,
    CHECK (JSON_VALID(json_data))
)
""")

# Insert using REPLACE to handle duplicate IDs

for ex in exercises:
    cur.execute(
        "REPLACE INTO exercises (id, json_data) VALUES (%s, %s)",
        (ex.get("id"), json.dumps(ex))
    )

conn.commit()
conn.close()

MySQL-specific features: The JSON_VALID check constraint ensures data integrity, while functions like JSON_EXTRACT() enable path-based queries: SELECT JSON_EXTRACT(json_data, '$.title') FROM exercises.

---

Alternative: Direct Database JSON Import (No Python)

Most modern SQL engines support importing JSON directly from the command line:

• PostgreSQL: psql -c "\copy exercises(data) FROM PROGRAM 'cat data/exercises.json'" with preprocessing
• MySQL: LOAD DATA INFILE combined with JSON_OBJECT() functions
• SQLite: .import command with JSON1 extension for parsing

These approaches eliminate the Python layer but require more manual schema mapping.

---

Summary

• SQLite — Best for embedded, zero-configuration deployments; use sqlite3 with standard JSON parsing
• PostgreSQL — Optimal for complex JSON queries; leverage JSONB with GIN indexes for performance
• MySQL — Enterprise-friendly with built-in JSON validation; JSON columns provide structured storage
• Source file — data/exercises.json from hasaneyldrm/exercises-dataset contains all exercise records in a flat, import-ready format

---

Frequently Asked Questions

What database schema should I use for the exercises dataset?

Define columns matching the JSON keys observed in data/exercises.json: id (integer primary key), title (text), description (text), difficulty (text), and tags (JSON array or separate junction table). The repository source code shows no nested objects beyond the tags array, so a flat or single-JSON-column schema suffices.

Can I import the dataset without writing Python code?

Yes—PostgreSQL and MySQL both support direct JSON loading via COPY or LOAD DATA commands, though you must preprocess the file to match each row format SQLite can import via the JSON1 extension and .import shell command.

Does the dataset include foreign key relationships?

No. The exercises-dataset contains independent exercise records with no relational dependencies. The tags field is an embedded array rather than a separate table, denormalized for simplicity.

Is the JSON structure stable across versions?

The repository follows semantic versioning in its releases. As of the current main branch, data/exercises.json maintains a consistent schema with required fields id, title, and optional fields description, difficulty, tags. Check the README.md for any schema migration notes in future releases.

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