2023-01-02-chess-pgn-update-column-types-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": [
    "### Functions to generate and run Alter Table commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def alter_cols(cursor, df, type_dict):\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",
    "    pkey_index, value_index = get_indexes(pkey_index=[0, 14], row_length=len(df.columns.to_list()))\n",
    "    columns = df.columns.to_list()\n",
    "    table_name = 'chesspgn_moves'\n",
    "    rows = df\n",
    "\n",
    "    for i in columns:\n",
    "        alter_str = f'ALTER TABLE {table_name} ALTER COLUMN {i} '\n",
    "        if type_dict[i][0] == str:\n",
    "            alter_str += f'VARCHAR({type_dict[i][1]})'\n",
    "        elif type_dict[i][0] == int:\n",
    "            alter_str += 'INT'\n",
    "        elif type_dict[i][0] == float:\n",
    "            alter_str += 'DECIMAL'\n",
    "        elif type_dict[i][0] == bool:\n",
    "            alter_str += 'BIT'\n",
    "        if type_dict[i][2] == False:\n",
    "            alter_str += ' NOT NULL'\n",
    "        elif type_dict[i][2] == True:\n",
    "            alter_str += ' NULL'\n",
    "        print(alter_str)\n",
    "        cursor.execute(alter_str)\n",
    "\n",
    "def set_pkeys(cursor, table_name, pkeys):\n",
    "    alter_str = f'ALTER TABLE {table_name} ADD CONSTRAINT pk_column_name PRIMARY KEY ('\n",
    "    for index,i in enumerate(pkeys):\n",
    "        alter_str += f'{i}'\n",
    "        if index != len(pkeys)-1:\n",
    "            alter_str += ', '\n",
    "    alter_str += ')'\n",
    "    print(alter_str)\n",
    "    cursor.execute(alter_str)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load column data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('C:\\\\Users\\\\chris\\\\Documents\\\\GitHub\\\\Scripts\\\\chesspgn_moves.csv')\n",
    "type_dict = pickle.load(open('C:\\\\Users\\\\chris\\\\Documents\\\\GitHub\\\\Scripts\\\\pgn_col_types',\"rb\"))"
   ]
  },
  {
   "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",
    "alter_cols(cursor=cursor, df=df, type_dict=type_dict)\n",
    "set_pkeys(cursor=cursor, table_name='chesspgn_moves', pkeys=['W_Name', 'B_Name', 'Date', 'Time', 'Move_Num'])\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
}