de-datadefinition-training.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9129cdcb",
   "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": "10b493f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clustertab.config']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('clustertab.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a471cc22",
   "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": "code",
   "execution_count": 4,
   "id": "4216af6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'retail_db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "623308f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "credentials = \"postgresql://{}:{}@{}:{}/{}\".format(user,passwd,host,port,db)\n",
    "\n",
    "#using psycopg2 to test connection since there are no tables\n",
    "import psycopg2\n",
    "try:\n",
    "    conn = psycopg2.connect(host=host,dbname=db,user=user,password=passwd,port=port)\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    \n",
    "conn.set_session(autocommit=True)\n",
    "\n",
    "try:\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "except:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6846fc00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/retail_db'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "77f47076",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Helper functions to work with the database\n",
    "def schemaGen(dataframe, schemaName):\n",
    "    localSchema = pd.io.sql.get_schema(dataframe,schemaName)\n",
    "    localSchema = localSchema.replace('TEXT','VARCHAR(255)').replace('INTEGER','NUMERIC').replace('\\n','').replace('\"',\"\")\n",
    "    return \"\".join(localSchema)\n",
    "\n",
    "#Using pandas read_sql for getting schema\n",
    "def getSchema(tableName, credentials):\n",
    "    schema = pd.read_sql(\"\"\"SELECT table_catalog, table_name, \n",
    "                column_name, data_type, \n",
    "                ordinal_position, column_default, character_maximum_length,\n",
    "                is_nullable FROM information_schema.columns where table_name='{}'\"\"\".format(tableName),con=credentials)\n",
    "    return schema\n",
    "\n",
    "#Issue is in using pd.read_sql to write data to the database. so using psycopg2\n",
    "def queryTable(query):\n",
    "    try:\n",
    "        schema = cur.execute(query)\n",
    "        return \n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        \n",
    "#This doesn't return anything\n",
    "\n",
    "#Using the pd.read_sql for getting data from db\n",
    "def queryBase(query):\n",
    "    requiredTable = pd.read_sql(query,con=credentials)\n",
    "    return requiredTable\n",
    "\n",
    "#This returns the dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e886e87b",
   "metadata": {},
   "source": [
    "**Data Definition** is kept seperately from the **DQL (Data Query)** and **DML (Data Manipulation)** Languages, or clauses.\n",
    "\n",
    "DDL tasks are \n",
    "\n",
    "- Creating Tables : Objects inside the database\n",
    "\n",
    "- Creating Indexes for Performance increase\n",
    "\n",
    "- Creating Constraint (NOT NULL, CHECK, PRIMARY KEY, UNIQUE etc)\n",
    "\n",
    "Another important command is ALTER. Helps in Adding Columns, Dropping and Changing datatypes\n",
    "\n",
    "Less known operation is adding comments on Table and Column levels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5367a0e0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "ResourceClosedError",
     "evalue": "This result object does not return rows. It has been closed automatically.",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mResourceClosedError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [8], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mqueryBase\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[38;5;124;43mCREATE TABLE users (\u001b[39;49m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124;43m    user_id SERIAL PRIMARY KEY,\u001b[39;49m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124;43m    user_first_name VARCHAR(30) NOT NULL,\u001b[39;49m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;124;43m    user_last_name VARCHAR(30) NOT NULL,\u001b[39;49m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;124;43m    user_email_id VARCHAR(50) NOT NULL,\u001b[39;49m\n\u001b[1;32m      6\u001b[0m \u001b[38;5;124;43m    user_email_validated BOOLEAN DEFAULT FALSE,\u001b[39;49m\n\u001b[1;32m      7\u001b[0m \u001b[38;5;124;43m    user_password VARCHAR(200),\u001b[39;49m\n\u001b[1;32m      8\u001b[0m \u001b[38;5;124;43m    user_role VARCHAR(1) NOT NULL DEFAULT \u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mU\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m, --U and A\u001b[39;49m\n\u001b[1;32m      9\u001b[0m \u001b[38;5;124;43m    is_active BOOLEAN DEFAULT FALSE,\u001b[39;49m\n\u001b[1;32m     10\u001b[0m \u001b[38;5;124;43m    created_dt DATE DEFAULT CURRENT_DATE,\u001b[39;49m\n\u001b[1;32m     11\u001b[0m \u001b[38;5;124;43m    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\u001b[39;49m\n\u001b[1;32m     12\u001b[0m \u001b[38;5;124;43m);\u001b[39;49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn [7], line 24\u001b[0m, in \u001b[0;36mqueryBase\u001b[0;34m(query)\u001b[0m\n\u001b[1;32m     23\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mqueryBase\u001b[39m(query):\n\u001b[0;32m---> 24\u001b[0m     requiredTable \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcredentials\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     25\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m requiredTable\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:590\u001b[0m, in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    581\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m pandas_sql\u001b[38;5;241m.\u001b[39mread_table(\n\u001b[1;32m    582\u001b[0m         sql,\n\u001b[1;32m    583\u001b[0m         index_col\u001b[38;5;241m=\u001b[39mindex_col,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    587\u001b[0m         chunksize\u001b[38;5;241m=\u001b[39mchunksize,\n\u001b[1;32m    588\u001b[0m     )\n\u001b[1;32m    589\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m--> 590\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    591\u001b[0m \u001b[43m        \u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    592\u001b[0m \u001b[43m        \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    593\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparams\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    594\u001b[0m \u001b[43m        \u001b[49m\u001b[43mcoerce_float\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcoerce_float\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    595\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    596\u001b[0m \u001b[43m        \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    597\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:1574\u001b[0m, in \u001b[0;36mSQLDatabase.read_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1564\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_query_iterator(\n\u001b[1;32m   1565\u001b[0m         result,\n\u001b[1;32m   1566\u001b[0m         chunksize,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1571\u001b[0m         dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[1;32m   1572\u001b[0m     )\n\u001b[1;32m   1573\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m-> 1574\u001b[0m     data \u001b[38;5;241m=\u001b[39m \u001b[43mresult\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfetchall\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1575\u001b[0m     frame \u001b[38;5;241m=\u001b[39m _wrap_result(\n\u001b[1;32m   1576\u001b[0m         data,\n\u001b[1;32m   1577\u001b[0m         columns,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1581\u001b[0m         dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[1;32m   1582\u001b[0m     )\n\u001b[1;32m   1583\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m frame\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:1058\u001b[0m, in \u001b[0;36mResult.fetchall\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1055\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mfetchall\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m   1056\u001b[0m     \u001b[38;5;124;03m\"\"\"A synonym for the :meth:`_engine.Result.all` method.\"\"\"\u001b[39;00m\n\u001b[0;32m-> 1058\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_allrows\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:399\u001b[0m, in \u001b[0;36mResultInternal._allrows\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    395\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_allrows\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m    397\u001b[0m     post_creational_filter \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_post_creational_filter\n\u001b[0;32m--> 399\u001b[0m     make_row \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_row_getter\u001b[49m\n\u001b[1;32m    401\u001b[0m     rows \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_fetchall_impl()\n\u001b[1;32m    402\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m make_row:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:1184\u001b[0m, in \u001b[0;36mHasMemoized.memoized_attribute.__get__\u001b[0;34m(self, obj, cls)\u001b[0m\n\u001b[1;32m   1182\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m obj \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m   1183\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\n\u001b[0;32m-> 1184\u001b[0m obj\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__dict__\u001b[39m[\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m] \u001b[38;5;241m=\u001b[39m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfget\u001b[49m\u001b[43m(\u001b[49m\u001b[43mobj\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1185\u001b[0m obj\u001b[38;5;241m.\u001b[39m_memoized_keys \u001b[38;5;241m|\u001b[39m\u001b[38;5;241m=\u001b[39m {\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m\u001b[38;5;18m__name__\u001b[39m}\n\u001b[1;32m   1186\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/result.py:320\u001b[0m, in \u001b[0;36mResultInternal._row_getter\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    317\u001b[0m key_style \u001b[38;5;241m=\u001b[39m real_result\u001b[38;5;241m.\u001b[39m_process_row\u001b[38;5;241m.\u001b[39m_default_key_style\n\u001b[1;32m    318\u001b[0m metadata \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_metadata\n\u001b[0;32m--> 320\u001b[0m keymap \u001b[38;5;241m=\u001b[39m \u001b[43mmetadata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_keymap\u001b[49m\n\u001b[1;32m    321\u001b[0m processors \u001b[38;5;241m=\u001b[39m metadata\u001b[38;5;241m.\u001b[39m_processors\n\u001b[1;32m    322\u001b[0m tf \u001b[38;5;241m=\u001b[39m metadata\u001b[38;5;241m.\u001b[39m_tuplefilter\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1221\u001b[0m, in \u001b[0;36m_NoResultMetaData._keymap\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1219\u001b[0m \u001b[38;5;129m@property\u001b[39m\n\u001b[1;32m   1220\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_keymap\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m-> 1221\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_we_dont_return_rows\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/cursor.py:1202\u001b[0m, in \u001b[0;36m_NoResultMetaData._we_dont_return_rows\u001b[0;34m(self, err)\u001b[0m\n\u001b[1;32m   1201\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_we_dont_return_rows\u001b[39m(\u001b[38;5;28mself\u001b[39m, err\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m-> 1202\u001b[0m     \u001b[43mutil\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1203\u001b[0m \u001b[43m        \u001b[49m\u001b[43mexc\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mResourceClosedError\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1204\u001b[0m \u001b[43m            \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mThis result object does not return rows. \u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\n\u001b[1;32m   1205\u001b[0m \u001b[43m            \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mIt has been closed automatically.\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\n\u001b[1;32m   1206\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1207\u001b[0m \u001b[43m        \u001b[49m\u001b[43mreplace_context\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merr\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1208\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208\u001b[0m, in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m     exception\u001b[38;5;241m.\u001b[39m__cause__ \u001b[38;5;241m=\u001b[39m replace_context\n\u001b[1;32m    207\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 208\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exception\n\u001b[1;32m    209\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m    210\u001b[0m     \u001b[38;5;66;03m# credit to\u001b[39;00m\n\u001b[1;32m    211\u001b[0m     \u001b[38;5;66;03m# https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/\u001b[39;00m\n\u001b[1;32m    212\u001b[0m     \u001b[38;5;66;03m# as the __traceback__ object creates a cycle\u001b[39;00m\n\u001b[1;32m    213\u001b[0m     \u001b[38;5;28;01mdel\u001b[39;00m exception, replace_context, from_, with_traceback\n",
      "\u001b[0;31mResourceClosedError\u001b[0m: This result object does not return rows. It has been closed automatically."
     ]
    }
   ],
   "source": [
    "queryBase(\"\"\"CREATE TABLE users ( \n",
    "    user_id SERIAL PRIMARY KEY,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN DEFAULT FALSE,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
    "    is_active BOOLEAN DEFAULT FALSE,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE,\n",
    "    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "33f598ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping the above table to create the same table differently\n",
    "\n",
    "queryTable(\"\"\"DROP TABLE users\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b2e4fa33",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users ( \n",
    "    user_id INT,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1),\n",
    "    is_active BOOLEAN,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE\n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "93b0fe16",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"COMMENT ON TABLE users IS 'The table stores the user data'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7ebc0db8",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character varying</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name          data_type  \\\n",
       "0     retail_db      users               user_id            integer   \n",
       "1     retail_db      users       user_first_name  character varying   \n",
       "2     retail_db      users        user_last_name  character varying   \n",
       "3     retail_db      users         user_email_id  character varying   \n",
       "4     retail_db      users  user_email_validated            boolean   \n",
       "5     retail_db      users         user_password  character varying   \n",
       "6     retail_db      users             user_role  character varying   \n",
       "7     retail_db      users             is_active            boolean   \n",
       "8     retail_db      users            created_dt               date   \n",
       "\n",
       "   ordinal_position column_default  character_maximum_length is_nullable  \n",
       "0                 1           None                       NaN         YES  \n",
       "1                 2           None                      30.0          NO  \n",
       "2                 3           None                      30.0          NO  \n",
       "3                 4           None                      50.0          NO  \n",
       "4                 5           None                       NaN         YES  \n",
       "5                 6           None                     200.0         YES  \n",
       "6                 7           None                       1.0         YES  \n",
       "7                 8           None                       NaN         YES  \n",
       "8                 9   CURRENT_DATE                       NaN         YES  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, \n",
    "                column_name, data_type, \n",
    "                ordinal_position, column_default, charact er_maximum_length,\n",
    "                is_nullable FROM information_schema.columns WHERE table_name = 'users'\n",
    "            ORDER BY ordinal_position\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "596168ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DROP SEQUENCE IF EXISTS users_user_id_seq\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "388bc4be",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE SEQUENCE users_user_id_seq\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "85336646",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character varying</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name          data_type  \\\n",
       "0     retail_db      users            created_dt               date   \n",
       "1     retail_db      users  user_email_validated            boolean   \n",
       "2     retail_db      users             is_active            boolean   \n",
       "3     retail_db      users               user_id            integer   \n",
       "4     retail_db      users             user_role  character varying   \n",
       "5     retail_db      users         user_password  character varying   \n",
       "6     retail_db      users       user_first_name  character varying   \n",
       "7     retail_db      users        user_last_name  character varying   \n",
       "8     retail_db      users         user_email_id  character varying   \n",
       "\n",
       "   ordinal_position column_default  character_maximum_length is_nullable  \n",
       "0                 9   CURRENT_DATE                       NaN         YES  \n",
       "1                 5           None                       NaN         YES  \n",
       "2                 8           None                       NaN         YES  \n",
       "3                 1           None                       NaN         YES  \n",
       "4                 7           None                       1.0         YES  \n",
       "5                 6           None                     200.0         YES  \n",
       "6                 2           None                      30.0          NO  \n",
       "7                 3           None                      30.0          NO  \n",
       "8                 4           None                      50.0          NO  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema(\"users\",credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0535efcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "5100d60a",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('users_user_id_seq'::regclass)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character varying</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name          data_type  \\\n",
       "0     retail_db      users            created_dt               date   \n",
       "1     retail_db      users  user_email_validated            boolean   \n",
       "2     retail_db      users             is_active            boolean   \n",
       "3     retail_db      users               user_id            integer   \n",
       "4     retail_db      users             user_role  character varying   \n",
       "5     retail_db      users         user_password  character varying   \n",
       "6     retail_db      users       user_first_name  character varying   \n",
       "7     retail_db      users        user_last_name  character varying   \n",
       "8     retail_db      users         user_email_id  character varying   \n",
       "\n",
       "   ordinal_position                          column_default  \\\n",
       "0                 9                            CURRENT_DATE   \n",
       "1                 5                                    None   \n",
       "2                 8                                    None   \n",
       "3                 1  nextval('users_user_id_seq'::regclass)   \n",
       "4                 7                                    None   \n",
       "5                 6                                    None   \n",
       "6                 2                                    None   \n",
       "7                 3                                    None   \n",
       "8                 4                                    None   \n",
       "\n",
       "   character_maximum_length is_nullable  \n",
       "0                       NaN         YES  \n",
       "1                       NaN         YES  \n",
       "2                       NaN         YES  \n",
       "3                       NaN         YES  \n",
       "4                       1.0         YES  \n",
       "5                     200.0         YES  \n",
       "6                      30.0          NO  \n",
       "7                      30.0          NO  \n",
       "8                      50.0          NO  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema(\"users\",credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "465ebb90",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ALTER COLUMN user_email_validated SET DEFAULT FALSE\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "a65432ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ALTER COLUMN is_active SET DEFAULT FALSE\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1fd68944",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('users_user_id_seq'::regclass)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character varying</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name          data_type  \\\n",
       "0     retail_db      users            created_dt               date   \n",
       "1     retail_db      users  user_email_validated            boolean   \n",
       "2     retail_db      users             is_active            boolean   \n",
       "3     retail_db      users               user_id            integer   \n",
       "4     retail_db      users             user_role  character varying   \n",
       "5     retail_db      users         user_password  character varying   \n",
       "6     retail_db      users       user_first_name  character varying   \n",
       "7     retail_db      users        user_last_name  character varying   \n",
       "8     retail_db      users         user_email_id  character varying   \n",
       "\n",
       "   ordinal_position                          column_default  \\\n",
       "0                 9                            CURRENT_DATE   \n",
       "1                 5                                   false   \n",
       "2                 8                                   false   \n",
       "3                 1  nextval('users_user_id_seq'::regclass)   \n",
       "4                 7                                    None   \n",
       "5                 6                                    None   \n",
       "6                 2                                    None   \n",
       "7                 3                                    None   \n",
       "8                 4                                    None   \n",
       "\n",
       "   character_maximum_length is_nullable  \n",
       "0                       NaN         YES  \n",
       "1                       NaN         YES  \n",
       "2                       NaN         YES  \n",
       "3                       NaN         YES  \n",
       "4                       1.0         YES  \n",
       "5                     200.0         YES  \n",
       "6                      30.0          NO  \n",
       "7                      30.0          NO  \n",
       "8                      50.0          NO  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema(\"users\",credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "dd14f5ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ALTER COLUMN user_role SET DATA TYPE CHAR(1)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "dca7c22f",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ALTER COLUMN user_role SET DEFAULT 'U'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "5f1ea35a",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('users_user_id_seq'::regclass)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character</td>\n",
       "      <td>7</td>\n",
       "      <td>'U'::bpchar</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name          data_type  \\\n",
       "0     retail_db      users            created_dt               date   \n",
       "1     retail_db      users  user_email_validated            boolean   \n",
       "2     retail_db      users             is_active            boolean   \n",
       "3     retail_db      users               user_id            integer   \n",
       "4     retail_db      users             user_role          character   \n",
       "5     retail_db      users         user_password  character varying   \n",
       "6     retail_db      users       user_first_name  character varying   \n",
       "7     retail_db      users        user_last_name  character varying   \n",
       "8     retail_db      users         user_email_id  character varying   \n",
       "\n",
       "   ordinal_position                          column_default  \\\n",
       "0                 9                            CURRENT_DATE   \n",
       "1                 5                                   false   \n",
       "2                 8                                   false   \n",
       "3                 1  nextval('users_user_id_seq'::regclass)   \n",
       "4                 7                             'U'::bpchar   \n",
       "5                 6                                    None   \n",
       "6                 2                                    None   \n",
       "7                 3                                    None   \n",
       "8                 4                                    None   \n",
       "\n",
       "   character_maximum_length is_nullable  \n",
       "0                       NaN         YES  \n",
       "1                       NaN         YES  \n",
       "2                       NaN         YES  \n",
       "3                       NaN         YES  \n",
       "4                       1.0         YES  \n",
       "5                     200.0         YES  \n",
       "6                      30.0          NO  \n",
       "7                      30.0          NO  \n",
       "8                      50.0          NO  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema(\"users\",credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d819b6f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5fe52d06",
   "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_name</th>\n",
       "      <th>column_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>ordinal_position</th>\n",
       "      <th>column_default</th>\n",
       "      <th>character_maximum_length</th>\n",
       "      <th>is_nullable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_id</td>\n",
       "      <td>integer</td>\n",
       "      <td>1</td>\n",
       "      <td>nextval('users_user_id_seq'::regclass)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_validated</td>\n",
       "      <td>boolean</td>\n",
       "      <td>5</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>is_active</td>\n",
       "      <td>boolean</td>\n",
       "      <td>8</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>created_dt</td>\n",
       "      <td>date</td>\n",
       "      <td>9</td>\n",
       "      <td>CURRENT_DATE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>last_updated_ts</td>\n",
       "      <td>timestamp without time zone</td>\n",
       "      <td>10</td>\n",
       "      <td>CURRENT_TIMESTAMP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_password</td>\n",
       "      <td>character varying</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>200.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_first_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_last_name</td>\n",
       "      <td>character varying</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_email_id</td>\n",
       "      <td>character varying</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>user_role</td>\n",
       "      <td>character</td>\n",
       "      <td>7</td>\n",
       "      <td>'U'::bpchar</td>\n",
       "      <td>1.0</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name           column_name                    data_type  \\\n",
       "0     retail_db      users               user_id                      integer   \n",
       "1     retail_db      users  user_email_validated                      boolean   \n",
       "2     retail_db      users             is_active                      boolean   \n",
       "3     retail_db      users            created_dt                         date   \n",
       "4     retail_db      users       last_updated_ts  timestamp without time zone   \n",
       "5     retail_db      users         user_password            character varying   \n",
       "6     retail_db      users       user_first_name            character varying   \n",
       "7     retail_db      users        user_last_name            character varying   \n",
       "8     retail_db      users         user_email_id            character varying   \n",
       "9     retail_db      users             user_role                    character   \n",
       "\n",
       "   ordinal_position                          column_default  \\\n",
       "0                 1  nextval('users_user_id_seq'::regclass)   \n",
       "1                 5                                   false   \n",
       "2                 8                                   false   \n",
       "3                 9                            CURRENT_DATE   \n",
       "4                10                       CURRENT_TIMESTAMP   \n",
       "5                 6                                    None   \n",
       "6                 2                                    None   \n",
       "7                 3                                    None   \n",
       "8                 4                                    None   \n",
       "9                 7                             'U'::bpchar   \n",
       "\n",
       "   character_maximum_length is_nullable  \n",
       "0                       NaN         YES  \n",
       "1                       NaN         YES  \n",
       "2                       NaN         YES  \n",
       "3                       NaN         YES  \n",
       "4                       NaN         YES  \n",
       "5                     200.0         YES  \n",
       "6                      30.0          NO  \n",
       "7                      30.0          NO  \n",
       "8                      50.0          NO  \n",
       "9                       1.0         YES  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getSchema('users',credentials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c49403de",
   "metadata": {},
   "source": [
    "There are multiple constraints that can be enforced on the columns like PRIMARY KEY, UNIQUE, NOT NULL. The Foreign Key and Check Constraints have to understood throughly. One thing I came to know is that there can multiple column with UNIQUE constraints, and **Foreign Key can be defined agains the column that is UNIQUE**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "32c4f660",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16444_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16444_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16444_4_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_type        constraint_name\n",
       "0     retail_db      users           CHECK  2200_16444_2_not_null\n",
       "1     retail_db      users           CHECK  2200_16444_3_not_null\n",
       "2     retail_db      users           CHECK  2200_16444_4_not_null"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "                FROM information_schema.table_constraints\n",
    "                WHERE table_name = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e70c8464",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping the above table to create the same table differently\n",
    "\n",
    "queryTable(\"\"\"DROP TABLE IF EXISTS users \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "eb2cff11",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users ( \n",
    "    user_id INT,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1),\n",
    "    is_active BOOLEAN,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE\n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "38076e94",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_4_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_type        constraint_name\n",
       "0     retail_db      users           CHECK  2200_16457_2_not_null\n",
       "1     retail_db      users           CHECK  2200_16457_3_not_null\n",
       "2     retail_db      users           CHECK  2200_16457_4_not_null"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d5dbf55c",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ADD PRIMARY KEY (user_id) \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "cb497101",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>users_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_1_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_4_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_type        constraint_name\n",
       "0     retail_db      users     PRIMARY KEY             users_pkey\n",
       "1     retail_db      users           CHECK  2200_16457_1_not_null\n",
       "2     retail_db      users           CHECK  2200_16457_2_not_null\n",
       "3     retail_db      users           CHECK  2200_16457_3_not_null\n",
       "4     retail_db      users           CHECK  2200_16457_4_not_null"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0cc998d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "syntax error at or near \"PRIMARY\"\n",
      "LINE 1: ALTER TABLE users DROP CONSTRAINT PRIMARY KEY (user_id) \n",
      "                                          ^\n",
      "\n"
     ]
    }
   ],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users DROP CONSTRAINT PRIMARY KEY (user_id) \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8febee7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ADD UNIQUE (user_email_id) \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "a0f9a4b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users\n",
    "            ALTER COLUMN user_email_validated SET NOT NULL,\n",
    "            ALTER COLUMN user_role SET NOT NULL\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "2828807f",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>users_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>users_user_email_id_key</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_1_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_4_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_5_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_7_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_type          constraint_name\n",
       "0     retail_db      users     PRIMARY KEY               users_pkey\n",
       "1     retail_db      users          UNIQUE  users_user_email_id_key\n",
       "2     retail_db      users           CHECK    2200_16457_1_not_null\n",
       "3     retail_db      users           CHECK    2200_16457_2_not_null\n",
       "4     retail_db      users           CHECK    2200_16457_3_not_null\n",
       "5     retail_db      users           CHECK    2200_16457_4_not_null\n",
       "6     retail_db      users           CHECK    2200_16457_5_not_null\n",
       "7     retail_db      users           CHECK    2200_16457_7_not_null"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "cef18fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#practice table\n",
    "queryTable(\"\"\"CREATE TABLE user_logins(\n",
    "                user_login_id SERIAL PRIMARY KEY,\n",
    "                user_id INT,\n",
    "                user_login_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
    "                user_ip_addr VARCHAR(20))\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "53c9feed",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>user_logins</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>user_logins_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>user_logins</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16466_1_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog   table_name constraint_type        constraint_name\n",
       "0     retail_db  user_logins     PRIMARY KEY       user_logins_pkey\n",
       "1     retail_db  user_logins           CHECK  2200_16466_1_not_null"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'user_logins'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "24124def",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE user_logins ADD FOREIGN KEY (user_id) REFERENCES users(user_id)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "bf37d677",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>user_logins</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>user_logins_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>user_logins</td>\n",
       "      <td>FOREIGN KEY</td>\n",
       "      <td>user_logins_user_id_fkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>user_logins</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16466_1_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog   table_name constraint_type           constraint_name\n",
       "0     retail_db  user_logins     PRIMARY KEY          user_logins_pkey\n",
       "1     retail_db  user_logins     FOREIGN KEY  user_logins_user_id_fkey\n",
       "2     retail_db  user_logins           CHECK     2200_16466_1_not_null"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'user_logins'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "263fa9cf",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>users_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>UNIQUE</td>\n",
       "      <td>users_user_email_id_key</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_1_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_4_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_5_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16457_7_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_type          constraint_name\n",
       "0     retail_db      users     PRIMARY KEY               users_pkey\n",
       "1     retail_db      users          UNIQUE  users_user_email_id_key\n",
       "2     retail_db      users           CHECK    2200_16457_1_not_null\n",
       "3     retail_db      users           CHECK    2200_16457_2_not_null\n",
       "4     retail_db      users           CHECK    2200_16457_3_not_null\n",
       "5     retail_db      users           CHECK    2200_16457_4_not_null\n",
       "6     retail_db      users           CHECK    2200_16457_5_not_null\n",
       "7     retail_db      users           CHECK    2200_16457_7_not_null"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_ schema.table_constraints\n",
    "            WHERE table_name = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "a6b40ea0",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE INDEX order_items_oid_idx ON order_items(order_item_order_id)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "3b28f385",
   "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_name</th>\n",
       "      <th>constraint_type</th>\n",
       "      <th>constraint_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>PRIMARY KEY</td>\n",
       "      <td>order_items_pkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_1_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_2_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_3_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_4_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_5_not_null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>order_items</td>\n",
       "      <td>CHECK</td>\n",
       "      <td>2200_16416_6_not_null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog   table_name constraint_type        constraint_name\n",
       "0     retail_db  order_items     PRIMARY KEY       order_items_pkey\n",
       "1     retail_db  order_items           CHECK  2200_16416_1_not_null\n",
       "2     retail_db  order_items           CHECK  2200_16416_2_not_null\n",
       "3     retail_db  order_items           CHECK  2200_16416_3_not_null\n",
       "4     retail_db  order_items           CHECK  2200_16416_4_not_null\n",
       "5     retail_db  order_items           CHECK  2200_16416_5_not_null\n",
       "6     retail_db  order_items           CHECK  2200_16416_6_not_null"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT table_catalog, table_name, constraint_type, constraint_name\n",
    "            FROM information_schema.table_constraints\n",
    "            WHERE table_name = 'order_items'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "96558821",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>indexname</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>indexdef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_pkey</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_pkey ON public.users...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_user_email_id_key</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_user_email_id_key ON...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schemaname tablename                indexname tablespace  \\\n",
       "0     public     users               users_pkey       None   \n",
       "1     public     users  users_user_email_id_key       None   \n",
       "\n",
       "                                            indexdef  \n",
       "0  CREATE UNIQUE INDEX users_pkey ON public.users...  \n",
       "1  CREATE UNIQUE INDEX users_user_email_id_key ON...  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "            FROM pg_catalog.pg_indexes\n",
    "            WHERE schemaname = 'public'\n",
    "            AND tablename = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "84564854",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DROP TABLE IF EXISTS users cascade\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "472f8214",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"CREATE TABLE users ( \n",
    "    user_id INT,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1),\n",
    "    is_active BOOLEAN,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE\n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "93d34d88",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>indexname</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>indexdef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [schemaname, tablename, indexname, tablespace, indexdef]\n",
       "Index: []"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "                FROM pg_catalog.pg_indexes\n",
    "                WHERE schemaname = 'public'\n",
    "                AND tablename = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "d9360051",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users\n",
    "            ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'),\n",
    "            ADD PRIMARY KEY (user_id)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "de06ff91",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>indexname</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>indexdef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_pkey</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_pkey ON public.users...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schemaname tablename   indexname tablespace  \\\n",
       "0     public     users  users_pkey       None   \n",
       "\n",
       "                                            indexdef  \n",
       "0  CREATE UNIQUE INDEX users_pkey ON public.users...  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "                FROM pg_catalog.pg_indexes\n",
    "                WHERE schemaname = 'public'\n",
    "                AND tablename = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "c628a34d",
   "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_name</th>\n",
       "      <th>constraint_name</th>\n",
       "      <th>indexname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>users</td>\n",
       "      <td>users_pkey</td>\n",
       "      <td>users_pkey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  table_catalog table_name constraint_name   indexname\n",
       "0     retail_db      users      users_pkey  users_pkey"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT tc.table_catalog,\n",
    "    tc.table_name, \n",
    "    tc.constraint_name,\n",
    "    pi.indexname\n",
    "FROM information_schema.table_constraints tc JOIN pg_catalog.pg_indexes pi\n",
    "    ON tc.constraint_name = pi.indexname\n",
    "WHERE tc.table_schema = 'public'\n",
    "    AND tc.table_name = 'users'\n",
    "    AND tc.constraint_type = 'PRIMARY KEY'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "0d309672",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"ALTER TABLE users ADD UNIQUE (user_email_id) \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "183ce11c",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>indexname</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>indexdef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_pkey</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_pkey ON public.users...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_user_email_id_key</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_user_email_id_key ON...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schemaname tablename                indexname tablespace  \\\n",
       "0     public     users               users_pkey       None   \n",
       "1     public     users  users_user_email_id_key       None   \n",
       "\n",
       "                                            indexdef  \n",
       "0  CREATE UNIQUE INDEX users_pkey ON public.users...  \n",
       "1  CREATE UNIQUE INDEX users_user_email_id_key ON...  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "                FROM pg_catalog.pg_indexes \n",
    "                WHERE schemaname = 'public'\n",
    "                AND tablename = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "602f36dc",
   "metadata": {},
   "source": [
    "Surrogate Keys are columns that are created by the data engineer when the given table doesn't have any relevant keys "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "1a4aa86b",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"DROP SEQUENCE IF EXISTS test_seq\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "c0fe5387",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "relation \"test_seq\" already exists\n",
      "\n"
     ]
    }
   ],
   "source": [
    "queryTable(\"\"\"CREATE SEQUENCE test_seq\n",
    "                START WITH 101\n",
    "                MINVALUE 101\n",
    "                MAXVALUE 1000\n",
    "                INCREMENT BY 100\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "776376aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"SELECT currval('test_seq')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "360aaf22",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"SELECT nextval('test_seq')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "074288f2",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(psycopg2.errors.ObjectNotInPrerequisiteState) currval of sequence \"test_seq\" is not yet defined in this session\n\n[SQL: SELECT currval('test_seq')]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mObjectNotInPrerequisiteState\u001b[0m              Traceback (most recent call last)",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1900\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1899\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[0;32m-> 1900\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1901\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1902\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1904\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    735\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m--> 736\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mObjectNotInPrerequisiteState\u001b[0m: currval of sequence \"test_seq\" is not yet defined in this session\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [84], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mqueryBase\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[38;5;124;43mSELECT currval(\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mtest_seq\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m)\u001b[39;49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn [24], line 27\u001b[0m, in \u001b[0;36mqueryBase\u001b[0;34m(query)\u001b[0m\n\u001b[1;32m     26\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mqueryBase\u001b[39m(query):\n\u001b[0;32m---> 27\u001b[0m     requiredTable \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcredentials\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     28\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m requiredTable\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:590\u001b[0m, in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    581\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m pandas_sql\u001b[38;5;241m.\u001b[39mread_table(\n\u001b[1;32m    582\u001b[0m         sql,\n\u001b[1;32m    583\u001b[0m         index_col\u001b[38;5;241m=\u001b[39mindex_col,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    587\u001b[0m         chunksize\u001b[38;5;241m=\u001b[39mchunksize,\n\u001b[1;32m    588\u001b[0m     )\n\u001b[1;32m    589\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m--> 590\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    591\u001b[0m \u001b[43m        \u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    592\u001b[0m \u001b[43m        \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    593\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparams\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    594\u001b[0m \u001b[43m        \u001b[49m\u001b[43mcoerce_float\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcoerce_float\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    595\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    596\u001b[0m \u001b[43m        \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    597\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:1560\u001b[0m, in \u001b[0;36mSQLDatabase.read_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1512\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   1513\u001b[0m \u001b[38;5;124;03mRead SQL query into a DataFrame.\u001b[39;00m\n\u001b[1;32m   1514\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1556\u001b[0m \n\u001b[1;32m   1557\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   1558\u001b[0m args \u001b[38;5;241m=\u001b[39m _convert_params(sql, params)\n\u001b[0;32m-> 1560\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1561\u001b[0m columns \u001b[38;5;241m=\u001b[39m result\u001b[38;5;241m.\u001b[39mkeys()\n\u001b[1;32m   1563\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m chunksize \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:1405\u001b[0m, in \u001b[0;36mSQLDatabase.execute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1403\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mexecute\u001b[39m(\u001b[38;5;28mself\u001b[39m, \u001b[38;5;241m*\u001b[39margs, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs):\n\u001b[1;32m   1404\u001b[0m     \u001b[38;5;124;03m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[39;00m\n\u001b[0;32m-> 1405\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnectable\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecution_options\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m<string>:2\u001b[0m, in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py:402\u001b[0m, in \u001b[0;36m_decorate_with_warning.<locals>.warned\u001b[0;34m(fn, *args, **kwargs)\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m skip_warning:\n\u001b[1;32m    401\u001b[0m     _warn_with_version(message, version, wtype, stacklevel\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m3\u001b[39m)\n\u001b[0;32m--> 402\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfn\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3257\u001b[0m, in \u001b[0;36mEngine.execute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   3239\u001b[0m \u001b[38;5;124;03m\"\"\"Executes the given construct and returns a\u001b[39;00m\n\u001b[1;32m   3240\u001b[0m \u001b[38;5;124;03m:class:`_engine.CursorResult`.\u001b[39;00m\n\u001b[1;32m   3241\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   3254\u001b[0m \n\u001b[1;32m   3255\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m   3256\u001b[0m connection \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mconnect(close_with_result\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m)\n\u001b[0;32m-> 3257\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mmultiparams\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1365\u001b[0m, in \u001b[0;36mConnection.execute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1356\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(statement, util\u001b[38;5;241m.\u001b[39mstring_types):\n\u001b[1;32m   1357\u001b[0m     util\u001b[38;5;241m.\u001b[39mwarn_deprecated_20(\n\u001b[1;32m   1358\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mPassing a string to Connection.execute() is \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   1359\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdeprecated and will be removed in version 2.0.  Use the \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1362\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdriver-level SQL string.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   1363\u001b[0m     )\n\u001b[0;32m-> 1365\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_exec_driver_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1366\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1367\u001b[0m \u001b[43m        \u001b[49m\u001b[43mmultiparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1368\u001b[0m \u001b[43m        \u001b[49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1369\u001b[0m \u001b[43m        \u001b[49m\u001b[43m_EMPTY_EXECUTION_OPTS\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1370\u001b[0m \u001b[43m        \u001b[49m\u001b[43mfuture\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m   1371\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1373\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m   1374\u001b[0m     meth \u001b[38;5;241m=\u001b[39m statement\u001b[38;5;241m.\u001b[39m_execute_on_connection\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1669\u001b[0m, in \u001b[0;36mConnection._exec_driver_sql\u001b[0;34m(self, statement, multiparams, params, execution_options, future)\u001b[0m\n\u001b[1;32m   1659\u001b[0m         (\n\u001b[1;32m   1660\u001b[0m             statement,\n\u001b[1;32m   1661\u001b[0m             distilled_params,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1665\u001b[0m             statement, distilled_parameters, execution_options\n\u001b[1;32m   1666\u001b[0m         )\n\u001b[1;32m   1668\u001b[0m dialect \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect\n\u001b[0;32m-> 1669\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_execute_context\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1670\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1671\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecution_ctx_cls\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_init_statement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1672\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1673\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1674\u001b[0m \u001b[43m    \u001b[49m\u001b[43mexecution_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1675\u001b[0m \u001b[43m    \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1676\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   1677\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1679\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m future:\n\u001b[1;32m   1680\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1943\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1940\u001b[0m             branched\u001b[38;5;241m.\u001b[39mclose()\n\u001b[1;32m   1942\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[0;32m-> 1943\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_dbapi_exception\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1944\u001b[0m \u001b[43m        \u001b[49m\u001b[43me\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1945\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1947\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2124\u001b[0m, in \u001b[0;36mConnection._handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   2122\u001b[0m     util\u001b[38;5;241m.\u001b[39mraise_(newraise, with_traceback\u001b[38;5;241m=\u001b[39mexc_info[\u001b[38;5;241m2\u001b[39m], from_\u001b[38;5;241m=\u001b[39me)\n\u001b[1;32m   2123\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m should_wrap:\n\u001b[0;32m-> 2124\u001b[0m     \u001b[43mutil\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   2125\u001b[0m \u001b[43m        \u001b[49m\u001b[43msqlalchemy_exception\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mwith_traceback\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mexc_info\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m2\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfrom_\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43me\u001b[49m\n\u001b[1;32m   2126\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2127\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m   2128\u001b[0m     util\u001b[38;5;241m.\u001b[39mraise_(exc_info[\u001b[38;5;241m1\u001b[39m], with_traceback\u001b[38;5;241m=\u001b[39mexc_info[\u001b[38;5;241m2\u001b[39m])\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/util/compat.py:208\u001b[0m, in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    205\u001b[0m     exception\u001b[38;5;241m.\u001b[39m__cause__ \u001b[38;5;241m=\u001b[39m replace_context\n\u001b[1;32m    207\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 208\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exception\n\u001b[1;32m    209\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m    210\u001b[0m     \u001b[38;5;66;03m# credit to\u001b[39;00m\n\u001b[1;32m    211\u001b[0m     \u001b[38;5;66;03m# https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/\u001b[39;00m\n\u001b[1;32m    212\u001b[0m     \u001b[38;5;66;03m# as the __traceback__ object creates a cycle\u001b[39;00m\n\u001b[1;32m    213\u001b[0m     \u001b[38;5;28;01mdel\u001b[39;00m exception, replace_context, from_, with_traceback\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1900\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[1;32m   1898\u001b[0m                 \u001b[38;5;28;01mbreak\u001b[39;00m\n\u001b[1;32m   1899\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[0;32m-> 1900\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   1901\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[1;32m   1902\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1904\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n\u001b[1;32m   1905\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdispatch\u001b[38;5;241m.\u001b[39mafter_cursor_execute(\n\u001b[1;32m   1906\u001b[0m         \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m   1907\u001b[0m         cursor,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1911\u001b[0m         context\u001b[38;5;241m.\u001b[39mexecutemany,\n\u001b[1;32m   1912\u001b[0m     )\n",
      "File \u001b[0;32m/usr/lib/python3.10/site-packages/sqlalchemy/engine/default.py:736\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    735\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[0;32m--> 736\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (psycopg2.errors.ObjectNotInPrerequisiteState) currval of sequence \"test_seq\" is not yet defined in this session\n\n[SQL: SELECT currval('test_seq')]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"
     ]
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT currval('test_seq')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "98feee75",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"\n",
    "    DROP TABLE IF EXISTS users;\n",
    "    CREATE TABLE users ( \n",
    "    user_id SERIAL PRIMARY KEY,\n",
    "    user_first_name VARCHAR(30) NOT NULL,\n",
    "    user_last_name VARCHAR(30) NOT NULL,\n",
    "    user_email_id VARCHAR(50) NOT NULL,\n",
    "    user_email_validated BOOLEAN,\n",
    "    user_password VARCHAR(200),\n",
    "    user_role VARCHAR(1),\n",
    "    is_active BOOLEAN,\n",
    "    created_dt DATE DEFAULT CURRENT_DATE\n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "7c02c53e",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>indexname</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>indexdef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>users</td>\n",
       "      <td>users_pkey</td>\n",
       "      <td>None</td>\n",
       "      <td>CREATE UNIQUE INDEX users_pkey ON public.users...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schemaname tablename   indexname tablespace  \\\n",
       "0     public     users  users_pkey       None   \n",
       "\n",
       "                                            indexdef  \n",
       "0  CREATE UNIQUE INDEX users_pkey ON public.users...  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "                FROM pg_catalog.pg_indexes \n",
    "                WHERE schemaname = 'public'\n",
    "                AND tablename = 'users'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "633a1029",
   "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>sequence_catalog</th>\n",
       "      <th>sequence_schema</th>\n",
       "      <th>sequence_name</th>\n",
       "      <th>data_type</th>\n",
       "      <th>numeric_precision</th>\n",
       "      <th>numeric_precision_radix</th>\n",
       "      <th>numeric_scale</th>\n",
       "      <th>start_value</th>\n",
       "      <th>minimum_value</th>\n",
       "      <th>maximum_value</th>\n",
       "      <th>increment</th>\n",
       "      <th>cycle_option</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>public</td>\n",
       "      <td>users_user_id_seq</td>\n",
       "      <td>bigint</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9223372036854775807</td>\n",
       "      <td>1</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>public</td>\n",
       "      <td>user_logins_user_login_id_seq</td>\n",
       "      <td>integer</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2147483647</td>\n",
       "      <td>1</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>public</td>\n",
       "      <td>test_seq</td>\n",
       "      <td>bigint</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>101</td>\n",
       "      <td>1000</td>\n",
       "      <td>100</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retail_db</td>\n",
       "      <td>public</td>\n",
       "      <td>users_user_id_seq1</td>\n",
       "      <td>integer</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2147483647</td>\n",
       "      <td>1</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  sequence_catalog sequence_schema                  sequence_name data_type  \\\n",
       "0        retail_db          public              users_user_id_seq    bigint   \n",
       "1        retail_db          public  user_logins_user_login_id_seq   integer   \n",
       "2        retail_db          public                       test_seq    bigint   \n",
       "3        retail_db          public             users_user_id_seq1   integer   \n",
       "\n",
       "   numeric_precision  numeric_precision_radix  numeric_scale start_value  \\\n",
       "0                 64                        2              0           1   \n",
       "1                 32                        2              0           1   \n",
       "2                 64                        2              0         101   \n",
       "3                 32                        2              0           1   \n",
       "\n",
       "  minimum_value        maximum_value increment cycle_option  \n",
       "0             1  9223372036854775807         1           NO  \n",
       "1             1           2147483647         1           NO  \n",
       "2           101                 1000       100           NO  \n",
       "3             1           2147483647         1           NO  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT *\n",
    "                FROM information_schema.sequences\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b6bb4cf",
   "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
}