dvdrental_SchemaImplementation.ipynb

{
 "cells": [
  {
   "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": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config.read('clusterdvd.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c4bb5934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'172.17.0.2'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config['POSTGRES']['PG_HOST']"
   ]
  },
  {
   "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": 7,
   "id": "86635a7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "53435a3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/dvdrental'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e4195156",
   "metadata": {},
   "outputs": [],
   "source": [
    "filmTable = pd.read_sql(\"\"\"SELECT * FROM film\"\"\",con=credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d464a8bb",
   "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>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>release_year</th>\n",
       "      <th>language_id</th>\n",
       "      <th>rental_duration</th>\n",
       "      <th>rental_rate</th>\n",
       "      <th>length</th>\n",
       "      <th>replacement_cost</th>\n",
       "      <th>rating</th>\n",
       "      <th>last_update</th>\n",
       "      <th>special_features</th>\n",
       "      <th>fulltext</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>133</td>\n",
       "      <td>Chamber Italian</td>\n",
       "      <td>A Fateful Reflection of a Moose And a Husband ...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>4.99</td>\n",
       "      <td>117</td>\n",
       "      <td>14.99</td>\n",
       "      <td>NC-17</td>\n",
       "      <td>2013-05-26 14:50:58.951</td>\n",
       "      <td>[Trailers]</td>\n",
       "      <td>'chamber':1 'fate':4 'husband':11 'italian':2 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>384</td>\n",
       "      <td>Grosse Wonderful</td>\n",
       "      <td>A Epic Drama of a Cat And a Explorer who must ...</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>4.99</td>\n",
       "      <td>49</td>\n",
       "      <td>19.99</td>\n",
       "      <td>R</td>\n",
       "      <td>2013-05-26 14:50:58.951</td>\n",
       "      <td>[Behind the Scenes]</td>\n",
       "      <td>'australia':18 'cat':8 'drama':5 'epic':4 'exp...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   film_id             title  \\\n",
       "0      133   Chamber Italian   \n",
       "1      384  Grosse Wonderful   \n",
       "\n",
       "                                         description  release_year  \\\n",
       "0  A Fateful Reflection of a Moose And a Husband ...          2006   \n",
       "1  A Epic Drama of a Cat And a Explorer who must ...          2006   \n",
       "\n",
       "   language_id  rental_duration  rental_rate  length  replacement_cost rating  \\\n",
       "0            1                7         4.99     117             14.99  NC-17   \n",
       "1            1                5         4.99      49             19.99      R   \n",
       "\n",
       "              last_update     special_features  \\\n",
       "0 2013-05-26 14:50:58.951           [Trailers]   \n",
       "1 2013-05-26 14:50:58.951  [Behind the Scenes]   \n",
       "\n",
       "                                            fulltext  \n",
       "0  'chamber':1 'fate':4 'husband':11 'italian':2 ...  \n",
       "1  'australia':18 'cat':8 'drama':5 'epic':4 'exp...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filmTable.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "150dee3e",
   "metadata": {},
   "source": [
    "Getting the schema from the existing tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "70edda81",
   "metadata": {},
   "outputs": [],
   "source": [
    "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": 26,
   "id": "3f0bad66",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>inventory</td>\n",
       "      <td>inventory_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('inventory_inventory_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>inventory</td>\n",
       "      <td>film_id</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>inventory</td>\n",
       "      <td>store_id</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>inventory</td>\n",
       "      <td>last_update</td>\n",
       "      <td>4</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>4 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name   column_name  ordinal_position  \\\n",
       "0     dvdrental       public  inventory  inventory_id                 1   \n",
       "1     dvdrental       public  inventory       film_id                 2   \n",
       "2     dvdrental       public  inventory      store_id                 3   \n",
       "3     dvdrental       public  inventory   last_update                 4   \n",
       "\n",
       "                                    column_default is_nullable  \\\n",
       "0  nextval('inventory_inventory_id_seq'::regclass)          NO   \n",
       "1                                             None          NO   \n",
       "2                                             None          NO   \n",
       "3                                            now()          NO   \n",
       "\n",
       "                     data_type character_maximum_length  \\\n",
       "0                      integer                     None   \n",
       "1                     smallint                     None   \n",
       "2                     smallint                     None   \n",
       "3  timestamp without time zone                     None   \n",
       "\n",
       "  character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                   None  ...           NO                 None   \n",
       "1                   None  ...           NO                 None   \n",
       "2                   None  ...           NO                 None   \n",
       "3                   None  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "\n",
       "[4 rows x 44 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('inventory',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "0d3944a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def queryTable(query, credentials):\n",
    "    try:\n",
    "        pd.read_sql(query, credentials)\n",
    "        return\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d2cdf472",
   "metadata": {},
   "outputs": [],
   "source": [
    "createDim1 = \"\"\"CREATE TABLE dimdate(\n",
    "                date_key integer NOT NULL PRIMARY KEY,\n",
    "                date date NOT NULL,\n",
    "                year smallint NOT NULL,\n",
    "                quarter smallint NOT NULL,\n",
    "                month smallint NOT NULL,\n",
    "                day smallint NOT NULL,\n",
    "                week smallint NOT NULL,\n",
    "                is_weekend boolean\n",
    "            )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "7d9f93d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "createDim2 = \"\"\"CREATE TABLE dimcustomer(\n",
    "                customer_key SERIAL PRIMARY KEY,\n",
    "                customer_id  SMALLINT NOT NULL,\n",
    "                first_name VARCHAR(45) NOT NULL,\n",
    "                last_name VARCHAR(45) NOT NULL,\n",
    "                email VARCHAR(60),\n",
    "                address VARCHAR(50) NOT NULL,\n",
    "                address2 VARCHAR(50),\n",
    "                district VARCHAR(50) NOT NULL,\n",
    "                city VARCHAR(50) NOT NULL,\n",
    "                country VARCHAR(50) NOT NULL,\n",
    "                postal_code VARCHAR(10),\n",
    "                phone VARCHAR(20) NOT NULL,\n",
    "                active smallint NOT NULL,\n",
    "                create_date timestamp NOT NULL,\n",
    "                start_date date NOT NULL,\n",
    "                end_date date NOT NULL\n",
    "            )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "578d7fec",
   "metadata": {},
   "outputs": [],
   "source": [
    "createDim3 = \"\"\"CREATE TABLE dimmovie(\n",
    "                movie_key SERIAL PRIMARY KEY,\n",
    "                film_id  SMALLINT NOT NULL,\n",
    "                title VARCHAR(45) NOT NULL,\n",
    "                description TEXT,\n",
    "                release_year YEAR,\n",
    "                language VARCHAR(20),\n",
    "                original_language VARCHAR(20),\n",
    "                rental_duration SMALLINT NOT NULL,\n",
    "                length smallINT NOT NULL,\n",
    "                rating VARCHAR(5) NOT NULL,\n",
    "                special_features VARCHAR(60) NOT NULL\n",
    "            )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "5a8a8259",
   "metadata": {},
   "outputs": [],
   "source": [
    "createDim4 = \"\"\"CREATE TABLE dimstore(\n",
    "                store_key SERIAL PRIMARY KEY,\n",
    "                store_id  SMALLINT NOT NULL,\n",
    "                manager_first_name VARCHAR(45) NOT NULL,\n",
    "                manager_last_name VARCHAR(45) NOT NULL,\n",
    "                address VARCHAR(50) NOT NULL,\n",
    "                address2 VARCHAR(50),\n",
    "                district VARCHAR(20) NOT NULL,\n",
    "                city VARCHAR(20) NOT NULL,\n",
    "                country VARCHAR(50) NOT NULL,\n",
    "                postal_code VARCHAR(10),\n",
    "                start_date DATE NOT NULL,\n",
    "                end_date DATE NOT NULL\n",
    "                )\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8219879a",
   "metadata": {},
   "source": [
    "Making the the table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "7c833a66",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(createDim1, credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "097891f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(createDim2, credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "531474b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(createDim3, credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "fa9d4a94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(createDim4, credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "b1e2b4c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def queryBase(query,credentials):\n",
    "    requiredTable = pd.read_sql(query,con=credentials)\n",
    "    return requiredTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "9ccdd5a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"SELECT * FROM dimdate\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a1dac1d2",
   "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>date_key</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week</th>\n",
       "      <th>is_weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70214</td>\n",
       "      <td>2007-02-14</td>\n",
       "      <td>2007</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70317</td>\n",
       "      <td>2007-03-17</td>\n",
       "      <td>2007</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_key        date  year  quarter  month  day  week  is_weekend\n",
       "0     70214  2007-02-14  2007        1      2   14     7       False\n",
       "1     70317  2007-03-17  2007        1      3   17    11        True"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(query,credentials).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f93f4b5",
   "metadata": {},
   "source": [
    "Executing insertion into the single dimdate table from payment table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "ab5d06c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertQuery = \"\"\"\n",
    "        INSERT INTO dimdate (date_key, date, year, quarter, month, day, week, is_weekend)\n",
    "        SELECT \n",
    "            DISTINCT(TO_CHAR(payment_date::DATE, 'yyMMDD')::integer) as date_key,\n",
    "            date(payment_date) as date,\n",
    "            EXTRACT(year FROM payment_date) as year,\n",
    "            EXTRACT(quarter FROM payment_date) as quarter,\n",
    "            EXTRACT(month FROM payment_date) as month,\n",
    "            EXTRACT(day FROM payment_date) as day,\n",
    "            EXTRACT(week FROM payment_date) as week,\n",
    "            CASE WHEN EXTRACT(ISODOW FROM payment_date) IN (6,7) THEN true else false END;\n",
    "        FROM payment;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "5b86f5fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(insertQuery, credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "143acb41",
   "metadata": {},
   "source": [
    "Multiple tables are joined and the data is extracted from it\n",
    "and dimcustomer is filled. All the below \"getSchema\" function calls were executed when the necessary columns where required to be checked. \n",
    "\n",
    "### Major Benefit 1\n",
    "The benefit of writing functions that they can be called when you need to refer during the Query is written. There is no need to switch between multiple windows, or use mouse to get the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "e64e1092",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>country_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('country_country_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>last_update</td>\n",
       "      <td>3</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>country</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>3 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public    country   country_id                 1   \n",
       "1     dvdrental       public    country  last_update                 3   \n",
       "2     dvdrental       public    country      country                 2   \n",
       "\n",
       "                                column_default is_nullable  \\\n",
       "0  nextval('country_country_id_seq'::regclass)          NO   \n",
       "1                                        now()          NO   \n",
       "2                                         None          NO   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0                      integer                       NaN   \n",
       "1  timestamp without time zone                       NaN   \n",
       "2            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\n",
       "0            None                None             None             None   \n",
       "1            None                None             None             None   \n",
       "2            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "\n",
       "[3 rows x 44 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('country',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "4d4f2933",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>last_update</td>\n",
       "      <td>8</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>city_id</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('address_address_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>district</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>4</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>phone</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>postal_code</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>10.0</td>\n",
       "      <td>40.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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address2</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>8 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public    address  last_update                 8   \n",
       "1     dvdrental       public    address      city_id                 5   \n",
       "2     dvdrental       public    address   address_id                 1   \n",
       "3     dvdrental       public    address     district                 4   \n",
       "4     dvdrental       public    address        phone                 7   \n",
       "5     dvdrental       public    address  postal_code                 6   \n",
       "6     dvdrental       public    address      address                 2   \n",
       "7     dvdrental       public    address     address2                 3   \n",
       "\n",
       "                                column_default is_nullable  \\\n",
       "0                                        now()          NO   \n",
       "1                                         None          NO   \n",
       "2  nextval('address_address_id_seq'::regclass)          NO   \n",
       "3                                         None          NO   \n",
       "4                                         None          NO   \n",
       "5                                         None         YES   \n",
       "6                                         None          NO   \n",
       "7                                         None         YES   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0  timestamp without time zone                       NaN   \n",
       "1                     smallint                       NaN   \n",
       "2                      integer                       NaN   \n",
       "3            character varying                      20.0   \n",
       "4            character varying                      20.0   \n",
       "5            character varying                      10.0   \n",
       "6            character varying                      50.0   \n",
       "7            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                     NaN  ...           NO                 None   \n",
       "3                    80.0  ...           NO                 None   \n",
       "4                    80.0  ...           NO                 None   \n",
       "5                    40.0  ...           NO                 None   \n",
       "6                   200.0  ...           NO                 None   \n",
       "7                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5            None                None             None             None   \n",
       "6            None                None             None             None   \n",
       "7            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "4             NO        NEVER                  None          YES  \n",
       "5             NO        NEVER                  None          YES  \n",
       "6             NO        NEVER                  None          YES  \n",
       "7             NO        NEVER                  None          YES  \n",
       "\n",
       "[8 rows x 44 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('address',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "9ea8dd2b",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>active</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>store_id</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>create_date</td>\n",
       "      <td>8</td>\n",
       "      <td>('now'::text)::date</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>last_update</td>\n",
       "      <td>9</td>\n",
       "      <td>now()</td>\n",
       "      <td>YES</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>customer_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('customer_customer_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>address_id</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>activebool</td>\n",
       "      <td>7</td>\n",
       "      <td>true</td>\n",
       "      <td>NO</td>\n",
       "      <td>boolean</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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>first_name</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>8</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>last_name</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>9</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>customer</td>\n",
       "      <td>email</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>10 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public   customer       active                10   \n",
       "1     dvdrental       public   customer     store_id                 2   \n",
       "2     dvdrental       public   customer  create_date                 8   \n",
       "3     dvdrental       public   customer  last_update                 9   \n",
       "4     dvdrental       public   customer  customer_id                 1   \n",
       "5     dvdrental       public   customer   address_id                 6   \n",
       "6     dvdrental       public   customer   activebool                 7   \n",
       "7     dvdrental       public   customer   first_name                 3   \n",
       "8     dvdrental       public   customer    last_name                 4   \n",
       "9     dvdrental       public   customer        email                 5   \n",
       "\n",
       "                                  column_default is_nullable  \\\n",
       "0                                           None         YES   \n",
       "1                                           None          NO   \n",
       "2                            ('now'::text)::date          NO   \n",
       "3                                          now()         YES   \n",
       "4  nextval('customer_customer_id_seq'::regclass)          NO   \n",
       "5                                           None          NO   \n",
       "6                                           true          NO   \n",
       "7                                           None          NO   \n",
       "8                                           None          NO   \n",
       "9                                           None         YES   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0                      integer                       NaN   \n",
       "1                     smallint                       NaN   \n",
       "2                         date                       NaN   \n",
       "3  timestamp without time zone                       NaN   \n",
       "4                      integer                       NaN   \n",
       "5                     smallint                       NaN   \n",
       "6                      boolean                       NaN   \n",
       "7            character varying                      45.0   \n",
       "8            character varying                      45.0   \n",
       "9            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                     NaN  ...           NO                 None   \n",
       "3                     NaN  ...           NO                 None   \n",
       "4                     NaN  ...           NO                 None   \n",
       "5                     NaN  ...           NO                 None   \n",
       "6                     NaN  ...           NO                 None   \n",
       "7                   180.0  ...           NO                 None   \n",
       "8                   180.0  ...           NO                 None   \n",
       "9                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5            None                None             None             None   \n",
       "6            None                None             None             None   \n",
       "7            None                None             None             None   \n",
       "8            None                None             None             None   \n",
       "9            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "4             NO        NEVER                  None          YES  \n",
       "5             NO        NEVER                  None          YES  \n",
       "6             NO        NEVER                  None          YES  \n",
       "7             NO        NEVER                  None          YES  \n",
       "8             NO        NEVER                  None          YES  \n",
       "9             NO        NEVER                  None          YES  \n",
       "\n",
       "[10 rows x 44 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('customer',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "f3814dd7",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>last_update</td>\n",
       "      <td>8</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>city_id</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('address_address_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>district</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>4</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>phone</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>postal_code</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>10.0</td>\n",
       "      <td>40.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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>address</td>\n",
       "      <td>address2</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>8 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public    address  last_update                 8   \n",
       "1     dvdrental       public    address      city_id                 5   \n",
       "2     dvdrental       public    address   address_id                 1   \n",
       "3     dvdrental       public    address     district                 4   \n",
       "4     dvdrental       public    address        phone                 7   \n",
       "5     dvdrental       public    address  postal_code                 6   \n",
       "6     dvdrental       public    address      address                 2   \n",
       "7     dvdrental       public    address     address2                 3   \n",
       "\n",
       "                                column_default is_nullable  \\\n",
       "0                                        now()          NO   \n",
       "1                                         None          NO   \n",
       "2  nextval('address_address_id_seq'::regclass)          NO   \n",
       "3                                         None          NO   \n",
       "4                                         None          NO   \n",
       "5                                         None         YES   \n",
       "6                                         None          NO   \n",
       "7                                         None         YES   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0  timestamp without time zone                       NaN   \n",
       "1                     smallint                       NaN   \n",
       "2                      integer                       NaN   \n",
       "3            character varying                      20.0   \n",
       "4            character varying                      20.0   \n",
       "5            character varying                      10.0   \n",
       "6            character varying                      50.0   \n",
       "7            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                     NaN  ...           NO                 None   \n",
       "3                    80.0  ...           NO                 None   \n",
       "4                    80.0  ...           NO                 None   \n",
       "5                    40.0  ...           NO                 None   \n",
       "6                   200.0  ...           NO                 None   \n",
       "7                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5            None                None             None             None   \n",
       "6            None                None             None             None   \n",
       "7            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "4             NO        NEVER                  None          YES  \n",
       "5             NO        NEVER                  None          YES  \n",
       "6             NO        NEVER                  None          YES  \n",
       "7             NO        NEVER                  None          YES  \n",
       "\n",
       "[8 rows x 44 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('address',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "acf2a5ed",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>city</td>\n",
       "      <td>city_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('city_city_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>city</td>\n",
       "      <td>country_id</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>city</td>\n",
       "      <td>last_update</td>\n",
       "      <td>4</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>city</td>\n",
       "      <td>city</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>4 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public       city      city_id                 1   \n",
       "1     dvdrental       public       city   country_id                 3   \n",
       "2     dvdrental       public       city  last_update                 4   \n",
       "3     dvdrental       public       city         city                 2   \n",
       "\n",
       "                          column_default is_nullable  \\\n",
       "0  nextval('city_city_id_seq'::regclass)          NO   \n",
       "1                                   None          NO   \n",
       "2                                  now()          NO   \n",
       "3                                   None          NO   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0                      integer                       NaN   \n",
       "1                     smallint                       NaN   \n",
       "2  timestamp without time zone                       NaN   \n",
       "3            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                     NaN  ...           NO                 None   \n",
       "3                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "\n",
       "[4 rows x 44 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('city',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "70d5b7b5",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>country_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('country_country_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>last_update</td>\n",
       "      <td>3</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>country</td>\n",
       "      <td>country</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>3 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public    country   country_id                 1   \n",
       "1     dvdrental       public    country  last_update                 3   \n",
       "2     dvdrental       public    country      country                 2   \n",
       "\n",
       "                                column_default is_nullable  \\\n",
       "0  nextval('country_country_id_seq'::regclass)          NO   \n",
       "1                                        now()          NO   \n",
       "2                                         None          NO   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0                      integer                       NaN   \n",
       "1  timestamp without time zone                       NaN   \n",
       "2            character varying                      50.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                   200.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\n",
       "0            None                None             None             None   \n",
       "1            None                None             None             None   \n",
       "2            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "\n",
       "[3 rows x 44 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('country',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "1eef8160",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>customer_key</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('dimcustomer_customer_key_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>customer_id</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>active</td>\n",
       "      <td>13</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>create_date</td>\n",
       "      <td>14</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>start_date</td>\n",
       "      <td>15</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>end_date</td>\n",
       "      <td>16</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>address2</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>district</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>8</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>city</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>9</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>country</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>10</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>postal_code</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>10.0</td>\n",
       "      <td>40.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>11</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>phone</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>12</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>first_name</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>13</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>last_name</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>14</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>email</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>60.0</td>\n",
       "      <td>240.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>15</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimcustomer</td>\n",
       "      <td>address</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>16 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   table_catalog table_schema   table_name   column_name  ordinal_position  \\\n",
       "0      dvdrental       public  dimcustomer  customer_key                 1   \n",
       "1      dvdrental       public  dimcustomer   customer_id                 2   \n",
       "2      dvdrental       public  dimcustomer        active                13   \n",
       "3      dvdrental       public  dimcustomer   create_date                14   \n",
       "4      dvdrental       public  dimcustomer    start_date                15   \n",
       "5      dvdrental       public  dimcustomer      end_date                16   \n",
       "6      dvdrental       public  dimcustomer      address2                 7   \n",
       "7      dvdrental       public  dimcustomer      district                 8   \n",
       "8      dvdrental       public  dimcustomer          city                 9   \n",
       "9      dvdrental       public  dimcustomer       country                10   \n",
       "10     dvdrental       public  dimcustomer   postal_code                11   \n",
       "11     dvdrental       public  dimcustomer         phone                12   \n",
       "12     dvdrental       public  dimcustomer    first_name                 3   \n",
       "13     dvdrental       public  dimcustomer     last_name                 4   \n",
       "14     dvdrental       public  dimcustomer         email                 5   \n",
       "15     dvdrental       public  dimcustomer       address                 6   \n",
       "\n",
       "                                       column_default is_nullable  \\\n",
       "0   nextval('dimcustomer_customer_key_seq'::regclass)          NO   \n",
       "1                                                None          NO   \n",
       "2                                                None          NO   \n",
       "3                                                None          NO   \n",
       "4                                                None          NO   \n",
       "5                                                None          NO   \n",
       "6                                                None          NO   \n",
       "7                                                None          NO   \n",
       "8                                                None          NO   \n",
       "9                                                None          NO   \n",
       "10                                               None         YES   \n",
       "11                                               None          NO   \n",
       "12                                               None          NO   \n",
       "13                                               None          NO   \n",
       "14                                               None         YES   \n",
       "15                                               None          NO   \n",
       "\n",
       "                      data_type  character_maximum_length  \\\n",
       "0                       integer                       NaN   \n",
       "1                      smallint                       NaN   \n",
       "2                      smallint                       NaN   \n",
       "3   timestamp without time zone                       NaN   \n",
       "4                          date                       NaN   \n",
       "5                          date                       NaN   \n",
       "6             character varying                      50.0   \n",
       "7             character varying                      20.0   \n",
       "8             character varying                      20.0   \n",
       "9             character varying                      50.0   \n",
       "10            character varying                      10.0   \n",
       "11            character varying                      20.0   \n",
       "12            character varying                      45.0   \n",
       "13            character varying                      45.0   \n",
       "14            character varying                      60.0   \n",
       "15            character varying                      50.0   \n",
       "\n",
       "    character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                      NaN  ...           NO                 None   \n",
       "1                      NaN  ...           NO                 None   \n",
       "2                      NaN  ...           NO                 None   \n",
       "3                      NaN  ...           NO                 None   \n",
       "4                      NaN  ...           NO                 None   \n",
       "5                      NaN  ...           NO                 None   \n",
       "6                    200.0  ...           NO                 None   \n",
       "7                     80.0  ...           NO                 None   \n",
       "8                     80.0  ...           NO                 None   \n",
       "9                    200.0  ...           NO                 None   \n",
       "10                    40.0  ...           NO                 None   \n",
       "11                    80.0  ...           NO                 None   \n",
       "12                   180.0  ...           NO                 None   \n",
       "13                   180.0  ...           NO                 None   \n",
       "14                   240.0  ...           NO                 None   \n",
       "15                   200.0  ...           NO                 None   \n",
       "\n",
       "    identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5             None                None             None             None   \n",
       "6             None                None             None             None   \n",
       "7             None                None             None             None   \n",
       "8             None                None             None             None   \n",
       "9             None                None             None             None   \n",
       "10            None                None             None             None   \n",
       "11            None                None             None             None   \n",
       "12            None                None             None             None   \n",
       "13            None                None             None             None   \n",
       "14            None                None             None             None   \n",
       "15            None                None             None             None   \n",
       "\n",
       "   identity_cycle is_generated generation_expression is_updatable  \n",
       "0              NO        NEVER                  None          YES  \n",
       "1              NO        NEVER                  None          YES  \n",
       "2              NO        NEVER                  None          YES  \n",
       "3              NO        NEVER                  None          YES  \n",
       "4              NO        NEVER                  None          YES  \n",
       "5              NO        NEVER                  None          YES  \n",
       "6              NO        NEVER                  None          YES  \n",
       "7              NO        NEVER                  None          YES  \n",
       "8              NO        NEVER                  None          YES  \n",
       "9              NO        NEVER                  None          YES  \n",
       "10             NO        NEVER                  None          YES  \n",
       "11             NO        NEVER                  None          YES  \n",
       "12             NO        NEVER                  None          YES  \n",
       "13             NO        NEVER                  None          YES  \n",
       "14             NO        NEVER                  None          YES  \n",
       "15             NO        NEVER                  None          YES  \n",
       "\n",
       "[16 rows x 44 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('dimcustomer',credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd991312",
   "metadata": {},
   "source": [
    "### Major Benefit 2:\n",
    "When doing the join and need to refer the table columns, the functions written inside the jupyter environment can be used for querying the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "a77aa516",
   "metadata": {},
   "outputs": [],
   "source": [
    "checkQuery_error = \"\"\"\n",
    "        SELECT c.customer_id as customer_key,\n",
    "                c.customer_id, c.first_name, c.last_name, c.email,\n",
    "                a.address, a.address2, a.district, ci.city, co.country, \n",
    "                postal_code, a.phone, c.active, c.create_date,now() as start_date,\n",
    "                now() as end_date\n",
    "        FROM customer AS c\n",
    "        JOIN address ON c.address_id = a.address_id\n",
    "        JOIN city ON a.city_id = ci.city_id\n",
    "        JOIN country ON ci.country_id = co.country_id;\n",
    "\"\"\"\n",
    "#The tables are not having aliases... Observe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "19ca63c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "checkQuery = \"\"\"\n",
    "        SELECT c.customer_id as customer_key,\n",
    "                c.customer_id, c.first_name, c.last_name, c.email,\n",
    "                a.address, a.address2, a.district, ci.city, co.country, \n",
    "                postal_code, a.phone, c.active, c.create_date,now() as start_date,\n",
    "                now() as end_date\n",
    "        FROM customer AS c\n",
    "        JOIN address AS a ON c.address_id = a.address_id\n",
    "        JOIN city AS ci ON a.city_id = ci.city_id\n",
    "        JOIN country AS co ON ci.country_id = co.country_id;\n",
    "\"\"\"\n",
    "#this executes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4f585ec",
   "metadata": {},
   "source": [
    "### Major Benefit 3:\n",
    "After writing complicated select queries they can be wrong due to various reasons. If required the \"Wrong\" queries can be retained as a reference..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "14fa7e9d",
   "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>customer_key</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address</th>\n",
       "      <th>address2</th>\n",
       "      <th>district</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>phone</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>524</td>\n",
       "      <td>524</td>\n",
       "      <td>Jared</td>\n",
       "      <td>Ely</td>\n",
       "      <td>jared.ely@sakilacustomer.org</td>\n",
       "      <td>1003 Qinhuangdao Street</td>\n",
       "      <td></td>\n",
       "      <td>West Java</td>\n",
       "      <td>Purwakarta</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>25972</td>\n",
       "      <td>35533115997</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2022-11-01 11:05:07.699618+00:00</td>\n",
       "      <td>2022-11-01 11:05:07.699618+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "      <td>mary.smith@sakilacustomer.org</td>\n",
       "      <td>1913 Hanoi Way</td>\n",
       "      <td></td>\n",
       "      <td>Nagasaki</td>\n",
       "      <td>Sasebo</td>\n",
       "      <td>Japan</td>\n",
       "      <td>35200</td>\n",
       "      <td>28303384290</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2022-11-01 11:05:07.699618+00:00</td>\n",
       "      <td>2022-11-01 11:05:07.699618+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_key  customer_id first_name last_name  \\\n",
       "0           524          524      Jared       Ely   \n",
       "1             1            1       Mary     Smith   \n",
       "\n",
       "                           email                  address address2   district  \\\n",
       "0   jared.ely@sakilacustomer.org  1003 Qinhuangdao Street           West Java   \n",
       "1  mary.smith@sakilacustomer.org           1913 Hanoi Way            Nagasaki   \n",
       "\n",
       "         city    country postal_code        phone  active create_date  \\\n",
       "0  Purwakarta  Indonesia       25972  35533115997       1  2006-02-14   \n",
       "1      Sasebo      Japan       35200  28303384290       1  2006-02-14   \n",
       "\n",
       "                        start_date                         end_date  \n",
       "0 2022-11-01 11:05:07.699618+00:00 2022-11-01 11:05:07.699618+00:00  \n",
       "1 2022-11-01 11:05:07.699618+00:00 2022-11-01 11:05:07.699618+00:00  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(checkQuery,credentials).head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "1e31573c",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertCustomer = \"\"\"\n",
    "            INSERT INTO dimcustomer(customer_key,customer_id,first_name,\n",
    "            last_name,email,address,address2,district,city,country,\n",
    "            postal_code,phone,active,create_date,start_date,end_date)\n",
    "            SELECT c.customer_id as customer_key,\n",
    "                c.customer_id, c.first_name, c.last_name, c.email,\n",
    "                a.address, a.address2, a.district, ci.city, co.country, \n",
    "                postal_code, a.phone, c.active, c.create_date,now() as start_date,\n",
    "                now() as end_date\n",
    "            FROM customer AS c\n",
    "            JOIN address AS a ON c.address_id = a.address_id\n",
    "            JOIN city AS ci ON a.city_id = ci.city_id\n",
    "            JOIN country AS co ON ci.country_id = co.country_id;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c88df206",
   "metadata": {},
   "source": [
    "#### Inserting data into dimcustomer table and consecutively checking contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "5466bade",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(insertCustomer,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "d4ec4940",
   "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>customer_key</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>address</th>\n",
       "      <th>address2</th>\n",
       "      <th>district</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>phone</th>\n",
       "      <th>active</th>\n",
       "      <th>create_date</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>524</td>\n",
       "      <td>524</td>\n",
       "      <td>Jared</td>\n",
       "      <td>Ely</td>\n",
       "      <td>jared.ely@sakilacustomer.org</td>\n",
       "      <td>1003 Qinhuangdao Street</td>\n",
       "      <td></td>\n",
       "      <td>West Java</td>\n",
       "      <td>Purwakarta</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>25972</td>\n",
       "      <td>35533115997</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>2022-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mary</td>\n",
       "      <td>Smith</td>\n",
       "      <td>mary.smith@sakilacustomer.org</td>\n",
       "      <td>1913 Hanoi Way</td>\n",
       "      <td></td>\n",
       "      <td>Nagasaki</td>\n",
       "      <td>Sasebo</td>\n",
       "      <td>Japan</td>\n",
       "      <td>35200</td>\n",
       "      <td>28303384290</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-02-14</td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>2022-11-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_key  customer_id first_name last_name  \\\n",
       "0           524          524      Jared       Ely   \n",
       "1             1            1       Mary     Smith   \n",
       "\n",
       "                           email                  address address2   district  \\\n",
       "0   jared.ely@sakilacustomer.org  1003 Qinhuangdao Street           West Java   \n",
       "1  mary.smith@sakilacustomer.org           1913 Hanoi Way            Nagasaki   \n",
       "\n",
       "         city    country postal_code        phone  active create_date  \\\n",
       "0  Purwakarta  Indonesia       25972  35533115997       1  2006-02-14   \n",
       "1      Sasebo      Japan       35200  28303384290       1  2006-02-14   \n",
       "\n",
       "   start_date    end_date  \n",
       "0  2022-11-01  2022-11-01  \n",
       "1  2022-11-01  2022-11-01  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryCheckdimCustomer = \"\"\"SELECT * FROM dimcustomer\"\"\"\n",
    "queryBase(queryCheckdimCustomer,credentials).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25f0e62d",
   "metadata": {},
   "source": [
    "Referring the tables that we are planning to join, here referring inventory table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "5efc5047",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>store_key</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('dimstore_store_key_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>store_id</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>start_date</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>end_date</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>address</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>address2</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>district</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>city</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>8</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>country</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>9</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>postal_code</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>10.0</td>\n",
       "      <td>40.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>10</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>manager_first_name</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>11</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimstore</td>\n",
       "      <td>manager_last_name</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>12 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   table_catalog table_schema table_name         column_name  \\\n",
       "0      dvdrental       public   dimstore           store_key   \n",
       "1      dvdrental       public   dimstore            store_id   \n",
       "2      dvdrental       public   dimstore          start_date   \n",
       "3      dvdrental       public   dimstore            end_date   \n",
       "4      dvdrental       public   dimstore             address   \n",
       "5      dvdrental       public   dimstore            address2   \n",
       "6      dvdrental       public   dimstore            district   \n",
       "7      dvdrental       public   dimstore                city   \n",
       "8      dvdrental       public   dimstore             country   \n",
       "9      dvdrental       public   dimstore         postal_code   \n",
       "10     dvdrental       public   dimstore  manager_first_name   \n",
       "11     dvdrental       public   dimstore   manager_last_name   \n",
       "\n",
       "    ordinal_position                               column_default is_nullable  \\\n",
       "0                  1  nextval('dimstore_store_key_seq'::regclass)          NO   \n",
       "1                  2                                         None          NO   \n",
       "2                 11                                         None          NO   \n",
       "3                 12                                         None          NO   \n",
       "4                  5                                         None          NO   \n",
       "5                  6                                         None         YES   \n",
       "6                  7                                         None          NO   \n",
       "7                  8                                         None          NO   \n",
       "8                  9                                         None          NO   \n",
       "9                 10                                         None         YES   \n",
       "10                 3                                         None          NO   \n",
       "11                 4                                         None          NO   \n",
       "\n",
       "            data_type  character_maximum_length  character_octet_length  ...  \\\n",
       "0             integer                       NaN                     NaN  ...   \n",
       "1            smallint                       NaN                     NaN  ...   \n",
       "2                date                       NaN                     NaN  ...   \n",
       "3                date                       NaN                     NaN  ...   \n",
       "4   character varying                      50.0                   200.0  ...   \n",
       "5   character varying                      50.0                   200.0  ...   \n",
       "6   character varying                      20.0                    80.0  ...   \n",
       "7   character varying                      20.0                    80.0  ...   \n",
       "8   character varying                      50.0                   200.0  ...   \n",
       "9   character varying                      10.0                    40.0  ...   \n",
       "10  character varying                      45.0                   180.0  ...   \n",
       "11  character varying                      45.0                   180.0  ...   \n",
       "\n",
       "    is_identity  identity_generation  identity_start  identity_increment  \\\n",
       "0            NO                 None            None                None   \n",
       "1            NO                 None            None                None   \n",
       "2            NO                 None            None                None   \n",
       "3            NO                 None            None                None   \n",
       "4            NO                 None            None                None   \n",
       "5            NO                 None            None                None   \n",
       "6            NO                 None            None                None   \n",
       "7            NO                 None            None                None   \n",
       "8            NO                 None            None                None   \n",
       "9            NO                 None            None                None   \n",
       "10           NO                 None            None                None   \n",
       "11           NO                 None            None                None   \n",
       "\n",
       "   identity_maximum identity_minimum identity_cycle is_generated  \\\n",
       "0              None             None             NO        NEVER   \n",
       "1              None             None             NO        NEVER   \n",
       "2              None             None             NO        NEVER   \n",
       "3              None             None             NO        NEVER   \n",
       "4              None             None             NO        NEVER   \n",
       "5              None             None             NO        NEVER   \n",
       "6              None             None             NO        NEVER   \n",
       "7              None             None             NO        NEVER   \n",
       "8              None             None             NO        NEVER   \n",
       "9              None             None             NO        NEVER   \n",
       "10             None             None             NO        NEVER   \n",
       "11             None             None             NO        NEVER   \n",
       "\n",
       "   generation_expression is_updatable  \n",
       "0                   None          YES  \n",
       "1                   None          YES  \n",
       "2                   None          YES  \n",
       "3                   None          YES  \n",
       "4                   None          YES  \n",
       "5                   None          YES  \n",
       "6                   None          YES  \n",
       "7                   None          YES  \n",
       "8                   None          YES  \n",
       "9                   None          YES  \n",
       "10                  None          YES  \n",
       "11                  None          YES  \n",
       "\n",
       "[12 rows x 44 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('dimstore',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "3fd30ce8",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>store</td>\n",
       "      <td>store_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('store_store_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>store</td>\n",
       "      <td>manager_staff_id</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>store</td>\n",
       "      <td>address_id</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>store</td>\n",
       "      <td>last_update</td>\n",
       "      <td>4</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>4 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name       column_name  ordinal_position  \\\n",
       "0     dvdrental       public      store          store_id                 1   \n",
       "1     dvdrental       public      store  manager_staff_id                 2   \n",
       "2     dvdrental       public      store        address_id                 3   \n",
       "3     dvdrental       public      store       last_update                 4   \n",
       "\n",
       "                            column_default is_nullable  \\\n",
       "0  nextval('store_store_id_seq'::regclass)          NO   \n",
       "1                                     None          NO   \n",
       "2                                     None          NO   \n",
       "3                                    now()          NO   \n",
       "\n",
       "                     data_type character_maximum_length  \\\n",
       "0                      integer                     None   \n",
       "1                     smallint                     None   \n",
       "2                     smallint                     None   \n",
       "3  timestamp without time zone                     None   \n",
       "\n",
       "  character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                   None  ...           NO                 None   \n",
       "1                   None  ...           NO                 None   \n",
       "2                   None  ...           NO                 None   \n",
       "3                   None  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "\n",
       "[4 rows x 44 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('store',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "cd12e068",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>picture</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>bytea</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>address_id</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>store_id</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>active</td>\n",
       "      <td>7</td>\n",
       "      <td>true</td>\n",
       "      <td>NO</td>\n",
       "      <td>boolean</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>last_update</td>\n",
       "      <td>10</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>staff_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('staff_staff_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>first_name</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>last_name</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>45.0</td>\n",
       "      <td>180.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>8</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>password</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>40.0</td>\n",
       "      <td>160.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>9</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>email</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>character varying</td>\n",
       "      <td>50.0</td>\n",
       "      <td>200.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>10</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>staff</td>\n",
       "      <td>username</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>16.0</td>\n",
       "      <td>64.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>11 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0      dvdrental       public      staff      picture                11   \n",
       "1      dvdrental       public      staff   address_id                 4   \n",
       "2      dvdrental       public      staff     store_id                 6   \n",
       "3      dvdrental       public      staff       active                 7   \n",
       "4      dvdrental       public      staff  last_update                10   \n",
       "5      dvdrental       public      staff     staff_id                 1   \n",
       "6      dvdrental       public      staff   first_name                 2   \n",
       "7      dvdrental       public      staff    last_name                 3   \n",
       "8      dvdrental       public      staff     password                 9   \n",
       "9      dvdrental       public      staff        email                 5   \n",
       "10     dvdrental       public      staff     username                 8   \n",
       "\n",
       "                             column_default is_nullable  \\\n",
       "0                                      None         YES   \n",
       "1                                      None          NO   \n",
       "2                                      None          NO   \n",
       "3                                      true          NO   \n",
       "4                                     now()          NO   \n",
       "5   nextval('staff_staff_id_seq'::regclass)          NO   \n",
       "6                                      None          NO   \n",
       "7                                      None          NO   \n",
       "8                                      None         YES   \n",
       "9                                      None         YES   \n",
       "10                                     None          NO   \n",
       "\n",
       "                      data_type  character_maximum_length  \\\n",
       "0                         bytea                       NaN   \n",
       "1                      smallint                       NaN   \n",
       "2                      smallint                       NaN   \n",
       "3                       boolean                       NaN   \n",
       "4   timestamp without time zone                       NaN   \n",
       "5                       integer                       NaN   \n",
       "6             character varying                      45.0   \n",
       "7             character varying                      45.0   \n",
       "8             character varying                      40.0   \n",
       "9             character varying                      50.0   \n",
       "10            character varying                      16.0   \n",
       "\n",
       "    character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                      NaN  ...           NO                 None   \n",
       "1                      NaN  ...           NO                 None   \n",
       "2                      NaN  ...           NO                 None   \n",
       "3                      NaN  ...           NO                 None   \n",
       "4                      NaN  ...           NO                 None   \n",
       "5                      NaN  ...           NO                 None   \n",
       "6                    180.0  ...           NO                 None   \n",
       "7                    180.0  ...           NO                 None   \n",
       "8                    160.0  ...           NO                 None   \n",
       "9                    200.0  ...           NO                 None   \n",
       "10                    64.0  ...           NO                 None   \n",
       "\n",
       "    identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5             None                None             None             None   \n",
       "6             None                None             None             None   \n",
       "7             None                None             None             None   \n",
       "8             None                None             None             None   \n",
       "9             None                None             None             None   \n",
       "10            None                None             None             None   \n",
       "\n",
       "   identity_cycle is_generated generation_expression is_updatable  \n",
       "0              NO        NEVER                  None          YES  \n",
       "1              NO        NEVER                  None          YES  \n",
       "2              NO        NEVER                  None          YES  \n",
       "3              NO        NEVER                  None          YES  \n",
       "4              NO        NEVER                  None          YES  \n",
       "5              NO        NEVER                  None          YES  \n",
       "6              NO        NEVER                  None          YES  \n",
       "7              NO        NEVER                  None          YES  \n",
       "8              NO        NEVER                  None          YES  \n",
       "9              NO        NEVER                  None          YES  \n",
       "10             NO        NEVER                  None          YES  \n",
       "\n",
       "[11 rows x 44 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('staff',credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "505e1c2e",
   "metadata": {},
   "source": [
    "#### Inserting data into dimstore table and consecutively checking contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "4d70c872",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertStore = \"\"\"\n",
    "        INSERT INTO dimstore(store_key,store_id,start_date,end_date,\n",
    "            address,address2,district,city,country,\n",
    "            postal_code,manager_first_name,manager_last_name)\n",
    "            SELECT s.store_id as store_key,\n",
    "                s.store_id, \n",
    "                now() as start_date,\n",
    "                now() as end_date,\n",
    "                a.address, \n",
    "                a.address2, \n",
    "                a.district, \n",
    "                ci.city, \n",
    "                co.country, \n",
    "                postal_code, \n",
    "                st.first_name as manager_first_name, \n",
    "                st.last_name as manager_last_name \n",
    "            FROM store AS s\n",
    "            JOIN staff AS st ON s.manager_staff_id = st.staff_id\n",
    "            JOIN address AS a ON st.address_id = a.address_id\n",
    "            JOIN city AS ci ON a.city_id = ci.city_id\n",
    "            JOIN country AS co ON ci.country_id = co.country_id;\n",
    "\"\"\"\n",
    "#before FROM there comma should not be there"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "5815113d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(insertStore,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "e03de89a",
   "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>store_key</th>\n",
       "      <th>store_id</th>\n",
       "      <th>manager_first_name</th>\n",
       "      <th>manager_last_name</th>\n",
       "      <th>address</th>\n",
       "      <th>address2</th>\n",
       "      <th>district</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mike</td>\n",
       "      <td>Hillyer</td>\n",
       "      <td>23 Workhaven Lane</td>\n",
       "      <td>None</td>\n",
       "      <td>Alberta</td>\n",
       "      <td>Lethbridge</td>\n",
       "      <td>Canada</td>\n",
       "      <td></td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>2022-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Jon</td>\n",
       "      <td>Stephens</td>\n",
       "      <td>1411 Lillydale Drive</td>\n",
       "      <td>None</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Woodridge</td>\n",
       "      <td>Australia</td>\n",
       "      <td></td>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>2022-11-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_key  store_id manager_first_name manager_last_name  \\\n",
       "0          1         1               Mike           Hillyer   \n",
       "1          2         2                Jon          Stephens   \n",
       "\n",
       "                address address2 district        city    country postal_code  \\\n",
       "0     23 Workhaven Lane     None  Alberta  Lethbridge     Canada               \n",
       "1  1411 Lillydale Drive     None      QLD   Woodridge  Australia               \n",
       "\n",
       "   start_date    end_date  \n",
       "0  2022-11-01  2022-11-01  \n",
       "1  2022-11-01  2022-11-01  "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryCheckdimStore = \"\"\"SELECT * FROM dimstore\"\"\"\n",
    "queryBase(queryCheckdimStore,credentials).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d273b04a",
   "metadata": {},
   "source": [
    "Insert the factsales table, this contains the important step of references"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "7b514984",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>fulltext</td>\n",
       "      <td>13</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>tsvector</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>rating</td>\n",
       "      <td>10</td>\n",
       "      <td>'G'::mpaa_rating</td>\n",
       "      <td>YES</td>\n",
       "      <td>USER-DEFINED</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>last_update</td>\n",
       "      <td>11</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>film_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('film_film_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>release_year</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>language_id</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>rental_duration</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>rental_rate</td>\n",
       "      <td>7</td>\n",
       "      <td>4.99</td>\n",
       "      <td>NO</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>8</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>length</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>smallint</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>9</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>replacement_cost</td>\n",
       "      <td>9</td>\n",
       "      <td>19.99</td>\n",
       "      <td>NO</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>10</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>title</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character varying</td>\n",
       "      <td>255.0</td>\n",
       "      <td>1.020000e+03</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>11</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>description</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>text</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.073742e+09</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>12</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>film</td>\n",
       "      <td>special_features</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>ARRAY</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",
       "  </tbody>\n",
       "</table>\n",
       "<p>13 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   table_catalog table_schema table_name       column_name  ordinal_position  \\\n",
       "0      dvdrental       public       film          fulltext                13   \n",
       "1      dvdrental       public       film            rating                10   \n",
       "2      dvdrental       public       film       last_update                11   \n",
       "3      dvdrental       public       film           film_id                 1   \n",
       "4      dvdrental       public       film      release_year                 4   \n",
       "5      dvdrental       public       film       language_id                 5   \n",
       "6      dvdrental       public       film   rental_duration                 6   \n",
       "7      dvdrental       public       film       rental_rate                 7   \n",
       "8      dvdrental       public       film            length                 8   \n",
       "9      dvdrental       public       film  replacement_cost                 9   \n",
       "10     dvdrental       public       film             title                 2   \n",
       "11     dvdrental       public       film       description                 3   \n",
       "12     dvdrental       public       film  special_features                12   \n",
       "\n",
       "                           column_default is_nullable  \\\n",
       "0                                    None          NO   \n",
       "1                        'G'::mpaa_rating         YES   \n",
       "2                                   now()          NO   \n",
       "3   nextval('film_film_id_seq'::regclass)          NO   \n",
       "4                                    None         YES   \n",
       "5                                    None          NO   \n",
       "6                                       3          NO   \n",
       "7                                    4.99          NO   \n",
       "8                                    None         YES   \n",
       "9                                   19.99          NO   \n",
       "10                                   None          NO   \n",
       "11                                   None         YES   \n",
       "12                                   None         YES   \n",
       "\n",
       "                      data_type  character_maximum_length  \\\n",
       "0                      tsvector                       NaN   \n",
       "1                  USER-DEFINED                       NaN   \n",
       "2   timestamp without time zone                       NaN   \n",
       "3                       integer                       NaN   \n",
       "4                       integer                       NaN   \n",
       "5                      smallint                       NaN   \n",
       "6                      smallint                       NaN   \n",
       "7                       numeric                       NaN   \n",
       "8                      smallint                       NaN   \n",
       "9                       numeric                       NaN   \n",
       "10            character varying                     255.0   \n",
       "11                         text                       NaN   \n",
       "12                        ARRAY                       NaN   \n",
       "\n",
       "    character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                      NaN  ...           NO                 None   \n",
       "1                      NaN  ...           NO                 None   \n",
       "2                      NaN  ...           NO                 None   \n",
       "3                      NaN  ...           NO                 None   \n",
       "4                      NaN  ...           NO                 None   \n",
       "5                      NaN  ...           NO                 None   \n",
       "6                      NaN  ...           NO                 None   \n",
       "7                      NaN  ...           NO                 None   \n",
       "8                      NaN  ...           NO                 None   \n",
       "9                      NaN  ...           NO                 None   \n",
       "10            1.020000e+03  ...           NO                 None   \n",
       "11            1.073742e+09  ...           NO                 None   \n",
       "12                     NaN  ...           NO                 None   \n",
       "\n",
       "    identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5             None                None             None             None   \n",
       "6             None                None             None             None   \n",
       "7             None                None             None             None   \n",
       "8             None                None             None             None   \n",
       "9             None                None             None             None   \n",
       "10            None                None             None             None   \n",
       "11            None                None             None             None   \n",
       "12            None                None             None             None   \n",
       "\n",
       "   identity_cycle is_generated generation_expression is_updatable  \n",
       "0              NO        NEVER                  None          YES  \n",
       "1              NO        NEVER                  None          YES  \n",
       "2              NO        NEVER                  None          YES  \n",
       "3              NO        NEVER                  None          YES  \n",
       "4              NO        NEVER                  None          YES  \n",
       "5              NO        NEVER                  None          YES  \n",
       "6              NO        NEVER                  None          YES  \n",
       "7              NO        NEVER                  None          YES  \n",
       "8              NO        NEVER                  None          YES  \n",
       "9              NO        NEVER                  None          YES  \n",
       "10             NO        NEVER                  None          YES  \n",
       "11             NO        NEVER                  None          YES  \n",
       "12             NO        NEVER                  None          YES  \n",
       "\n",
       "[13 rows x 44 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('film',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "9b2769a3",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>language</td>\n",
       "      <td>language_id</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('language_language_id_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>language</td>\n",
       "      <td>last_update</td>\n",
       "      <td>3</td>\n",
       "      <td>now()</td>\n",
       "      <td>NO</td>\n",
       "      <td>timestamp without time zone</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>language</td>\n",
       "      <td>name</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>character</td>\n",
       "      <td>20.0</td>\n",
       "      <td>80.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>3 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name  column_name  ordinal_position  \\\n",
       "0     dvdrental       public   language  language_id                 1   \n",
       "1     dvdrental       public   language  last_update                 3   \n",
       "2     dvdrental       public   language         name                 2   \n",
       "\n",
       "                                  column_default is_nullable  \\\n",
       "0  nextval('language_language_id_seq'::regclass)          NO   \n",
       "1                                          now()          NO   \n",
       "2                                           None          NO   \n",
       "\n",
       "                     data_type  character_maximum_length  \\\n",
       "0                      integer                       NaN   \n",
       "1  timestamp without time zone                       NaN   \n",
       "2                    character                      20.0   \n",
       "\n",
       "   character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                     NaN  ...           NO                 None   \n",
       "1                     NaN  ...           NO                 None   \n",
       "2                    80.0  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\n",
       "0            None                None             None             None   \n",
       "1            None                None             None             None   \n",
       "2            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "\n",
       "[3 rows x 44 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('language',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "ee49908a",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertMovie = f\"\"\"\n",
    "        INSERT INTO dimmovie(movie_key, film_id, title, description, \n",
    "        release_year , language, original_language ,rental_duration ,\n",
    "        length, rating, special_features)\n",
    "        SELECT \n",
    "            f.film_id as movie_key,\n",
    "            f.film_id,\n",
    "            f.title,\n",
    "            f.description,\n",
    "            f.release_year,\n",
    "            l.last_update as language,\n",
    "            l.name as original_language,\n",
    "            f.rental_duration,\n",
    "            f.length,\n",
    "            f.rating,\n",
    "            f.special_features\n",
    "        FROM film as f\n",
    "        JOIN language as l ON f.language_id = l.language_id\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "d2a7f845",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(insertMovie,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "23716a76",
   "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>movie_key</th>\n",
       "      <th>film_id</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>release_year</th>\n",
       "      <th>language</th>\n",
       "      <th>original_language</th>\n",
       "      <th>rental_duration</th>\n",
       "      <th>length</th>\n",
       "      <th>rating</th>\n",
       "      <th>special_features</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>133</td>\n",
       "      <td>133</td>\n",
       "      <td>Chamber Italian</td>\n",
       "      <td>A Fateful Reflection of a Moose And a Husband ...</td>\n",
       "      <td>2006</td>\n",
       "      <td>2006-02-15 10:02:19</td>\n",
       "      <td>English</td>\n",
       "      <td>7</td>\n",
       "      <td>117</td>\n",
       "      <td>NC-17</td>\n",
       "      <td>{Trailers}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>384</td>\n",
       "      <td>384</td>\n",
       "      <td>Grosse Wonderful</td>\n",
       "      <td>A Epic Drama of a Cat And a Explorer who must ...</td>\n",
       "      <td>2006</td>\n",
       "      <td>2006-02-15 10:02:19</td>\n",
       "      <td>English</td>\n",
       "      <td>5</td>\n",
       "      <td>49</td>\n",
       "      <td>R</td>\n",
       "      <td>{\"Behind the Scenes\"}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movie_key  film_id             title  \\\n",
       "0        133      133   Chamber Italian   \n",
       "1        384      384  Grosse Wonderful   \n",
       "\n",
       "                                         description  release_year  \\\n",
       "0  A Fateful Reflection of a Moose And a Husband ...          2006   \n",
       "1  A Epic Drama of a Cat And a Explorer who must ...          2006   \n",
       "\n",
       "              language original_language  rental_duration  length rating  \\\n",
       "0  2006-02-15 10:02:19           English                7     117  NC-17   \n",
       "1  2006-02-15 10:02:19           English                5      49      R   \n",
       "\n",
       "        special_features  \n",
       "0             {Trailers}  \n",
       "1  {\"Behind the Scenes\"}  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryCheckMovie = \"\"\"SELECT * FROM dimmovie\"\"\"\n",
    "queryBase(queryCheckMovie,credentials).head(2)\n",
    "#There is mistake in language column. The last_update date is populated instead of language name."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fe1e141",
   "metadata": {},
   "source": [
    "### Preparing the final factsales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "cb64a2ac",
   "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>date_key</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week</th>\n",
       "      <th>is_weekend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70214</td>\n",
       "      <td>2007-02-14</td>\n",
       "      <td>2007</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70317</td>\n",
       "      <td>2007-03-17</td>\n",
       "      <td>2007</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_key        date  year  quarter  month  day  week  is_weekend\n",
       "0     70214  2007-02-14  2007        1      2   14     7       False\n",
       "1     70317  2007-03-17  2007        1      3   17    11        True"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase('SELECT * FROM dimdate', credentials).head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "61423971",
   "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>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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>date_key</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>date</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>date</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>year</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>quarter</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>month</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>day</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>6</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>week</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>NO</td>\n",
       "      <td>smallint</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>7</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>dimdate</td>\n",
       "      <td>is_weekend</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>boolean</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>8 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name column_name  ordinal_position  \\\n",
       "0     dvdrental       public    dimdate    date_key                 1   \n",
       "1     dvdrental       public    dimdate        date                 2   \n",
       "2     dvdrental       public    dimdate        year                 3   \n",
       "3     dvdrental       public    dimdate     quarter                 4   \n",
       "4     dvdrental       public    dimdate       month                 5   \n",
       "5     dvdrental       public    dimdate         day                 6   \n",
       "6     dvdrental       public    dimdate        week                 7   \n",
       "7     dvdrental       public    dimdate  is_weekend                 8   \n",
       "\n",
       "  column_default is_nullable data_type character_maximum_length  \\\n",
       "0           None          NO   integer                     None   \n",
       "1           None          NO      date                     None   \n",
       "2           None          NO  smallint                     None   \n",
       "3           None          NO  smallint                     None   \n",
       "4           None          NO  smallint                     None   \n",
       "5           None          NO  smallint                     None   \n",
       "6           None          NO  smallint                     None   \n",
       "7           None         YES   boolean                     None   \n",
       "\n",
       "  character_octet_length  ...  is_identity  identity_generation  \\\n",
       "0                   None  ...           NO                 None   \n",
       "1                   None  ...           NO                 None   \n",
       "2                   None  ...           NO                 None   \n",
       "3                   None  ...           NO                 None   \n",
       "4                   None  ...           NO                 None   \n",
       "5                   None  ...           NO                 None   \n",
       "6                   None  ...           NO                 None   \n",
       "7                   None  ...           NO                 None   \n",
       "\n",
       "   identity_start  identity_increment identity_maximum identity_minimum  \\\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",
       "5            None                None             None             None   \n",
       "6            None                None             None             None   \n",
       "7            None                None             None             None   \n",
       "\n",
       "  identity_cycle is_generated generation_expression is_updatable  \n",
       "0             NO        NEVER                  None          YES  \n",
       "1             NO        NEVER                  None          YES  \n",
       "2             NO        NEVER                  None          YES  \n",
       "3             NO        NEVER                  None          YES  \n",
       "4             NO        NEVER                  None          YES  \n",
       "5             NO        NEVER                  None          YES  \n",
       "6             NO        NEVER                  None          YES  \n",
       "7             NO        NEVER                  None          YES  \n",
       "\n",
       "[8 rows x 44 columns]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('dimdate',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "8c9224c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "createfactSales = \"\"\"CREATE TABLE factsales(\n",
    "                    sales_key SERIAL PRIMARY KEY,\n",
    "                    date_key integer REFERENCES dimdate(date_key),\n",
    "                    customer_key integer REFERENCES dimcustomer(customer_key),\n",
    "                    movie_key integer REFERENCES dimmovie(movie_key),\n",
    "                    store_key integer REFERENCES dimstore(store_key),\n",
    "                    sales_amount numeric)\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "7f532fd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    },
    {
     "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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>sales_key</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('factsales_sales_key_seq'::regclass)</td>\n",
       "      <td>NO</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>date_key</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>customer_key</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>movie_key</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>store_key</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>integer</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>5</th>\n",
       "      <td>dvdrental</td>\n",
       "      <td>public</td>\n",
       "      <td>factsales</td>\n",
       "      <td>sales_amount</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>YES</td>\n",
       "      <td>numeric</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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>6 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_schema table_name   column_name  ordinal_position  \\\n",
       "0     dvdrental       public  factsales     sales_key                 1   \n",
       "1     dvdrental       public  factsales      date_key                 2   \n",
       "2     dvdrental       public  factsales  customer_key                 3   \n",
       "3     dvdrental       public  factsales     movie_key                 4   \n",
       "4     dvdrental       public  factsales     store_key                 5   \n",
       "5     dvdrental       public  factsales  sales_amount                 6   \n",
       "\n",
       "                                 column_default is_nullable data_type  \\\n",
       "0  nextval('factsales_sales_key_seq'::regclass)          NO   integer   \n",
       "1                                          None         YES   integer   \n",
       "2                                          None         YES   integer   \n",
       "3                                          None         YES   integer   \n",
       "4                                          None         YES   integer   \n",
       "5                                          None         YES   numeric   \n",
       "\n",
       "  character_maximum_length character_octet_length  ...  is_identity  \\\n",
       "0                     None                   None  ...           NO   \n",
       "1                     None                   None  ...           NO   \n",
       "2                     None                   None  ...           NO   \n",
       "3                     None                   None  ...           NO   \n",
       "4                     None                   None  ...           NO   \n",
       "5                     None                   None  ...           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",
       "5                 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",
       "5             None             NO        NEVER                  None   \n",
       "\n",
       "  is_updatable  \n",
       "0          YES  \n",
       "1          YES  \n",
       "2          YES  \n",
       "3          YES  \n",
       "4          YES  \n",
       "5          YES  \n",
       "\n",
       "[6 rows x 44 columns]"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inserting the factsales table and checking it\n",
    "queryTable(createfactSales, credentials)\n",
    "getSchema('factsales',credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "588c8148",
   "metadata": {},
   "outputs": [],
   "source": [
    "insertSale = \"\"\"INSERT INTO factsales(date_key, customer_key, movie_key,\n",
    "                store_key, sales_amount)\n",
    "                SELECT\n",
    "                    DISTINCT(TO_CHAR(payment_date::DATE, 'yyMMDD')::integer) as date_key,\n",
    "                    p.customer_id as customer_key,\n",
    "                    i.film_id as movie_key,\n",
    "                    i.store_id as store_key,\n",
    "                    p.amount as sales_amount\n",
    "                FROM payment as p\n",
    "                JOIN rental r ON p.rental_id = r.rental_id\n",
    "                JOIN inventory i ON r.inventory_id = i.inventory_id;\"\"\"\n",
    "\n",
    "#The distinct keyword is required for the datekey. We are creating it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "c5cbf01d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This result object does not return rows. It has been closed automatically.\n"
     ]
    }
   ],
   "source": [
    "queryTable(insertSale,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "997ccb4a",
   "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>sales_key</th>\n",
       "      <th>date_key</th>\n",
       "      <th>customer_key</th>\n",
       "      <th>movie_key</th>\n",
       "      <th>store_key</th>\n",
       "      <th>sales_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>70428</td>\n",
       "      <td>177</td>\n",
       "      <td>149</td>\n",
       "      <td>2</td>\n",
       "      <td>1.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>70412</td>\n",
       "      <td>246</td>\n",
       "      <td>518</td>\n",
       "      <td>2</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>70409</td>\n",
       "      <td>43</td>\n",
       "      <td>608</td>\n",
       "      <td>1</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>70409</td>\n",
       "      <td>174</td>\n",
       "      <td>267</td>\n",
       "      <td>1</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>70218</td>\n",
       "      <td>86</td>\n",
       "      <td>767</td>\n",
       "      <td>2</td>\n",
       "      <td>4.99</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14591</th>\n",
       "      <td>14592</td>\n",
       "      <td>70302</td>\n",
       "      <td>375</td>\n",
       "      <td>621</td>\n",
       "      <td>1</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14592</th>\n",
       "      <td>14593</td>\n",
       "      <td>70301</td>\n",
       "      <td>192</td>\n",
       "      <td>122</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14593</th>\n",
       "      <td>14594</td>\n",
       "      <td>70317</td>\n",
       "      <td>303</td>\n",
       "      <td>899</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14594</th>\n",
       "      <td>14595</td>\n",
       "      <td>70318</td>\n",
       "      <td>247</td>\n",
       "      <td>827</td>\n",
       "      <td>2</td>\n",
       "      <td>7.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14595</th>\n",
       "      <td>14596</td>\n",
       "      <td>70319</td>\n",
       "      <td>118</td>\n",
       "      <td>115</td>\n",
       "      <td>2</td>\n",
       "      <td>6.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14596 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       sales_key  date_key  customer_key  movie_key  store_key  sales_amount\n",
       "0              1     70428           177        149          2          1.99\n",
       "1              2     70412           246        518          2          4.99\n",
       "2              3     70409            43        608          1          4.99\n",
       "3              4     70409           174        267          1          4.99\n",
       "4              5     70218            86        767          2          4.99\n",
       "...          ...       ...           ...        ...        ...           ...\n",
       "14591      14592     70302           375        621          1          5.99\n",
       "14592      14593     70301           192        122          1          0.99\n",
       "14593      14594     70317           303        899          1          0.99\n",
       "14594      14595     70318           247        827          2          7.99\n",
       "14595      14596     70319           118        115          2          6.99\n",
       "\n",
       "[14596 rows x 6 columns]"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM factsales\"\"\", credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb4a9c59",
   "metadata": {},
   "source": [
    "### Running the final query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "8c287c51",
   "metadata": {},
   "outputs": [],
   "source": [
    "finalQuery = \"\"\"SELECT dm.title, dd.month, dc.city, SUM(sales_amount) as revenue\n",
    "                FROM factsales as fs\n",
    "                JOIN dimmovie as dm ON fs.movie_key = dm.movie_key\n",
    "                JOIN dimdate as dd ON fs.date_key = dd.date_key\n",
    "                JOIN dimcustomer as dc ON fs.customer_key = dc.customer_key\n",
    "                GROUP BY (dm.title, dd.month, dc.city)\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "f6ac9f3d",
   "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>title</th>\n",
       "      <th>month</th>\n",
       "      <th>city</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>French Holiday</td>\n",
       "      <td>2</td>\n",
       "      <td>Bayugan</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jekyll Frogmen</td>\n",
       "      <td>4</td>\n",
       "      <td>Varanasi (Benares)</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Intentions Empire</td>\n",
       "      <td>4</td>\n",
       "      <td>Pjatigorsk</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>League Hellfighters</td>\n",
       "      <td>3</td>\n",
       "      <td>Coacalco de Berriozbal</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Grit Clockwork</td>\n",
       "      <td>3</td>\n",
       "      <td>Bchar</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14535</th>\n",
       "      <td>Beneath Rush</td>\n",
       "      <td>3</td>\n",
       "      <td>Namibe</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14536</th>\n",
       "      <td>Liaisons Sweet</td>\n",
       "      <td>3</td>\n",
       "      <td>Tonghae</td>\n",
       "      <td>7.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14537</th>\n",
       "      <td>Sun Confessions</td>\n",
       "      <td>4</td>\n",
       "      <td>Ashdod</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14538</th>\n",
       "      <td>Name Detective</td>\n",
       "      <td>3</td>\n",
       "      <td>Adoni</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14539</th>\n",
       "      <td>Movie Shakespeare</td>\n",
       "      <td>4</td>\n",
       "      <td>Yingkou</td>\n",
       "      <td>6.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14540 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  month                    city  revenue\n",
       "0           French Holiday      2                 Bayugan     4.99\n",
       "1           Jekyll Frogmen      4      Varanasi (Benares)     5.99\n",
       "2        Intentions Empire      4              Pjatigorsk     4.99\n",
       "3      League Hellfighters      3  Coacalco de Berriozbal     4.99\n",
       "4           Grit Clockwork      3                   Bchar     3.99\n",
       "...                    ...    ...                     ...      ...\n",
       "14535         Beneath Rush      3                  Namibe     0.99\n",
       "14536       Liaisons Sweet      3                 Tonghae     7.99\n",
       "14537      Sun Confessions      4                  Ashdod     3.99\n",
       "14538       Name Detective      3                   Adoni     4.99\n",
       "14539    Movie Shakespeare      4                 Yingkou     6.99\n",
       "\n",
       "[14540 rows x 4 columns]"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(finalQuery,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "effdb66f",
   "metadata": {},
   "outputs": [],
   "source": [
    "orderQuery = \"\"\"SELECT dm.title, dd.month, dc.city, SUM(sales_amount) as revenue\n",
    "                FROM factsales as fs\n",
    "                JOIN dimmovie as dm ON fs.movie_key = dm.movie_key\n",
    "                JOIN dimdate as dd ON fs.date_key = dd.date_key\n",
    "                JOIN dimcustomer as dc ON fs.customer_key = dc.customer_key\n",
    "                GROUP BY (dm.title, dd.month, dc.city)\n",
    "                ORDER BY revenue DESC\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "f2c0d60a",
   "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>title</th>\n",
       "      <th>month</th>\n",
       "      <th>city</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Caribbean Liberty</td>\n",
       "      <td>4</td>\n",
       "      <td>Ibirit</td>\n",
       "      <td>16.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eagles Panky</td>\n",
       "      <td>3</td>\n",
       "      <td>Datong</td>\n",
       "      <td>16.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lust Lock</td>\n",
       "      <td>4</td>\n",
       "      <td>Coatzacoalcos</td>\n",
       "      <td>15.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Innocent Usual</td>\n",
       "      <td>2</td>\n",
       "      <td>Valparai</td>\n",
       "      <td>13.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Breakfast Goldfinger</td>\n",
       "      <td>4</td>\n",
       "      <td>Ife</td>\n",
       "      <td>13.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14535</th>\n",
       "      <td>Minority Kiss</td>\n",
       "      <td>5</td>\n",
       "      <td>Battambang</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14536</th>\n",
       "      <td>State Wasteland</td>\n",
       "      <td>5</td>\n",
       "      <td>Erlangen</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14537</th>\n",
       "      <td>Trouble Date</td>\n",
       "      <td>5</td>\n",
       "      <td>Changhwa</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14538</th>\n",
       "      <td>Lawless Vision</td>\n",
       "      <td>5</td>\n",
       "      <td>Nagareyama</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14539</th>\n",
       "      <td>Titanic Boondock</td>\n",
       "      <td>5</td>\n",
       "      <td>Santo Andr</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14540 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  month           city  revenue\n",
       "0         Caribbean Liberty      4         Ibirit    16.98\n",
       "1              Eagles Panky      3         Datong    16.98\n",
       "2                 Lust Lock      4  Coatzacoalcos    15.98\n",
       "3            Innocent Usual      2       Valparai    13.98\n",
       "4      Breakfast Goldfinger      4            Ife    13.98\n",
       "...                     ...    ...            ...      ...\n",
       "14535         Minority Kiss      5     Battambang     0.00\n",
       "14536       State Wasteland      5       Erlangen     0.00\n",
       "14537          Trouble Date      5       Changhwa     0.00\n",
       "14538        Lawless Vision      5     Nagareyama     0.00\n",
       "14539      Titanic Boondock      5     Santo Andr     0.00\n",
       "\n",
       "[14540 rows x 4 columns]"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(orderQuery,credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df464faa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}