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

> Easily import the exercises dataset into SQLite, PostgreSQL, and MySQL databases. Learn how to parse the JSON file and insert records into SQL tables with practical examples.

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

---

**Yes, you can import the exercises-dataset into any SQL database by parsing the [`data/exercises.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json)** — Contains an array of objects, each representing an exercise with fields like `id`, `title`, `description`, `tags`, and `difficulty`
- **[`README.md`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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.

```python
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.

```python
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.

```python
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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) maintains a consistent schema with required fields `id`, `title`, and optional fields `description`, `difficulty`, `tags`. Check the [`README.md`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/README.md) for any schema migration notes in future releases.