Got an API that you want to scrape into a SQL database? Check out this utility that accepts JSON and its type/name, prompts to you tell it which is the ID key, and creates types also for array-valued keys in a similar way. It could be extended to create table-creation statements in SQL.

json2sql.py

import json


def add_table(name, json, exclude=None):
    non_array_columns = [key for key in json if not isinstance(
        json[key], list) and key != exclude]
    id_column = prompt_for_selection(non_array_columns, name)
    non_array_columns = [x for x in non_array_columns if x != id_column]
    array_values = [key for key in json if isinstance(
        json[key], list) and key != id_column and key != exclude]
    global tables
    tables.append({
        'name': name,
        'id_column': id_column,
        'columns': non_array_columns,
    })
    for array_value in array_values:
        tmp = json[array_value][0]
        tmp[name] = json[id_column]
        print('\nlooking now at', array_value)
        add_table(array_value, tmp, name)


def prompt_for_selection(options, name):
    if len(options) == 1:
        return options[0]
    print('what is the ID column for', name, "?")
    c = 0
    for option in options:
        print(c, option)
        c += 1
    option_id = input("type a number from the options: ")
    try:
        answer = int(option_id)
        assert answer >= 0
        assert answer < len(options)
        return options[answer]
    except:
        print('invalid option... try again or quit')
        return prompt_for_selection(options, name)


if __name__ == '__main__':
    tables = []
    while True:
        again = input('...got any json? y/n ').lower()
        if again == 'y':
            data = input('paste it now and hit enter. ')
            name = input('tell me the name of this json: ')
            try:
                ready = json.loads(data)
                add_table(name, ready)
            except Exception as e:
                print(e)
                print('it did not parse. Try again?')
        else:
            answer = input("what to see a demo? (y/n) ")
            if answer == 'y':
                data = {"key1":1, "id":2,"transactions":[{"eh":3, "txId": 4}]}
                print("LOOK AT THISSS", data)
                print("we'll call it 'purchase'")
                name = 'purchase'
                add_table(name, data)
                break
    print(tables)