Mds_BizdataModeling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a747ff44",
   "metadata": {},
   "source": [
    "### Sample Business Data modeling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d694f507",
   "metadata": {},
   "source": [
    "Notebook models the sample dataset that has been retrived from AWS data exchange. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "247bf59d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d6cdeb7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "config = configparser.ConfigParser()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5b50a309",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clustermds.config']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config.read('clustermds.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c4bb5934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'mdsusbiz'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config['POSTGRES']['PG_DB']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4d719646",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = config['POSTGRES']['PG_DB']\n",
    "user = config['POSTGRES']['PG_UNAME']\n",
    "passwd = config['POSTGRES']['PG_PASS']\n",
    "port = config['POSTGRES']['PG_PORT']\n",
    "host = config['POSTGRES']['PG_HOST']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc18ed89",
   "metadata": {},
   "source": [
    "Using the pandas internal read_sql method to get the data from the DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "86635a7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "53435a3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/mdsusbiz'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fd428ffd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#using psycopg2 to test connection since there are no tables\n",
    "\n",
    "import psycopg2\n",
    "try:\n",
    "    conn = psycopg2.connect(host=host,dbname=db,user=user,password=passwd,port=port)\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e4856ca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f464add2",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "except:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a43078b",
   "metadata": {},
   "source": [
    "Following steps to start the data modeling\n",
    "\n",
    "0) Ingest the data into the database under mds Raw Table\n",
    "\n",
    "1) Identify the primary key that will be used as reference. Design the schema around the data\n",
    "\n",
    "2) Design the facttable and dimension table\n",
    "\n",
    "4) Create tables and insert data into the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f8099c7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdsdata = pd.read_csv('sample_mdsUsBusinessDataNoContacts.txt',delimiter='|')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "419d82d1",
   "metadata": {},
   "source": [
    "#### Writing additional helpers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "70edda81",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using pandas read_sql for getting schema\n",
    "def getSchema(tableName, credentials):\n",
    "    schema = pd.read_sql(\"\"\"SELECT * FROM information_schema.columns where table_name='{}'\"\"\".format(tableName),con=credentials)\n",
    "    return schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0d3944a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Issue is in using pd.read_sql to write data to the database. so using psycopg2\n",
    "def queryTable(query):\n",
    "    try:\n",
    "        schema = cur.execute(query)\n",
    "        return \n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        \n",
    "#This doesn't return anything"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5d1587d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using the pd.read_sql for getting data from db\n",
    "def queryBase(query):\n",
    "    requiredTable = pd.read_sql(query,con=credentials)\n",
    "    return requiredTable\n",
    "\n",
    "#This returns the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3beb7697",
   "metadata": {},
   "source": [
    "Data Specification:\n",
    "\n",
    "Row: 2000 entries, 0 to 1999\n",
    "\n",
    "Columns: 163 entries, EFX_ID to EFX_EXTRACT_DATE\n",
    "\n",
    "dtypes: float64(42), int64(26), object(95)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9c4781a1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def schemaGen(dataframe, schemaName):\n",
    "    localSchema = pd.io.sql.get_schema(dataframe,schemaName)\n",
    "    localSchema = localSchema.replace('TEXT','VARCHAR(255)').replace('INTEGER','NUMERIC').replace('\\n','').replace('\"',\"\")\n",
    "    return \"\".join(localSchema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "9cff8413",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CREATE TABLE mdsdata (EFX_ID NUMERIC,  EFX_NAME VARCHAR(255),  EFX_LEGAL_NAME VARCHAR(255),  EFX_ADDRESS VARCHAR(255),  EFX_CITY VARCHAR(255),  EFX_STATE VARCHAR(255),  EFX_STATEC NUMERIC,  EFX_ZIPCODE NUMERIC,  EFX_ZIP4 REAL,  EFX_LAT REAL,  EFX_LO- REAL,  EFX_GEOPREC NUMERIC,  EFX_REGION REAL,  EFX_CTRYISOCD VARCHAR(255),  EFX_CTRYNUM NUMERIC,  EFX_CTRYNAME VARCHAR(255),  EFX_COUNTYNM VARCHAR(255),  EFX_COUNTY NUMERIC,  EFX_CMSA VARCHAR(255),  EFX_CMSADESC VARCHAR(255),  EFX_SOHO VARCHAR(255),  EFX_BIZ VARCHAR(255),  EFX_RES VARCHAR(255),  EFX_CMRA VARCHAR(255),  EFX_CONGRESS NUMERIC,  EFX_SECADR VARCHAR(255),  EFX_SECCTY VARCHAR(255),  EFX_SECSTAT VARCHAR(255),  EFX_STATEC2 REAL,  EFX_SECZIP REAL,  EFX_SECZIP4 REAL,  EFX_SECLAT REAL,  EFX_SECLO- REAL,  EFX_SECGEOPREC REAL,  EFX_SECREGION REAL,  EFX_SECCTRYISOCD VARCHAR(255),  EFX_SECCTRYNUM NUMERIC,  EFX_SECCTRYNAME VARCHAR(255),  EFX_CTRYTELCD NUMERIC,  EFX_GENDER VARCHAR(255),  EFX_ETHNICITY VARCHAR(255),  EFX_MBE VARCHAR(255),  EFX_WBE VARCHAR(255),  EFX_VET VARCHAR(255),  EFX_BUSSIZE VARCHAR(255),  EFX_GOV VARCHAR(255),  EFX_FGOV VARCHAR(255),  EFX_NONPROFIT VARCHAR(255),  EFX_EDU VARCHAR(255),  EFX_BUSSTAT VARCHAR(255),  EFX_BUSSTATCD REAL,  EFX_WEB VARCHAR(255),  EFX_YREST NUMERIC,  EFX_CORPEMPCNT NUMERIC,  EFX_LOCEMPCNT NUMERIC,  EFX_CORPEMPCD VARCHAR(255),  EFX_LOCEMPCD VARCHAR(255),  EFX_CORPAMOUNT NUMERIC,  EFX_CORPAMOUNTCD VARCHAR(255),  EFX_CORPAMOUNTTP VARCHAR(255),  EFX_CORPAMOUNTPREC VARCHAR(255),  EFX_LOCAMOUNT NUMERIC,  EFX_LOCAMOUNTCD VARCHAR(255),  EFX_LOCAMOUNTTP VARCHAR(255),  EFX_LOCAMOUNTPREC VARCHAR(255),  EFX_PUBLIC REAL,  EFX_STKEXC REAL,  EFX_TCKSYM REAL,  EFX_PRIMSIC REAL,  EFX_SECSIC1 REAL,  EFX_SECSIC2 REAL,  EFX_SECSIC3 REAL,  EFX_SECSIC4 REAL,  EFX_PRIMSICDESC VARCHAR(255),  EFX_SECSICDESC1 VARCHAR(255),  EFX_SECSICDESC2 VARCHAR(255),  EFX_SECSICDESC3 VARCHAR(255),  EFX_SECSICDESC4 VARCHAR(255),  EFX_PRIMNAICSCODE REAL,  EFX_SECNAICS1 REAL,  EFX_SECNAICS2 REAL,  EFX_SECNAICS3 REAL,  EFX_SECNAICS4 REAL,  EFX_PRIMNAICSDESC VARCHAR(255),  EFX_SECNAICSDESC1 VARCHAR(255),  EFX_SECNAICSDESC2 VARCHAR(255),  EFX_SECNAICSDESC3 VARCHAR(255),  EFX_SECNAICSDESC4 VARCHAR(255),  EFX_LEGSUBNUMALL NUMERIC,  EFX_LEGSUBNAMEALL VARCHAR(255),  EFX_LEGSUBADDRESSALL VARCHAR(255),  EFX_LEGSUBCITYALL VARCHAR(255),  EFX_LEGSUBSTATEALL VARCHAR(255),  EFX_LEGSUBZIPCODEALL NUMERIC,  EFX_LEGSUBZIP4ALL REAL,  EFX_LEGSUBCOUNTYALL REAL,  EFX_LEGSUBCTRYISOCDALL VARCHAR(255),  EFX_LEGSUBCTRYNUMALL NUMERIC,  EFX_LEGSUBCTRYNAMEALL VARCHAR(255),  EFX_LEGDOMULTNUMALL NUMERIC,  EFX_LEGDOMULTNAMEALL VARCHAR(255),  EFX_LEGDOMULTADDRESSALL VARCHAR(255),  EFX_LEGDOMULTCITYALL VARCHAR(255),  EFX_LEGDOMULTSTATEALL VARCHAR(255),  EFX_LEGDOMULTZIPCODEALL NUMERIC,  EFX_LEGDOMULTZIP4ALL REAL,  EFX_LEGDOMULTCOUNTYALL REAL,  EFX_LEGDOMULTCTRYISOCDALL VARCHAR(255),  EFX_LEGDOMULTCTRYNUMALL NUMERIC,  EFX_LEGDOMULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTNUMALL NUMERIC,  EFX_LEGULTNAMEALL VARCHAR(255),  EFX_LEGULTADDRESSALL VARCHAR(255),  EFX_LEGULTCITYALL VARCHAR(255),  EFX_LEGULTSTATEALL VARCHAR(255),  EFX_LEGULTZIPCODEALL VARCHAR(255),  EFX_LEGULTZIP4ALL REAL,  EFX_LEGULTCOUNTYALL REAL,  EFX_LEGULTCTRYISOCDALL VARCHAR(255),  EFX_LEGULTCTRYNUMALL NUMERIC,  EFX_LEGULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTPARENTIND VARCHAR(255),  EFX_LEGPARENTIND VARCHAR(255),  EFX_LEGLINKEDIND VARCHAR(255),  EFX_AFFLULTNUMALL NUMERIC,  EFX_AFFLULTNAMEALL VARCHAR(255),  EFX_AFFLULTADDRESSALL VARCHAR(255),  EFX_AFFLULTCITYALL VARCHAR(255),  EFX_AFFLULTSTATEALL VARCHAR(255),  EFX_AFFLULTZIPCODEALL REAL,  EFX_AFFLULTZIP4ALL REAL,  EFX_AFFULTCOUNTYALL REAL,  EFX_AFFLULTCTRYISOCDALL VARCHAR(255),  EFX_AFFLULTCTRYNUMALL NUMERIC,  EFX_AFFLULTCTRYNAMEALL VARCHAR(255),  EFX_AFFLLINKEDIND VARCHAR(255),  EFX_FRANCHISE VARCHAR(255),  EFX_AFFLULTPARENTIND VARCHAR(255),  EFX_AFFLPARENTIND VARCHAR(255),  EFX_FOREIGN VARCHAR(255),  EFX_FAILRATE REAL,  EFX_FAILLEVEL REAL,  EFX_FAILREASON1 VARCHAR(255),  EFX_FAILREASON2 VARCHAR(255),  EFX_FAILREASON3 VARCHAR(255),  EFX_FAILREASON4 VARCHAR(255),  EFX_CREDITSCORE REAL,  EFX_CREDITCLASS REAL,  EFX_CREDITPERC REAL,  EFX_CREDITREASON1 VARCHAR(255),  EFX_CREDITREASON2 VARCHAR(255),  EFX_CREDITREASON3 VARCHAR(255),  EFX_CREDITREASON4 VARCHAR(255),  EFX_BANKRUPTCY VARCHAR(255),  EFX_DEAD REAL,  EFX_DEADDT REAL,  EFX_MRKT_TELEVER VARCHAR(255),  EFX_MRKT_TELESCORE NUMERIC,  EFX_MRKT_TOTALSCORE NUMERIC,  EFX_MRKT_TOTALIND VARCHAR(255),  EFX_MRKT_VACANT VARCHAR(255),  EFX_MRKT_SEASONAL REAL,  EFX_EXTRACT_DATE VARCHAR(255))'"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schemaGen(mdsdata,'mdsdata')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "dc3d4032",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "ResourceClosedError",
     "evalue": "This result object does not return rows. It has been closed automatically.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mResourceClosedError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [18], line 8\u001b[0m\n\u001b[1;32m      1\u001b[0m createTable1 \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\"\"\u001b[39m\u001b[38;5;124mCREATE TABLE mdsdata (EFX_ID NUMERIC,  EFX_NAME VARCHAR(255),  EFX_LEGAL_NAME VARCHAR(255),  EFX_ADDRESS VARCHAR(255),  EFX_CITY VARCHAR(255),  EFX_STATE VARCHAR(255),  EFX_STATEC NUMERIC,  EFX_ZIPCODE NUMERIC,  EFX_ZIP4 REAL,  EFX_LAT REAL,  EFX_LO REAL,  EFX_GEOPREC NUMERIC,  EFX_REGION REAL,  EFX_CTRYISOCD VARCHAR(255),  EFX_CTRYNUM NUMERIC,  EFX_CTRYNAME VARCHAR(255),  EFX_COUNTYNM VARCHAR(255),  EFX_COUNTY NUMERIC,  EFX_CMSA VARCHAR(255),  EFX_CMSADESC VARCHAR(255),  EFX_SOHO VARCHAR(255),  EFX_BIZ VARCHAR(255),  EFX_RES VARCHAR(255),  EFX_CMRA VARCHAR(255),  EFX_CONGRESS NUMERIC,  EFX_SECADR VARCHAR(255),  EFX_SECCTY VARCHAR(255),  EFX_SECSTAT VARCHAR(255),  EFX_STATEC2 REAL,  EFX_SECZIP REAL,  EFX_SECZIP4 REAL,  EFX_SECLAT REAL,  EFX_SECLO REAL,  EFX_SECGEOPREC REAL,  EFX_SECREGION REAL,  EFX_SECCTRYISOCD VARCHAR(255),  EFX_SECCTRYNUM NUMERIC,  EFX_SECCTRYNAME VARCHAR(255),  EFX_CTRYTELCD NUMERIC,  EFX_GENDER VARCHAR(255),  EFX_ETHNICITY VARCHAR(255),  EFX_MBE VARCHAR(255),  EFX_WBE VARCHAR(255),  EFX_VET VARCHAR(255),  EFX_BUSSIZE VARCHAR(255),  EFX_GOV VARCHAR(255),  EFX_FGOV VARCHAR(255),  EFX_NONPROFIT VARCHAR(255),  EFX_EDU VARCHAR(255),  EFX_BUSSTAT VARCHAR(255),  EFX_BUSSTATCD REAL,  EFX_WEB VARCHAR(255),  EFX_YREST NUMERIC,  EFX_CORPEMPCNT NUMERIC,  EFX_LOCEMPCNT NUMERIC,  \u001b[39m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124mEFX_CORPEMPCD VARCHAR(255),  EFX_LOCEMPCD VARCHAR(255),  EFX_CORPAMOUNT NUMERIC,  EFX_CORPAMOUNTCD VARCHAR(255),  EFX_CORPAMOUNTTP VARCHAR(255),  EFX_CORPAMOUNTPREC VARCHAR(255),  EFX_LOCAMOUNT NUMERIC,  EFX_LOCAMOUNTCD VARCHAR(255),  EFX_LOCAMOUNTTP VARCHAR(255),  EFX_LOCAMOUNTPREC VARCHAR(255),  EFX_PUBLIC REAL,  \u001b[39m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124mEFX_STKEXC REAL,  EFX_TCKSYM REAL,  EFX_PRIMSIC REAL,  EFX_SECSIC1 REAL,  EFX_SECSIC2 REAL,  EFX_SECSIC3 REAL,  EFX_SECSIC4 REAL,  EFX_PRIMSICDESC VARCHAR(255),  \u001b[39m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[38;5;124mEFX_SECNAICSDESC4 VARCHAR(255),  EFX_LEGSUBNUMALL NUMERIC,  EFX_LEGSUBNAMEALL VARCHAR(255),  EFX_LEGSUBADDRESSALL VARCHAR(255),  EFX_LEGSUBCITYALL VARCHAR(255),  EFX_LEGSUBSTATEALL VARCHAR(255),  EFX_LEGSUBZIPCODEALL NUMERIC,  \u001b[39m\n\u001b[1;32m      7\u001b[0m \u001b[38;5;124mEFX_LEGSUBZIP4ALL REAL,  EFX_LEGSUBCOUNTYALL REAL,  EFX_LEGSUBCTRYISOCDALL VARCHAR(255),  EFX_LEGSUBCTRYNUMALL NUMERIC,  EFX_LEGSUBCTRYNAMEALL VARCHAR(255),  EFX_LEGDOMULTNUMALL NUMERIC,  EFX_LEGDOMULTNAMEALL VARCHAR(255),  EFX_LEGDOMULTADDRESSALL VARCHAR(255),  EFX_LEGDOMULTCITYALL VARCHAR(255),  EFX_LEGDOMULTSTATEALL VARCHAR(255),  EFX_LEGDOMULTZIPCODEALL NUMERIC,  EFX_LEGDOMULTZIP4ALL REAL,  EFX_LEGDOMULTCOUNTYALL REAL,  EFX_LEGDOMULTCTRYISOCDALL VARCHAR(255),  EFX_LEGDOMULTCTRYNUMALL NUMERIC,  EFX_LEGDOMULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTNUMALL NUMERIC,  EFX_LEGULTNAMEALL VARCHAR(255),  EFX_LEGULTADDRESSALL VARCHAR(255),  EFX_LEGULTCITYALL VARCHAR(255),  EFX_LEGULTSTATEALL VARCHAR(255),  EFX_LEGULTZIPCODEALL VARCHAR(255),  EFX_LEGULTZIP4ALL REAL,  EFX_LEGULTCOUNTYALL REAL,  EFX_LEGULTCTRYISOCDALL VARCHAR(255),  EFX_LEGULTCTRYNUMALL NUMERIC,  EFX_LEGULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTPARENTIND VARCHAR(255),  EFX_LEGPARENTIND VARCHAR(255),  EFX_LEGLINKEDIND VARCHAR(255),  EFX_AFFLULTNUMALL NUMERIC,  EFX_AFFLULTNAMEALL VARCHAR(255),  EFX_AFFLULTADDRESSALL VARCHAR(255),  EFX_AFFLULTCITYALL VARCHAR(255),  EFX_AFFLULTSTATEALL VARCHAR(255),  EFX_AFFLULTZIPCODEALL REAL,  EFX_AFFLULTZIP4ALL REAL,  EFX_AFFULTCOUNTYALL REAL,  EFX_AFFLULTCTRYISOCDALL VARCHAR(255),  EFX_AFFLULTCTRYNUMALL NUMERIC,  EFX_AFFLULTCTRYNAMEALL VARCHAR(255),  EFX_AFFLLINKEDIND VARCHAR(255),  EFX_FRANCHISE VARCHAR(255),  EFX_AFFLULTPARENTIND VARCHAR(255),  EFX_AFFLPARENTIND VARCHAR(255),  EFX_FOREIGN VARCHAR(255),  EFX_FAILRATE REAL,  EFX_FAILLEVEL REAL,  EFX_FAILREASON1 VARCHAR(255),  EFX_FAILREASON2 VARCHAR(255),  EFX_FAILREASON3 VARCHAR(255),  EFX_FAILREASON4 VARCHAR(255),  EFX_CREDITSCORE REAL,  EFX_CREDITCLASS REAL,  EFX_CREDITPERC REAL,  EFX_CREDITREASON1 VARCHAR(255),  EFX_CREDITREASON2 VARCHAR(255),  EFX_CREDITREASON3 VARCHAR(255),  EFX_CREDITREASON4 VARCHAR(255),  EFX_BANKRUPTCY VARCHAR(255),  EFX_DEAD REAL,  EFX_DEADDT REAL,  EFX_MRKT_TELEVER VARCHAR(255),  EFX_MRKT_TELESCORE NUMERIC,  EFX_MRKT_TOTALSCORE NUMERIC,  EFX_MRKT_TOTALIND VARCHAR(255),  EFX_MRKT_VACANT VARCHAR(255),  EFX_MRKT_SEASONAL REAL,  EFX_EXTRACT_DATE VARCHAR(255))\u001b[39m\u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[0;32m----> 8\u001b[0m \u001b[43mqueryBase\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcreateTable1\u001b[49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn [14], line 3\u001b[0m, in \u001b[0;36mqueryBase\u001b[0;34m(query)\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mqueryBase\u001b[39m(query):\n\u001b[0;32m----> 3\u001b[0m     requiredTable \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcredentials\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      4\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m requiredTable\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:590\u001b[0m, in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    581\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m pandas_sql\u001b[38;5;241m.\u001b[39mread_table(\n\u001b[1;32m    582\u001b[0m         sql,\n\u001b[1;32m    583\u001b[0m         index_col\u001b[38;5;241m=\u001b[39mindex_col,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    587\u001b[0m         chunksize\u001b[38;5;241m=\u001b[39mchunksize,\n\u001b[1;32m    588\u001b[0m     )\n\u001b[1;32m    589\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m--> 590\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    591\u001b[0m \u001b[43m        \u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    592\u001b[0m \u001b[43m        \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    593\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparams\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    594\u001b[0m \u001b[43m        \u001b[49m\u001b[43mcoerce_float\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcoerce_float\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    595\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    596\u001b[0m \u001b[43m        \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    597\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:1574\u001b[0m, in \u001b[0;36mSQLDatabase.read_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1564\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_query_iterator(\n\u001b[1;32m   1565\u001b[0m         result,\n\u001b[1;32m   1566\u001b[0m         chunksize,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1571\u001b[0m         dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[1;32m   1572\u001b[0m     )\n\u001b[1;32m   1573\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m-> 1574\u001b[0m     data \u001b[38;5;241m=\u001b[39m \u001b[43mresult\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfetchall\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1575\u001b[0m     frame \u001b[38;5;241m=\u001b[39m _wrap_result(\n\u001b[1;32m   1576\u001b[0m         data,\n\u001b[1;32m   1577\u001b[0m         columns,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1581\u001b[0m         dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[1;32m   1582\u001b[0m     )\n\u001b[1;32m   1583\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m frame\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:1058\u001b[0m, in \u001b[0;36mResult.fetchall\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1055\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mfetchall\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m   1056\u001b[0m     \u001b[38;5;124;03m\"\"\"A synonym for the :meth:`_engine.Result.all` method.\"\"\"\u001b[39;00m\n\u001b[0;32m-> 1058\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_allrows\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:399\u001b[0m, in \u001b[0;36mResultInternal._allrows\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    395\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_allrows\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m    397\u001b[0m     post_creational_filter \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_post_creational_filter\n\u001b[0;32m--> 399\u001b[0m     make_row \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_row_getter\u001b[49m\n\u001b[1;32m    401\u001b[0m     rows \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_fetchall_impl()\n\u001b[1;32m    402\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m make_row:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:1184\u001b[0m, in \u001b[0;36mHasMemoized.memoized_attribute.__get__\u001b[0;34m(self, obj, cls)\u001b[0m\n\u001b[1;32m   1182\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m obj \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m   1183\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\n\u001b[0;32m-> 1184\u001b[0m obj\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__dict__\u001b[39m[\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m] \u001b[38;5;241m=\u001b[39m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfget\u001b[49m\u001b[43m(\u001b[49m\u001b[43mobj\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1185\u001b[0m obj\u001b[38;5;241m.\u001b[39m_memoized_keys \u001b[38;5;241m|\u001b[39m\u001b[38;5;241m=\u001b[39m {\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m}\n\u001b[1;32m   1186\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:320\u001b[0m, in \u001b[0;36mResultInternal._row_getter\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    317\u001b[0m key_style \u001b[38;5;241m=\u001b[39m real_result\u001b[38;5;241m.\u001b[39m_process_row\u001b[38;5;241m.\u001b[39m_default_key_style\n\u001b[1;32m    318\u001b[0m metadata \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_metadata\n\u001b[0;32m--> 320\u001b[0m keymap \u001b[38;5;241m=\u001b[39m \u001b[43mmetadata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_keymap\u001b[49m\n\u001b[1;32m    321\u001b[0m processors \u001b[38;5;241m=\u001b[39m metadata\u001b[38;5;241m.\u001b[39m_processors\n\u001b[1;32m    322\u001b[0m tf \u001b[38;5;241m=\u001b[39m metadata\u001b[38;5;241m.\u001b[39m_tuplefilter\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1221\u001b[0m, in \u001b[0;36m_NoResultMetaData._keymap\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1219\u001b[0m \u001b[38;5;129m@property\u001b[39m\n\u001b[1;32m   1220\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_keymap\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m-> 1221\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_we_dont_return_rows\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1202\u001b[0m, in \u001b[0;36m_NoResultMetaData._we_dont_return_rows\u001b[0;34m(self, err)\u001b[0m\n\u001b[1;32m   1201\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_we_dont_return_rows\u001b[39m(\u001b[38;5;28mself\u001b[39m, err\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m-> 1202\u001b[0m     \u001b[43mutil\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1203\u001b[0m \u001b[43m        \u001b[49m\u001b[43mexc\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mResourceClosedError\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1204\u001b[0m \u001b[43m            \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mThis result object does not return rows. \u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\n\u001b[1;32m   1205\u001b[0m \u001b[43m            \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mIt has been closed automatically.\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\n\u001b[1;32m   1206\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1207\u001b[0m \u001b[43m        \u001b[49m\u001b[43mreplace_context\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merr\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1208\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208\u001b[0m, in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m     exception\u001b[38;5;241m.\u001b[39m__cause__ \u001b[38;5;241m=\u001b[39m replace_context\n\u001b[1;32m    207\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 208\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exception\n\u001b[1;32m    209\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m    210\u001b[0m     \u001b[38;5;66;03m# credit to\u001b[39;00m\n\u001b[1;32m    211\u001b[0m     \u001b[38;5;66;03m# https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/\u001b[39;00m\n\u001b[1;32m    212\u001b[0m     \u001b[38;5;66;03m# as the __traceback__ object creates a cycle\u001b[39;00m\n\u001b[1;32m    213\u001b[0m     \u001b[38;5;28;01mdel\u001b[39;00m exception, replace_context, from_, with_traceback\n",
      "\u001b[0;31mResourceClosedError\u001b[0m: This result object does not return rows. It has been closed automatically."
     ]
    }
   ],
   "source": [
    "createTable1 = \"\"\"CREATE TABLE mdsdata (EFX_ID NUMERIC,  EFX_NAME VARCHAR(255),  EFX_LEGAL_NAME VARCHAR(255),  EFX_ADDRESS VARCHAR(255),  EFX_CITY VARCHAR(255),  EFX_STATE VARCHAR(255),  EFX_STATEC NUMERIC,  EFX_ZIPCODE NUMERIC,  EFX_ZIP4 REAL,  EFX_LAT REAL,  EFX_LO REAL,  EFX_GEOPREC NUMERIC,  EFX_REGION REAL,  EFX_CTRYISOCD VARCHAR(255),  EFX_CTRYNUM NUMERIC,  EFX_CTRYNAME VARCHAR(255),  EFX_COUNTYNM VARCHAR(255),  EFX_COUNTY NUMERIC,  EFX_CMSA VARCHAR(255),  EFX_CMSADESC VARCHAR(255),  EFX_SOHO VARCHAR(255),  EFX_BIZ VARCHAR(255),  EFX_RES VARCHAR(255),  EFX_CMRA VARCHAR(255),  EFX_CONGRESS NUMERIC,  EFX_SECADR VARCHAR(255),  EFX_SECCTY VARCHAR(255),  EFX_SECSTAT VARCHAR(255),  EFX_STATEC2 REAL,  EFX_SECZIP REAL,  EFX_SECZIP4 REAL,  EFX_SECLAT REAL,  EFX_SECLO REAL,  EFX_SECGEOPREC REAL,  EFX_SECREGION REAL,  EFX_SECCTRYISOCD VARCHAR(255),  EFX_SECCTRYNUM NUMERIC,  EFX_SECCTRYNAME VARCHAR(255),  EFX_CTRYTELCD NUMERIC,  EFX_GENDER VARCHAR(255),  EFX_ETHNICITY VARCHAR(255),  EFX_MBE VARCHAR(255),  EFX_WBE VARCHAR(255),  EFX_VET VARCHAR(255),  EFX_BUSSIZE VARCHAR(255),  EFX_GOV VARCHAR(255),  EFX_FGOV VARCHAR(255),  EFX_NONPROFIT VARCHAR(255),  EFX_EDU VARCHAR(255),  EFX_BUSSTAT VARCHAR(255),  EFX_BUSSTATCD REAL,  EFX_WEB VARCHAR(255),  EFX_YREST NUMERIC,  EFX_CORPEMPCNT NUMERIC,  EFX_LOCEMPCNT NUMERIC,  \n",
    "EFX_CORPEMPCD VARCHAR(255),  EFX_LOCEMPCD VARCHAR(255),  EFX_CORPAMOUNT NUMERIC,  EFX_CORPAMOUNTCD VARCHAR(255),  EFX_CORPAMOUNTTP VARCHAR(255),  EFX_CORPAMOUNTPREC VARCHAR(255),  EFX_LOCAMOUNT NUMERIC,  EFX_LOCAMOUNTCD VARCHAR(255),  EFX_LOCAMOUNTTP VARCHAR(255),  EFX_LOCAMOUNTPREC VARCHAR(255),  EFX_PUBLIC REAL,  \n",
    "EFX_STKEXC REAL,  EFX_TCKSYM REAL,  EFX_PRIMSIC REAL,  EFX_SECSIC1 REAL,  EFX_SECSIC2 REAL,  EFX_SECSIC3 REAL,  EFX_SECSIC4 REAL,  EFX_PRIMSICDESC VARCHAR(255),  \n",
    "EFX_SECSICDESC1 VARCHAR(255),  EFX_SECSICDESC2 VARCHAR(255),  EFX_SECSICDESC3 VARCHAR(255),  EFX_SECSICDESC4 VARCHAR(255),  EFX_PRIMNAICSCODE REAL,  EFX_SECNAICS1 REAL,  \n",
    "EFX_SECNAICS2 REAL,  EFX_SECNAICS3 REAL,  EFX_SECNAICS4 REAL,  EFX_PRIMNAICSDESC VARCHAR(255),  EFX_SECNAICSDESC1 VARCHAR(255),  EFX_SECNAICSDESC2 VARCHAR(255),  EFX_SECNAICSDESC3 VARCHAR(255),  \n",
    "EFX_SECNAICSDESC4 VARCHAR(255),  EFX_LEGSUBNUMALL NUMERIC,  EFX_LEGSUBNAMEALL VARCHAR(255),  EFX_LEGSUBADDRESSALL VARCHAR(255),  EFX_LEGSUBCITYALL VARCHAR(255),  EFX_LEGSUBSTATEALL VARCHAR(255),  EFX_LEGSUBZIPCODEALL NUMERIC,  \n",
    "EFX_LEGSUBZIP4ALL REAL,  EFX_LEGSUBCOUNTYALL REAL,  EFX_LEGSUBCTRYISOCDALL VARCHAR(255),  EFX_LEGSUBCTRYNUMALL NUMERIC,  EFX_LEGSUBCTRYNAMEALL VARCHAR(255),  EFX_LEGDOMULTNUMALL NUMERIC,  EFX_LEGDOMULTNAMEALL VARCHAR(255),  EFX_LEGDOMULTADDRESSALL VARCHAR(255),  EFX_LEGDOMULTCITYALL VARCHAR(255),  EFX_LEGDOMULTSTATEALL VARCHAR(255),  EFX_LEGDOMULTZIPCODEALL NUMERIC,  EFX_LEGDOMULTZIP4ALL REAL,  EFX_LEGDOMULTCOUNTYALL REAL,  EFX_LEGDOMULTCTRYISOCDALL VARCHAR(255),  EFX_LEGDOMULTCTRYNUMALL NUMERIC,  EFX_LEGDOMULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTNUMALL NUMERIC,  EFX_LEGULTNAMEALL VARCHAR(255),  EFX_LEGULTADDRESSALL VARCHAR(255),  EFX_LEGULTCITYALL VARCHAR(255),  EFX_LEGULTSTATEALL VARCHAR(255),  EFX_LEGULTZIPCODEALL VARCHAR(255),  EFX_LEGULTZIP4ALL REAL,  EFX_LEGULTCOUNTYALL REAL,  EFX_LEGULTCTRYISOCDALL VARCHAR(255),  EFX_LEGULTCTRYNUMALL NUMERIC,  EFX_LEGULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTPARENTIND VARCHAR(255),  EFX_LEGPARENTIND VARCHAR(255),  EFX_LEGLINKEDIND VARCHAR(255),  EFX_AFFLULTNUMALL NUMERIC,  EFX_AFFLULTNAMEALL VARCHAR(255),  EFX_AFFLULTADDRESSALL VARCHAR(255),  EFX_AFFLULTCITYALL VARCHAR(255),  EFX_AFFLULTSTATEALL VARCHAR(255),  EFX_AFFLULTZIPCODEALL REAL,  EFX_AFFLULTZIP4ALL REAL,  EFX_AFFULTCOUNTYALL REAL,  EFX_AFFLULTCTRYISOCDALL VARCHAR(255),  EFX_AFFLULTCTRYNUMALL NUMERIC,  EFX_AFFLULTCTRYNAMEALL VARCHAR(255),  EFX_AFFLLINKEDIND VARCHAR(255),  EFX_FRANCHISE VARCHAR(255),  EFX_AFFLULTPARENTIND VARCHAR(255),  EFX_AFFLPARENTIND VARCHAR(255),  EFX_FOREIGN VARCHAR(255),  EFX_FAILRATE REAL,  EFX_FAILLEVEL REAL,  EFX_FAILREASON1 VARCHAR(255),  EFX_FAILREASON2 VARCHAR(255),  EFX_FAILREASON3 VARCHAR(255),  EFX_FAILREASON4 VARCHAR(255),  EFX_CREDITSCORE REAL,  EFX_CREDITCLASS REAL,  EFX_CREDITPERC REAL,  EFX_CREDITREASON1 VARCHAR(255),  EFX_CREDITREASON2 VARCHAR(255),  EFX_CREDITREASON3 VARCHAR(255),  EFX_CREDITREASON4 VARCHAR(255),  EFX_BANKRUPTCY VARCHAR(255),  EFX_DEAD REAL,  EFX_DEADDT REAL,  EFX_MRKT_TELEVER VARCHAR(255),  EFX_MRKT_TELESCORE NUMERIC,  EFX_MRKT_TOTALSCORE NUMERIC,  EFX_MRKT_TOTALIND VARCHAR(255),  EFX_MRKT_VACANT VARCHAR(255),  EFX_MRKT_SEASONAL REAL,  EFX_EXTRACT_DATE VARCHAR(255))\"\"\"\n",
    "queryBase(createTable1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eb1cbf6",
   "metadata": {},
   "source": [
    "### Loading Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "db873c85",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>table_catalog</th>\n",
       "      <th>table_schema</th>\n",
       "      <th>table_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>is_nullable</th>\n",
       "      <th>data_type</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>character_octet_length</th>\n",
       "      <th>...</th>\n",
       "      <th>is_identity</th>\n",
       "      <th>identity_generation</th>\n",
       "      <th>identity_start</th>\n",
       "      <th>identity_increment</th>\n",
       "      <th>identity_maximum</th>\n",
       "      <th>identity_minimum</th>\n",
       "      <th>identity_cycle</th>\n",
       "      <th>is_generated</th>\n",
       "      <th>generation_expression</th>\n",
       "      <th>is_updatable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legsubcountyall</td>\n",
       "      <td>96</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>real</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_locempcnt</td>\n",
       "      <td>55</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legsubctrynumall</td>\n",
       "      <td>98</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_seclo</td>\n",
       "      <td>33</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>real</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legdomultnumall</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>numeric</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legultaddressall</td>\n",
       "      <td>113</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legultcityall</td>\n",
       "      <td>114</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legultstateall</td>\n",
       "      <td>115</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legultzipcodeall</td>\n",
       "      <td>116</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>mdsusbiz</td>\n",
       "      <td>public</td>\n",
       "      <td>mdsdata</td>\n",
       "      <td>efx_legultctryisocdall</td>\n",
       "      <td>119</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NO</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>NEVER</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>163 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    table_catalog table_schema table_name             column_name  \\\n",
       "0        mdsusbiz       public    mdsdata     efx_legsubcountyall   \n",
       "1        mdsusbiz       public    mdsdata           efx_locempcnt   \n",
       "2        mdsusbiz       public    mdsdata    efx_legsubctrynumall   \n",
       "3        mdsusbiz       public    mdsdata               efx_seclo   \n",
       "4        mdsusbiz       public    mdsdata     efx_legdomultnumall   \n",
       "..            ...          ...        ...                     ...   \n",
       "158      mdsusbiz       public    mdsdata    efx_legultaddressall   \n",
       "159      mdsusbiz       public    mdsdata       efx_legultcityall   \n",
       "160      mdsusbiz       public    mdsdata      efx_legultstateall   \n",
       "161      mdsusbiz       public    mdsdata    efx_legultzipcodeall   \n",
       "162      mdsusbiz       public    mdsdata  efx_legultctryisocdall   \n",
       "\n",
       "     ordinal_position column_default is_nullable          data_type  \\\n",
       "0                  96           None         YES               real   \n",
       "1                  55           None         YES            numeric   \n",
       "2                  98           None         YES            numeric   \n",
       "3                  33           None         YES               real   \n",
       "4                 100           None         YES            numeric   \n",
       "..                ...            ...         ...                ...   \n",
       "158               113           None         YES  character varying   \n",
       "159               114           None         YES  character varying   \n",
       "160               115           None         YES  character varying   \n",
       "161               116           None         YES  character varying   \n",
       "162               119           None         YES  character varying   \n",
       "\n",
       "     character_maximum_length  character_octet_length  ...  is_identity  \\\n",
       "0                         NaN                     NaN  ...           NO   \n",
       "1                         NaN                     NaN  ...           NO   \n",
       "2                         NaN                     NaN  ...           NO   \n",
       "3                         NaN                     NaN  ...           NO   \n",
       "4                         NaN                     NaN  ...           NO   \n",
       "..                        ...                     ...  ...          ...   \n",
       "158                     255.0                  1020.0  ...           NO   \n",
       "159                     255.0                  1020.0  ...           NO   \n",
       "160                     255.0                  1020.0  ...           NO   \n",
       "161                     255.0                  1020.0  ...           NO   \n",
       "162                     255.0                  1020.0  ...           NO   \n",
       "\n",
       "     identity_generation identity_start identity_increment identity_maximum  \\\n",
       "0                   None           None               None             None   \n",
       "1                   None           None               None             None   \n",
       "2                   None           None               None             None   \n",
       "3                   None           None               None             None   \n",
       "4                   None           None               None             None   \n",
       "..                   ...            ...                ...              ...   \n",
       "158                 None           None               None             None   \n",
       "159                 None           None               None             None   \n",
       "160                 None           None               None             None   \n",
       "161                 None           None               None             None   \n",
       "162                 None           None               None             None   \n",
       "\n",
       "    identity_minimum identity_cycle is_generated generation_expression  \\\n",
       "0               None             NO        NEVER                  None   \n",
       "1               None             NO        NEVER                  None   \n",
       "2               None             NO        NEVER                  None   \n",
       "3               None             NO        NEVER                  None   \n",
       "4               None             NO        NEVER                  None   \n",
       "..               ...            ...          ...                   ...   \n",
       "158             None             NO        NEVER                  None   \n",
       "159             None             NO        NEVER                  None   \n",
       "160             None             NO        NEVER                  None   \n",
       "161             None             NO        NEVER                  None   \n",
       "162             None             NO        NEVER                  None   \n",
       "\n",
       "    is_updatable  \n",
       "0            YES  \n",
       "1            YES  \n",
       "2            YES  \n",
       "3            YES  \n",
       "4            YES  \n",
       "..           ...  \n",
       "158          YES  \n",
       "159          YES  \n",
       "160          YES  \n",
       "161          YES  \n",
       "162          YES  \n",
       "\n",
       "[163 rows x 44 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check if the table in the database has the correct schema\n",
    "getSchema(\"mdsdata\",credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0fb4eb67",
   "metadata": {},
   "outputs": [],
   "source": [
    "copymdsData = \"\"\"COPY mdsdata from '/var/lib/postgresql/data/sample_mdsUsBusinessDataNoContacts.txt'\n",
    "        DELIMITER '|' CSV HEADER\"\"\"\n",
    "queryTable(copymdsData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "0a64bfea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_name</th>\n",
       "      <th>efx_legal_name</th>\n",
       "      <th>efx_address</th>\n",
       "      <th>efx_city</th>\n",
       "      <th>efx_state</th>\n",
       "      <th>efx_statec</th>\n",
       "      <th>efx_zipcode</th>\n",
       "      <th>efx_zip4</th>\n",
       "      <th>efx_lat</th>\n",
       "      <th>...</th>\n",
       "      <th>efx_bankruptcy</th>\n",
       "      <th>efx_dead</th>\n",
       "      <th>efx_deaddt</th>\n",
       "      <th>efx_mrkt_telever</th>\n",
       "      <th>efx_mrkt_telescore</th>\n",
       "      <th>efx_mrkt_totalscore</th>\n",
       "      <th>efx_mrkt_totalind</th>\n",
       "      <th>efx_mrkt_vacant</th>\n",
       "      <th>efx_mrkt_seasonal</th>\n",
       "      <th>efx_extract_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>123 MAIN SRD</td>\n",
       "      <td>BOSTON</td>\n",
       "      <td>MA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10011.0</td>\n",
       "      <td>1869.0</td>\n",
       "      <td>42.356213</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>6/21/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 163 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id              efx_name        efx_legal_name    efx_address efx_city  \\\n",
       "0     1.0  WOODS ARCADE WILLOWS  WOODS ARCADE WILLOWS  123 MAIN SRD    BOSTON   \n",
       "\n",
       "  efx_state  efx_statec  efx_zipcode  efx_zip4    efx_lat  ...  \\\n",
       "0        MA        25.0      10011.0    1869.0  42.356213  ...   \n",
       "\n",
       "   efx_bankruptcy  efx_dead efx_deaddt efx_mrkt_telever  efx_mrkt_telescore  \\\n",
       "0            None      None       None                Y                 5.0   \n",
       "\n",
       "  efx_mrkt_totalscore efx_mrkt_totalind  efx_mrkt_vacant efx_mrkt_seasonal  \\\n",
       "0                 5.0                 M             None              None   \n",
       "\n",
       "  efx_extract_date  \n",
       "0        6/21/2021  \n",
       "\n",
       "[1 rows x 163 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "checkmdsData = \"\"\"SELECT * FROM mdsdata ORDER BY efx_id ASC LIMIT 1\"\"\"\n",
    "queryBase(checkmdsData)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d84a5f12",
   "metadata": {},
   "source": [
    "### What is the objective modeling mdsData?\n",
    "\n",
    "1) To break the 163 column data into easily manageable chunks of conceptual information\n",
    "2) Create a star Schema to enable ease of data analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "1e92fd06",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dataColumns = mdsdata.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "9ed6189c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['EFX_AFFLLINKEDIND', 'EFX_FRANCHISE', 'EFX_AFFLULTPARENTIND',\n",
       "       'EFX_AFFLPARENTIND', 'EFX_FOREIGN', 'EFX_FAILRATE', 'EFX_FAILLEVEL',\n",
       "       'EFX_FAILREASON1', 'EFX_FAILREASON2', 'EFX_FAILREASON3',\n",
       "       'EFX_FAILREASON4', 'EFX_CREDITSCORE', 'EFX_CREDITCLASS',\n",
       "       'EFX_CREDITPERC', 'EFX_CREDITREASON1', 'EFX_CREDITREASON2',\n",
       "       'EFX_CREDITREASON3', 'EFX_CREDITREASON4', 'EFX_BANKRUPTCY', 'EFX_DEAD',\n",
       "       'EFX_DEADDT', 'EFX_MRKT_TELEVER', 'EFX_MRKT_TELESCORE',\n",
       "       'EFX_MRKT_TOTALSCORE', 'EFX_MRKT_TOTALIND', 'EFX_MRKT_VACANT',\n",
       "       'EFX_MRKT_SEASONAL', 'EFX_EXTRACT_DATE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataColumns[135:163]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "c4cdfd0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EFX_FOREIGN</th>\n",
       "      <th>EFX_FAILRATE</th>\n",
       "      <th>EFX_FAILLEVEL</th>\n",
       "      <th>EFX_FAILREASON1</th>\n",
       "      <th>EFX_CREDITSCORE</th>\n",
       "      <th>EFX_CREDITCLASS</th>\n",
       "      <th>EFX_CREDITPERC</th>\n",
       "      <th>EFX_CREDITREASON1</th>\n",
       "      <th>EFX_CREDITREASON2</th>\n",
       "      <th>EFX_CREDITREASON3</th>\n",
       "      <th>EFX_CREDITREASON4</th>\n",
       "      <th>EFX_MRKT_TELEVER</th>\n",
       "      <th>EFX_MRKT_TELESCORE</th>\n",
       "      <th>EFX_MRKT_TOTALSCORE</th>\n",
       "      <th>EFX_MRKT_TOTALIND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4</td>\n",
       "      <td>1183.000000</td>\n",
       "      <td>1183.000000</td>\n",
       "      <td>480</td>\n",
       "      <td>1981.000000</td>\n",
       "      <td>1981.000000</td>\n",
       "      <td>1981.000000</td>\n",
       "      <td>1149</td>\n",
       "      <td>1149</td>\n",
       "      <td>1146</td>\n",
       "      <td>1082</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000.00000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C5007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C0066</td>\n",
       "      <td>C0066</td>\n",
       "      <td>C1209</td>\n",
       "      <td>C1324</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>511</td>\n",
       "      <td>327</td>\n",
       "      <td>217</td>\n",
       "      <td>267</td>\n",
       "      <td>1346</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.153001</td>\n",
       "      <td>1.300930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>442.134780</td>\n",
       "      <td>3.175669</td>\n",
       "      <td>40.147905</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.65600</td>\n",
       "      <td>3.577000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.030917</td>\n",
       "      <td>0.506196</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.687357</td>\n",
       "      <td>0.747003</td>\n",
       "      <td>18.303884</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.86478</td>\n",
       "      <td>1.824482</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>427.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>461.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>474.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>545.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>97.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       EFX_FOREIGN  EFX_FAILRATE  EFX_FAILLEVEL EFX_FAILREASON1  \\\n",
       "count            4   1183.000000    1183.000000             480   \n",
       "unique           1           NaN            NaN               8   \n",
       "top              Y           NaN            NaN           C5007   \n",
       "freq             4           NaN            NaN             250   \n",
       "mean           NaN      3.153001       1.300930             NaN   \n",
       "std            NaN      2.030917       0.506196             NaN   \n",
       "min            NaN      0.000000       0.000000             NaN   \n",
       "25%            NaN      1.000000       1.000000             NaN   \n",
       "50%            NaN      3.000000       1.000000             NaN   \n",
       "75%            NaN      5.000000       2.000000             NaN   \n",
       "max            NaN      9.000000       3.000000             NaN   \n",
       "\n",
       "        EFX_CREDITSCORE  EFX_CREDITCLASS  EFX_CREDITPERC EFX_CREDITREASON1  \\\n",
       "count       1981.000000      1981.000000     1981.000000              1149   \n",
       "unique              NaN              NaN             NaN                12   \n",
       "top                 NaN              NaN             NaN             C0066   \n",
       "freq                NaN              NaN             NaN               511   \n",
       "mean         442.134780         3.175669       40.147905               NaN   \n",
       "std           66.687357         0.747003       18.303884               NaN   \n",
       "min            0.000000         0.000000        0.000000               NaN   \n",
       "25%          427.000000         3.000000       31.000000               NaN   \n",
       "50%          461.000000         3.000000       39.000000               NaN   \n",
       "75%          474.000000         3.000000       44.000000               NaN   \n",
       "max          545.000000         5.000000       97.000000               NaN   \n",
       "\n",
       "       EFX_CREDITREASON2 EFX_CREDITREASON3 EFX_CREDITREASON4 EFX_MRKT_TELEVER  \\\n",
       "count               1149              1146              1082             2000   \n",
       "unique                21                22                19                2   \n",
       "top                C0066             C1209             C1324                Y   \n",
       "freq                 327               217               267             1346   \n",
       "mean                 NaN               NaN               NaN              NaN   \n",
       "std                  NaN               NaN               NaN              NaN   \n",
       "min                  NaN               NaN               NaN              NaN   \n",
       "25%                  NaN               NaN               NaN              NaN   \n",
       "50%                  NaN               NaN               NaN              NaN   \n",
       "75%                  NaN               NaN               NaN              NaN   \n",
       "max                  NaN               NaN               NaN              NaN   \n",
       "\n",
       "        EFX_MRKT_TELESCORE  EFX_MRKT_TOTALSCORE EFX_MRKT_TOTALIND  \n",
       "count           2000.00000          2000.000000              2000  \n",
       "unique                 NaN                  NaN                 2  \n",
       "top                    NaN                  NaN                 M  \n",
       "freq                   NaN                  NaN              1348  \n",
       "mean               3.65600             3.577000               NaN  \n",
       "std                1.86478             1.824482               NaN  \n",
       "min                1.00000             1.000000               NaN  \n",
       "25%                1.00000             1.000000               NaN  \n",
       "50%                5.00000             5.000000               NaN  \n",
       "75%                5.00000             5.000000               NaN  \n",
       "max                5.00000             5.000000               NaN  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mdsdata[['EFX_FOREIGN', 'EFX_FAILRATE', 'EFX_FAILLEVEL',\n",
    "       'EFX_FAILREASON1', 'EFX_CREDITSCORE', 'EFX_CREDITCLASS',\n",
    "       'EFX_CREDITPERC', 'EFX_CREDITREASON1', 'EFX_CREDITREASON2',\n",
    "       'EFX_CREDITREASON3', 'EFX_CREDITREASON4', 'EFX_MRKT_TELEVER', 'EFX_MRKT_TELESCORE',\n",
    "       'EFX_MRKT_TOTALSCORE', 'EFX_MRKT_TOTALIND']].describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "996d033c",
   "metadata": {},
   "source": [
    "### How to select the columns that will be important for the analysis?\n",
    "\n",
    "1) Select the columns and run a describe(include='all) command \n",
    "\n",
    "2) Each column in the result will have a count in 1st row of the describe result\n",
    "\n",
    "3) If the count is more than 50% of the overall count, then take the data in the row.\n",
    "\n",
    "4) The columns that are below 50% are saved as miscellaneous. (this calls for unwanted work)\n",
    "\n",
    "The above process uses the Schema generated by Pandas on the input data frame. It uses the describe() method to identify the important columns based on the count. \n",
    "\n",
    "5) The database tables are created from the columns that are selected from the Pandas dataframe. To build the tables, the schema function is used to get the selected schema. Then it is copied as shown below steps of table creation\n",
    "\n",
    "6) After that immediately insert the data into the Table that has been created already\n",
    "\n",
    "7) Check the table is having the data by querying it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f7e713f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_location = \"\"\"CREATE TABLE dim_location (EFX_ID NUMERIC PRIMARY KEY,  EFX_NAME VARCHAR(255),  EFX_LEGAL_NAME VARCHAR(255),  EFX_ADDRESS VARCHAR(255),  EFX_CITY VARCHAR(255),  EFX_STATE VARCHAR(255),  EFX_STATEC NUMERIC,  EFX_ZIPCODE NUMERIC,  EFX_ZIP4 REAL,  EFX_LAT REAL,  EFX_LO REAL,  EFX_GEOPREC NUMERIC,  EFX_REGION REAL,  EFX_CTRYISOCD VARCHAR(255),  EFX_CTRYNUM NUMERIC,  EFX_CTRYNAME VARCHAR(255),  EFX_COUNTYNM VARCHAR(255),  EFX_COUNTY NUMERIC)\"\"\"\n",
    "queryTable(dim_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "3fa40ca6",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_location = \"\"\"INSERT INTO dim_location (EFX_ID, EFX_NAME, EFX_LEGAL_NAME, EFX_ADDRESS, EFX_CITY,\n",
    "                       EFX_STATE, EFX_STATEC, EFX_ZIPCODE, EFX_ZIP4, EFX_LAT,\n",
    "                       EFX_LO, EFX_GEOPREC, EFX_REGION, EFX_CTRYISOCD, EFX_CTRYNUM,\n",
    "                       EFX_CTRYNAME, EFX_COUNTYNM, EFX_COUNTY)\n",
    "                     SELECT EFX_ID, EFX_NAME, EFX_LEGAL_NAME, EFX_ADDRESS, EFX_CITY,\n",
    "                       EFX_STATE, EFX_STATEC, EFX_ZIPCODE, EFX_ZIP4, EFX_LAT,\n",
    "                       EFX_LO, EFX_GEOPREC, EFX_REGION, EFX_CTRYISOCD, EFX_CTRYNUM,\n",
    "                       EFX_CTRYNAME, EFX_COUNTYNM, EFX_COUNTY\n",
    "                     FROM mdsdata\"\"\"\n",
    "queryTable(insert_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "79ba7e7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_name</th>\n",
       "      <th>efx_legal_name</th>\n",
       "      <th>efx_address</th>\n",
       "      <th>efx_city</th>\n",
       "      <th>efx_state</th>\n",
       "      <th>efx_statec</th>\n",
       "      <th>efx_zipcode</th>\n",
       "      <th>efx_zip4</th>\n",
       "      <th>efx_lat</th>\n",
       "      <th>efx_lo</th>\n",
       "      <th>efx_geoprec</th>\n",
       "      <th>efx_region</th>\n",
       "      <th>efx_ctryisocd</th>\n",
       "      <th>efx_ctrynum</th>\n",
       "      <th>efx_ctryname</th>\n",
       "      <th>efx_countynm</th>\n",
       "      <th>efx_county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>123 MAIN SRD</td>\n",
       "      <td>BOSTON</td>\n",
       "      <td>MA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10011.0</td>\n",
       "      <td>1869.0</td>\n",
       "      <td>42.356213</td>\n",
       "      <td>-71.050415</td>\n",
       "      <td>9.0</td>\n",
       "      <td>None</td>\n",
       "      <td>USA</td>\n",
       "      <td>840.0</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>Hampden County</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id              efx_name        efx_legal_name    efx_address efx_city  \\\n",
       "0     1.0  WOODS ARCADE WILLOWS  WOODS ARCADE WILLOWS  123 MAIN SRD    BOSTON   \n",
       "\n",
       "  efx_state  efx_statec  efx_zipcode  efx_zip4    efx_lat     efx_lo  \\\n",
       "0        MA        25.0      10011.0    1869.0  42.356213 -71.050415   \n",
       "\n",
       "   efx_geoprec efx_region efx_ctryisocd  efx_ctrynum  \\\n",
       "0          9.0       None           USA        840.0   \n",
       "\n",
       "               efx_ctryname    efx_countynm  efx_county  \n",
       "0  United States of America  Hampden County        13.0  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_location LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "58fb9acb",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_business = \"\"\"CREATE TABLE dim_business(EFX_ID NUMERIC PRIMARY KEY,EFX_BUSSIZE VARCHAR(255),EFX_BUSSTAT VARCHAR(255),  EFX_BUSSTATCD REAL,  EFX_WEB VARCHAR(255),  EFX_YREST NUMERIC,  EFX_CORPEMPCNT NUMERIC,  EFX_LOCEMPCNT NUMERIC,  EFX_CORPEMPCD VARCHAR(255),  EFX_LOCEMPCD VARCHAR(255),  EFX_CORPAMOUNT NUMERIC,  EFX_CORPAMOUNTCD VARCHAR(255),  EFX_CORPAMOUNTTP VARCHAR(255),  EFX_CORPAMOUNTPREC VARCHAR(255),\n",
    "                                EFX_LOCAMOUNT NUMERIC,  EFX_LOCAMOUNTCD VARCHAR(255),  EFX_LOCAMOUNTTP VARCHAR(255),  EFX_LOCAMOUNTPREC VARCHAR(255))\"\"\"\n",
    "queryTable(dim_business)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "f5e67115",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_business = \"\"\" INSERT INTO dim_business(EFX_ID, EFX_BUSSIZE, EFX_BUSSTAT,\n",
    "               EFX_BUSSTATCD, EFX_WEB, EFX_YREST, EFX_CORPEMPCNT,\n",
    "               EFX_LOCEMPCNT, EFX_CORPEMPCD, EFX_LOCEMPCD, EFX_CORPAMOUNT,\n",
    "               EFX_CORPAMOUNTCD, EFX_CORPAMOUNTTP, EFX_CORPAMOUNTPREC,\n",
    "               EFX_LOCAMOUNT, EFX_LOCAMOUNTCD, EFX_LOCAMOUNTTP,\n",
    "               EFX_LOCAMOUNTPREC)\n",
    "            SELECT EFX_ID, EFX_BUSSIZE, EFX_BUSSTAT,\n",
    "               EFX_BUSSTATCD, EFX_WEB, EFX_YREST, EFX_CORPEMPCNT,\n",
    "               EFX_LOCEMPCNT, EFX_CORPEMPCD, EFX_LOCEMPCD, EFX_CORPAMOUNT,\n",
    "               EFX_CORPAMOUNTCD, EFX_CORPAMOUNTTP, EFX_CORPAMOUNTPREC,\n",
    "               EFX_LOCAMOUNT, EFX_LOCAMOUNTCD, EFX_LOCAMOUNTTP,\n",
    "               EFX_LOCAMOUNTPREC\n",
    "            FROM mdsdata\"\"\"\n",
    "queryTable(insert_business)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "8d974963",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_bussize</th>\n",
       "      <th>efx_busstat</th>\n",
       "      <th>efx_busstatcd</th>\n",
       "      <th>efx_web</th>\n",
       "      <th>efx_yrest</th>\n",
       "      <th>efx_corpempcnt</th>\n",
       "      <th>efx_locempcnt</th>\n",
       "      <th>efx_corpempcd</th>\n",
       "      <th>efx_locempcd</th>\n",
       "      <th>efx_corpamount</th>\n",
       "      <th>efx_corpamountcd</th>\n",
       "      <th>efx_corpamounttp</th>\n",
       "      <th>efx_corpamountprec</th>\n",
       "      <th>efx_locamount</th>\n",
       "      <th>efx_locamountcd</th>\n",
       "      <th>efx_locamounttp</th>\n",
       "      <th>efx_locamountprec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>L</td>\n",
       "      <td>Corporation</td>\n",
       "      <td>4.0</td>\n",
       "      <td>www.woodsarcadewillows.com</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>14229.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>K</td>\n",
       "      <td>E</td>\n",
       "      <td>4565823.0</td>\n",
       "      <td>K</td>\n",
       "      <td>Corporate Revenue</td>\n",
       "      <td>ACTUAL</td>\n",
       "      <td>4623.0</td>\n",
       "      <td>D</td>\n",
       "      <td>Location Sales</td>\n",
       "      <td>MDL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id efx_bussize  efx_busstat  efx_busstatcd                     efx_web  \\\n",
       "0     1.0           L  Corporation            4.0  www.woodsarcadewillows.com   \n",
       "\n",
       "   efx_yrest  efx_corpempcnt  efx_locempcnt efx_corpempcd efx_locempcd  \\\n",
       "0     1998.0         14229.0           77.0             K            E   \n",
       "\n",
       "   efx_corpamount efx_corpamountcd   efx_corpamounttp efx_corpamountprec  \\\n",
       "0       4565823.0                K  Corporate Revenue             ACTUAL   \n",
       "\n",
       "   efx_locamount efx_locamountcd efx_locamounttp efx_locamountprec  \n",
       "0         4623.0               D  Location Sales               MDL  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_business LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "56013e58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "relation \"dim_security\" already exists\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dim_security = \"\"\"CREATE TABLE dim_security(EFX_ID NUMERIC PRIMARY KEY,EFX_PRIMSIC REAL,  EFX_SECSIC1 REAL,  EFX_SECSIC2 REAL,EFX_PRIMSICDESC VARCHAR(255),  EFX_SECSICDESC1 VARCHAR(255),  EFX_SECSICDESC2 VARCHAR(255), EFX_PRIMNAICSCODE REAL,  EFX_SECNAICS1 REAL,  EFX_SECNAICS2 REAL, EFX_PRIMNAICSDESC VARCHAR(255),  EFX_SECNAICSDESC1 VARCHAR(255),  EFX_SECNAICSDESC2 VARCHAR(255))\"\"\"\n",
    "queryTable(dim_security)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "91db26b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_security = \"\"\" INSERT INTO dim_security(EFX_ID, EFX_PRIMSIC, EFX_SECSIC1, EFX_SECSIC2, EFX_PRIMSICDESC, EFX_SECSICDESC1, EFX_SECSICDESC2,\n",
    "                            EFX_PRIMNAICSCODE,EFX_SECNAICS1, EFX_SECNAICS2,\n",
    "                            EFX_PRIMNAICSDESC, EFX_SECNAICSDESC1, EFX_SECNAICSDESC2)\n",
    "                    SELECT EFX_ID, EFX_PRIMSIC, EFX_SECSIC1, EFX_SECSIC2, EFX_PRIMSICDESC, EFX_SECSICDESC1, EFX_SECSICDESC2,\n",
    "                            EFX_PRIMNAICSCODE,EFX_SECNAICS1, EFX_SECNAICS2, EFX_PRIMNAICSDESC, EFX_SECNAICSDESC1, EFX_SECNAICSDESC2\n",
    "                    FROM mdsdata\"\"\"\n",
    "queryTable(insert_security)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "86f11320",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_primsic</th>\n",
       "      <th>efx_secsic1</th>\n",
       "      <th>efx_secsic2</th>\n",
       "      <th>efx_primsicdesc</th>\n",
       "      <th>efx_secsicdesc1</th>\n",
       "      <th>efx_secsicdesc2</th>\n",
       "      <th>efx_primnaicscode</th>\n",
       "      <th>efx_secnaics1</th>\n",
       "      <th>efx_secnaics2</th>\n",
       "      <th>efx_primnaicsdesc</th>\n",
       "      <th>efx_secnaicsdesc1</th>\n",
       "      <th>efx_secnaicsdesc2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>8361.0</td>\n",
       "      <td>8011.0</td>\n",
       "      <td>8300.0</td>\n",
       "      <td>RESIDENTIAL CARE</td>\n",
       "      <td>OFFICES AND CLINICS OF DOCTORS OF MEDICINE</td>\n",
       "      <td>SOCIAL SERVICES</td>\n",
       "      <td>623990.0</td>\n",
       "      <td>623311.0</td>\n",
       "      <td>623110.0</td>\n",
       "      <td>Other Residential Care Facilities</td>\n",
       "      <td>Continuing Care Retirement Communities</td>\n",
       "      <td>Nursing Care Facilities (Skilled Nursing Facil...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id  efx_primsic  efx_secsic1  efx_secsic2   efx_primsicdesc  \\\n",
       "0     1.0       8361.0       8011.0       8300.0  RESIDENTIAL CARE   \n",
       "\n",
       "                              efx_secsicdesc1  efx_secsicdesc2  \\\n",
       "0  OFFICES AND CLINICS OF DOCTORS OF MEDICINE  SOCIAL SERVICES   \n",
       "\n",
       "   efx_primnaicscode  efx_secnaics1  efx_secnaics2  \\\n",
       "0           623990.0       623311.0       623110.0   \n",
       "\n",
       "                   efx_primnaicsdesc                       efx_secnaicsdesc1  \\\n",
       "0  Other Residential Care Facilities  Continuing Care Retirement Communities   \n",
       "\n",
       "                                   efx_secnaicsdesc2  \n",
       "0  Nursing Care Facilities (Skilled Nursing Facil...  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_security LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "bf9edbdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_legal = \"\"\"CREATE TABLE dim_legal(EFX_ID NUMERIC PRIMARY KEY,EFX_LEGSUBNUMALL NUMERIC,  EFX_LEGSUBNAMEALL VARCHAR(255),  EFX_LEGSUBADDRESSALL VARCHAR(255),  EFX_LEGSUBCITYALL VARCHAR(255), \n",
    "        EFX_LEGSUBSTATEALL VARCHAR(255),  EFX_LEGSUBZIPCODEALL NUMERIC,  EFX_LEGSUBZIP4ALL REAL,  EFX_LEGSUBCOUNTYALL REAL,  EFX_LEGSUBCTRYISOCDALL VARCHAR(255),  EFX_LEGSUBCTRYNUMALL NUMERIC,  \n",
    "        EFX_LEGSUBCTRYNAMEALL VARCHAR(255),  EFX_LEGDOMULTNUMALL NUMERIC,  EFX_LEGDOMULTNAMEALL VARCHAR(255),  EFX_LEGDOMULTADDRESSALL VARCHAR(255),  EFX_LEGDOMULTCITYALL VARCHAR(255),  \n",
    "        EFX_LEGDOMULTSTATEALL VARCHAR(255),  EFX_LEGDOMULTZIPCODEALL NUMERIC,  EFX_LEGDOMULTZIP4ALL REAL,  EFX_LEGDOMULTCOUNTYALL REAL,  EFX_LEGDOMULTCTRYISOCDALL VARCHAR(255),  EFX_LEGDOMULTCTRYNUMALL NUMERIC,  \n",
    "        EFX_LEGDOMULTCTRYNAMEALL VARCHAR(255),  EFX_LEGULTNUMALL NUMERIC,  EFX_LEGULTNAMEALL VARCHAR(255),  EFX_LEGULTADDRESSALL VARCHAR(255),  EFX_LEGULTCITYALL VARCHAR(255),  EFX_LEGULTSTATEALL VARCHAR(255),\n",
    "        EFX_LEGULTZIPCODEALL VARCHAR(255),  EFX_LEGULTZIP4ALL REAL,  EFX_LEGULTCOUNTYALL REAL,  EFX_LEGULTCTRYISOCDALL VARCHAR(255),  EFX_LEGULTCTRYNUMALL NUMERIC,  EFX_LEGULTCTRYNAMEALL VARCHAR(255))\"\"\"\n",
    "queryTable(dim_legal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "c77422ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_dimLegal = \"\"\"INSERT INTO dim_legal (EFX_ID, EFX_LEGSUBNUMALL, EFX_LEGSUBNAMEALL,\n",
    "                           EFX_LEGSUBADDRESSALL, EFX_LEGSUBCITYALL, EFX_LEGSUBSTATEALL,\n",
    "                           EFX_LEGSUBZIPCODEALL, EFX_LEGSUBZIP4ALL, EFX_LEGSUBCOUNTYALL,\n",
    "                           EFX_LEGSUBCTRYISOCDALL, EFX_LEGSUBCTRYNUMALL,\n",
    "                           EFX_LEGSUBCTRYNAMEALL,EFX_LEGDOMULTNUMALL, EFX_LEGDOMULTNAMEALL,\n",
    "                           EFX_LEGDOMULTADDRESSALL, EFX_LEGDOMULTCITYALL,\n",
    "                           EFX_LEGDOMULTSTATEALL, EFX_LEGDOMULTZIPCODEALL,\n",
    "                           EFX_LEGDOMULTZIP4ALL, EFX_LEGDOMULTCOUNTYALL,\n",
    "                           EFX_LEGDOMULTCTRYISOCDALL, EFX_LEGDOMULTCTRYNUMALL,\n",
    "                           EFX_LEGDOMULTCTRYNAMEALL, EFX_LEGULTNUMALL, EFX_LEGULTNAMEALL,\n",
    "                           EFX_LEGULTADDRESSALL, EFX_LEGULTCITYALL, EFX_LEGULTSTATEALL,\n",
    "                           EFX_LEGULTZIPCODEALL, EFX_LEGULTZIP4ALL, EFX_LEGULTCOUNTYALL,\n",
    "                           EFX_LEGULTCTRYISOCDALL, EFX_LEGULTCTRYNUMALL,\n",
    "                           EFX_LEGULTCTRYNAMEALL)\n",
    "                    SELECT EFX_ID, EFX_LEGSUBNUMALL, EFX_LEGSUBNAMEALL,\n",
    "                           EFX_LEGSUBADDRESSALL, EFX_LEGSUBCITYALL, EFX_LEGSUBSTATEALL,\n",
    "                           EFX_LEGSUBZIPCODEALL, EFX_LEGSUBZIP4ALL, EFX_LEGSUBCOUNTYALL,\n",
    "                           EFX_LEGSUBCTRYISOCDALL, EFX_LEGSUBCTRYNUMALL,\n",
    "                           EFX_LEGSUBCTRYNAMEALL,EFX_LEGDOMULTNUMALL, EFX_LEGDOMULTNAMEALL,\n",
    "                           EFX_LEGDOMULTADDRESSALL, EFX_LEGDOMULTCITYALL,\n",
    "                           EFX_LEGDOMULTSTATEALL, EFX_LEGDOMULTZIPCODEALL,\n",
    "                           EFX_LEGDOMULTZIP4ALL, EFX_LEGDOMULTCOUNTYALL,\n",
    "                           EFX_LEGDOMULTCTRYISOCDALL, EFX_LEGDOMULTCTRYNUMALL,\n",
    "                           EFX_LEGDOMULTCTRYNAMEALL, EFX_LEGULTNUMALL, EFX_LEGULTNAMEALL,\n",
    "                           EFX_LEGULTADDRESSALL, EFX_LEGULTCITYALL, EFX_LEGULTSTATEALL,\n",
    "                           EFX_LEGULTZIPCODEALL, EFX_LEGULTZIP4ALL, EFX_LEGULTCOUNTYALL,\n",
    "                           EFX_LEGULTCTRYISOCDALL, EFX_LEGULTCTRYNUMALL,\n",
    "                           EFX_LEGULTCTRYNAMEALL\n",
    "                    FROM mdsdata\"\"\"\n",
    "queryTable(insert_dimLegal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "c5b57de3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_legsubnumall</th>\n",
       "      <th>efx_legsubnameall</th>\n",
       "      <th>efx_legsubaddressall</th>\n",
       "      <th>efx_legsubcityall</th>\n",
       "      <th>efx_legsubstateall</th>\n",
       "      <th>efx_legsubzipcodeall</th>\n",
       "      <th>efx_legsubzip4all</th>\n",
       "      <th>efx_legsubcountyall</th>\n",
       "      <th>efx_legsubctryisocdall</th>\n",
       "      <th>...</th>\n",
       "      <th>efx_legultnameall</th>\n",
       "      <th>efx_legultaddressall</th>\n",
       "      <th>efx_legultcityall</th>\n",
       "      <th>efx_legultstateall</th>\n",
       "      <th>efx_legultzipcodeall</th>\n",
       "      <th>efx_legultzip4all</th>\n",
       "      <th>efx_legultcountyall</th>\n",
       "      <th>efx_legultctryisocdall</th>\n",
       "      <th>efx_legultctrynumall</th>\n",
       "      <th>efx_legultctrynameall</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>41712345.0</td>\n",
       "      <td>WOODS ARCADE WILLOWS CORP</td>\n",
       "      <td>1600 S BROAD ST</td>\n",
       "      <td>GWM</td>\n",
       "      <td>PA</td>\n",
       "      <td>19348.0</td>\n",
       "      <td>3346.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>WOODS ARCADE WILLOWS CORP</td>\n",
       "      <td>1101 E. STATE ST.</td>\n",
       "      <td>KENNETT AVE</td>\n",
       "      <td>PA</td>\n",
       "      <td>19348</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>USA</td>\n",
       "      <td>840.0</td>\n",
       "      <td>United States of America</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id  efx_legsubnumall          efx_legsubnameall efx_legsubaddressall  \\\n",
       "0     1.0        41712345.0  WOODS ARCADE WILLOWS CORP      1600 S BROAD ST   \n",
       "\n",
       "  efx_legsubcityall efx_legsubstateall  efx_legsubzipcodeall  \\\n",
       "0               GWM                 PA               19348.0   \n",
       "\n",
       "   efx_legsubzip4all  efx_legsubcountyall efx_legsubctryisocdall  ...  \\\n",
       "0             3346.0                 29.0                    USA  ...   \n",
       "\n",
       "           efx_legultnameall efx_legultaddressall  efx_legultcityall  \\\n",
       "0  WOODS ARCADE WILLOWS CORP    1101 E. STATE ST.        KENNETT AVE   \n",
       "\n",
       "  efx_legultstateall efx_legultzipcodeall efx_legultzip4all  \\\n",
       "0                 PA                19348              None   \n",
       "\n",
       "  efx_legultcountyall  efx_legultctryisocdall efx_legultctrynumall  \\\n",
       "0                None                     USA                840.0   \n",
       "\n",
       "      efx_legultctrynameall  \n",
       "0  United States of America  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_legal LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "1332c7b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_affiliate = \"\"\"CREATE TABLE dim_affiliate(EFX_ID NUMERIC PRIMARY KEY, EFX_AFFLULTNUMALL NUMERIC,  EFX_AFFLULTNAMEALL VARCHAR(255),  EFX_AFFLULTADDRESSALL VARCHAR(255),  \n",
    "                                            EFX_AFFLULTCITYALL VARCHAR(255),  EFX_AFFLULTSTATEALL VARCHAR(255),  EFX_AFFLULTZIPCODEALL REAL,  EFX_AFFLULTZIP4ALL REAL,  EFX_AFFULTCOUNTYALL REAL,  \n",
    "                                            EFX_AFFLULTCTRYISOCDALL VARCHAR(255),  EFX_AFFLULTCTRYNUMALL NUMERIC,  EFX_AFFLULTCTRYNAMEALL VARCHAR(255))\"\"\"\n",
    "\n",
    "queryTable(dim_affiliate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "638c9266",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_affiliate = \"\"\"INSERT INTO dim_affiliate(EFX_ID, EFX_AFFLULTNUMALL, EFX_AFFLULTNAMEALL,\n",
    "                               EFX_AFFLULTADDRESSALL, EFX_AFFLULTCITYALL, EFX_AFFLULTSTATEALL,\n",
    "                               EFX_AFFLULTZIPCODEALL, EFX_AFFLULTZIP4ALL, EFX_AFFULTCOUNTYALL,\n",
    "                               EFX_AFFLULTCTRYISOCDALL, EFX_AFFLULTCTRYNUMALL,EFX_AFFLULTCTRYNAMEALL)\n",
    "                    SELECT EFX_ID, EFX_AFFLULTNUMALL, EFX_AFFLULTNAMEALL,\n",
    "                               EFX_AFFLULTADDRESSALL, EFX_AFFLULTCITYALL, EFX_AFFLULTSTATEALL,\n",
    "                               EFX_AFFLULTZIPCODEALL, EFX_AFFLULTZIP4ALL, EFX_AFFULTCOUNTYALL,\n",
    "                               EFX_AFFLULTCTRYISOCDALL, EFX_AFFLULTCTRYNUMALL,EFX_AFFLULTCTRYNAMEALL\n",
    "                    FROM mdsdata\"\"\"\n",
    "\n",
    "queryTable(insert_affiliate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "bdec36ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_afflultnumall</th>\n",
       "      <th>efx_afflultnameall</th>\n",
       "      <th>efx_afflultaddressall</th>\n",
       "      <th>efx_afflultcityall</th>\n",
       "      <th>efx_afflultstateall</th>\n",
       "      <th>efx_afflultzipcodeall</th>\n",
       "      <th>efx_afflultzip4all</th>\n",
       "      <th>efx_affultcountyall</th>\n",
       "      <th>efx_afflultctryisocdall</th>\n",
       "      <th>efx_afflultctrynumall</th>\n",
       "      <th>efx_afflultctrynameall</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>13435354.0</td>\n",
       "      <td>WOODS ARCADE WILLOWS CORP</td>\n",
       "      <td>1101 E. STATE ST.</td>\n",
       "      <td>KENNETT AVE</td>\n",
       "      <td>PA</td>\n",
       "      <td>19348.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>USA</td>\n",
       "      <td>840.0</td>\n",
       "      <td>United States of America</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id  efx_afflultnumall         efx_afflultnameall efx_afflultaddressall  \\\n",
       "0     1.0         13435354.0  WOODS ARCADE WILLOWS CORP     1101 E. STATE ST.   \n",
       "\n",
       "  efx_afflultcityall efx_afflultstateall  efx_afflultzipcodeall  \\\n",
       "0        KENNETT AVE                  PA                19348.0   \n",
       "\n",
       "  efx_afflultzip4all efx_affultcountyall efx_afflultctryisocdall  \\\n",
       "0               None                None                     USA   \n",
       "\n",
       "   efx_afflultctrynumall    efx_afflultctrynameall  \n",
       "0                  840.0  United States of America  "
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_affiliate LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "4d693d9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_marketData = \"\"\"CREATE TABLE dim_market(EFX_ID NUMERIC PRIMARY KEY,EFX_FOREIGN VARCHAR(255),  EFX_FAILRATE REAL,  EFX_FAILLEVEL REAL,  EFX_FAILREASON1 VARCHAR(255), EFX_CREDITSCORE REAL,  EFX_CREDITCLASS REAL,  \n",
    "                    EFX_CREDITPERC REAL,  EFX_CREDITREASON1 VARCHAR(255),EFX_CREDITREASON2 VARCHAR(255),EFX_CREDITREASON3 VARCHAR(255),EFX_CREDITREASON4 VARCHAR(255),\n",
    "                    EFX_MRKT_TELEVER VARCHAR(255),  EFX_MRKT_TELESCORE NUMERIC, EFX_MRKT_TOTALSCORE NUMERIC,  EFX_MRKT_TOTALIND VARCHAR(255))\"\"\"\n",
    "queryTable(dim_marketData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "7a52f2d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_marketData = \"\"\"INSERT INTO dim_market(EFX_ID, EFX_FOREIGN, EFX_FAILRATE, EFX_FAILLEVEL,\n",
    "                                EFX_FAILREASON1, EFX_CREDITSCORE, EFX_CREDITCLASS,\n",
    "                               EFX_CREDITPERC, EFX_CREDITREASON1, EFX_CREDITREASON2,\n",
    "                               EFX_CREDITREASON3, EFX_CREDITREASON4, EFX_MRKT_TELEVER, EFX_MRKT_TELESCORE,\n",
    "                               EFX_MRKT_TOTALSCORE, EFX_MRKT_TOTALIND)\n",
    "                     SELECT EFX_ID, EFX_FOREIGN, EFX_FAILRATE, EFX_FAILLEVEL,\n",
    "                                EFX_FAILREASON1, EFX_CREDITSCORE, EFX_CREDITCLASS,\n",
    "                               EFX_CREDITPERC, EFX_CREDITREASON1, EFX_CREDITREASON2,\n",
    "                               EFX_CREDITREASON3, EFX_CREDITREASON4, EFX_MRKT_TELEVER, EFX_MRKT_TELESCORE,\n",
    "                               EFX_MRKT_TOTALSCORE, EFX_MRKT_TOTALIND\n",
    "                    FROM mdsdata\"\"\"\n",
    "queryTable(insert_marketData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "ff252d8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>efx_foreign</th>\n",
       "      <th>efx_failrate</th>\n",
       "      <th>efx_faillevel</th>\n",
       "      <th>efx_failreason1</th>\n",
       "      <th>efx_creditscore</th>\n",
       "      <th>efx_creditclass</th>\n",
       "      <th>efx_creditperc</th>\n",
       "      <th>efx_creditreason1</th>\n",
       "      <th>efx_creditreason2</th>\n",
       "      <th>efx_creditreason3</th>\n",
       "      <th>efx_creditreason4</th>\n",
       "      <th>efx_mrkt_telever</th>\n",
       "      <th>efx_mrkt_telescore</th>\n",
       "      <th>efx_mrkt_totalscore</th>\n",
       "      <th>efx_mrkt_totalind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>386.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>C0066</td>\n",
       "      <td>C1175</td>\n",
       "      <td>C1065</td>\n",
       "      <td>C0392</td>\n",
       "      <td>Y</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   efx_id efx_foreign  efx_failrate  efx_faillevel efx_failreason1  \\\n",
       "0     1.0        None           3.0            1.0            None   \n",
       "\n",
       "   efx_creditscore  efx_creditclass  efx_creditperc efx_creditreason1  \\\n",
       "0            386.0              4.0            22.0             C0066   \n",
       "\n",
       "  efx_creditreason2 efx_creditreason3 efx_creditreason4 efx_mrkt_telever  \\\n",
       "0             C1175             C1065             C0392                Y   \n",
       "\n",
       "   efx_mrkt_telescore  efx_mrkt_totalscore efx_mrkt_totalind  \n",
       "0                 5.0                  5.0                 M  "
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM dim_market LIMIT 1\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76c4ddd1",
   "metadata": {},
   "source": [
    "The 163 column dataset has been seperated into the individual tables with its own primary key. The tables are segregated by the type of the data contained in the column. The schema is still incomplete. The fact table is yet to be build. To build that the probable facts needs to be reviewed and created.\n",
    "\n",
    "That will be the next step... Before that, need to identfy the facts that needs to be in the factsTable. The facts are numeric in nature. So starting with dim_market table, list the important looking (\"what you think important\") below\n",
    "\n",
    "dim_market has \"efx_mrkt_totalscore\", efx_creditscore, efx_failrate, efx_creditperc\n",
    "\n",
    "dim_business has efx_busstatcd, efx_corpempcnt, efx_locempcnt,efx_corpamount, efx_locamount\n",
    "\n",
    "Now lets build the factTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4edacec0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_name</th>\n",
       "      <th>efx_mrkt_totalscore</th>\n",
       "      <th>efx_creditscore</th>\n",
       "      <th>efx_failrate</th>\n",
       "      <th>efx_creditperc</th>\n",
       "      <th>efx_busstatcd</th>\n",
       "      <th>efx_corpempcnt</th>\n",
       "      <th>efx_locempcnt</th>\n",
       "      <th>efx_corpamount</th>\n",
       "      <th>efx_locamount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>5.0</td>\n",
       "      <td>386.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14229.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>4565823.0</td>\n",
       "      <td>4623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABCDE RENTALS</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>8530000.0</td>\n",
       "      <td>3004.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABCD DOWNSTREET BANK</td>\n",
       "      <td>5.0</td>\n",
       "      <td>507.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8036.0</td>\n",
       "      <td>3207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABCDE FURNITTURE CO</td>\n",
       "      <td>1.0</td>\n",
       "      <td>212.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>37900.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>80077865.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DUMMY PARK AND RECREATION INC</td>\n",
       "      <td>5.0</td>\n",
       "      <td>333.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10194.0</td>\n",
       "      <td>7484.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        efx_name  efx_mrkt_totalscore  efx_creditscore  \\\n",
       "0           WOODS ARCADE WILLOWS                  5.0            386.0   \n",
       "1                  ABCDE RENTALS                  5.0              NaN   \n",
       "2           ABCD DOWNSTREET BANK                  5.0            507.0   \n",
       "3            ABCDE FURNITTURE CO                  1.0            212.0   \n",
       "4  DUMMY PARK AND RECREATION INC                  5.0            333.0   \n",
       "\n",
       "   efx_failrate  efx_creditperc  efx_busstatcd  efx_corpempcnt  efx_locempcnt  \\\n",
       "0           3.0            22.0            4.0         14229.0           77.0   \n",
       "1           6.0             NaN            4.0         19100.0           12.0   \n",
       "2           3.0            72.0            4.0            85.0           19.0   \n",
       "3           1.0             4.0            4.0         37900.0          220.0   \n",
       "4           7.0            10.0            4.0            66.0           20.0   \n",
       "\n",
       "   efx_corpamount  efx_locamount  \n",
       "0       4565823.0         4623.0  \n",
       "1       8530000.0         3004.0  \n",
       "2          8036.0         3207.0  \n",
       "3      80077865.0            0.0  \n",
       "4         10194.0         7484.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testQuery = \"\"\"SELECT dl.efx_name, dm.efx_mrkt_totalscore, dm.efx_creditscore, dm.efx_failrate, \n",
    "                      dm.efx_creditperc, db.efx_busstatcd, db.efx_corpempcnt, db.efx_locempcnt,\n",
    "                      db.efx_corpamount, db.efx_locamount\n",
    "                FROM dim_market AS dm \n",
    "                JOIN dim_location AS dl ON dm.efx_id = dl.efx_id\n",
    "                JOIN dim_business AS db ON dl.efx_id = db.efx_id\n",
    "                LIMIT 5\"\"\"\n",
    "queryBase(testQuery)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "70719d4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "createFact = \"\"\"CREATE TABLE factTable(efx_id NUMERIC,\n",
    "                    location_key INTEGER REFERENCES dim_location(efx_id),\n",
    "                    business_key INTEGER REFERENCES dim_business(efx_id),\n",
    "                    security_key INTEGER REFERENCES dim_security(efx_id),\n",
    "                    market_key INTEGER REFERENCES dim_market(efx_id),\n",
    "                    legal_key INTEGER REFERENCES dim_legal(efx_id),\n",
    "                    affiliate_key INTEGER REFERENCES dim_affiliate(efx_id),\n",
    "                    efx_name VARCHAR(255), efx_mrkt_totalscore REAL, \n",
    "                    efx_creditscore REAL, efx_failrate REAL, \n",
    "                    efx_creditperc REAL,efx_busstatcd REAL, \n",
    "                    efx_corpempcnt REAL,efx_locempcnt REAL,\n",
    "                    efx_corpamount REAL, efx_locamount REAL)\"\"\"\n",
    "queryTable(createFact)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "75a5de4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertFact = \"\"\"INSERT INTO factTable(\n",
    "                    location_key,\n",
    "                    business_key,\n",
    "                    security_key,\n",
    "                    market_key,\n",
    "                    legal_key,\n",
    "                    affiliate_key,\n",
    "                    efx_name, efx_mrkt_totalscore, efx_creditscore,efx_failrate, \n",
    "                    efx_creditperc,efx_busstatcd,efx_corpempcnt,efx_locempcnt,\n",
    "                    efx_corpamount,efx_locamount)\n",
    "                SELECT dl.efx_id as location_key,\n",
    "                    db.efx_id as business_key,\n",
    "                    ds.efx_id as security_key,\n",
    "                    dm.efx_id as market_key,\n",
    "                    dle.efx_id as legal_key,\n",
    "                    df.efx_id as affiliate_key,\n",
    "                    dl.efx_name, dm.efx_mrkt_totalscore, dm.efx_creditscore, dm.efx_failrate, \n",
    "                    dm.efx_creditperc, db.efx_busstatcd, db.efx_corpempcnt, db.efx_locempcnt,\n",
    "                    db.efx_corpamount, db.efx_locamount\n",
    "                FROM dim_market AS dm \n",
    "                JOIN dim_location AS dl ON dm.efx_id = dl.efx_id\n",
    "                JOIN dim_business AS db ON dl.efx_id = db.efx_id\n",
    "                JOIN dim_affiliate AS df ON db.efx_id = df.efx_id\n",
    "                JOIN dim_legal AS dle ON df.efx_id = dle.efx_id\n",
    "                JOIN dim_security AS ds ON dle.efx_id = ds.efx_id\n",
    "                \"\"\"\n",
    "queryTable(insertFact)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "918eca5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>efx_id</th>\n",
       "      <th>location_key</th>\n",
       "      <th>business_key</th>\n",
       "      <th>security_key</th>\n",
       "      <th>market_key</th>\n",
       "      <th>legal_key</th>\n",
       "      <th>affiliate_key</th>\n",
       "      <th>efx_name</th>\n",
       "      <th>efx_mrkt_totalscore</th>\n",
       "      <th>efx_creditscore</th>\n",
       "      <th>efx_failrate</th>\n",
       "      <th>efx_creditperc</th>\n",
       "      <th>efx_busstatcd</th>\n",
       "      <th>efx_corpempcnt</th>\n",
       "      <th>efx_locempcnt</th>\n",
       "      <th>efx_corpamount</th>\n",
       "      <th>efx_locamount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>WOODS ARCADE WILLOWS</td>\n",
       "      <td>5.0</td>\n",
       "      <td>386.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14229.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>4565823.0</td>\n",
       "      <td>4623.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>ABCDE RENTALS</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>8530000.0</td>\n",
       "      <td>3004.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  efx_id  location_key  business_key  security_key  market_key  legal_key  \\\n",
       "0   None             1             1             1           1          1   \n",
       "1   None             2             2             2           2          2   \n",
       "\n",
       "   affiliate_key              efx_name  efx_mrkt_totalscore  efx_creditscore  \\\n",
       "0              1  WOODS ARCADE WILLOWS                  5.0            386.0   \n",
       "1              2         ABCDE RENTALS                  5.0              NaN   \n",
       "\n",
       "   efx_failrate  efx_creditperc  efx_busstatcd  efx_corpempcnt  efx_locempcnt  \\\n",
       "0           3.0            22.0            4.0         14229.0           77.0   \n",
       "1           6.0             NaN            4.0         19100.0           12.0   \n",
       "\n",
       "   efx_corpamount  efx_locamount  \n",
       "0       4565823.0         4623.0  \n",
       "1       8530000.0         3004.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM facttable LIMIT 2\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4cf9846",
   "metadata": {},
   "source": [
    "The efx_id is redundant in the facttable as it is referenced from multiple tables already. \n",
    "Another point to note is the record count is same in all the table. So these many references \n",
    "might seem not a requirement. They are required anyway, to provide each type of analysis to be sepeated."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}