How to load data from JSON File to MySQL Destination

Ensure that your JSON file is properly formatted and accessible. For this example, let's assume the file is named `data.json` and contains an array of objects.

You'll need the MySQL connector to interact with your MySQL database from Python. Install it using pip:

```bash

pip install mysql-connector-python

```

Load and parse your JSON file using Python's built-in `json` module.

```python

import json

# Load and parse the JSON file

with open('data.json', 'r') as file:

    data = json.load(file)

```

Use the MySQL connector to establish a connection to your MySQL server.

```python

import mysql.connector

# Connect to the MySQL database

db_connection = mysql.connector.connect(

    host='localhost',

    user='your_username',

    password='your_password',

    database='your_database'

)

cursor = db_connection.cursor()

```

Replace `'your_username'`, `'your_password'`, and `'your_database'` with your actual MySQL username, password, and database name.

Create a MySQL table that matches the structure of your JSON data. For this example, let's assume each object in the JSON array has `id`, `name`, and `age` fields.

```sql

CREATE TABLE IF NOT EXISTS people (

    id INT PRIMARY KEY,

    name VARCHAR(255),

    age INT

);

```

You can execute this SQL command using the cursor object:

```python

cursor.execute("""

CREATE TABLE IF NOT EXISTS people (

    id INT PRIMARY KEY,

    name VARCHAR(255),

    age INT

)

""")

```

#Step 6: Insert JSON Data into the MySQL Table

Iterate over the JSON data and insert each record into the MySQL table.

```python

# Insert JSON data into MySQL table

for record in data:

    sql_query = "INSERT INTO people (id, name, age) VALUES (%s, %s, %s)"

    values = (record['id'], record['name'], record['age'])

    cursor.execute(sql_query, values)

# Commit the changes

db_connection.commit()

```

Make sure that the keys in `record` (i.e., `'id'`, `'name'`, and `'age'`) match the fields in your JSON objects.

Use try-except blocks to handle any exceptions and ensure that the database connection is closed properly.

```python

try:

    # (Insert JSON data into MySQL table - see Step 6)

    # ...

except mysql.connector.Error as e:

    print(f"Error: {e}")

finally:

    if db_connection.is_connected():

        cursor.close()

        db_connection.close()

        print("MySQL connection is closed")

```

After running your Python script, verify that the data has been successfully transferred to the MySQL database. You can do this by querying the MySQL database using a MySQL client or a database management tool.

```sql

SELECT * FROM people;

```

This command will display the contents of the `people` table, and you should see the data from your JSON file.