For Migrating Execom Data to SQL Statements

script.py

import json

# load the data from the JSON file
with open('execom.json') as f:
    data = json.load(f)

# define the sids mapping
sids = {
    'main': 0,
    'compsoc': 1,
    'comsoc': 2,
    'pes': 3,
    'aps': 4,
    'sps': 5,
    'ras': 6,
    'wie': 7,
    'sight': 8,
    'cas': 9,
    'sc': 10
}

sql = "INSERT INTO execom_members (sid, firstName, lastName, position, imagePath, tenureStart, tenureEnd) VALUES "

for key, members in data.items():
    sid = sids[key]
    for member in members:
        name_parts = member['name'].split()
        first_name = name_parts[0]
        last_name = ' '.join(name_parts[1:])
        position = member['position']
        image_path = 'https://ieee-rvce.org/assets/images/' + member['image'][1:]
        tenure_start = '2023-01-05'
        tenure_end = 'NULL'
        sql += f"({sid}, '{first_name}', '{last_name}', '{position}', '{image_path}', '{tenure_start}', {tenure_end}),"

# trimming the last comma
sql = sql[:-1]
# add the semicolon at the end
sql += ';'
print(sql)