2023-01-02-chess-pgn-update-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 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": 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 generate and run Insert/Update commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def upload_data(cursor, df):\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",
    "    added = 0\n",
    "    updated = 0\n",
    "\n",
    "    pkey_index, value_index = get_indexes(pkey_index=[0, 2, 5, 6, 13], row_length=len(df.columns.to_list()))\n",
    "    columns = df.columns.to_list()\n",
    "    table_name = 'chesspgn_moves'\n",
    "\n",
    "    for index,row in df.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))\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",
    "            elif str(row.to_list()[i]) == 'True':\n",
    "                update_str += '1'\n",
    "            elif str(row.to_list()[i]) == 'False':\n",
    "                update_str += '0'\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[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(row.to_list())\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": [
    "### Load data to be inserted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "curr_path = os.getcwd()\n",
    "os.chdir(path='G:\\\\My Drive\\\\Datasets\\\\Chess PGN')\n",
    "df = pd.read_csv('chesspgn_moves.csv')\n",
    "df = df.fillna('NULL')\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": 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.\n",
    "# ['Drafts', 'Picks', 'Matches', 'Matches_Inverted', 'Games', 'Games_Inverted', 'Plays']\n",
    "upload_data(cursor=cursor, df=df)\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
}