predefinedfunction-sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 3,
   "id": "10b493f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['clustertab.config']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('clustertab.config')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 7,
   "id": "4216af6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'retail_db'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 9,
   "id": "6846fc00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'postgresql://postgres:1234@172.17.0.2:5432/retail_db'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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 * 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": "dcdb2e31",
   "metadata": {},
   "source": [
    "### Purpose of Pre-Defined Functions\n",
    "\n",
    "* How to use official documentation of Postgres to get syntax and symantecs of the pre-defined functions?\n",
    "* Understand different categories of functions\n",
    "* How to use functions effectively using real world examples?\n",
    "* How to manipulate strings and dates?\n",
    "* How to deal with nulls, convert data types etc?\n",
    "* Self evaluate by solving the exercises by using multiple functions in tandem.\n",
    "\n",
    "Postgres provides robust set of pre-defined functions to come up with solutions quickly as per the business requirements. There are many functions, but we will see the most common ones here.\n",
    "* Following are the categories of functions that are more commonly used.\n",
    "  * String Manipulation\n",
    "  * Date Manipulation\n",
    "  * Numeric Functions\n",
    "  * Type Conversion Functions\n",
    "  * CASE and WHEN\n",
    "  * and more\n",
    "* One can go to the official documentation from [Postgres website]\n",
    "\n",
    "## String Manipulation Functions\n",
    "\n",
    "We use string manipulation functions quite extensively. Here are some of the important functions which we typically use.\n",
    "* Case Conversion - `lower`, `upper`, `initcap`\n",
    "* Getting size of the column value - `length`\n",
    "* Extracting Data - `substr` and `split_part`\n",
    "* Trimming and Padding functions - `trim`, `rtrim`, `ltrim`, `rpad` and `lpad`\n",
    "* Reversing strings - `reverse`\n",
    "* Concatenating multiple strings `concat` and `concat_ws`\n",
    "\n",
    "## Date Manipulation Functions\n",
    "\n",
    "Let us go through some of the important date manipulation functions.\n",
    "* Getting Current Date and Timestamp\n",
    "* Date Arithmetic using `INTERVAL` and `-` operator\n",
    "* Getting beginning date or time using `date_trunc`\n",
    "* Extracting information using `to_char` as well as calendar functions.\n",
    "* Dealing with unix timestamp using `from_unixtime`, `to_unix_timestamp`\n",
    "\n",
    "\n",
    "## Handling NULL Values\n",
    "\n",
    "Let us understand how to handle nulls.\n",
    "* By default if we try to add or concatenate null to another column or expression or literal, it will return null.\n",
    "* If we want to replace null with some default value, we can use `coalesce`.\n",
    "  * Replace commission_pct with 0 if it is null.\n",
    "* `coalesce` returns first not null value if we pass multiple arguments to it.\n",
    "* We have a function called as `nullif`. If the first argument is equal to second argument, it returns null. It is typically used when we compare against 2 columns where nulls are also involved.\n",
    "* You might have seen functions like `nvl`, `nvl2` etc with respect to databases like Oracle. Postgres does not support them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f90221e4",
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0     50"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#There are 50 string related functions or routines\n",
    "queryBase(\"\"\"SELECT COUNT(1) FROM information_schema.routines \n",
    "                WHERE routine_name ~ 'str'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d34c0eb2",
   "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>substring</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>iqu</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  substring\n",
       "0       iqu"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT substring('liquidmoon' FROM 2 for 3)\"\"\") #position and length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e8c39d82",
   "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>substring</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>iqu</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  substring\n",
       "0       iqu"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Two ways\n",
    "queryBase(\"\"\"SELECT substring('liquidmoon',2,3)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "09450575",
   "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>lower_result</th>\n",
       "      <th>upper_result</th>\n",
       "      <th>initial_cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hellworld</td>\n",
       "      <td>HELLWORLD</td>\n",
       "      <td>Hellworld</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  lower_result upper_result initial_cap\n",
       "0    hellworld    HELLWORLD   Hellworld"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#These queries are directly executing on the database server. Not creating any tables\n",
    "queryBase(\"\"\"SELECT lower('HellWOrlD') as lower_result,\n",
    "                    upper('HellWOrlD') as upper_result,\n",
    "                    initcap('HellWOrlD') as initial_cap\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e783bf86",
   "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>wordlength</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   wordlength\n",
       "0           9"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#These queries are directly executing on the database server. Not creating any tables\n",
    "queryBase(\"\"\"SELECT length('HellWOrlD') as wordLength\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8246c4bf",
   "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>database_fname</th>\n",
       "      <th>lower_custfname</th>\n",
       "      <th>upper_custlname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Richard</td>\n",
       "      <td>richard</td>\n",
       "      <td>HERNANDEZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mary</td>\n",
       "      <td>mary</td>\n",
       "      <td>BARRETT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ann</td>\n",
       "      <td>ann</td>\n",
       "      <td>SMITH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mary</td>\n",
       "      <td>mary</td>\n",
       "      <td>JONES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Robert</td>\n",
       "      <td>robert</td>\n",
       "      <td>HUDSON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  database_fname lower_custfname upper_custlname\n",
       "0        Richard         richard       HERNANDEZ\n",
       "1           Mary            mary         BARRETT\n",
       "2            Ann             ann           SMITH\n",
       "3           Mary            mary           JONES\n",
       "4         Robert          robert          HUDSON"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#The functions gets applied and they are returned in another column.\n",
    "queryBase(\"\"\"SELECT customer_fname AS database_fname,\n",
    "                    lower(customer_fname) AS lower_custFname, \n",
    "                    upper(customer_lname) AS upper_custLname\n",
    "                    FROM customers\n",
    "                    LIMIT 5\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9b5d1ee5",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0   2013"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Note the index is starting with 1, not 0\n",
    "queryBase(\"\"\"SELECT substr('2013-07-25 00:00:00.0', 1, 4) AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "4e9ccfc8",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0   2013"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Note the index is starting with 1, not 0\n",
    "queryBase(\"\"\"SELECT substring('2013-07-25 00:00:00.0', 1, 4) AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "91b02a7f",
   "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>initials</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RiHe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MaBa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  initials\n",
       "0     RiHe\n",
       "1     MaBa"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT CONCAT(SUBSTRING(customer_fname, 1, 2),\n",
    "                        SUBSTRING(customer_lname, 1, 2)) AS initials\n",
    "          FROM customers LIMIT 2\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "04cf427b",
   "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>initials</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RHe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MBa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  initials\n",
       "0      RHe\n",
       "1      MBa"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT CONCAT(SUBSTRING(customer_fname, 1, 1),\n",
    "                        SUBSTRING(customer_lname, 1, 2)) AS initials\n",
    "          FROM customers LIMIT 2\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e62a48ee",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       result\n",
       "0  00:00:00.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT substring('2013-07-25 00:00:00.0' from 12) AS result\"\"\")\n",
    "#one of the ways to access the timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4e6b1c2a",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0    123"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT left('123 456 7890', 3) AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "616bb122",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0    890"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT right('123 456 7890', 3) AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "79f647ed",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>andez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>on</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0  andez\n",
       "1    ett\n",
       "2      h\n",
       "3      s\n",
       "4     on"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT right(customer_lname,length(customer_lname) - 4) AS result\n",
    "                FROM customers LIMIT 5\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f7725581",
   "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>unique_id</th>\n",
       "      <th>unique_id_first3</th>\n",
       "      <th>unique_id_last4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>241-80-7115</td>\n",
       "      <td>241</td>\n",
       "      <td>0-7115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>399-88-3617</td>\n",
       "      <td>399</td>\n",
       "      <td>8-3617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>586-92-5361</td>\n",
       "      <td>586</td>\n",
       "      <td>2-5361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>694-30-6851</td>\n",
       "      <td>694</td>\n",
       "      <td>0-6851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>733-17-4217</td>\n",
       "      <td>733</td>\n",
       "      <td>7-4217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>831-59-5593</td>\n",
       "      <td>831</td>\n",
       "      <td>9-5593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>873-68-9778</td>\n",
       "      <td>873</td>\n",
       "      <td>8-9778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>876-99-585</td>\n",
       "      <td>876</td>\n",
       "      <td>99-585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>884-65-284</td>\n",
       "      <td>884</td>\n",
       "      <td>65-284</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     unique_id unique_id_first3 unique_id_last4\n",
       "0  241-80-7115              241          0-7115\n",
       "1  399-88-3617              399          8-3617\n",
       "2           48               48            None\n",
       "3  586-92-5361              586          2-5361\n",
       "4  694-30-6851              694          0-6851\n",
       "5  733-17-4217              733          7-4217\n",
       "6  831-59-5593              831          9-5593\n",
       "7  873-68-9778              873          8-9778\n",
       "8   876-99-585              876          99-585\n",
       "9   884-65-284              884          65-284"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"WITH unique_ids AS (\n",
    "    SELECT '241-80-7115' AS unique_id UNION\n",
    "    SELECT '694-30-6851' UNION\n",
    "    SELECT '586-92-5361' UNION\n",
    "    SELECT '884-65-284' UNION\n",
    "    SELECT '876-99-585' UNION\n",
    "    SELECT '831-59-5593' UNION\n",
    "    SELECT '399-88-3617' UNION\n",
    "    SELECT '733-17-4217' UNION\n",
    "    SELECT '873-68-9778' UNION\n",
    "    SELECT '48'\n",
    ") SELECT unique_id,\n",
    "    substring(unique_id FROM 1 FOR 3) AS unique_id_first3,\n",
    "    substring(unique_id FROM '......$') AS unique_id_last4 \n",
    "FROM unique_ids\n",
    "ORDER BY unique_id\"\"\") #the ...$ option is very interesting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "1ee60f23",
   "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>order_id</th>\n",
       "      <th>datepart</th>\n",
       "      <th>order_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>CLOSED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>COMPLETE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>PROCESSING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>-07-2</td>\n",
       "      <td>PENDING_PAYMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id datepart     order_status\n",
       "0         1    -07-2           CLOSED\n",
       "1         2    -07-2  PENDING_PAYMENT\n",
       "2         3    -07-2         COMPLETE\n",
       "3         4    -07-2           CLOSED\n",
       "4         5    -07-2         COMPLETE\n",
       "5         6    -07-2         COMPLETE\n",
       "6         7    -07-2         COMPLETE\n",
       "7         8    -07-2       PROCESSING\n",
       "8         9    -07-2  PENDING_PAYMENT\n",
       "9        10    -07-2  PENDING_PAYMENT"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#If in doubt can check in the below query. The ::varchar changes the type of variable4\n",
    "queryBase(\"\"\"SELECT order_id, substr(order_date::varchar, 5, 5) AS datepart,\n",
    "            order_status\n",
    "            FROM orders LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "b1cb80e3",
   "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>split_part</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  split_part\n",
       "0         26"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT split_part('2013-07-26','-',3)\"\"\")#The number provides the "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "56b013f1",
   "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>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>Vancouver</td>\n",
       "      <td>Washington</td>\n",
       "      <td>98687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  street       city       state postal_code\n",
       "0         Vancouver  Washington       98687"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"WITH addresses AS (\n",
    "    SELECT '593 Fair Oaks Pass, Frankfort, Kentucky, 40618' AS address UNION\n",
    "    SELECT ', Vancouver, Washington, 98687' UNION\n",
    "    SELECT '83047 Glacier Hill Circle, Sacramento, California, 94237' UNION\n",
    "    SELECT '935 Columbus Junction, Cincinnati, Ohio, 45213' UNION\n",
    "    SELECT '03010 Nevada Crossing, El Paso, Texas, 88579' UNION\n",
    "    SELECT '9 Dunning Circle, , Arizona, 85271' UNION\n",
    "    SELECT '96 Fair Oaks Way, Decatur, Illinois, 62525' UNION\n",
    "    SELECT '999 Caliangt Avenue, Greenville, South Carolina, 29615' UNION\n",
    "    SELECT '2 Saint Paul Trail, Bridgeport, , 06673' UNION\n",
    "    SELECT '3 Reindahl Center, Ogden, Utah'\n",
    ") SELECT split_part(address, ', ', 1) street,\n",
    "    split_part(address, ', ', 2) city,\n",
    "    split_part(address, ', ', 3) state,\n",
    "    split_part(address, ', ', 4) postal_code\n",
    "FROM addresses\n",
    "WHERE split_part(address, ',',1)=''\n",
    "ORDER BY postal_code\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a2039b6f",
   "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>unique_id</th>\n",
       "      <th>unique_id_first3</th>\n",
       "      <th>unique_id_last4</th>\n",
       "      <th>unique_id_last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>241-80-7115</td>\n",
       "      <td>241</td>\n",
       "      <td>7115</td>\n",
       "      <td>7115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>399-88-3617</td>\n",
       "      <td>399</td>\n",
       "      <td>3617</td>\n",
       "      <td>3617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>480-69-032</td>\n",
       "      <td>480</td>\n",
       "      <td>-032</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>586-92-5361</td>\n",
       "      <td>586</td>\n",
       "      <td>5361</td>\n",
       "      <td>5361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>694-30-6851</td>\n",
       "      <td>694</td>\n",
       "      <td>6851</td>\n",
       "      <td>6851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>733-17-4217</td>\n",
       "      <td>733</td>\n",
       "      <td>4217</td>\n",
       "      <td>4217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>831-59-5593</td>\n",
       "      <td>831</td>\n",
       "      <td>5593</td>\n",
       "      <td>5593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>873-68-9778</td>\n",
       "      <td>873</td>\n",
       "      <td>9778</td>\n",
       "      <td>9778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>876-99-585</td>\n",
       "      <td>876</td>\n",
       "      <td>-585</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>884-65-284</td>\n",
       "      <td>884</td>\n",
       "      <td>-284</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     unique_id unique_id_first3 unique_id_last4 unique_id_last\n",
       "0  241-80-7115              241            7115           7115\n",
       "1  399-88-3617              399            3617           3617\n",
       "2   480-69-032              480            -032        Invalid\n",
       "3  586-92-5361              586            5361           5361\n",
       "4  694-30-6851              694            6851           6851\n",
       "5  733-17-4217              733            4217           4217\n",
       "6  831-59-5593              831            5593           5593\n",
       "7  873-68-9778              873            9778           9778\n",
       "8   876-99-585              876            -585        Invalid\n",
       "9   884-65-284              884            -284        Invalid"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#if is represente as case when expression end AS exprs \n",
    "queryBase(\"\"\"WITH unique_ids AS (\n",
    "    SELECT '241-80-7115' AS unique_id UNION\n",
    "    SELECT '694-30-6851' UNION\n",
    "    SELECT '586-92-5361' UNION\n",
    "    SELECT '884-65-284' UNION\n",
    "    SELECT '876-99-585' UNION\n",
    "    SELECT '831-59-5593' UNION\n",
    "    SELECT '399-88-3617' UNION\n",
    "    SELECT '733-17-4217' UNION\n",
    "    SELECT '873-68-9778' UNION\n",
    "    SELECT '480-69-032'\n",
    ") SELECT unique_id,\n",
    "    substring(unique_id FROM 1 FOR 3) AS unique_id_first3,\n",
    "    substring(unique_id FROM '....$') AS unique_id_last4,\n",
    "    CASE WHEN length(split_part(unique_id, '-', 3)) = 4\n",
    "        THEN split_part(unique_id, '-', 3)\n",
    "        ELSE 'Invalid'\n",
    "    END AS unique_id_last\n",
    "FROM unique_ids\n",
    "ORDER BY unique_id\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "dbd981c1",
   "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>position</th>\n",
       "      <th>position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   position  position\n",
       "0         3         0"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT position('@' IN 'it@versity.com'),\n",
    "    position ('@' IN 'itversity.com')\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "9fb3a05f",
   "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>spacepos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   spacepos\n",
       "0         5\n",
       "1         5\n",
       "2         5\n",
       "3         5\n",
       "4         3"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT strpos(customer_street,' ') AS spacePos FROM customers LIMIT 5\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "6d043634",
   "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>spacepos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   spacepos\n",
       "0         5\n",
       "1         5\n",
       "2         5\n",
       "3         5\n",
       "4         3"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " queryBase(\"\"\"SELECT position(' ' IN customer_street) AS spacePos FROM customers LIMIT 5\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "1b6a139f",
   "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>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   length\n",
       "0      14"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT length(rtrim('     HELLORoma     '))\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "96419dcb",
   "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>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   length\n",
       "0       9"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT length(trim('     HELLORoma     '))\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "d177818c",
   "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>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   length\n",
       "0      19"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Count the number of characters, space is trimmed and the \n",
    "queryBase(\"\"\"SELECT length(ltrim('     HELLORoma-----','-'))\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "322fe797",
   "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>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   length\n",
       "0       9"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Count the number of characters, you will realize one side of the word is trimed\n",
    "queryBase(\"\"\"SELECT length(ltrim('     HELLORoma'))\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "f6949ae0",
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>mydate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>7</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  mydate\n",
       "0  2013      7      25"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT 2013 AS year, 7 AS month, 25 AS myDate\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "8b54aa76",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  result\n",
       "0  00007"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT lpad(7::varchar, 5, '0') AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "2120b761",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00000000000000000058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 result\n",
       "0  00000000000000000058"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT lpad(58::varchar, 20, '0') AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "18ed6eb9",
   "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>year</th>\n",
       "      <th>month</th>\n",
       "      <th>mydate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>7</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  mydate\n",
       "0  2013      7      25"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM (SELECT 2013 AS year, 7 AS month, 25 AS myDate) q\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "c32eef36",
   "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>order_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-07-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_date\n",
       "0  2013-07-25"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT concat(year, '-', lpad(month::varchar, 2, '0'), '-',\n",
    "              lpad(myDate::varchar, 2, '0')) AS order_date\n",
    "FROM\n",
    "    (SELECT 2013 AS year, 7 AS month, 25 AS myDate) q\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "8b2b1ba9",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hello World</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        result\n",
       "0  Hello World"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT replace('Halo World', 'alo', 'ello') AS result\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "627a42b3",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hello8z World</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          result\n",
       "0  Hello8z World"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT overlay('Halo World' PLACING 'ello8z' FROM 2 for 3) AS result\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "048d4130",
   "metadata": {},
   "source": [
    "### Starting Date Time manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "e2d2320b",
   "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>current_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  current_date\n",
       "0   2022-11-13"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT current_date AS current_date\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "4f7599b4",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-13 07:26:18.707577+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        current_ts\n",
       "0 2022-11-13 07:26:18.707577+00:00"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT current_timestamp AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "9a1e06da",
   "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>substring</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  substring\n",
       "0      2022"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT SUBSTRING(current_date::VARCHAR, 1, 4)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "57a77673",
   "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>results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2025-07-13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     results\n",
       "0 2025-07-13"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT current_date + interval '32 MONTHS' AS results\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "3da9d3a5",
   "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>results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-13 08:18:53.238883+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           results\n",
       "0 2022-11-13 08:18:53.238883+00:00"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT current_timestamp + interval '50 MINUTES' AS results\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "68408d25",
   "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>results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-16 10:19:11.756409+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           results\n",
       "0 2022-11-16 10:19:11.756409+00:00"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT current_timestamp + interval '3 DAYS 2 HOURS 50 MINUTES' AS results\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "ce55aef6",
   "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>subdatae</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   subdatae\n",
       "0       444"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT '2019-03-20'::date - '2017-12-31'::date AS subdatae\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "0371c126",
   "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>year_beginning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             year_beginning\n",
       "0 2022-01-01 00:00:00+00:00"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT date_trunc('YEAR', current_date) AS year_beginning\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "7a33924d",
   "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>year_beginning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             year_beginning\n",
       "0 2022-11-01 00:00:00+00:00"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT date_trunc('MONTH', current_date) AS year_beginning\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a484d764",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  current_ts\n",
       "0       2022"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char(current_timestamp, 'yyyy') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "65aa1f21",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  current_ts\n",
       "0          6"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char((current_timestamp + interval '5 days'), 'D') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "553477e6",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  current_ts\n",
       "0         07"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char((current_timestamp + interval '5 days'), 'HH') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "579953d7",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>friday   07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    current_ts\n",
       "0  friday   07"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char((current_timestamp + interval '5 days'), 'dayHH') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "15badb51",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-friday   07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         current_ts\n",
       "0  2022-friday   07"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char((current_timestamp + interval '5 days'), 'yyyy-dayHH') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "b80f52e2",
   "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>current_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-friday   :07:11:53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                current_ts\n",
       "0  2022-friday   :07:11:53"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT to_char((current_timestamp + interval '5 days'), 'yyyy-day:HH:MM:SS') AS current_ts\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "b44621a1",
   "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>extract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   extract\n",
       "0     45.0"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT EXTRACT(week FROM current_date)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "6dbe9052",
   "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>extract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>317.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   extract\n",
       "0    317.0"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT EXTRACT(doy FROM current_date)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "ed90df1a",
   "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>extract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   extract\n",
       "0     42.0"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT EXTRACT(minute FROM current_timestamp)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "971d6577",
   "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>extract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.668325e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        extract\n",
       "0  1.668325e+09"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT EXTRACT('epoch' FROM current_timestamp)\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1ab7cbb",
   "metadata": {},
   "source": [
    "### Numerical Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "75bdece7",
   "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>abs</th>\n",
       "      <th>abs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.5</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    abs  abs\n",
       "0  10.5   10"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT abs(-10.5), abs(10)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "06e0b1d4",
   "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>order_item_subtotal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>250.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_item_subtotal\n",
       "0               199.99\n",
       "1               250.00\n",
       "2               129.99"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#First Filter\n",
    "queryBase(\"\"\"SELECT order_item_subtotal FROM order_items\n",
    "WHERE order_item_order_id = 2\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "450cc1a9",
   "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>order_revenue_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>193.326667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_revenue_avg\n",
       "0         193.326667"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Then make functions to work on that\n",
    "queryBase(\"\"\"SELECT avg(order_item_subtotal) AS order_revenue_avg FROM order_items\n",
    "WHERE order_item_order_id = 2\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "7f05fa56",
   "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>order_item_order_id</th>\n",
       "      <th>order_revenue_sum</th>\n",
       "      <th>order_revenue_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.980000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>579.98</td>\n",
       "      <td>193.326667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>699.85</td>\n",
       "      <td>174.962500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1129.86</td>\n",
       "      <td>225.972000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>579.92</td>\n",
       "      <td>193.306667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>729.84</td>\n",
       "      <td>182.460000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>599.96</td>\n",
       "      <td>199.986667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10</td>\n",
       "      <td>651.92</td>\n",
       "      <td>130.384000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11</td>\n",
       "      <td>919.79</td>\n",
       "      <td>183.958000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>1299.87</td>\n",
       "      <td>259.974000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_item_order_id  order_revenue_sum  order_revenue_avg\n",
       "0                    1             299.98         299.980000\n",
       "1                    2             579.98         193.326667\n",
       "2                    4             699.85         174.962500\n",
       "3                    5            1129.86         225.972000\n",
       "4                    7             579.92         193.306667\n",
       "5                    8             729.84         182.460000\n",
       "6                    9             599.96         199.986667\n",
       "7                   10             651.92         130.384000\n",
       "8                   11             919.79         183.958000\n",
       "9                   12            1299.87         259.974000"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "            SELECT order_item_order_id, \n",
    "                sum(order_item_subtotal) AS order_revenue_sum,\n",
    "                avg(order_item_subtotal) AS order_revenue_avg\n",
    "            FROM order_items\n",
    "            GROUP BY order_item_order_id\n",
    "            ORDER BY order_item_order_id\n",
    "            LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "3b6c5b8a",
   "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>rnd</th>\n",
       "      <th>flr</th>\n",
       "      <th>ciel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    rnd   flr   ciel\n",
       "0  10.5  10.0  101.0"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT\n",
    "    round(10.48,1) rnd,\n",
    "    floor(10.48) flr,\n",
    "    ceil(100.478) ciel\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "314f9490",
   "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>order_item_order_id</th>\n",
       "      <th>order_revenue_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>579.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>699.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1129.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>579.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_item_order_id  order_revenue_avg\n",
       "0                    1             299.98\n",
       "1                    2             579.98\n",
       "2                    4             699.85\n",
       "3                    5            1129.86\n",
       "4                    7             579.92"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#When using the expression inside the function the the results have to be type casted\n",
    "queryBase(\"\"\"SELECT order_item_order_id, \n",
    "    round(sum(order_item_subtotal)::numeric, 2) AS order_revenue_avg \n",
    "FROM order_items\n",
    "GROUP BY order_item_order_id\n",
    "LIMIT 5\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "6747be7f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_item_order_id</th>\n",
       "      <th>order_revenue_sum</th>\n",
       "      <th>order_item_subtotal_min</th>\n",
       "      <th>order_item_subtotal_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>579.98</td>\n",
       "      <td>129.99</td>\n",
       "      <td>250.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>699.85</td>\n",
       "      <td>49.98</td>\n",
       "      <td>299.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>1129.86</td>\n",
       "      <td>99.96</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>579.92</td>\n",
       "      <td>79.95</td>\n",
       "      <td>299.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8</td>\n",
       "      <td>729.84</td>\n",
       "      <td>50.00</td>\n",
       "      <td>299.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>599.96</td>\n",
       "      <td>199.98</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10</td>\n",
       "      <td>651.92</td>\n",
       "      <td>21.99</td>\n",
       "      <td>199.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11</td>\n",
       "      <td>919.79</td>\n",
       "      <td>49.98</td>\n",
       "      <td>399.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>1299.87</td>\n",
       "      <td>100.00</td>\n",
       "      <td>499.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_item_order_id  order_revenue_sum  order_item_subtotal_min  \\\n",
       "0                    1             299.98                   299.98   \n",
       "1                    2             579.98                   129.99   \n",
       "2                    4             699.85                    49.98   \n",
       "3                    5            1129.86                    99.96   \n",
       "4                    7             579.92                    79.95   \n",
       "5                    8             729.84                    50.00   \n",
       "6                    9             599.96                   199.98   \n",
       "7                   10             651.92                    21.99   \n",
       "8                   11             919.79                    49.98   \n",
       "9                   12            1299.87                   100.00   \n",
       "\n",
       "   order_item_subtotal_max  \n",
       "0                   299.98  \n",
       "1                   250.00  \n",
       "2                   299.95  \n",
       "3                   299.98  \n",
       "4                   299.98  \n",
       "5                   299.95  \n",
       "6                   199.99  \n",
       "7                   199.99  \n",
       "8                   399.96  \n",
       "9                   499.95  "
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT order_item_order_id, \n",
    "    round(sum(order_item_subtotal)::numeric, 2) AS order_revenue_sum,\n",
    "    min(order_item_subtotal) AS order_item_subtotal_min,\n",
    "    max(order_item_subtotal) AS order_item_subtotal_max \n",
    "FROM order_items\n",
    "GROUP BY order_item_order_id\n",
    "LIMIT 10\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "03c6cad9",
   "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>?column?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ?column?\n",
       "0        41"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT (random() * 100)::int + 1\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "3d7cce4c",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0       9"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT '09'::int AS result\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "ae578310",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0       9"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT '09'::int AS result\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "150efd45",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0    0.04"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT cast('0.04000' AS FLOAT) AS result\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "2e97c8b1",
   "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>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month\n",
       "0    09"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT to_char('2020-09-30'::date, 'MM') AS month\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "aa0c9d3b",
   "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>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   result\n",
       "0    0.04"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT cast('0.04000' AS FLOAT) AS result\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "2866e74c",
   "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>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  month\n",
       "0    09"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT split_part('2020-09-30', '-', 2) AS month\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "e5ed3ecb",
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "(psycopg2.errors.AmbiguousFunction) function to_char(unknown, unknown) is not unique\nLINE 2: SELECT to_char('2020-09-30', 'DAY') AS month\n               ^\nHINT:  Could not choose a best candidate function. You might need to add explicit type casts.\n\n[SQL: \nSELECT to_char('2020-09-30', 'DAY') AS month\n]\n(Background on this error at: https://sqlalche.me/e/14/f405)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAmbiguousFunction\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;31mAmbiguousFunction\u001b[0m: function to_char(unknown, unknown) is not unique\nLINE 2: SELECT to_char('2020-09-30', 'DAY') AS month\n               ^\nHINT:  Could not choose a best candidate function. You might need to add explicit type casts.\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[0;32mIn [143], 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\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124;43mSELECT to_char(\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m2020-09-30\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[38;5;124;43mDAY\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m) AS month\u001b[39;49m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn [10], 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: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;31mProgrammingError\u001b[0m: (psycopg2.errors.AmbiguousFunction) function to_char(unknown, unknown) is not unique\nLINE 2: SELECT to_char('2020-09-30', 'DAY') AS month\n               ^\nHINT:  Could not choose a best candidate function. You might need to add explicit type casts.\n\n[SQL: \nSELECT to_char('2020-09-30', 'DAY') AS month\n]\n(Background on this error at: https://sqlalche.me/e/14/f405)"
     ]
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT to_char('2020-09-30', 'DAY') AS month\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "4b2f0937",
   "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>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WEDNESDAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       month\n",
       "0  WEDNESDAY"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"\n",
    "SELECT to_char('2020-09-30'::date, 'DAY') AS month\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0206c4db",
   "metadata": {},
   "source": [
    "### Null handlers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "00897188",
   "metadata": {},
   "outputs": [],
   "source": [
    "queryTable(\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS sales(\n",
    "    sales_person_id INT,\n",
    "    sales_amount FLOAT,\n",
    "    commission_pct INT\n",
    ")\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "1cd201f5",
   "metadata": {},
   "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 [147], 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;43mINSERT INTO sales VALUES\u001b[39;49m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124;43m    (1, 1000, 10),\u001b[39;49m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124;43m    (2, 1500, 8),\u001b[39;49m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;124;43m    (3, 500, NULL),\u001b[39;49m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;124;43m    (4, 800, 5),\u001b[39;49m\n\u001b[1;32m      6\u001b[0m \u001b[38;5;124;43m    (5, 250, NULL)\u001b[39;49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn [10], 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(\"\"\"INSERT INTO sales VALUES\n",
    "    (1, 1000, 10),\n",
    "    (2, 1500, 8),\n",
    "    (3, 500, NULL),\n",
    "    (4, 800, 5),\n",
    "    (5, 250, NULL)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "a27e337a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales_person_id</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>commission_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>800.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>250.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_person_id  sales_amount  commission_pct\n",
       "0                1        1000.0            10.0\n",
       "1                2        1500.0             8.0\n",
       "2                3         500.0             NaN\n",
       "3                4         800.0             5.0\n",
       "4                5         250.0             NaN"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT * FROM sales\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "3e6c5485",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales_person_id</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>commission_pct</th>\n",
       "      <th>incorrect_commission_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>800.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>250.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_person_id  sales_amount  commission_pct  incorrect_commission_amount\n",
       "0                1        1000.0            10.0                        100.0\n",
       "1                2        1500.0             8.0                        120.0\n",
       "2                3         500.0             NaN                          NaN\n",
       "3                4         800.0             5.0                         40.0\n",
       "4                5         250.0             NaN                          NaN"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT s.*, \n",
    "    round((sales_amount * commission_pct / 100)::numeric, 2) AS incorrect_commission_amount\n",
    "FROM sales AS s\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "a56e606b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales_person_id</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>commission_pct</th>\n",
       "      <th>commission_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>800.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>250.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sales_person_id  sales_amount  commission_pct  commission_amount\n",
       "0                1        1000.0            10.0              100.0\n",
       "1                2        1500.0             8.0              120.0\n",
       "2                3         500.0             NaN                0.0\n",
       "3                4         800.0             5.0               40.0\n",
       "4                5         250.0             NaN                0.0"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT s.*, \n",
    "    round((sales_amount * coalesce(commission_pct, 0) / 100)::numeric, 2) AS commission_amount\n",
    "FROM sales AS s\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "feccc319",
   "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>nullif</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  nullif\n",
       "0   None"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT nullif(1, 1)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "b824c067",
   "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>nullif</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nullif\n",
       "0       1"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queryBase(\"\"\"SELECT nullif(1, 0)\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0be7064",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd8d1c10",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a33adf6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52a9898e",
   "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
}