Advanced SQL for Application Development/03.Object Relational Mapping with SQL-Alchemy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import os\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import Column, String, Integer\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connect to DB with SQL Alchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "USER = os.environ['POSTGRE_USERNAME']\n",
    "PASSWORD = os.environ['POSTGRE_PASSWORD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('postgres', 'admin')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "USER, PASSWORD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### create engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_conn_string = 'postgresql+psycopg2://'+USER+':'+PASSWORD+'@localhost/db_Advanced_SQL_Application_Development'\n",
    "engine = create_engine(db_conn_string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### create session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session = sessionmaker(engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create base class\n",
    "base = declarative_base()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# product class\n",
    "class Product(base):\n",
    "    __tablename__ = 'products'\n",
    "    product_id = Column(Integer, primary_key=True)\n",
    "    product_name = Column(String)\n",
    "    product_type = Column(String)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query products using Product class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "products = session.query(Product)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.orm.query.Query at 0xfdc8b5fdc0>"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reflector oven\n",
      "Convection microwave\n",
      "Pressure fryer\n",
      "Multicooker\n",
      "Food steamer\n",
      "Chapati maker\n",
      "Mess kit\n",
      "Rotisserie\n",
      "Sous-vide cooker\n",
      "Rocket mass heater\n",
      "Cheesemelter\n",
      "Hot plate\n",
      "Flattop grill\n",
      "Wet grinder\n",
      "Masonry oven\n",
      "Chocolatera\n",
      "Turkey fryer\n",
      "Bread machine\n",
      "Roasting jack\n",
      "Brasero (heater)\n",
      "Susceptor\n",
      "Slow cooker\n",
      "Butane torch\n",
      "Microwave oven\n",
      "Solar cooker\n",
      "Deep fryer\n",
      "Popcorn maker\n",
      "Russian oven\n",
      "Clome oven\n",
      "Convection oven\n",
      "Beehive oven\n",
      "Toaster and toaster ovens\n",
      "Field kitchen\n",
      "Corn roaster\n",
      "Self-cleaning oven\n",
      "Wood-fired oven\n",
      "Kitchener range\n",
      "Rice polisher\n",
      "Soy milk maker\n",
      "Crepe maker\n",
      "Oven\n",
      "Hot box (appliance)\n",
      "Combi steamer\n",
      "Rice cooker\n",
      "Fire pot\n",
      "Salamander broiler\n",
      "Vacuum fryer\n",
      "Fufu Machine\n",
      "Tabun oven\n",
      "Pancake machine\n",
      "Barbecue grill\n",
      "Panini sandwich grill\n",
      "Air fryer\n",
      "Chorkor oven\n",
      "Communal oven\n",
      "Pressure cooker\n",
      "Halogen oven\n",
      "Instant Pot\n",
      "Waffle iron\n",
      "Stove\n",
      "Earth oven\n",
      "Electric cooker\n",
      "Espresso machine\n",
      "Coffee pot\n"
     ]
    }
   ],
   "source": [
    "for product in products:\n",
    "    print(product.product_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Search for specific product\n",
    "- filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "products = session.query(Product).filter(Product.product_type=='fryer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pressure fryer\n",
      "Turkey fryer\n",
      "Deep fryer\n",
      "Vacuum fryer\n",
      "Air fryer\n"
     ]
    }
   ],
   "source": [
    "for product in products:\n",
    "    print(product.product_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----------\n",
    "----------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenge\n",
    "define a class using SQLalchemy that corresponds to a supplier table which has four column:\n",
    "\n",
    "- supplier_id (integer)\n",
    "- supplier_name(variable character string)\n",
    "- supplier_region(varaible character string)\n",
    "- supplier_level (integer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Supplier(base):\n",
    "    __tablename__ = 'suppliers'\n",
    "    supplier_id = Column(Integer, primary_key=True)\n",
    "    supplier_name = Column(String)\n",
    "    supplier_region = Column(String)\n",
    "    supplier_level = Column(Integer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}