import os
import mysql.connector

# edit this to your database
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="example",
    database="example",
)

mycursor = mydb.cursor()
mycursor.execute("show tables")

# query all tables
tables = []
for x in mycursor:
    tables.append(x[0])

for table in tables:
    json_path = os.path.join(os.getcwd(), "result", table + '.json')
    if os.path.exists(json_path):
        os.remove(json_path)
    # read all table data and write it into json file
    sql_command = f"select * from `{table}`"
    mycursor.execute(sql_command)
    myresult = mycursor.fetchall()
    with open(json_path, 'a') as f:
        f.write("[\n")
        for i in range(len(myresult)):
            f.write("  {\n")
            for j in range(len(myresult[i])):
                f.write(f'    "{mycursor.column_names[j]}": "{myresult[i][j]}",\n')
            f.write("  },\n")
        f.write("]\n")