<gh_stars>1-10
import json
import psycopg2
import argparse

def do_migration(patch_major, patch_minor, recipe_version, migration_file):
    connection = psycopg2.connect(user = "aucbot_user",
                                    password = "<PASSWORD>!",
                                    host = "localhost",
                                    port = "55532",
                                    database = "aucbot")

    cursor = connection.cursor()

    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

    cursor.execute('''
    SELECT recipe_version_id
    FROM recipe_versions
    WHERE
        patch_major = %s
        AND patch_minor = %s
        AND recipe_version = %s
    ''', (patch_major, patch_minor, recipe_version))
    res = cursor.fetchall()

    version_id = res[0][0]

    cursor.execute('''
        select * from recipe_professions
    ''')

    res = cursor.fetchall()
    # build recipe dict
    recipe_professions = {}
    for entry in res:
        recipe_professions[entry[1].lower()] = entry[0]

    with open(migration_file, 'r') as f:
        recipes = json.loads(f.read())

        for r in recipes['recipes']:
            prof_id = r['profession']
            if prof_id == "":
                prof_id = recipe_professions['no profession']
            else:
                prof_id = recipe_professions[r['profession'].lower()]
            rank = r['rank']
            if rank == "":
                rank = 0
            else:
                rank = rank[rank.index(' '):]

            cursor.execute('''
            INSERT INTO recipes (spell_id, item_id, profession_id, rank, count, name, recipe_version_id) VALUES
            (%s, %s, %s, %s, %s, %s, %s)
            RETURNING recipe_id
            ''', (r['spellID'], r['itemID'], prof_id, rank, r['count'], r['name'], version_id))
            res = cursor.fetchone()
            recipe_id = res[0]
            print('Inserted recipe %d' % recipe_id)

            for reagent in r['reagents']:
                # insert reagent
                cursor.execute('''
                    INSERT INTO reagents (item_id, count) VALUES
                    (%s, %s)
                    RETURNING reagent_id
                    ''', (reagent['itemID'], reagent['count']))
                res = cursor.fetchone()
                reagent_id = res[0]

                # then insert relationship
                cursor.execute('''
                    INSERT INTO recipe_reagents (recipe_id, reagent_id) VALUES
                    (%s, %s)
                    ''', (recipe_id, reagent_id))

    connection.commit()


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument('--patchMajor', type=int, help='patch major version e.g. "8"')
    parser.add_argument('--patchMinor', type=int, help='patch minor version e.g. "3"')
    parser.add_argument('--recipeVersion', type=int, help='version of recipe data e.g. "1"')
    parser.add_argument('--migrationFile', type=str, help='path to migration file')

    args = parser.parse_args()

    do_migration(args.patchMajor, args.patchMinor, args.recipeVersion, args.migrationFile)

import json
import psycopg2
import argparse

def do_migration(patch_major, patch_minor, recipe_version, migration_file):
    connection = psycopg2.connect(user = "aucbot_user",
                                    password = "<PASSWORD>!",
                                    host = "localhost",
                                    port = "55532",
                                    database = "aucbot")

    cursor = connection.cursor()

    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

    cursor.execute('''
    SELECT recipe_version_id
    FROM recipe_versions
    WHERE
        patch_major = %s
        AND patch_minor = %s
        AND recipe_version = %s
    ''', (patch_major, patch_minor, recipe_version))
    res = cursor.fetchall()

    version_id = res[0][0]

    cursor.execute('''
        select * from recipe_professions
    ''')

    res = cursor.fetchall()
    # build recipe dict
    recipe_professions = {}
    for entry in res:
        recipe_professions[entry[1].lower()] = entry[0]

    with open(migration_file, 'r') as f:
        recipes = json.loads(f.read())

        for r in recipes['recipes']:
            prof_id = r['profession']
            if prof_id == "":
                prof_id = recipe_professions['no profession']
            else:
                prof_id = recipe_professions[r['profession'].lower()]
            rank = r['rank']
            if rank == "":
                rank = 0
            else:
                rank = rank[rank.index(' '):]

            cursor.execute('''
            INSERT INTO recipes (spell_id, item_id, profession_id, rank, count, name, recipe_version_id) VALUES
            (%s, %s, %s, %s, %s, %s, %s)
            RETURNING recipe_id
            ''', (r['spellID'], r['itemID'], prof_id, rank, r['count'], r['name'], version_id))
            res = cursor.fetchone()
            recipe_id = res[0]
            print('Inserted recipe %d' % recipe_id)

            for reagent in r['reagents']:
                # insert reagent
                cursor.execute('''
                    INSERT INTO reagents (item_id, count) VALUES
                    (%s, %s)
                    RETURNING reagent_id
                    ''', (reagent['itemID'], reagent['count']))
                res = cursor.fetchone()
                reagent_id = res[0]

                # then insert relationship
                cursor.execute('''
                    INSERT INTO recipe_reagents (recipe_id, reagent_id) VALUES
                    (%s, %s)
                    ''', (recipe_id, reagent_id))

    connection.commit()


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument('--patchMajor', type=int, help='patch major version e.g. "8"')
    parser.add_argument('--patchMinor', type=int, help='patch minor version e.g. "3"')
    parser.add_argument('--recipeVersion', type=int, help='version of recipe data e.g. "1"')
    parser.add_argument('--migrationFile', type=str, help='path to migration file')

    args = parser.parse_args()

    do_migration(args.patchMajor, args.patchMinor, args.recipeVersion, args.migrationFile)
SELECT recipe_version_id     FROM recipe_versions     WHERE         patch_major = %s         AND patch_minor = %s         AND recipe_version = %s select * from recipe_professions # build recipe dict INSERT INTO recipes (spell_id, item_id, profession_id, rank, count, name, recipe_version_id) VALUES             (%s, %s, %s, %s, %s, %s, %s)             RETURNING recipe_id # insert reagent INSERT INTO reagents (item_id, count) VALUES                     (%s, %s)                     RETURNING reagent_id # then insert relationship INSERT INTO recipe_reagents (recipe_id, reagent_id) VALUES                     (%s, %s)