How to convert nested json object into a mysql table?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

import json
import mysql.connector

# Connect to MySQL
db = mysql.connector.connect(
  host="localhost",
  user="your_username",
  password="your_password",
  database="your_database"
)
cursor = db.cursor()

# Create the table
create_table_query = "CREATE TABLE IF NOT EXISTS table_name (column1 datatype, column2 datatype, ...)"
cursor.execute(create_table_query)

# Read the JSON file
with open('nested_json_file.json') as file:
    data = json.load(file)

# Iterate over the JSON object and insert values into the table
for item in data:
    column1_value = item['column1_key']
    column2_value = item['column2_key']
    # Extract other values as needed
    
    # Insert values into the table
    insert_query = "INSERT INTO table_name (column1, column2, ...) VALUES (%s, %s, ...)"
    values = (column1_value, column2_value, ...)
    cursor.execute(insert_query, values)

# Commit the changes and close the connection
db.commit()
cursor.close()
db.close()