04-CRUD using SQLAlchemy Core.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/pp/tuts/sqlalchemy/env/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
      "  \"\"\")\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\n",
    "    Column, DateTime, ForeignKey, Numeric, CheckConstraint\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "metadata = MetaData()\n",
    "\n",
    "engine = create_engine(\"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts\")\n",
    "\n",
    "customers = Table('customers', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('first_name', String(100), nullable=False),\n",
    "    Column('last_name', String(100), nullable=False),\n",
    "    Column('username', String(50), nullable=False),\n",
    "    Column('email', String(200), nullable=False),\n",
    "    Column('address', String(200), nullable=False),\n",
    "    Column('town', String(50), nullable=False),\n",
    "    Column('created_on', DateTime(), default=datetime.now),\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    ")\n",
    "\n",
    "\n",
    "items = Table('items', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('name', String(200), nullable=False),\n",
    "    Column('cost_price', Numeric(10, 2), nullable=False),\n",
    "    Column('selling_price', Numeric(10, 2),  nullable=False),\n",
    "    Column('quantity', Integer(), nullable=False),\n",
    "    CheckConstraint('quantity > 0', name='quantity_check')\n",
    ")\n",
    "\n",
    "\n",
    "orders = Table('orders', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('customer_id', ForeignKey('customers.id')),\n",
    "    Column('date_placed', DateTime(), default=datetime.now),\n",
    "    Column('date_shipped', DateTime())\n",
    ")\n",
    "\n",
    "\n",
    "order_lines = Table('order_lines', metadata,\n",
    "    Column('id', Integer(), primary_key=True),\n",
    "    Column('order_id', ForeignKey('orders.id')),\n",
    "    Column('item_id', ForeignKey('items.id')),\n",
    "    Column('quantity', Integer())\n",
    ")\n",
    "\n",
    "\n",
    "metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ins = customers.insert().values(\n",
    "    first_name = 'John',\n",
    "    last_name = 'Green',\n",
    "    username = 'johngreen',\n",
    "    email = 'johngreen@mail.com',\n",
    "    address = '164 Hidden Valley Road',\n",
    "    town = 'Norfolk'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO customers (first_name, last_name, username, email, address, town, created_on, updated_on) VALUES (:first_name, :last_name, :username, :email, :address, :town, :created_on, :updated_on)'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'address': '164 Hidden Valley Road',\n",
       " 'created_on': None,\n",
       " 'email': 'johngreen@mail.com',\n",
       " 'first_name': 'John',\n",
       " 'last_name': 'Green',\n",
       " 'town': 'Norfolk',\n",
       " 'updated_on': None,\n",
       " 'username': 'johngreen'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ins.compile().params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.base.Connection at 0x7fcf080609e8>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x7fcf0806d128>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = engine.connect()\n",
    "conn\n",
    "r = conn.execute(ins)\n",
    "r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.inserted_primary_key\n",
    "type(r.inserted_primary_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# metadata.drop_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import insert\n",
    "\n",
    "ins = insert(customers).values(\n",
    "    first_name = 'Katherine',\n",
    "    last_name = 'Wilson',\n",
    "    username = 'katwilson',\n",
    "    email = 'katwilson@gmail.com',\n",
    "    address = '4685 West Side Avenue',\n",
    "    town = 'Peterbrugh'\n",
    ")\n",
    "\n",
    "r = conn.execute(ins)\n",
    "r.inserted_primary_key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Multiple Inserts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[3]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ins = insert(customers)\n",
    "\n",
    "r = conn.execute(ins, \n",
    "    first_name = \"Tim\", \n",
    "    last_name = \"Snyder\", \n",
    "    username = \"timsnyder\", \n",
    "    email = \"timsnyder@mail.com\",\n",
    "    address = '1611 Sundown Lane',\n",
    "    town = 'Langdale'\n",
    ")\n",
    "r.inserted_primary_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(ins, [\n",
    "        {\n",
    "            \"first_name\": \"John\", \n",
    "            \"last_name\": \"Lara\", \n",
    "            \"username\": \"johnlara\", \n",
    "            \"email\":\"johnlara@mail.com\", \n",
    "            \"address\": \"3073 Derek Drive\",\n",
    "            \"town\": \"Norfolk\"\n",
    "        },\n",
    "        {\n",
    "            \"first_name\": \"Sarah\", \n",
    "            \"last_name\": \"Tomlin\", \n",
    "            \"username\": \"sarahtomlin\", \n",
    "            \"email\":\"sarahtomlin@mail.com\",\n",
    "            \"address\": \"3572 Poplar Avenue\",\n",
    "            \"town\": \"Norfolk\"\n",
    "        },\n",
    "        {\n",
    "            \"first_name\": \"Pablo\", \n",
    "            \"last_name\": \"Gibson\", \n",
    "            \"username\": \"pablogibson\", \n",
    "            \"email\":\"pablogibson@mail.com\",\n",
    "            \"address\": \"3494 Murry Street\",\n",
    "            \"town\": \"Peterbrugh\"\n",
    "        },\n",
    "        {\n",
    "            \"first_name\": \"Pablo\", \n",
    "            \"last_name\": \"Lewis\", \n",
    "            \"username\": \"pablolewis\", \n",
    "            \"email\":\"pablolewis@mail.com\",\n",
    "            \"address\": \"3282 Jerry Toth Drive\",\n",
    "            \"town\": \"Peterbrugh\"\n",
    "        },\n",
    "    ])\n",
    "\n",
    "r.rowcount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_list = [\n",
    "    {\n",
    "        \"name\":\"Chair\",\n",
    "        \"cost_price\": 9.21,\n",
    "        \"selling_price\": 10.81,\n",
    "        \"quantity\": 10\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Pen\",\n",
    "        \"cost_price\": 3.45,\n",
    "        \"selling_price\": 4.51,\n",
    "        \"quantity\": 3\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Headphone\",\n",
    "        \"cost_price\": 15.52,\n",
    "        \"selling_price\": 16.81,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Travel Bag\",\n",
    "        \"cost_price\": 20.1,\n",
    "        \"selling_price\": 24.21,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Keyboard\",\n",
    "        \"cost_price\": 20.12,\n",
    "        \"selling_price\": 22.11,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Monitor\",\n",
    "        \"cost_price\": 200.14,\n",
    "        \"selling_price\": 212.89,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Watch\",\n",
    "        \"cost_price\": 100.58,\n",
    "        \"selling_price\": 104.41,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "    {\n",
    "        \"name\":\"Water Bottle\",\n",
    "        \"cost_price\": 20.89,\n",
    "        \"selling_price\": 25.00,\n",
    "        \"quantity\": 50\n",
    "    },\n",
    "]\n",
    "\n",
    "order_list = [\n",
    "    {\n",
    "        \"customer_id\": 1\n",
    "    },\n",
    "    {\n",
    "        \"customer_id\": 1\n",
    "    }\n",
    "]\n",
    "\n",
    "order_line_list = [\n",
    "    {\n",
    "        \"order_id\": 1,\n",
    "        \"item_id\": 1,\n",
    "        \"quantity\": 5\n",
    "    }, \n",
    "    {\n",
    "        \"order_id\": 1,\n",
    "        \"item_id\": 2,\n",
    "        \"quantity\": 2\n",
    "    }, \n",
    "    {\n",
    "        \"order_id\": 1,\n",
    "        \"item_id\": 3,\n",
    "        \"quantity\": 1\n",
    "    },\n",
    "    {\n",
    "        \"order_id\": 2,\n",
    "        \"item_id\": 1,\n",
    "        \"quantity\": 5\n",
    "    },\n",
    "    {\n",
    "        \"order_id\": 2,\n",
    "        \"item_id\": 2,\n",
    "        \"quantity\": 4\n",
    "    },\n",
    "]\n",
    "\n",
    "r = conn.execute(insert(items), items_list)\n",
    "r.rowcount\n",
    "r = conn.execute(insert(orders), order_list)\n",
    "r.rowcount\n",
    "r = conn.execute(insert(order_lines), order_line_list)\n",
    "r.rowcount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \\nFROM customers'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = customers.select()\n",
    "str(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \\nFROM customers'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "s = select([customers])\n",
    "str(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553)),\n",
       " (2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 8, 1, 17, 22, 4, 349494), datetime.datetime(2018, 8, 1, 17, 22, 4, 349530)),\n",
       " (4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783007), datetime.datetime(2018, 8, 1, 17, 22, 35, 783039)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783050), datetime.datetime(2018, 8, 1, 17, 22, 35, 783059)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783068), datetime.datetime(2018, 8, 1, 17, 22, 35, 783077)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783086), datetime.datetime(2018, 8, 1, 17, 22, 35, 783095))]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "r.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553))\n",
      "(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936))\n",
      "(3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 8, 1, 17, 22, 4, 349494), datetime.datetime(2018, 8, 1, 17, 22, 4, 349530))\n",
      "(4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783007), datetime.datetime(2018, 8, 1, 17, 22, 35, 783039))\n",
      "(5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783050), datetime.datetime(2018, 8, 1, 17, 22, 35, 783059))\n",
      "(6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783068), datetime.datetime(2018, 8, 1, 17, 22, 35, 783077))\n",
      "(7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783086), datetime.datetime(2018, 8, 1, 17, 22, 35, 783095))\n"
     ]
    }
   ],
   "source": [
    "rs = conn.execute(s)\n",
    "for row in rs:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = select([customers])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553))"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936))"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "r.fetchone()\n",
    "r.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### fetchmany()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553)),\n",
       " (2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 8, 1, 17, 22, 4, 349494), datetime.datetime(2018, 8, 1, 17, 22, 4, 349530))]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783007), datetime.datetime(2018, 8, 1, 17, 22, 35, 783039)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783050), datetime.datetime(2018, 8, 1, 17, 22, 35, 783059)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783068), datetime.datetime(2018, 8, 1, 17, 22, 35, 783077)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783086), datetime.datetime(2018, 8, 1, 17, 22, 35, 783095))]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "r.fetchmany(3)\n",
    "r.fetchmany(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "r.first()\n",
    "# r.first()  # the connection is now closed, the subsequent call to first() will result in an error"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### rowcount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "r.rowcount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'first_name',\n",
       " 'last_name',\n",
       " 'username',\n",
       " 'email',\n",
       " 'address',\n",
       " 'town',\n",
       " 'created_on',\n",
       " 'updated_on']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### scalar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r.scalar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553))"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "sqlalchemy.engine.result.RowProxy"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = conn.execute(s)\n",
    "row = r.fetchone()\n",
    "row\n",
    "type(row)\n",
    "row['id'], row['first_name']    # access column data via column name\n",
    "row[0], row[1]    # access column data via column index position\n",
    "row[customers.c.id], row[customers.c.first_name]    # access column data via Column object\n",
    "row.id, row.first_name    # access column data via attribute"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \\nFROM items \\nWHERE items.cost_price > :cost_price_1'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    items.c.cost_price > 20\n",
    ")\n",
    "\n",
    "str(s)\n",
    "r = conn.execute(s)\n",
    "r.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price + items.selling_price > :param_1 AND items.quantity > :quantity_1\n"
     ]
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "    where(items.c.cost_price + items.c.selling_price > 50).\\\n",
    "    where(items.c.quantity > 10)\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bitwise Operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price > :cost_price_1 OR items.quantity < :quantity_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(\n",
    "    (items.c.cost_price > 200 ) | \n",
    "    (items.c.quantity < 5)\n",
    ") \n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity != :quantity_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(    \n",
    "    ~(items.c.quantity == 50)\n",
    ") \n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity != :quantity_1 AND items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(\n",
    "    ~(items.c.quantity == 50) &\n",
    "    (items.c.cost_price < 20)\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Conjunctions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import and_, or_, not_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(    \n",
    "    and_(\n",
    "        items.c.quantity >= 50,\n",
    "        items.c.cost_price < 100,\n",
    "    )\n",
    ") \n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 OR items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(    \n",
    "    or_(\n",
    "        items.c.quantity >= 50,\n",
    "        items.c.cost_price < 100,\n",
    "    )\n",
    ") \n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1 AND items.name != :name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).\\\n",
    "where(    \n",
    "    and_(\n",
    "        items.c.quantity >= 50,\n",
    "        items.c.cost_price < 100,\n",
    "        not_(\n",
    "            items.c.name == 'Headphone'            \n",
    "        ),        \n",
    "    )\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Common Comparison Operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IS NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped \n",
      "FROM orders \n",
      "WHERE orders.date_shipped IS NULL\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), None),\n",
       " (2, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), None)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([orders]).where(\n",
    "    orders.c.date_shipped == None\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IS NOT NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped \n",
      "FROM orders \n",
      "WHERE orders.date_shipped IS NOT NULL\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([orders]).where(\n",
    "    orders.c.date_shipped != None\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name IN (:first_name_1, :first_name_2)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 13, 23, 26513), datetime.datetime(2018, 8, 1, 17, 13, 23, 26553)),\n",
       " (4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783007), datetime.datetime(2018, 8, 1, 17, 22, 35, 783039)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 8, 1, 17, 22, 35, 783050), datetime.datetime(2018, 8, 1, 17, 22, 35, 783059))]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([customers]).where(\n",
    "    customers.c.first_name.in_([\"Sarah\", \"John\"])\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT IN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name NOT IN (:first_name_1, :first_name_2)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 8, 1, 17, 22, 4, 349494), datetime.datetime(2018, 8, 1, 17, 22, 4, 349530)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783068), datetime.datetime(2018, 8, 1, 17, 22, 35, 783077)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783086), datetime.datetime(2018, 8, 1, 17, 22, 35, 783095))]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([customers]).where(\n",
    "    customers.c.first_name.notin_([\"Sarah\", \"John\"])\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### BETWEEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price BETWEEN :cost_price_1 AND :cost_price_2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    items.c.cost_price.between(10, 20)\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT BETWEEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price NOT BETWEEN :cost_price_1 AND :cost_price_2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    not_(items.c.cost_price.between(10, 20))\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LIKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    items.c.name.like(\"Wa%\")\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE lower(items.name) LIKE lower(:name_1)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    items.c.name.ilike(\"wa%\")\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT LIKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name NOT LIKE :name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    not_(items.c.name.like(\"wa%\"))\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordering Result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity > :quantity_1 ORDER BY items.cost_price\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    items.c.quantity > 10\n",
    ").order_by(items.c.cost_price)\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity > :quantity_1 ORDER BY items.cost_price DESC\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import desc\n",
    "\n",
    "s = select([items]).where(\n",
    "    items.c.quantity > 10\n",
    ").order_by(desc(items.c.cost_price))\n",
    "\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity, items.cost_price DESC\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).order_by(\n",
    "    items.c.quantity, \n",
    "    desc(items.c.cost_price)\n",
    ")\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limiting Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity\n",
      " LIMIT :param_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).order_by(\n",
    "    items.c.quantity\n",
    ").limit(2)\n",
    "\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity\n",
      " LIMIT :param_1 OFFSET :param_2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).order_by(\n",
    "    items.c.quantity\n",
    ").limit(2).offset(2)\n",
    "\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limiting Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.name, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['name', 'quantity']"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('Headphone', 50),\n",
       " ('Travel Bag', 50),\n",
       " ('Keyboard', 50),\n",
       " ('Monitor', 50),\n",
       " ('Watch', 50),\n",
       " ('Water Bottle', 50)]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items.c.name, items.c.quantity]).where(\n",
    "    items.c.quantity ==  50\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS anon_1 \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['name', 'quantity', 'anon_1']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('Headphone', 50, Decimal('84.05')),\n",
       " ('Travel Bag', 50, Decimal('121.05')),\n",
       " ('Keyboard', 50, Decimal('110.55')),\n",
       " ('Monitor', 50, Decimal('1064.45')),\n",
       " ('Watch', 50, Decimal('522.05')),\n",
       " ('Water Bottle', 50, Decimal('125.00'))]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items.c.name, items.c.quantity, items.c.selling_price * 5 ]).where(\n",
    "    items.c.quantity ==  50\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS price \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['name', 'quantity', 'price']"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('Headphone', 50, Decimal('84.05')),\n",
       " ('Travel Bag', 50, Decimal('121.05')),\n",
       " ('Keyboard', 50, Decimal('110.55')),\n",
       " ('Monitor', 50, Decimal('1064.45')),\n",
       " ('Watch', 50, Decimal('522.05')),\n",
       " ('Water Bottle', 50, Decimal('125.00'))]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([\n",
    "        items.c.name, \n",
    "        items.c.quantity, \n",
    "        (items.c.selling_price * 5).label('price') \n",
    "    ]).where(\n",
    "    items.c.quantity ==  50\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing Built-in Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['timeofday_1',\n",
       " 'localtime_1',\n",
       " 'current_timestamp_1',\n",
       " 'date_part_1',\n",
       " 'now_1',\n",
       " 'pow_1',\n",
       " 'sqrt_1',\n",
       " 'pi_1',\n",
       " 'floor_1',\n",
       " 'ceil_1',\n",
       " 'lower_1',\n",
       " 'upper_1',\n",
       " 'length_1',\n",
       " 'trim_1',\n",
       " 'chr_1']"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('Wed Aug 01 17:58:55.627110 2018 IST', datetime.time(17, 27, 1, 784686), datetime.datetime(2018, 8, 1, 17, 27, 1, 784686, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 8.0, datetime.datetime(2018, 8, 1, 17, 27, 1, 784686, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 16.0, 21.0, 3.14159265358979, 3.0, 4.0, 'abc', 'ABC', 3, 'ab c', 'A')]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import func\n",
    "\n",
    "c = [\n",
    "    \n",
    "    ##  date/time functions  ##\n",
    "    \n",
    "    func.timeofday(),\n",
    "    func.localtime(),\n",
    "    func.current_timestamp(),    \n",
    "    func.date_part(\"month\", func.now()),        \n",
    "    func.now(),\n",
    "    \n",
    "    ##  mathematical functions  ##\n",
    "    \n",
    "    func.pow(4,2),\n",
    "    func.sqrt(441),\n",
    "    func.pi(),        \n",
    "    func.floor(func.pi()),\n",
    "    func.ceil(func.pi()),\n",
    "    \n",
    "    ##  string functions  ##\n",
    "    \n",
    "    func.lower(\"ABC\"),\n",
    "    func.upper(\"abc\"),\n",
    "    func.length(\"abc\"),\n",
    "    func.trim(\"  ab c  \"),    \n",
    "    func.chr(65),        \n",
    "]\n",
    "\n",
    "s = select(c)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT sum(items.quantity) AS sum_1, avg(items.quantity) AS avg_1, max(items.quantity) AS max_1, min(items.quantity) AS min_1, count(customers.id) AS count_1 \n",
      "FROM items, customers\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['sum_1', 'avg_1', 'max_1', 'min_1', 'count_1']"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(2191, Decimal('39.1250000000000000'), 50, 3, 56)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import func\n",
    "\n",
    "c = [ \n",
    "    func.sum(items.c.quantity),\n",
    "    func.avg(items.c.quantity),    \n",
    "    func.max(items.c.quantity),\n",
    "    func.min(items.c.quantity),\n",
    "    func.count(customers.c.id),    \n",
    "]\n",
    "\n",
    "s = select(c)\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(:count_1) AS count, customers.town \n",
      "FROM customers GROUP BY customers.town\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Langdale'), (3, 'Peterbrugh'), (3, 'Norfolk')]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import func\n",
    "\n",
    "c = [ \n",
    "    func.count(\"*\").label('count'),         \n",
    "    customers.c.town      \n",
    "]\n",
    "\n",
    "s = select(c).group_by(customers.c.town)\n",
    "\n",
    "print(s)\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(:count_1) AS count, customers.town \n",
      "FROM customers GROUP BY customers.town \n",
      "HAVING count(:count_2) > :count_3\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['count', 'town']"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'Peterbrugh'), (3, 'Norfolk')]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import func\n",
    "\n",
    "c = [\n",
    "    func.count(\"*\").label('count'),\n",
    "    customers.c.town      \n",
    "]\n",
    "\n",
    "s = select(c).group_by(customers.c.town).having(func.count(\"*\") > 2)\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customers JOIN orders ON customers.id = orders.customer_id\n"
     ]
    }
   ],
   "source": [
    "print(customers.join(orders))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customers JOIN items ON customers.address LIKE customers.first_name || :first_name_1\n"
     ]
    }
   ],
   "source": [
    "print(customers.join(items,\n",
    "                 customers.c.address.like(customers.c.first_name + '%')\n",
    "             )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id, customers.first_name \n",
      "FROM customers\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['id', 'first_name']"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'John'),\n",
       " (2, 'Katherine'),\n",
       " (3, 'Tim'),\n",
       " (4, 'John'),\n",
       " (5, 'Sarah'),\n",
       " (6, 'Pablo'),\n",
       " (7, 'Pablo')]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([        \n",
    "    customers.c.id,\n",
    "    customers.c.first_name\n",
    "]).select_from(\n",
    "    customers\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT orders.id, orders.date_placed \n",
      "FROM orders JOIN customers ON customers.id = orders.customer_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['id', 'date_placed']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277)),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299))]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([\n",
    "            orders.c.id,\n",
    "            orders.c.date_placed\n",
    "]).select_from(\n",
    "    orders.join(customers)\n",
    ").where(\n",
    "    and_(\n",
    "        customers.c.first_name == \"John\",\n",
    "        customers.c.last_name == \"Green\",\n",
    "    )\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT orders.id AS order_id, orders.date_placed, order_lines.quantity, items.name \n",
      "FROM orders JOIN customers ON customers.id = orders.customer_id JOIN order_lines ON orders.id = order_lines.order_id JOIN items ON items.id = order_lines.item_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['order_id', 'date_placed', 'quantity', 'name']"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 5, 'Chair'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 2, 'Pen'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 1, 'Headphone'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 5, 'Chair'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 4, 'Pen')]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([        \n",
    "    orders.c.id.label('order_id'),\n",
    "    orders.c.date_placed,\n",
    "    order_lines.c.quantity,\n",
    "    items.c.name,\n",
    "            \n",
    "]).select_from(\n",
    "    orders.join(customers).join(order_lines).join(items)\n",
    ").where(\n",
    "    and_(\n",
    "        customers.c.first_name == \"John\",\n",
    "        customers.c.last_name == \"Green\",\n",
    "    )\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('Katherine', None),\n",
       " ('Sarah', None),\n",
       " ('Pablo', None),\n",
       " ('John', None),\n",
       " ('Tim', None),\n",
       " ('Pablo', None)]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([        \n",
    "    customers.c.first_name,\n",
    "    orders.c.id,\n",
    "]).select_from(\n",
    "    customers.outerjoin(orders)\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM orders LEFT OUTER JOIN customers ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('John', 1), ('John', 2)]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([        \n",
    "    customers.c.first_name,\n",
    "    orders.c.id,\n",
    "]).select_from(\n",
    "    orders.outerjoin(customers)\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM orders FULL OUTER JOIN customers ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('Katherine', None),\n",
       " ('Sarah', None),\n",
       " ('Pablo', None),\n",
       " ('John', None),\n",
       " ('Tim', None),\n",
       " ('Pablo', None)]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([        \n",
    "    customers.c.first_name,\n",
    "    orders.c.id,\n",
    "]).select_from(\n",
    "    orders.outerjoin(customers, full=True)\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UPDATE items SET selling_price=:selling_price, quantity=:quantity WHERE items.name = :name_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import update\n",
    "\n",
    "s = update(items).where(\n",
    "    items.c.name == 'Water Bottle'\n",
    ").values(\n",
    "    selling_price = 30,\n",
    "    quantity = 60,\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.rowcount  # count of rows updated"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DELETE FROM customers WHERE customers.username LIKE :username_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import delete\n",
    "\n",
    "s = delete(customers).where(\n",
    "    customers.c.username.like('pablo%')\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.rowcount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.town \n",
      "FROM customers \n",
      "WHERE customers.id < :id_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Norfolk',), ('Peterbrugh',), ('Langdale',), ('Norfolk',), ('Norfolk',)]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT DISTINCT customers.town \n",
      "FROM customers \n",
      "WHERE customers.id < :id_1\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Peterbrugh',), ('Langdale',), ('Norfolk',)]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# without DISTINCT\n",
    "\n",
    "s = select([customers.c.town]).where(customers.c.id  < 10)\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.fetchall()\n",
    "\n",
    "\n",
    "# with DISTINCT\n",
    "\n",
    "s = select([customers.c.town]).where(customers.c.id  < 10).distinct()\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(DISTINCT customers.town) AS count_1, count(customers.town) AS count_2 \n",
      "FROM customers\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['count_1', 'count_2']"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(3, 5)]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import distinct\n",
    "\n",
    "s = select([        \n",
    "    func.count(distinct(customers.c.town)),\n",
    "    func.count(customers.c.town)\n",
    "])\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Casting "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT CAST(pi() AS INTEGER) AS anon_1, CAST(pi() AS NUMERIC(10, 2)) AS anon_2, CAST(:param_1 AS DATETIME) AS anon_3, CAST(:param_2 AS DATE) AS anon_4\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['anon_1', 'anon_2', 'anon_3', 'anon_4']"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(3, Decimal('3.14'), datetime.datetime(2010, 12, 1, 0, 0), datetime.date(2010, 12, 1))]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import cast, Date\n",
    "\n",
    "s = select([\n",
    "    cast(func.pi(), Integer),\n",
    "    cast(func.pi(), Numeric(10,2)),\n",
    "    cast(\"2010-12-01\", DateTime),\n",
    "    cast(\"2010-12-01\", Date),\n",
    "])    \n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1 UNION SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_2 ORDER BY id DESC\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle'),\n",
       " (7, 'Watch'),\n",
       " (5, 'Keyboard'),\n",
       " (4, 'Travel Bag'),\n",
       " (3, 'Headphone'),\n",
       " (2, 'Pen')]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import union\n",
    "\n",
    "s = union(\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"Wa%\")),\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"%e%\")),\n",
    ").order_by(desc(\"id\"))\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1 UNION ALL SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_2 ORDER BY id DESC\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle'),\n",
       " (8, 'Water Bottle'),\n",
       " (7, 'Watch'),\n",
       " (5, 'Keyboard'),\n",
       " (4, 'Travel Bag'),\n",
       " (3, 'Headphone'),\n",
       " (2, 'Pen')]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import union_all\n",
    "\n",
    "s = union_all(\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"Wa%\")),\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"%e%\")),\n",
    ").order_by(desc(\"id\"))\n",
    "\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Subqueries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.id IN (SELECT order_lines.item_id \n",
      "FROM customers JOIN orders ON customers.id = orders.customer_id JOIN order_lines ON orders.id = order_lines.order_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1 AND orders.id = :id_1)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(3, 'Headphone'), (1, 'Chair'), (2, 'Pen')]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items.c.id, items.c.name]).where(\n",
    "    items.c.id.in_( \n",
    "        select([order_lines.c.item_id]).select_from(customers.join(orders).join(order_lines)).where(    \n",
    "                and_(\n",
    "                    customers.c.first_name == 'John',\n",
    "                    customers.c.last_name == 'Green',\n",
    "                    orders.c.id == 1\n",
    "                )    \n",
    "        )\n",
    "    )\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Chair'), (2, 'Pen'), (3, 'Headphone')]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items.c.id, items.c.name]).select_from(customers.join(orders).join(order_lines).join(items)).where(    \n",
    "        and_(\n",
    "            customers.c.first_name == 'John',\n",
    "            customers.c.last_name == 'Green',\n",
    "            orders.c.id == 1\n",
    "        )    \n",
    ")\n",
    "\n",
    "rs = conn.execute(s)\n",
    "rs.keys()\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Raw Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT\n",
      "    orders.id as \"Order ID\", orders.date_placed, items.id, items.name\n",
      "FROM\n",
      "    customers\n",
      "INNER JOIN orders ON customers.id = orders.customer_id\n",
      "INNER JOIN order_lines ON order_lines.order_id = orders.id\n",
      "INNER JOIN items ON items.id= order_lines.item_id\n",
      "where customers.first_name = :first_name and customers.last_name = :last_name\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 1, 'Chair'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 2, 'Pen'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 3, 'Headphone'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 1, 'Chair'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 2, 'Pen')]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy.sql import text\n",
    "\n",
    "s = text(\n",
    "\"\"\"\n",
    "SELECT\n",
    "    orders.id as \"Order ID\", orders.date_placed, items.id, items.name\n",
    "FROM\n",
    "    customers\n",
    "INNER JOIN orders ON customers.id = orders.customer_id\n",
    "INNER JOIN order_lines ON order_lines.order_id = orders.id\n",
    "INNER JOIN items ON items.id= order_lines.item_id\n",
    "where customers.first_name = :first_name and customers.last_name = :last_name\n",
    "\"\"\"\n",
    ")\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s, first_name=\"John\", last_name='Green')\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name like 'Wa%' ORDER BY items.id desc\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle', Decimal('20.89'), Decimal('30.00'), 60),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50)]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([items]).where(\n",
    "    text(\"items.name like 'Wa%'\")\n",
    ").order_by(text(\"items.id desc\"))\n",
    "\n",
    "print(s)\n",
    "rs = conn.execute(s)\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), None),\n",
       " (2, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), None)]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rs = conn.execute(\"select * from  orders;\")\n",
    "rs.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.exc import IntegrityError\n",
    "\n",
    "\n",
    "def dispatch_order(order_id):\n",
    "\n",
    "    # check whether order_id is valid or not\n",
    "    r = conn.execute(select([func.count(\"*\")]).where(orders.c.id == order_id))\n",
    "    if not r.scalar():\n",
    "        raise ValueError(\"Invalid order id: {}\".format(order_id))\n",
    "\n",
    "    # fetch items in the order\n",
    "    s = select([order_lines.c.item_id, order_lines.c.quantity]).where(\n",
    "        order_lines.c.order_id == order_id\n",
    "    )\n",
    "\n",
    "    rs = conn.execute(s)\n",
    "    ordered_items_list = rs.fetchall()\n",
    "\n",
    "    # start transaction\n",
    "    t = conn.begin()\n",
    "\n",
    "    try:\n",
    "        for i in ordered_items_list:\n",
    "            u = update(items).where(\n",
    "                items.c.id == i.item_id\n",
    "            ).values(quantity = items.c.quantity - i.quantity)\n",
    "\n",
    "            rs = conn.execute(u)\n",
    "\n",
    "        u = update(orders).where(orders.c.id == order_id).values(date_shipped=datetime.now())\n",
    "        rs = conn.execute(u)\n",
    "        t.commit()\n",
    "        print(\"Transaction completed.\")\n",
    "\n",
    "    except IntegrityError as e:\n",
    "        print(e)\n",
    "        t.rollback()\n",
    "        print(\"Transaction failed.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transaction completed.\n"
     ]
    }
   ],
   "source": [
    "dispatch_order(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(psycopg2.IntegrityError) new row for relation \"items\" violates check constraint \"quantity_check\"\n",
      "DETAIL:  Failing row contains (1, Chair, 9.21, 10.81, 0).\n",
      " [SQL: 'UPDATE items SET quantity=(items.quantity - %(quantity_1)s) WHERE items.id = %(id_1)s'] [parameters: {'quantity_1': 5, 'id_1': 1}] (Background on this error at: http://sqlalche.me/e/gkpj)\n",
      "Transaction failed.\n"
     ]
    }
   ],
   "source": [
    "dispatch_order(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata.drop_all(engine)"
   ]
  },
  {
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}