premier_league_import.py

import json
import pymysql
import pymysql.cursors
import os
import csv
from datetime import datetime

with open("data/premier-league/schema.json") as fh:
    schema = json.loads(fh.read())
    fields = schema["fields"]

    columns = []
    for field in fields:
        if field["type"] == "string":
            dbtype = "varchar(255)"
        else:
            dbtype = field["type"]

        columns.append("    `{}` {}".format(
                field["name"].lower(),
                dbtype))

    sql_create = "CREATE TABLE games (\n"
    sql_create += ",\n".join(columns)
    sql_create += "\n) ENGINE=InnoDB"

    print(sql_create)

conn = pymysql.connect(host='10.0.1.20',
                       user='instructor',
                       password='',
                       db='instructor',
                       charset='utf8mb4',
                       cursorclass=pymysql.cursors.DictCursor)

with conn.cursor() as cursor:
    cursor.execute("DROP TABLE games")
    cursor.execute(sql_create)

with conn.cursor() as cursor:
    srcdir = "data/premier-league"
    files = sorted(os.listdir(srcdir))
    for file in files:
        if not file.endswith(".csv"): continue

        file = srcdir + "/" + file
        with open(file, "r") as fh:
            reader = csv.DictReader(fh, delimiter=',')
            for game in reader:

                date = datetime.strptime(game["Date"], "%d/%m/%y")
                date = date.date()

                game["Date"] = date

                columnlist = game.keys()
                dbcolumnlist = []
                for column in columnlist:
                    dbcolumnlist.append("`{}`".format(column.lower()))
                dbcolumnlist = ",".join(dbcolumnlist)
                placeholders = ",".join(["%s"]*len(columnlist))
                sql_insert = "INSERT INTO games ({}) VALUES ({})".format(dbcolumnlist, placeholders)

                print(sql_insert)

                cursor.execute(sql_insert, list(game.values()))
            conn.commit()