2022-12-30-mtgo-vintage-metagame-etl-azure-db.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import csv\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "import calendar\n",
    "import requests\n",
    "import pyodbc\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to get MS Azure SQL Database Login information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_login():\n",
    "\tserver = ''\n",
    "\tdb = ''\n",
    "\tuid = ''\n",
    "\tpasswd = ''\n",
    "\twith open(\"C:\\\\Users\\\\chris\\\\Documents\\\\GitHub\\\\Jupyter-Notebooks\\\\azure.txt\") as f:\n",
    "\t\tlines = f.read().split(\"\\n\")\n",
    "\t\tfor i in lines:\n",
    "\t\t\tif i.split(\"=\")[0] == \"server\":\n",
    "\t\t\t\tserver = i.split(\"=\")[1]\n",
    "\t\t\telif i.split(\"=\")[0] == \"db\":\n",
    "\t\t\t\tdb = i.split(\"=\")[1]\n",
    "\t\t\telif i.split(\"=\")[0] == \"uid\":\n",
    "\t\t\t\tuid = i.split(\"=\")[1]\n",
    "\t\t\telif i.split(\"=\")[0] == \"passwd\":\n",
    "\t\t\t\tpasswd = i.split(\"=\")[1]\t\n",
    "\treturn (server, db, uid, passwd)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to save Google Sheet to DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_sheet(spreadsheetID, sheetID, create_date, save):\n",
    "\turl = f\"https://docs.google.com/spreadsheets/d/{spreadsheetID}/gviz/tq?tqx=out:csv&gid={sheetID}\"\n",
    "\tres = requests.get(url)\n",
    "\tif save == True:\n",
    "\t\twith open(f\"vintage-metagame-rawdata-{create_date}.csv\", \"wb\") as f:\n",
    "\t\t\tf.write(res.content)\n",
    "\treturn pd.read_csv(url)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to clean and transform pulled data into two tables: Results and Events."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_merged_data(create_date, save, raw):\n",
    "\tvintage = raw[raw.columns[:11]]\n",
    "\tvintage.columns = ['Rank','Player','Wins','Losses','Byes','Arch','Subarch','Deck','Details','Date','Event_Type']\n",
    "\n",
    "\t# Replace NA values in 'byes' column with 0.\n",
    "\tvintage.Byes = vintage.Byes.fillna(0)\n",
    "\tvintage.Byes = vintage.Byes.astype(\"int\")\n",
    "\tvintage.Deck = vintage.Deck.fillna('NULL')\n",
    "\n",
    "\t# Propagate 'event_type' data to each record.\n",
    "\tvintage[\"Event_Type\"].replace({\"Showcase Qualifier\": \"Showcase_Qualifier\"}, inplace=True)\n",
    "\n",
    "\tevent_type = vintage.Event_Type.tolist()\n",
    "\tfor index,i in enumerate(event_type):\n",
    "\t\tif isinstance(i, str):\n",
    "\t\t\tnew = i\n",
    "\t\telse:\n",
    "\t\t\tevent_type[index] = new\n",
    "\t\n",
    "\tvintage[\"Event_Type\"] = event_type\n",
    "\n",
    "\tevent_cnt = 1\n",
    "\tdate_last = ''\n",
    "\tetype_last = ''\n",
    "\tevent_id = []\n",
    "\tdates_new = []\n",
    "\tfor index,row in vintage.iterrows():\n",
    "\t\tmonth = row['Date'].split(\"/\")[0].zfill(2)\n",
    "\t\tday = row['Date'].split(\"/\")[1].zfill(2)\n",
    "\t\tyear = row['Date'].split(\"/\")[2]\n",
    "\t\tif row['Rank'] == 1:\n",
    "\t\t\tif (f'20{year}-{month}-{day}' == date_last) and (row['Event_Type'] == etype_last):\n",
    "\t\t\t\tevent_cnt += 1\n",
    "\t\t\tif (f'20{year}-{month}-{day}' != date_last) or (row['Event_Type'] != etype_last):\n",
    "\t\t\t\tevent_cnt = 1\n",
    "\t\tetype = row['Event_Type']\n",
    "\t\tevent_id.append(f'20{year}-{month}-{day}-{etype}-{event_cnt}')\n",
    "\t\tdates_new.append(f'20{year}-{month}-{day}')\n",
    "\t\tdate_last = f'20{year}-{month}-{day}'\n",
    "\t\tetype_last = row['Event_Type']\n",
    "\tvintage[\"Event_ID\"] = event_id\n",
    "\n",
    "\t# Create a second table called Events. Remove duplicate records such that each row represents a unique event.\n",
    "\tevents = pd.DataFrame({\"Event_ID\" : event_id, \"Event_Type\" : event_type, \"Date\" : dates_new})\n",
    "\tevents = events.groupby([\"Event_ID\"], as_index=False)[\"Event_Type\", \"Date\"].last()\n",
    "\n",
    "\t# Add 'entries' column to Events table to represents number of players in each event.\n",
    "\tplayers = vintage.groupby([\"Event_ID\"], as_index=False)[\"Rank\"].max()\n",
    "\n",
    "\tevents = events.merge(players, on=\"Event_ID\")\n",
    "\tevents.rename(columns={\"Rank\" : \"Entries\", \"Date\" : \"Event_Date\"}, inplace=True)\n",
    "\n",
    "\t# Add 'day_of_week' column to Events table.\n",
    "\tevents[\"Day_Of_Week\"] = events[\"Event_Date\"].apply(lambda x: calendar.day_name[datetime.strptime(x, \"%Y-%m-%d\").weekday()])\n",
    "\n",
    "\t# Drop 'details' column. Drop 'date' and 'event_type' columns that are now in the Events table.\n",
    "\tvintage.drop([\"Details\"], axis=1, inplace=True)\n",
    "\tvintage.drop([\"Date\"], axis=1, inplace=True)\n",
    "\tvintage.drop([\"Event_Type\"], axis=1, inplace=True)\n",
    "\n",
    "\t# Rename 'rank' column to 'finish'.\n",
    "\tvintage.rename(columns={\"Rank\" : \"Finish\"}, inplace=True)\n",
    "\n",
    "\t# Replace commas and quotes because it breaks importing with SQL commands.\n",
    "\tvintage[\"Arch\"] = vintage[\"Arch\"].apply(lambda x: str(x).replace(\",\", \"\"))\n",
    "\tvintage[\"Subarch\"] = vintage[\"Subarch\"].apply(lambda x: str(x).replace(\",\", \"\"))\n",
    "\tvintage[\"Deck\"] = vintage[\"Deck\"].apply(lambda x: str(x).replace(\",\", \"\"))\n",
    "\t\n",
    "\t#vintage['arch'] = vintage['arch'].apply(lambda x: x.replace(\"'\", ''))\n",
    "\t#vintage['subarch'] = vintage['subarch'].apply(lambda x: x.replace(\"'\", ''))\n",
    "\t#vintage['deck'] = vintage['deck'].apply(lambda x: x.replace(\"'\", ''))\n",
    "\t\n",
    "\t# Save and export to CSV.\n",
    "\tif save == True:\n",
    "\t\tvintage.to_csv(f\"vintage-results-{create_date}.csv\", index=False)\n",
    "\t\tevents.to_csv(f\"vintage-events-{create_date}.csv\", index=False)\n",
    "\treturn (vintage, events)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pull data to be cleaned, transformed, and inserted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_date = date.today().strftime(\"%Y-%m-%d\")\n",
    "df = get_sheet(\"1wxR3iYna86qrdViwHjUPzHuw6bCNeMLb72M25hpUHYk\", \"1693401931\", create_date, save=False)\n",
    "results, events = clean_merged_data(create_date=create_date, save=False, raw=df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to generate and run Insert/Update commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def upload_data(cursor, table):\n",
    "    global added\n",
    "    global updated\n",
    "\n",
    "    def get_indexes(pkey_index, row_length):\n",
    "        return (pkey_index, [x for x in range(row_length) if x not in pkey_index])\n",
    "\n",
    "    for i in table:\n",
    "        added = 0\n",
    "        updated = 0\n",
    "        if i == 'Results':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[8, 0], row_length=len(results.columns.to_list()))\n",
    "            columns = results.columns.to_list()\n",
    "            table_name = 'vintage_results'\n",
    "            rows = results\n",
    "        elif i == 'Events':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0], row_length=len(events.columns.to_list()))\n",
    "            columns = events.columns.to_list()\n",
    "            table_name = 'vintage_events'\n",
    "            rows = events\n",
    "\n",
    "        for index,row in rows.iterrows():\n",
    "            insert_str = f'INSERT INTO {table_name} ('\n",
    "            for index,i in enumerate(columns):\n",
    "                insert_str += f'{i}'\n",
    "                if index != len(row)-1:\n",
    "                    insert_str += ', '\n",
    "            insert_str += ') VALUES ('\n",
    "            for i in range(len(row)):\n",
    "                insert_str += '?'\n",
    "                if i != len(row)-1:\n",
    "                    insert_str += ', '\n",
    "            insert_str += ')'\n",
    "            #print(insert_str)\n",
    "            #print(tuple(row.to_list()))\n",
    "            \n",
    "            update_str = f'UPDATE {table_name} SET '\n",
    "            for index,i in enumerate(value_index):\n",
    "                update_str += columns[i] + ' = '\n",
    "                if row.to_list()[i] == 'NULL':\n",
    "                    update_str += 'NULL'\n",
    "                elif type(row.to_list()[i]) == str:\n",
    "                    update_str += \"'\" + row.to_list()[i].replace(\"'\", \"''\") + \"'\"\n",
    "                else:\n",
    "                    update_str += str(row.to_list()[i])\n",
    "                if index != len(value_index)-1:\n",
    "                    update_str += ', '\n",
    "            update_str += ' WHERE '\n",
    "            for index,i in enumerate(pkey_index):\n",
    "                update_str += columns[i] + ' = '\n",
    "                if type(row.to_list()[i]) == str:\n",
    "                    update_str += \"'\" + row.to_list()[i] + \"'\"\n",
    "                else:\n",
    "                    update_str += str(row.to_list()[i])\n",
    "                if index != len(pkey_index)-1:\n",
    "                    update_str += ' AND '\n",
    "            #print(update_str)\n",
    "\n",
    "            try:\n",
    "                cursor.execute(insert_str, tuple(row.to_list()))\n",
    "                added += 1\n",
    "            except:\n",
    "                cursor.execute(update_str)\n",
    "                updated += 1\n",
    "        print(added, updated)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subset data before running Insert/Update commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_datetime(x):\n",
    "    return datetime.strptime(x, \"%Y-%m-%d\")\n",
    "\n",
    "#start_date = '2023-01-01'\n",
    "start_date = input('Enter import start date (YYYY-MM-DD): ')\n",
    "try:\n",
    "    start_date = datetime.strptime(start_date, \"%Y-%m-%d\")\n",
    "except ValueError:\n",
    "    start_date = datetime.strptime('01-01-2020', \"%Y-%m-%d\")\n",
    "\n",
    "events = events[events.Event_Date.apply(to_datetime) >= start_date]\n",
    "results = results[results.Event_ID.isin(events.Event_ID.unique().tolist())]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to Database and run function to upload data to SQL Database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "server, db, uid, passwd = get_login()\n",
    "conn_str = 'DRIVER={SQL Server};SERVER=tcp:' + f'{server};PORT=1433;DATABASE={db};UID={uid};PWD={passwd}'\n",
    "conn = pyodbc.connect(conn_str)\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Iterate over each row and upload to database. (~26 mins)\n",
    "# Events have to be added first because they are foreign keys for Results records.\n",
    "upload_data(cursor=cursor, table=['Events', 'Results'])\n",
    "\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.2"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "edf259275ad4a72d4dd5b452264ad5fb2b635233dff2a31edc6ebc740e55e21b"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}