populate.py

import csv
import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wpbvre999',
                             db='cs490',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

def populate_class(reader):
    sql = "INSERT INTO class (name, meets_at, room, fid) VALUES "
    values = ''
    for row in list(reader):
        values += "('%s', '%s', '%s', %s)," % tuple(row)
    return sql + values[:-1]+';'

def populate_enrolled(reader):
    sql = "INSERT INTO enrolled (snum, cname) VALUES "
    values = ''
    for row in list(reader):
        values += "(%s, '%s')," % tuple(row)
    return sql + values[:-1]+';'

def populate_faculty(reader):
    sql = "INSERT INTO faculty (fid, fname, deptid) VALUES "
    values = ''
    for row in list(reader):
        values += "(%s, '%s', %s)," % tuple(row)
    return sql + values[:-1]+';'

def populate_student(reader):
    sql = "INSERT INTO student (snum, sname, major, level, age) VALUES "
    values = ''
    for row in list(reader):
        values += "(%s, '%s', '%s', '%s', %s)," % tuple(row)
    return sql + values[:-1]+';'

if __name__ == "__main__":
    paths = map(lambda x: 'data/%s.csv' % (x), 
        ['class',
         'enrolled',
         'faculty',
         'student'])
    readers = list(map(lambda x: csv.reader(open(x)), paths))

    statements = [
        populate_faculty(readers[2]),
        populate_class(readers[0]),
        populate_student(readers[3]),
        populate_enrolled(readers[1])]

    try:
        with connection.cursor() as cursor:
            for statement in statements:
                cursor.execute(statement)
            connection.commit()
        connection.close()
    except Exception as e:
        print(e)