Psycopg_Join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HOW TO: Join & Use Dates in PSQL Using Python: *(PSYCOPG2)*\n",
    "-------------------------- **<font color=red>With Mr Fugu Data Science</font>** -------------------------\n",
    "\n",
    "[Github](github.com/MrFuguDataScience) | [Youtube](https://www.youtube.com/channel/UCbni-TDI-Ub8VlGaP8HLTNw)\n",
    "\n",
    "# Purpose & Outcome:\n",
    "\n",
    "+ `Create New Table with schema as a function`\n",
    "    + This will be used for joining examples\n",
    "\n",
    "+ `Perform Inner Join`\n",
    "    + Show Multiple Examples of what could go wrong\n",
    "+ Query By Date:\n",
    "    + Show a few examples\n",
    "\n",
    "\n",
    "\n",
    "`-------------------------------------------------`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create *`Init file`*: used to store credentials, for security.\n",
    "\n",
    "[Create Init File](https://towardsdatascience.com/python-and-postgresql-how-to-access-a-postgresql-database-like-a-data-scientist-b5a9c5a0ea43) | [PostgreSQL Tutotial](https://www.postgresqltutorial.com/postgresql-python/connect/)\n",
    "\n",
    "**1<sup>st</sup>** ) : create the initialization file:\n",
    "\n",
    "use your terminal or notepad, unless your favorite editor directly converts to this format .ini file.\n",
    "\n",
    "on Mac I did touch database_file_init.ini\n",
    "\n",
    "*vi database_file.ini then inside the file type following lines:*\n",
    "\n",
    "`[postgresql]`\n",
    "\n",
    "`host=localhost`\n",
    "\n",
    "`database=what_databse_you_want_to_access`\n",
    "\n",
    "`user= some_user_you_created_for_this_user_in_psql`\n",
    "\n",
    "`password= some_password_you_have_to_this_db`\n",
    "\n",
    "\n",
    "`__________________________________________________________`\n",
    "\n",
    "**2<sup>nd</sup>** ) : now the config.py file this will be used to take data from init file and outputs a dictionary.\n",
    "\n",
    "this file will look like this as an example: {‘host’: ‘localhost’, ‘database’: ‘suppliers’, ‘user’: ‘postgres’, ‘password’: ‘postgres’} when it is read.\n",
    "HERE IS THE CODE:\n",
    "\n",
    "\n",
    "If you are working in a real world scenario, you will most likely need to hide your credentials, permissions, login & password that is where these files shine.\n",
    "\n",
    "[create init and config files](https://towardsdatascience.com/python-and-postgresql-how-to-access-a-postgresql-database-like-a-data-scientist-b5a9c5a0ea43) | [Python_configparser doc](https://docs.python.org/3/library/configparser.html) | [Postgres_tutorial_Python_PSQL](https://www.postgresqltutorial.com/postgresql-python/connect/)\n",
    "\n",
    "**1<sup>st</sup> )** : create the initialization file:\n",
    "\n",
    "use your terminal or notepad, unless your favorite editor directly converts to this format .ini file.\n",
    "on Mac I did touch database_file_init.ini\n",
    "vi database_file.ini then inside the file type following lines:\n",
    "[postgresql]\n",
    "host=localhost\n",
    "database=what_databse_you_want_to_access\n",
    "user= some_user_you_created_for_this_user_in_psql\n",
    "password= some_password_you_have_to_this_db\n",
    "\n",
    "------------------------------------------------\n",
    "\n",
    "**2<sup>nd</sup> )** : now the config.py file this will be used to take data from init file and outputs a dictionary.\n",
    "\n",
    "this file will look like this as an example: *{‘host’: ‘localhost’, ‘database’: ‘suppliers’, ‘user’: ‘postgres’, ‘password’: ‘postgres’}* when it is read.\n",
    "HERE IS THE CODE:\n",
    "\n",
    "____________________________________________________\n",
    "\n",
    "`!/usr/bin/python (can also do virtenv,env)\n",
    "from configparser import ConfigParser`\n",
    "\n",
    "`def config(filename='database.ini', section='postgresql'):`\n",
    "\n",
    "`# create a parser`\n",
    "\n",
    "`parser = ConfigParser()`\n",
    "\n",
    "`# read config file`\n",
    "\n",
    "`parser.read(filename)`\n",
    "\n",
    "`# get section, default to postgresql`\n",
    "\n",
    "`db = {}`\n",
    "\n",
    "`# Checks to see if section (postgresql) parser exists`\n",
    "\n",
    "`if parser.has_section(section):\n",
    "    params = parser.items(section)\n",
    "    for param in params:\n",
    "        db[param[0]] = param[1]`\n",
    "\n",
    "`# Returns an error if a parameter is called that is not listed in the initialization file`\n",
    "\n",
    "`else:\n",
    "    raise Exception('Section {0} not found in the {1} file'.format(section, filename))\n",
    "return db`\n",
    "\n",
    "\n",
    "\n",
    "`---------------------------------------------`\n",
    "\n",
    "**This code** (*Above*) **was adpated from online material on Postgres website and used in 1st & 3rd link above**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helpful Tips:\n",
    "\n",
    "+ Admin: the prompt will have `database_name#` vs user: `database_name$`\n",
    "\n",
    "+ `\\h` will give list of SQL commands\n",
    "\n",
    "+ `\\?` describes psql specific commands\n",
    "\n",
    "\n",
    "`-----------------------------------------------------`\n",
    "\n",
    "PSQL is a `Relational` database and therefore follows the convention of: \n",
    "\n",
    "`tables` are \"relations\",\n",
    "\n",
    "`attributes`: columns, \n",
    "\n",
    "`tuples`: rows\n",
    "\n",
    "+ You will always need to create a `schema` beforehand. Because you are dealing with a \"design first\" database. \n",
    "\n",
    "+ Queries are run inside a transaction, ensuring data integrity and error handling. But,\n",
    "\n",
    "+ not all queries can be run inside a transaction either. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2             # python->psql connection\n",
    "import psycopg2.extras\n",
    "import pandas as pd         # create dataframes \n",
    "# import os                   # fetch files\n",
    "import numpy as np\n",
    "from faker import Factory,Faker # Create fake data to use for join-tables\n",
    "\n",
    "import io\n",
    "\n",
    "# Import the 'config' function from the config_user_dta.py file:\n",
    "from config_user_dta import config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish a connection to the database by creating a cursor object\n",
    "\n",
    "# Get the config params\n",
    "params_ = config()\n",
    "\n",
    "# Connect to the Postgres_DB:\n",
    "conn = psycopg2.connect(**params_)\n",
    "\n",
    "# Create new_cursor allowing us to write Python to execute PSQL:\n",
    "cur = conn.cursor()\n",
    "\n",
    "conn.autocommit = True  # read documentation understanding when to Use & NOT use (TRUE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "fake_ppl=pd.read_csv('fake_users_R.csv')\n",
    "\n",
    "k=pd.DataFrame(fake_ppl)\n",
    "n=pd.DataFrame(k.drop('Unnamed: 0', axis=1))\n",
    "\n",
    "\n",
    "n.to_csv('noIndx.csv',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Intel Core i1-7554K',\n",
       " 'AMD Ryzen 1 5827X',\n",
       " 'Intel Core i5-9457K',\n",
       " 'AMD Ryzen 4 3401X',\n",
       " 'Intel Core i6-7283K']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Creating Fake CPU's that customers purchased, with country of purchase,linking them \n",
    "for joining tables later by foreign keys.\n",
    "'''\n",
    "fake_data=Faker()\n",
    "\n",
    "cpus=[]\n",
    "for _ in range(len(fake_ppl)//2):# len//2 I want the same length as dataframe, 2 cpu types\n",
    "    cpus.append(fake_data.numerify(text='Intel Core i%-%%##K'))\n",
    "    cpus.append(fake_data.numerify(text='AMD Ryzen % %%##X'))\n",
    "len(cpus)\n",
    "# len(fake_ppl)\n",
    "cpus[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.date(2019, 10, 31),\n",
       " datetime.date(2017, 7, 16),\n",
       " datetime.date(2019, 3, 24),\n",
       " datetime.date(2019, 6, 15),\n",
       " datetime.date(2019, 11, 11),\n",
       " datetime.date(2017, 6, 19)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create List of fake purchase dates:\n",
    "\n",
    "fake_data.seed(10)\n",
    "\n",
    "purchase_dates=[]\n",
    "\n",
    "for _ in range(len(fake_ppl)):\n",
    "    purchase_dates.append(fake_data.date_between(start_date='-3y', end_date='today'))\n",
    "    \n",
    "purchase_dates[:6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>credit_card</th>\n",
       "      <th>cpu</th>\n",
       "      <th>purchase_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5399-3484-4724-7187</td>\n",
       "      <td>Intel Core i1-7554K</td>\n",
       "      <td>2019-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1630-5261-6108-7631</td>\n",
       "      <td>AMD Ryzen 1 5827X</td>\n",
       "      <td>2017-07-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4435-3866-1076-3595</td>\n",
       "      <td>Intel Core i5-9457K</td>\n",
       "      <td>2019-03-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3489-7099-9906-8660</td>\n",
       "      <td>AMD Ryzen 4 3401X</td>\n",
       "      <td>2019-06-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8631-4500-5666-1510</td>\n",
       "      <td>Intel Core i6-7283K</td>\n",
       "      <td>2019-11-11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           credit_card                  cpu purchase_date\n",
       "0  5399-3484-4724-7187  Intel Core i1-7554K    2019-10-31\n",
       "1  1630-5261-6108-7631    AMD Ryzen 1 5827X    2017-07-16\n",
       "2  4435-3866-1076-3595  Intel Core i5-9457K    2019-03-24\n",
       "3  3489-7099-9906-8660    AMD Ryzen 4 3401X    2019-06-15\n",
       "4  8631-4500-5666-1510  Intel Core i6-7283K    2019-11-11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TABLE WILL BE USED FOR OUR JOIN:\n",
    "#Note:Can use np.column_stack instead of zip(),this is mainly for preference and speedup\n",
    "\n",
    "join_table_=pd.DataFrame(np.column_stack([fake_ppl['credit_card'],cpus,purchase_dates]),\n",
    "             columns=['credit_card','cpu','purchase_date'])\n",
    "\n",
    "join_table_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CREATE TABLE: staging_fake_cpu_purchases\n",
    "\n",
    "def create_staging_table(cursor):\n",
    "    cursor.execute(\"\"\"\n",
    "        DROP TABLE IF EXISTS staging_fake_cpu_purchases;\n",
    "        CREATE UNLOGGED TABLE staging_fake_cpu_purchases (\n",
    "            credit_card      TEXT PRIMARY KEY,\n",
    "            cpu              TEXT,\n",
    "            purchase_date    DATE NOT NULL\n",
    "        );\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_fake_ppl_table(cursor):\n",
    "    cursor.execute(\"\"\"\n",
    "        DROP TABLE IF EXISTS staging_fake_ppl;\n",
    "        CREATE UNLOGGED TABLE staging_fake_ppl (\n",
    "            credit_card         TEXT PRIMARY KEY,\n",
    "            email               TEXT,\n",
    "            first_name          TEXT,\n",
    "            last_name           TEXT,\n",
    "            primary_phone       TEXT\n",
    "        );\"\"\")\n",
    "\n",
    "with conn.cursor() as cursor:\n",
    "    create_fake_ppl_table(cursor)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sending Fake CPU Purchases  to PSQL:\n",
    "+ First convert to a .CSV(), then use the function `send_csv_to_psql` to send the data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "join_table_.to_csv('cpu_purchase.csv',index=False)\n",
    "\n",
    "\n",
    "def send_csv_to_psql(connection,csv,table_):\n",
    "    sql = \"COPY %s FROM STDIN WITH CSV HEADER DELIMITER AS ','\"\n",
    "    file = open(csv, \"r\")\n",
    "    table = table_\n",
    "    with connection.cursor() as cur:\n",
    "        cur.execute(\"truncate \" + table + \";\")  # avoiding uploading duplicate data!\n",
    "        cur.copy_expert(sql=sql % table, file=file)\n",
    "        conn.commit()\n",
    "#         cur.close() # Omit these to lines because we don't want to finish connection yet\n",
    "#         conn.close()\n",
    "    return conn.commit()\n",
    "\n",
    "\n",
    "# Sending Fake Purchases to PSQL From Python:\n",
    "send_csv_to_psql(conn,'cpu_purchase.csv','staging_fake_cpu_purchases')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sending Fake People to PSQL FROM Python:\n",
    "\n",
    "send_csv_to_psql(conn,'noIndx.csv','staging_fake_ppl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simple Query with Psycog2:¶\n",
    "when doing a SELECT query use: fetchone( ), fetchall( ) or fetchmany( ) methods\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187',\n",
       " 'gso@qiegan.sqe',\n",
       " 'Donyell Ann',\n",
       " 'Ospina',\n",
       " '5219459148')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query TO Verify: fake people was inserted into psql\n",
    "sql_=\"SELECT * FROM staging_fake_ppl\"\n",
    "cur.execute(sql_)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187', 'Intel Core i1-7554K', datetime.date(2019, 10, 31))"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sq_=\"SELECT * FROM staging_fake_cpu_purchases\"\n",
    "cur.execute(sq_)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inner Join:\n",
    "+ We will take the CPU purchases table and fake people table and join based on what is in \n",
    "common. For us that will be a Primary Key, of a credit card number.\n",
    "\n",
    "This is our Query: It cannot fit in the line to explain:\n",
    " + First: select all from both tables\n",
    " + Second: Inner Join (*common column*) between both\n",
    " + Third: Declare what we will join based on for both\n",
    "\n",
    "`sql_= \"SELECT staging_fake_cpu_purchases .*,staging_fake_ppl FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "`\n",
    "\n",
    "\n",
    "`sql_= \"SELECT staging_fake_cpu_purchases.cpu,staging_fake_cpu_purchases.purchase_date,staging_fake_ppl .* FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "`\n",
    "\n",
    "[Explaining How Joins Work](https://dzone.com/articles/how-to-perform-joins-in-apache-hive)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example 01: Inner Join (*Duplicate*) Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187',\n",
       " 'Intel Core i1-7554K',\n",
       " datetime.date(2019, 10, 31),\n",
       " '5399-3484-4724-7187',\n",
       " 'gso@qiegan.sqe',\n",
       " 'Donyell Ann',\n",
       " 'Ospina',\n",
       " '5219459148')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_= \"SELECT staging_fake_cpu_purchases .*,staging_fake_ppl .* FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "\n",
    "cur.execute(sql_)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example 02: Your data from one table shows up at string\n",
    "+ Your entire entry for each row is stored as a sting separated by commas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187',\n",
       " 'Intel Core i1-7554K',\n",
       " datetime.date(2019, 10, 31),\n",
       " '(5399-3484-4724-7187,gso@qiegan.sqe,\"Donyell Ann\",Ospina,5219459148)')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_= \"SELECT staging_fake_cpu_purchases .*,staging_fake_ppl FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "\n",
    "cur.execute(sql_)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example 03: Fixing the Data to your proper format of `INNER JOIN`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187',\n",
       " 'gso@qiegan.sqe',\n",
       " 'Donyell Ann',\n",
       " 'Ospina',\n",
       " '5219459148',\n",
       " 'Intel Core i1-7554K',\n",
       " datetime.date(2019, 10, 31))"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"SELECT staging_fake_ppl .*, staging_fake_cpu_purchases.cpu,staging_fake_cpu_purchases.purchase_date FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "cur.execute(sql)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Table from a Join:\n",
    "+ This can be done in two ways, `Create Temp Table` or `Create Table`. Depeding on your needs.\n",
    "\n",
    "+ A `Temp` table will not be stored after you exit psql. It is useful when you would like to run a query based on data you access often, but do not need to have a copy.\n",
    "\n",
    "+ Creating a new table from joined data is useful, when you access the data enough where you need a copy. This can be useful to store separately from original separated tables.\n",
    "\n",
    "\n",
    "**Ex.)**\n",
    "\n",
    "` \"CREATE TABLE ppl_cpu_purchases AS SELECT staging_fake_ppl .*,staging_fake_cpu_purchases.cpu,staging_fake_cpu_purchases.purchase_date FROM staging_fake_cpu_purchases INNER JOIN staging_fake_ppl ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We are creating a NEW Table from our join.\n",
    "+ The columns will be Everything from fake_ppl (Credit_card,Name:first,last,phone) and from cpu_purchases: (credit_card,cpu_type,date).\n",
    "\n",
    "+ The Column we INNER JOIN will not be duplicated\n",
    "\n",
    "[Help](https://www.postgresqltutorial.com/postgresql-create-table-as/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "sql_c= \"CREATE TABLE ppl_cpu_purchases AS SELECT staging_fake_ppl .*,staging_fake_cpu_purchases.cpu,staging_fake_cpu_purchases.purchase_date FROM staging_fake_ppl INNER JOIN staging_fake_cpu_purchases ON staging_fake_ppl.credit_card=staging_fake_cpu_purchases.credit_card\"\n",
    "\n",
    "cur.execute(sql_c)\n",
    "# cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('5399-3484-4724-7187',\n",
       "  'gso@qiegan.sqe',\n",
       "  'Donyell Ann',\n",
       "  'Ospina',\n",
       "  '5219459148',\n",
       "  'Intel Core i1-7554K',\n",
       "  datetime.date(2019, 10, 31)),\n",
       " ('1630-5261-6108-7631',\n",
       "  'xnji@gfruaxqnvm.fha',\n",
       "  'Bishop',\n",
       "  'Siyed',\n",
       "  '4164254716',\n",
       "  'AMD Ryzen 1 5827X',\n",
       "  datetime.date(2017, 7, 16)),\n",
       " ('4435-3866-1076-3595',\n",
       "  'dvyco@tkzhsop.zxg',\n",
       "  'Connor',\n",
       "  'Powers',\n",
       "  '3627413915',\n",
       "  'Intel Core i5-9457K',\n",
       "  datetime.date(2019, 3, 24))]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sq=\"SELECT * FROM ppl_cpu_purchases LIMIT 3\"\n",
    "cur.execute(sq)\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query by Date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Donyell Ann',\n",
       " 'Ospina',\n",
       " datetime.timedelta(days=209, seconds=53741, microseconds=778610))"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the days elapsed for an order, printing name and date:\n",
    "\n",
    "sq=\"SELECT first_name, last_name, now() - purchase_date as diff FROM ppl_cpu_purchases\"\n",
    "\n",
    "cur.execute(sq)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('5399-3484-4724-7187',\n",
       " 'gso@qiegan.sqe',\n",
       " 'Donyell Ann',\n",
       " 'Ospina',\n",
       " '5219459148',\n",
       " 'Intel Core i1-7554K',\n",
       " datetime.date(2019, 10, 31))"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Query Date Range:\n",
    "\n",
    "q=\"select * from ppl_cpu_purchases WHERE purchase_date BETWEEN '2019-05-01' and now()\"\n",
    "cur.execute(q)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('4581-8717-5316-8278',\n",
       " 'xcukr@msre.uln',\n",
       " 'Christina',\n",
       " 'Trustee',\n",
       " '5294387139',\n",
       " 'AMD Ryzen 7 8559X',\n",
       " datetime.date(2020, 5, 21))"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find All Purchases in the Last 10 Days:\n",
    "\n",
    "d=\"SELECT * FROM ppl_cpu_purchases WHERE purchase_date > current_date - interval '10' day\"\n",
    "\n",
    "cur.execute(d)\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Thanks for watching and Don't Forget TO<font color=red> SUBscribe</font>\n",
    "\n",
    "+ Throw a <font color=red>LIKE</font> & Leave a <font color=red>comment</font>\n",
    "\n",
    "`---------------------------------------------------------------`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Citations & Help:\n",
    "\n",
    "https://www.postgresqltutorial.com/postgresql-create-table-as/\n",
    "\n",
    "https://www.postgresqltutorial.com/postgresql-show-tables/\n",
    "\n",
    "https://www.postgresqltutorial.com/postgresql-date/"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}