ch02.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,\n",
    "                        DateTime, ForeignKey, Boolean, create_engine)\n",
    "metadata = MetaData()\n",
    "\n",
    "cookies = Table('cookies', metadata,\n",
    "    Column('cookie_id', Integer(), primary_key=True),\n",
    "    Column('cookie_name', String(50), index=True),\n",
    "    Column('cookie_recipe_url', String(255)),\n",
    "    Column('cookie_sku', String(55)),\n",
    "    Column('quantity', Integer()),\n",
    "    Column('unit_cost', Numeric(12, 2))\n",
    ")\n",
    "\n",
    "users = Table('users', metadata,\n",
    "    Column('user_id', Integer(), primary_key=True),\n",
    "    Column('username', String(15), nullable=False, unique=True),\n",
    "    Column('email_address', String(255), nullable=False),\n",
    "    Column('phone', String(20), nullable=False),\n",
    "    Column('password', String(25), nullable=False),\n",
    "    Column('created_on', DateTime(), default=datetime.now),\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    ")\n",
    "\n",
    "orders = Table('orders', metadata,\n",
    "    Column('order_id', Integer(), primary_key=True),\n",
    "    Column('user_id', ForeignKey('users.user_id')),\n",
    "    Column('shipped', Boolean(), default=False)\n",
    ")\n",
    "\n",
    "line_items = Table('line_items', metadata,\n",
    "    Column('line_items_id', Integer(), primary_key=True),\n",
    "    Column('order_id', ForeignKey('orders.order_id')),\n",
    "    Column('cookie_id', ForeignKey('cookies.cookie_id')),\n",
    "    Column('quantity', Integer()),\n",
    "    Column('extended_cost', Numeric(12, 2))\n",
    ")\n",
    "\n",
    "engine = create_engine('sqlite:///:memory:')\n",
    "metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO cookies (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost) VALUES (:cookie_name, :cookie_recipe_url, :cookie_sku, :quantity, :unit_cost)\n"
     ]
    }
   ],
   "source": [
    "ins = cookies.insert().values(\n",
    "    cookie_name=\"chocolate chip\",\n",
    "    cookie_recipe_url=\"http://some.aweso.me/cookie/recipe.html\",\n",
    "    cookie_sku=\"CC01\",\n",
    "    quantity=\"12\",\n",
    "    unit_cost=\"0.50\"\n",
    ")\n",
    "print(str(ins))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'cookie_name': 'chocolate chip',\n",
       " 'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html',\n",
       " 'cookie_sku': 'CC01',\n",
       " 'quantity': '12',\n",
       " 'unit_cost': '0.50'}"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ins.compile().params"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "result = connection.execute(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.inserted_primary_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO cookies (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost) VALUES (:cookie_name, :cookie_recipe_url, :cookie_sku, :quantity, :unit_cost)'"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import insert\n",
    "ins = insert(cookies).values(\n",
    "    cookie_name=\"chocolate chip\",\n",
    "    cookie_recipe_url=\"http://some.aweso.me/cookie/recipe.html\",\n",
    "    cookie_sku=\"CC01\",\n",
    "    quantity=\"12\",\n",
    "    unit_cost=\"0.50\"\n",
    ")\n",
    "str(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = cookies.insert()\n",
    "result = connection.execute(ins, cookie_name='dark chocolate chip',\n",
    "                            cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',\n",
    "                            cookie_sku='CC02',\n",
    "                            quantity='1',\n",
    "                            unit_cost='0.75')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.inserted_primary_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "inventory_list = [\n",
    "    {\n",
    "        'cookie_name': 'peanut butter',\n",
    "        'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',\n",
    "        'cookie_sku': 'PB01',\n",
    "        'quantity': '24',\n",
    "        'unit_cost': '0.25'\n",
    "    },\n",
    "    {\n",
    "        'cookie_name': 'oatmeal raisin',\n",
    "        'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html',\n",
    "        'cookie_sku': 'EWW01',\n",
    "        'quantity': '100',\n",
    "        'unit_cost': '1.00'\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "result = connection.execute(ins, inventory_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.sql import select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "s = select([cookies])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT cookies.cookie_id, cookies.cookie_name, cookies.cookie_recipe_url, cookies.cookie_sku, cookies.quantity, cookies.unit_cost \\nFROM cookies'"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python34\\lib\\site-packages\\sqlalchemy\\sql\\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "rp = connection.execute(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "results = rp.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "first_row = results[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'chocolate chip'"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_row[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'chocolate chip'"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_row.cookie_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'chocolate chip'"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_row[cookies.c.cookie_name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "s = cookies.select()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rp = connection.execute(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip\n",
      "dark chocolate chip\n",
      "peanut butter\n",
      "oatmeal raisin\n"
     ]
    }
   ],
   "source": [
    "for record in rp:\n",
    "    print(record.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['cookie_name', 'quantity']\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "rp = connection.execute(s)\n",
    "print(rp.keys())\n",
    "results = rp.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('chocolate chip', 12),\n",
       " ('dark chocolate chip', 1),\n",
       " ('peanut butter', 24),\n",
       " ('oatmeal raisin', 100)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 - dark chocolate chip\n",
      "12 - chocolate chip\n",
      "24 - peanut butter\n",
      "100 - oatmeal raisin\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "s = s.order_by(cookies.c.quantity, cookies.c.cookie_name)\n",
    "rp = connection.execute(s)\n",
    "for cookie in rp:\n",
    "    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 - oatmeal raisin\n",
      "24 - peanut butter\n",
      "12 - chocolate chip\n",
      "1 - dark chocolate chip\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import desc\n",
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "s = s.order_by(desc(cookies.c.quantity))\n",
    "rp = connection.execute(s)\n",
    "for cookie in rp:\n",
    "    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['dark chocolate chip', 'chocolate chip']\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "s = s.order_by(cookies.c.quantity)\n",
    "s = s.limit(2)\n",
    "rp = connection.execute(s)\n",
    "print([result.cookie_name for result in rp])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.sql import func"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['%(87293344 count)s']\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "s = select([func.count(cookies.c.cookie_name)])\n",
    "rp = connection.execute(s)\n",
    "record = rp.first()\n",
    "print(record.keys())\n",
    "print(record.count_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['inventory_count']\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "s = select([func.count(cookies.c.cookie_name).label('inventory_count')])\n",
    "rp = connection.execute(s)\n",
    "record = rp.first()\n",
    "print(record.keys())\n",
    "print(record.inventory_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('cookie_id', 1), ('cookie_name', 'chocolate chip'), ('cookie_recipe_url', 'http://some.aweso.me/cookie/recipe.html'), ('cookie_sku', 'CC01'), ('quantity', 12), ('unit_cost', Decimal('0.50'))]\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')\n",
    "rp = connection.execute(s)\n",
    "record = rp.first()\n",
    "print(record.items())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%')).where(cookies.c.quantity == 12)\n",
    "rp = connection.execute(s)\n",
    "for record in rp.fetchall():\n",
    "    print(record.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT cookies.cookie_id, cookies.cookie_name, cookies.cookie_recipe_url, cookies.cookie_sku, cookies.quantity, cookies.unit_cost \\nFROM cookies \\nWHERE cookies.cookie_name LIKE :cookie_name_1 AND cookies.quantity = :quantity_1'"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "s=cookies.select(limit=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'CC01', 12, Decimal('0.50'))\n"
     ]
    }
   ],
   "source": [
    "for row in connection.execute(s):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('chocolate chip', 'SKU-CC01')\n",
      "('dark chocolate chip', 'SKU-CC02')\n",
      "('peanut butter', 'SKU-PB01')\n",
      "('oatmeal raisin', 'SKU-EWW01')\n"
     ]
    }
   ],
   "source": [
    "s= select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])\n",
    "for row in connection.execute(s):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip - 6.0000000000\n",
      "dark chocolate chip - 0.7500000000\n",
      "peanut butter - 6.0000000000\n",
      "oatmeal raisin - 100.0000000000\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python34\\lib\\site-packages\\sqlalchemy\\sql\\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "s= select([cookies.c.cookie_name, cookies.c.quantity * cookies.c.unit_cost])\n",
    "for row in connection.execute(s):\n",
    "    print('{} - {}'.format(row.cookie_name, row.anon_1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip - 6.00\n",
      "dark chocolate chip - 0.75\n",
      "peanut butter - 6.00\n",
      "oatmeal raisin - 100.00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python34\\lib\\site-packages\\sqlalchemy\\sql\\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import cast\n",
    "s= select([cookies.c.cookie_name, cast((cookies.c.quantity * cookies.c.unit_cost), Numeric(12,2)).label('inv_cost')])\n",
    "for row in connection.execute(s):\n",
    "    print('{} - {}'.format(row.cookie_name, row.inv_cost))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip            6.00\n",
      "dark chocolate chip       0.75\n",
      "peanut butter             6.00\n",
      "oatmeal raisin            100.00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python34\\lib\\site-packages\\sqlalchemy\\sql\\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import cast\n",
    "s= select([cookies.c.cookie_name, (cookies.c.quantity * cookies.c.unit_cost).label('inv_cost')])\n",
    "for row in connection.execute(s):\n",
    "    print('{:<25} {:.2f}'.format(row.cookie_name, row.inv_cost))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "peanut butter\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import and_, or_, not_\n",
    "s = select([cookies]).where(and_(\n",
    "    cookies.c.quantity > 23,\n",
    "    cookies.c.unit_cost < 0.40\n",
    "))\n",
    "for row in connection.execute(s):\n",
    "    print(row.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip\n",
      "dark chocolate chip\n",
      "peanut butter\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import and_, or_, not_\n",
    "s = select([cookies]).where(or_(\n",
    "    cookies.c.quantity.between(10, 50),\n",
    "    cookies.c.cookie_name.contains('chip')\n",
    "))\n",
    "for row in connection.execute(s):\n",
    "    print(row.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import update\n",
    "u = update(cookies).where(cookies.c.cookie_name == \"chocolate chip\")\n",
    "u = u.values(quantity=(cookies.c.quantity + 120))\n",
    "result = connection.execute(u)\n",
    "print(result.rowcount)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           cookie_id: 1\n",
      "         cookie_name: chocolate chip\n",
      "   cookie_recipe_url: http://some.aweso.me/cookie/recipe.html\n",
      "          cookie_sku: CC01\n",
      "            quantity: 132\n",
      "           unit_cost: 0.50\n"
     ]
    }
   ],
   "source": [
    "s = select([cookies]).where(cookies.c.cookie_name == \"chocolate chip\")\n",
    "result = connection.execute(s).first()\n",
    "for key in result.keys():\n",
    "    print('{:>20}: {}'.format(key, result[key]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import delete\n",
    "u = delete(cookies).where(cookies.c.cookie_name == \"dark chocolate chip\")\n",
    "result = connection.execute(u)\n",
    "print(result.rowcount)\n",
    "s = select([cookies]).where(cookies.c.cookie_name == \"dark chocolate chip\")\n",
    "result = connection.execute(s).fetchall()\n",
    "print(len(result))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "customer_list = [\n",
    "    {\n",
    "        'username': \"cookiemon\",\n",
    "        'email_address': \"mon@cookie.com\",\n",
    "        'phone': \"111-111-1111\",\n",
    "        'password': \"password\"\n",
    "    },\n",
    "    {\n",
    "        'username': \"cakeeater\",\n",
    "        'email_address': \"cakeeater@cake.com\",\n",
    "        'phone': \"222-222-2222\",\n",
    "        'password': \"password\"\n",
    "    },\n",
    "    {\n",
    "        'username': \"pieguy\",\n",
    "        'email_address': \"guy@pie.com\",\n",
    "        'phone': \"333-333-3333\",\n",
    "        'password': \"password\"\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = users.insert()\n",
    "result = connection.execute(ins, customer_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(orders).values(user_id=1, order_id=1)\n",
    "result = connection.execute(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(line_items)\n",
    "order_items = [\n",
    "    {\n",
    "        'order_id': 1,\n",
    "        'cookie_id': 1,\n",
    "        'quantity': 2,\n",
    "        'extended_cost': 1.00\n",
    "    },\n",
    "    {\n",
    "        'order_id': 1,\n",
    "        'cookie_id': 3,\n",
    "        'quantity': 12,\n",
    "        'extended_cost': 3.00\n",
    "    }\n",
    "]\n",
    "result = connection.execute(ins, order_items)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(orders).values(user_id=2, order_id=2)\n",
    "result = connection.execute(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(line_items)\n",
    "order_items = [\n",
    "    {\n",
    "        'order_id': 2,\n",
    "        'cookie_id': 1,\n",
    "        'quantity': 24,\n",
    "        'extended_cost': 12.00\n",
    "    },\n",
    "    {\n",
    "        'order_id': 2,\n",
    "        'cookie_id': 4,\n",
    "        'quantity': 6,\n",
    "        'extended_cost': 6.00\n",
    "    }\n",
    "]\n",
    "result = connection.execute(ins, order_items)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'cookiemon', '111-111-1111', 'chocolate chip', 2, Decimal('1.00'))\n",
      "(1, 'cookiemon', '111-111-1111', 'peanut butter', 12, Decimal('3.00'))\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python34\\lib\\site-packages\\sqlalchemy\\sql\\sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "columns = [orders.c.order_id, users.c.username, users.c.phone, cookies.c.cookie_name, line_items.c.quantity, line_items.c.extended_cost]\n",
    "cookiemon_orders = select(columns)\n",
    "cookiemon_orders = cookiemon_orders.select_from(users.join(orders).join(line_items).join(cookies)).where(users.c.username == 'cookiemon')\n",
    "result = connection.execute(cookiemon_orders).fetchall()\n",
    "for row in result:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT orders.order_id, users.username, users.phone, cookies.cookie_name, line_items.quantity, line_items.extended_cost \\nFROM users JOIN orders ON users.user_id = orders.user_id JOIN line_items ON orders.order_id = line_items.order_id JOIN cookies ON cookies.cookie_id = line_items.cookie_id \\nWHERE users.username = :username_1'"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(cookiemon_orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('cakeeater', 2)\n",
      "('cookiemon', 1)\n",
      "('pieguy', None)\n"
     ]
    }
   ],
   "source": [
    "columns = [users.c.username, orders.c.order_id]\n",
    "all_orders = select(columns)\n",
    "all_orders = all_orders.select_from(users.outerjoin(orders))\n",
    "result = connection.execute(all_orders).fetchall()\n",
    "for row in result:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT users.username, count(orders.order_id) AS count_1 \n",
      "FROM users LEFT OUTER JOIN orders ON users.user_id = orders.user_id GROUP BY users.username\n",
      "('cakeeater', 1)\n",
      "('cookiemon', 1)\n",
      "('pieguy', 0)\n"
     ]
    }
   ],
   "source": [
    "columns = [users.c.username, func.count(orders.c.order_id)]\n",
    "all_orders = select(columns)\n",
    "all_orders = all_orders.select_from(users.outerjoin(orders)).group_by(users.c.username)\n",
    "print(str(all_orders))\n",
    "result = connection.execute(all_orders).fetchall()\n",
    "for row in result:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_orders_by_customer(cust_name):\n",
    "    columns = [orders.c.order_id, users.c.username, users.c.phone, cookies.c.cookie_name, line_items.c.quantity, line_items.c.extended_cost]\n",
    "    cust_orders = select(columns)\n",
    "    cust_orders = cust_orders.select_from(users.join(orders).join(line_items).join(cookies)).where(users.c.username == cust_name)\n",
    "    result = connection.execute(cust_orders).fetchall()\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),\n",
       " (2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_orders_by_customer(cust_name, shipped=None, details=False):\n",
    "    columns = [orders.c.order_id, users.c.username, users.c.phone]\n",
    "    joins = users.join(orders)\n",
    "    if details:\n",
    "        columns.extend([cookies.c.cookie_name,line_items.c.quantity, line_items.c.extended_cost])\n",
    "        joins=joins.join(line_items).join(cookies)\n",
    "    cust_orders = select(columns)\n",
    "    cust_orders = cust_orders.select_from(joins).where(users.c.username == cust_name)\n",
    "    if shipped is not None:\n",
    "        cust_orders = cust_orders.where(orders.c.shipped == shipped)\n",
    "    result = connection.execute(cust_orders).fetchall()\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'cakeeater', '222-222-2222')]"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),\n",
       " (2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater', details=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater', shipped=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'cakeeater', '222-222-2222')]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater', shipped=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'cakeeater', '222-222-2222', 'chocolate chip', 24, Decimal('12.00')),\n",
       " (2, 'cakeeater', '222-222-2222', 'oatmeal raisin', 6, Decimal('6.00'))]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_orders_by_customer('cakeeater', shipped=False, details=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 1, 0), (2, 2, 0)]\n"
     ]
    }
   ],
   "source": [
    "result = connection.execute(\"select * from orders\").fetchall()\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'cookiemon', 'mon@cookie.com', '111-111-1111', 'password', datetime.datetime(2015, 7, 10, 14, 3, 8, 455782), datetime.datetime(2015, 7, 10, 14, 3, 8, 455782))]\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import text\n",
    "stmt = select([users]).where(text('username=\"cookiemon\"'))\n",
    "print(connection.execute(stmt).fetchall())"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}