Skip to content

Instantly share code, notes, and snippets.

@aimerneige

aimerneige/sql2json.py

Last active April 25, 2023 09:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save aimerneige/245582d209b8fb5df86570a9787c3eca to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save aimerneige/245582d209b8fb5df86570a9787c3eca to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. aimerneige revised this gist Apr 25, 2023. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions sql2json.py
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,7 @@
    import os
    import mysql.connector

    # edit this to your database
    mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    @@ -11,6 +12,7 @@
    mycursor = mydb.cursor()
    mycursor.execute("show tables")

    # query all tables
    tables = []
    for x in mycursor:
    tables.append(x[0])
  2. aimerneige created this gist Apr 25, 2023.
    33 changes: 33 additions & 0 deletions sql2json.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    import os
    import mysql.connector

    mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="example",
    database="example",
    )

    mycursor = mydb.cursor()
    mycursor.execute("show 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")