How to convert JSON data to MySQL INSERT and CREATE TABLE queries?

Muhammet
February 19, 2024
163 views
1 vote
2 Answers

I have a JSON data set containing exercise information that I want to insert into a MySQL database.

Each exercise has properties such as name, force, level, etc.

I need to convert this JSON data into MySQL INSERT statements to add the data to a table, as well as a CREATE TABLE statement to define the table structure.

Here is an example snippet of the JSON data:

"exercises": [
    {
      "name": "3/4 Sit-Up",
      "force": "pull",
      "level": "beginner",
      "mechanic": "compound",
      "equipment": "body only",
      "primaryMuscles": [
        "abdominals"
      ],
      "secondaryMuscles": [],
      "instructions": [
        "Lie down on the floor and secure your feet. Your legs should be bent at the knees.",
        "Place your hands behind or to the side of your head. You will begin with your back on the ground. This will be your starting position.",
        "Flex your hips and spine to raise your torso toward your knees.",
        "At the top of the contraction your torso should be perpendicular to the ground. Reverse the motion, going only Â¾ of the way down.",
        "Repeat for the recommended amount of repetitions."
      ],
      "category": "strength"
    },
    {
      "name": "90/90 Hamstring",
      "force": "push",
      "level": "beginner",
      "mechanic": null,
      "equipment": "body only",
      "primaryMuscles": [
        "hamstrings"
      ],
      "secondaryMuscles": [
        "calves"
      ],
      "instructions": [
        "Lie on your back, with one leg extended straight out.",
        "With the other leg, bend the hip and knee to 90 degrees. You may brace your leg with your hands if necessary. This will be your starting position.",
        "Extend your leg straight into the air, pausing briefly at the top. Return the leg to the starting position.",
        "Repeat for 10-20 repetitions, and then switch to the other leg."
      ],
      "category": "stretching"
    },
    {
      "name": "Ab Crunch Machine",
      "force": "pull",
      "level": "intermediate",
      "mechanic": "isolation",
      "equipment": "machine",
      "primaryMuscles": [
        "abdominals"
      ],
      "secondaryMuscles": [],
      "instructions": [
        "Select a light resistance and sit down on the ab machine placing your feet under the pads provided and grabbing the top handles. Your arms should be bent at a 90 degree angle as you rest the triceps on the pads provided. This will be your starting position.",
        "At the same time, begin to lift the legs up as you crunch your upper torso. Breathe out as you perform this movement. Tip: Be sure to use a slow and controlled motion. Concentrate on using your abs to move the weight while relaxing your legs and feet.",
        "After a second pause, slowly return to the starting position as you breathe in.",
        "Repeat the movement for the prescribed amount of repetitions."
      ],
      "category": "strength"
    }]

Can anyone provide guidance on how to write a script or SQL queries to achieve this conversion effectively?

Thank you!

Answers

Chosen as BEST ANSWER

Guys here's how I solved with python script. Firstly create db and table that fits your json data.

CREATE TABLE exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    force_type VARCHAR(50),
    level VARCHAR(50),
    mechanic VARCHAR(50),
    equipment VARCHAR(50),
    primary_muscles JSON,
    secondary_muscles JSON,
    instructions JSON,
    category VARCHAR(50)
);

import mysql.connector
import json

# MySQL bağlantısını kur
mydb = mysql.connector.connect(
  host="localhost",
  user="yourname",
  password="yourpass",
  database="your db"
)
cursor = mydb.cursor()

# JSON dosyasını oku
with open('exercises.json') as f:
    print("Dosya okundu")
    data = json.load(f)
    ##print type of data
    


# Verileri MySQL'e ekle
for exercise in data:
    print("Veri ekleniyor: " + exercise['name'])
    sql = "INSERT INTO exercises (name, force_type, level, mechanic, equipment, primary_muscles, secondary_muscles, instructions, category) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    print(exercise['primaryMuscles'])
    val = (
        exercise['name'],
        exercise['force'],
        exercise['level'],
        exercise['mechanic'],
        exercise['equipment'],
        json.dumps(exercise['primaryMuscles']),
        json.dumps(exercise['secondaryMuscles']),
        json.dumps(exercise['instructions']),
        exercise['category'],
    )
    cursor.execute(sql, val)

# Değişiklikleri kaydet
mydb.commit()

# Bağlantıyı kapat
mydb.close()

(Edit)

To achieve the conversion using SQL instead of Python, you can create the table and generate the INSERT statements directly in SQL.

-- Create table to store exercise data
CREATE TABLE IF NOT EXISTS exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    force VARCHAR(255),
    level VARCHAR(255),
    mechanic VARCHAR(255),
    equipment VARCHAR(255),
    primaryMuscles VARCHAR(255),
    secondaryMuscles VARCHAR(255),
    instructions TEXT,
    category VARCHAR(255)
);

-- Insert statements for each exercise
INSERT INTO exercises (name, force, level, mechanic, equipment, primaryMuscles, secondaryMuscles, instructions, category)
VALUES 
(
    '3/4 Sit-Up',
    'pull',
    'beginner',
    'compound',
    'body only',
    'abdominals',
    NULL,
    'Lie down on the floor and secure your feet. Your legs should be bent at the knees.nPlace your hands behind or to the side of your head. You will begin with your back on the ground. This will be your starting position.nFlex your hips and spine to raise your torso toward your knees.nAt the top of the contraction your torso should be perpendicular to the ground. Reverse the motion, going only Â¾ of the way down.nRepeat for the recommended amount of repetitions.',
    'strength'
),
(
    '90/90 Hamstring',
    'push',
    'beginner',
    NULL,
    'body only',
    'hamstrings',
    'calves',
    'Lie on your back, with one leg extended straight out.nWith the other leg, bend the hip and knee to 90 degrees. You may brace your leg with your hands if necessary. This will be your starting position.nExtend your leg straight into the air, pausing briefly at the top. Return the leg to the starting position.nRepeat for 10-20 repetitions, and then switch to the other leg.',
    'stretching'
);

In this SQL script:

The CREATE TABLE statement creates a table named exercises with columns corresponding to the properties of the exercises.
The INSERT INTO statements insert each exercise’s data into the exercises table.
You can execute this SQL script in your MySQL database to create the table and insert the data. Adjust the data in the INSERT INTO statements as needed for your specific JSON data.

Please signup or login to give your own answer.

Click here to cancel reply.