2023-01-01-mtgo-tracker-update-azure-db.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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 os\n",
    "import pickle\n",
    "import copy\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": 2,
   "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": [
    "### Functions to get column headers and to invert our tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def header(table):\n",
    "    if table == \"Matches\":\n",
    "        return [\"Match_ID\",\"Draft_ID\",\"P1\",\"P1_Arch\",\"P1_Subarch\",\"P2\",\"P2_Arch\",\"P2_Subarch\",\"P1_Roll\",\"P2_Roll\",\n",
    "                \"Roll_Winner\",\"P1_Wins\",\"P2_Wins\",\"Match_Winner\",\"Format\",\"Limited_Format\",\"Match_Type\",\"Date\"]\n",
    "    elif table == \"Games\":\n",
    "        return [\"Match_ID\",\"P1\",\"P2\",\"Game_Num\",\"PD_Selector\",\"PD_Choice\",\"On_Play\",\"On_Draw\",\"P1_Mulls\",\n",
    "                \"P2_Mulls\",\"Turns\",\"Game_Winner\"]\n",
    "    elif table == \"Plays\":\n",
    "        return [\"Match_ID\",\"Game_Num\",\"Play_Num\",\"Turn_Num\",\"Casting_Player\",\"Action\",\"Primary_Card\",\"Target1\",\n",
    "                \"Target2\",\"Target3\",\"Opp_Target\",\"Self_Target\",\"Cards_Drawn\",\"Attackers\",\"Active_Player\",\"Nonactive_Player\"]\n",
    "    elif table == \"Drafts\":\n",
    "        return [\"Draft_ID\",\"Hero\",\"Player_2\",\"Player_3\",\"Player_4\",\"Player_5\",\"Player_6\",\"Player_7\",\"Player_8\",\n",
    "                \"Match_Wins\",\"Match_Losses\",\"Format\",\"Date\"]\n",
    "    elif table == \"Picks\":\n",
    "        return [\"Draft_ID\",\"Card\",\"Pack_Num\",\"Pick_Num\",\"Pick_Ovr\",\"Avail_1\",\"Avail_2\",\"Avail_3\",\"Avail_4\",\"Avail_5\",\n",
    "                \"Avail_6\",\"Avail_7\",\"Avail_8\",\"Avail_9\",\"Avail_10\",\"Avail_11\",\"Avail_12\",\"Avail_13\",\"Avail_14\"]\n",
    "    return []\n",
    "    \n",
    "def invert_join(ad):\n",
    "    # Input:  List[List[Matches],List[Games],List[Plays]]\n",
    "    # Output: List[List[Matches],List[Games],List[Plays]]\n",
    "\n",
    "    def swap_cols(data,header,col_a,col_b):\n",
    "        # Input:  List[Matches or Games],List[Headers],String,String\n",
    "        # Output: List[Matches]   \n",
    "\n",
    "        for index,i in enumerate(header):\n",
    "            if i == col_a:\n",
    "                a = index\n",
    "            elif i == col_b:\n",
    "                b = index\n",
    "        data[a], data[b] = data[b], data[a]\n",
    "\n",
    "    def invert_matchdata(data):\n",
    "        # Input:  List[Matches]\n",
    "        # Output: List[Matches]\n",
    "\n",
    "        swap_cols(data,header(\"Matches\"),\"P1\",\"P2\")\n",
    "        swap_cols(data,header(\"Matches\"),\"P1_Arch\",\"P2_Arch\")\n",
    "        swap_cols(data,header(\"Matches\"),\"P1_Subarch\",\"P2_Subarch\")\n",
    "        swap_cols(data,header(\"Matches\"),\"P1_Roll\",\"P2_Roll\")\n",
    "        swap_cols(data,header(\"Matches\"),\"P1_Wins\",\"P2_Wins\")\n",
    "\n",
    "        cols_to_invert = [\"Match_Winner\",\"Roll_Winner\"]\n",
    "        for i in cols_to_invert:\n",
    "            for index,j in enumerate(header(\"Matches\")):\n",
    "                if j == i:\n",
    "                    a = index\n",
    "            if data[a] == \"P1\":\n",
    "                data[a] = \"P2\"\n",
    "            elif data[a] == \"P2\":\n",
    "                data[a] = \"P1\"\n",
    "\n",
    "    def invert_gamedata(data):\n",
    "        # Input:  List[Games]\n",
    "        # Output: List[Games]\n",
    "\n",
    "        swap_cols(data,header(\"Games\"),\"P1\",\"P2\")\n",
    "        swap_cols(data,header(\"Games\"),\"P1_Mulls\",\"P2_Mulls\")\n",
    "        swap_cols(data,header(\"Games\"),\"On_Play\",\"On_Draw\")\n",
    "        \n",
    "        cols_to_invert = [\"PD_Selector\",\"Game_Winner\"]\n",
    "        for i in cols_to_invert:\n",
    "            for index,j in enumerate(header(\"Games\")):\n",
    "                if j == i:\n",
    "                    a = index\n",
    "            if data[a] == \"P1\":\n",
    "                data[a] = \"P2\"\n",
    "            elif data[a] == \"P2\":\n",
    "                data[a] = \"P1\"\n",
    "\n",
    "    ad_inverted = copy.deepcopy(ad)\n",
    "    for i in ad_inverted[0]:\n",
    "        invert_matchdata(i)\n",
    "    for i in ad_inverted[1]:\n",
    "        invert_gamedata(i)\n",
    "\n",
    "    ad_inverted[0] += ad[0]\n",
    "    ad_inverted[1] += ad[1]\n",
    "\n",
    "    return ad_inverted"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to generate and run Insert/Update commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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 == 'Drafts':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0], row_length=len(header(table='Drafts')))\n",
    "            columns = header(table='Drafts')\n",
    "            table_name = 'mtgo_drafts'\n",
    "            rows = DRAFTS_TABLE\n",
    "        elif i == 'Picks':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0, 4], row_length=len(header(table='Picks')))\n",
    "            columns = header(table='Picks')\n",
    "            table_name = 'mtgo_picks'\n",
    "            rows = PICKS_TABLE\n",
    "        elif i == 'Matches':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0], row_length=len(header(table='Matches')))\n",
    "            columns = header(table='Matches')\n",
    "            table_name = 'mtgo_matches'\n",
    "            rows = ALL_DATA[0]\n",
    "        elif i == 'Matches_Inverted':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0, 2], row_length=len(header(table='Matches')))\n",
    "            columns = header(table='Matches')\n",
    "            table_name = 'mtgo_matches_inverted'\n",
    "            rows = ALL_DATA_INVERTED[0]\n",
    "        elif i == 'Games':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0, 3], row_length=len(header(table='Games')))\n",
    "            columns = header(table='Games')\n",
    "            table_name = 'mtgo_games'\n",
    "            rows = ALL_DATA[1]\n",
    "        elif i == 'Games_Inverted':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0, 3, 2], row_length=len(header(table='Games')))\n",
    "            columns = header(table='Games')\n",
    "            table_name = 'mtgo_games_inverted'\n",
    "            rows = ALL_DATA_INVERTED[1]\n",
    "        elif i == 'Plays':\n",
    "            pkey_index, value_index = get_indexes(pkey_index=[0, 1, 2], row_length=len(header(table='Plays')))\n",
    "            columns = header(table='Plays')\n",
    "            table_name = 'mtgo_plays'\n",
    "            rows = ALL_DATA[2]\n",
    "\n",
    "        for row in rows:\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))\n",
    "            \n",
    "            update_str = f'UPDATE {table_name} SET '\n",
    "            for index,i in enumerate(value_index):\n",
    "                update_str += columns[i] + ' = '\n",
    "                if type(row[i]) == str:\n",
    "                    update_str += \"'\" + row[i].replace(\"'\", \"''\") + \"'\"\n",
    "                else:\n",
    "                    update_str += str(row[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[i]) == str:\n",
    "                    update_str += \"'\" + row[i] + \"'\"\n",
    "                else:\n",
    "                    update_str += str(row[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))\n",
    "                added += 1\n",
    "            except:\n",
    "                cursor.execute(update_str)\n",
    "                updated += 1\n",
    "        print(added, updated)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data to be inserted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "curr_path = os.getcwd()\n",
    "os.chdir(path='c:\\\\Users\\\\chris\\\\Documents\\\\GitHub\\\\MTGO-Tracker\\\\save\\\\')\n",
    "ALL_DATA = pickle.load(open(\"ALL_DATA\",\"rb\"))\n",
    "DRAFTS_TABLE = pickle.load(open(\"DRAFTS_TABLE\",\"rb\"))\n",
    "PICKS_TABLE = pickle.load(open(\"PICKS_TABLE\",\"rb\"))\n",
    "ALL_DATA_INVERTED = invert_join(ALL_DATA)\n",
    "os.chdir(curr_path)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to Database and run function to upload data to SQL Database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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.\n",
    "# ['Drafts', 'Picks', 'Matches', 'Matches_Inverted', 'Games', 'Games_Inverted', 'Plays']\n",
    "upload_data(cursor=cursor, table=['Plays'])\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 (tags/v3.10.2:a58ebcc, Jan 17 2022, 14:12:15) [MSC v.1929 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "edf259275ad4a72d4dd5b452264ad5fb2b635233dff2a31edc6ebc740e55e21b"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}