Convert SQL JSON Exports to INSERT Statements

convert_jsondata_to_sql.py

# Basic Script - CONVERT SQL JSON EXPORT TO INSERT STATEMENT

# Convert SQL JSON Exports to INSERT Statements

# Motivation:
# - I got feedup with Workbench import/export functionality. It is unreliable and slow, especially for large datasets or complex schemas.
# - Hopefully i can contribute to Workbench's program soonest.
# - This script aims to provide a more robust and efficient way to migrate data between databases or systems.

# Basic Features of this Script:
# - Handles JSON exports generated by various SQL databases.
# - Transforms JSON data into SQL INSERT statements compatible with the target database.
# - Customizable to accommodate different table structures and data types.
# - Improves data migration speed and reliability compared to traditional methods.


import json


def convert_exported_jsonformatted_data_to_sql_insert_command(
    table_name: str,
    json_file_path: str,
    all_columns: bool = True,
    columns_needed: list = [],
    write_to_disk: bool = False,
):
    with open(json_file_path, "r") as file:
        data = json.load(file)

    if not data:
        return "No data found in the JSON file."

    if all_columns:
        columns = list(data[0].keys())
    else:
        columns = columns_needed or []
        if not columns:
            return "No columns specified and all_columns is False."

    insert_statement = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES"

    for record in data:
        values = []
        for col in columns:
            value = record.get(col, "NULL")
            if isinstance(value, str):
                value = f"""'{value.replace("'", "''")}'"""
            elif isinstance(value, (list, dict)):
                value = f"""'{json.dumps(value).replace("'", "''")}'"""
            elif value is None:
                value = "NULL"
            else:
                value = str(value)
            values.append(value)

        insert_statement += f"\n({', '.join(values)}),"

    insert_statement = insert_statement.rstrip(",") + ";"

    if write_to_disk:
        with open(f"{table_name}.txt", "w") as output_file:
            output_file.write(insert_statement)

    return insert_statement


# Example
convert_exported_jsonformatted_data_to_sql_insert_command(
    table_name="activity_log",
    json_file_path="activity_log.json",
    all_columns=True,
    columns_needed=[],
    write_to_disk=True,
)