import mysql.connector
import json

cnx = mysql.connector.connect(
    database='idealfit', user='root', password='root', host='localhost'
)

cnx1 = mysql.connector.connect(
    database='idealfit', user='root', password='root', host='localhost'
)

cursor = cnx.cursor();
cursor1 = cnx1.cursor();

query = "SELECT a.recipe_id, a.recipe_item_type, b.meta_key, b.meta_value, b.recipe_item_id FROM wp_simmer_recipe_items a, wp_simmer_recipe_itemmeta b WHERE a.recipe_item_id = b.recipe_item_id GROUP BY a.recipe_item_id"
query1 = "SELECT * FROM wp_simmer_recipe_itemmeta WHERE recipe_item_id=(%s)"

cursor.execute(query)
rs = cursor.fetchall()

data = {}

for row in rs:
    if row[1] == 'instruction':
        cursor1.execute(query1, (row[4],))
        insD = cursor1.fetchall()
        for instruction in insD:
            if instruction[2] != 'is_heading':
                data.setdefault(row[0], {'instructions':[], 'ingredients': []})['instructions'].append(instruction[3]);
    else:
        cursor1.execute(query1, (row[4],))
        rd = cursor1.fetchall()
        ingredient = {}
        for itemMeta in rd:
            ingredient[itemMeta[2]] = itemMeta[3]
        data.setdefault(row[0], {'ingredients': [], 'instructions': []})['ingredients'].append(ingredient)


with open('data.json', 'w') as outfile:
    json.dump(data, outfile, sort_keys=True, indent=4)

cursor1.close()
cnx1.close()
cursor.close()
cnx.close()