ch07.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "engine = create_engine('sqlite:///:memory:')\n",
    "\n",
    "Session = sessionmaker(bind=engine)\n",
    "\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "from sqlalchemy import Column, Integer, Numeric, String, DateTime, ForeignKey, Boolean\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import relationship, backref\n",
    "\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "\n",
    "class Cookie(Base):\n",
    "    __tablename__ = 'cookies'\n",
    "\n",
    "    cookie_id = Column(Integer, primary_key=True)\n",
    "    cookie_name = Column(String(50), index=True)\n",
    "    cookie_recipe_url = Column(String(255))\n",
    "    cookie_sku = Column(String(55))\n",
    "    quantity = Column(Integer())\n",
    "    unit_cost = Column(Numeric(12, 2))\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"Cookie(cookie_name='{self.cookie_name}', \" \\\n",
    "                       \"cookie_recipe_url='{self.cookie_recipe_url}', \" \\\n",
    "                       \"cookie_sku='{self.cookie_sku}', \" \\\n",
    "                       \"quantity={self.quantity}, \" \\\n",
    "                       \"unit_cost={self.unit_cost})\".format(self=self)\n",
    "    \n",
    "    \n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    user_id = Column(Integer(), primary_key=True)\n",
    "    username = Column(String(15), nullable=False, unique=True)\n",
    "    email_address = Column(String(255), nullable=False)\n",
    "    phone = Column(String(20), nullable=False)\n",
    "    password = Column(String(25), nullable=False)\n",
    "    created_on = Column(DateTime(), default=datetime.now)\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"User(username='{self.username}', \" \\\n",
    "                     \"email_address='{self.email_address}', \" \\\n",
    "                     \"phone='{self.phone}', \" \\\n",
    "                     \"password='{self.password}')\".format(self=self)\n",
    "    \n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    order_id = Column(Integer(), primary_key=True)\n",
    "    user_id = Column(Integer(), ForeignKey('users.user_id'))\n",
    "    shipped = Column(Boolean(), default=False)\n",
    "    \n",
    "    user =  relationship(\"User\", backref=backref('orders', order_by=order_id))\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"Order(user_id={self.user_id}, \" \\\n",
    "                      \"shipped={self.shipped})\".format(self=self)\n",
    "\n",
    "\n",
    "class LineItem(Base):\n",
    "    __tablename__ = 'line_items'\n",
    "    line_item_id = Column(Integer(), primary_key=True)\n",
    "    order_id = Column(Integer(), ForeignKey('orders.order_id'))\n",
    "    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))\n",
    "    quantity = Column(Integer())\n",
    "    extended_cost = Column(Numeric(12, 2))\n",
    "    \n",
    "    order = relationship(\"Order\", backref=backref('line_items', order_by=line_item_id))\n",
    "    cookie = relationship(\"Cookie\", uselist=False)\n",
    "\n",
    "    def __repr__(self):\n",
    "        return \"LineItems(order_id={self.order_id}, \" \\\n",
    "                          \"cookie_id={self.cookie_id}, \" \\\n",
    "                          \"quantity={self.quantity}, \" \\\n",
    "                          \"extended_cost={self.extended_cost})\".format(\n",
    "                    self=self)    \n",
    "    \n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cc_cookie = Cookie(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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.add(cc_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/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"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cc_cookie.cookie_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dcc = Cookie(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",
    "mol = Cookie(cookie_name='molasses',\n",
    "             cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html',\n",
    "             cookie_sku='MOL01',\n",
    "             quantity=1,\n",
    "             unit_cost=0.80)\n",
    "session.add(dcc)\n",
    "session.add(mol)\n",
    "session.flush()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "3\n"
     ]
    }
   ],
   "source": [
    "print(dcc.cookie_id)\n",
    "print(mol.cookie_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "c1 = Cookie(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",
    "c2 = Cookie(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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.bulk_save_objects([c1,c2])\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "c1.cookie_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50), Cookie(cookie_name='dark chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html', cookie_sku='CC02', quantity=1, unit_cost=0.75), Cookie(cookie_name='molasses', cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html', cookie_sku='MOL01', quantity=1, unit_cost=0.80), Cookie(cookie_name='peanut butter', cookie_recipe_url='http://some.aweso.me/cookie/peanut.html', cookie_sku='PB01', quantity=24, unit_cost=0.25), Cookie(cookie_name='oatmeal raisin', cookie_recipe_url='http://some.okay.me/cookie/raisin.html', cookie_sku='EWW01', quantity=100, unit_cost=1.00)]\n"
     ]
    }
   ],
   "source": [
    "cookies = session.query(Cookie).all()\n",
    "print(cookies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n",
      "Cookie(cookie_name='dark chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html', cookie_sku='CC02', quantity=1, unit_cost=0.75)\n",
      "Cookie(cookie_name='molasses', cookie_recipe_url='http://some.aweso.me/cookie/recipe_molasses.html', cookie_sku='MOL01', quantity=1, unit_cost=0.80)\n",
      "Cookie(cookie_name='peanut butter', cookie_recipe_url='http://some.aweso.me/cookie/peanut.html', cookie_sku='PB01', quantity=24, unit_cost=0.25)\n",
      "Cookie(cookie_name='oatmeal raisin', cookie_recipe_url='http://some.okay.me/cookie/raisin.html', cookie_sku='EWW01', quantity=100, unit_cost=1.00)\n"
     ]
    }
   ],
   "source": [
    "for cookie in session.query(Cookie):  \n",
    "    print(cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'chocolate chip', 12)\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Cookie.cookie_name, Cookie.quantity).first())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  1 - dark chocolate chip\n",
      "  1 - molasses\n",
      " 12 - chocolate chip\n",
      " 24 - peanut butter\n",
      "100 - oatmeal raisin\n"
     ]
    }
   ],
   "source": [
    "for cookie in session.query(Cookie).order_by(Cookie.quantity):\n",
    "    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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",
      "  1 - molasses\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import desc\n",
    "for cookie in session.query(Cookie).order_by(desc(Cookie.quantity)):\n",
    "    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'dark chocolate chip', u'molasses']\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie).order_by(Cookie.quantity)[:2]\n",
    "print([result.cookie_name for result in query])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'dark chocolate chip', u'molasses']\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie).order_by(Cookie.quantity).limit(2)\n",
    "print([result.cookie_name for result in query])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "138\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import func\n",
    "inv_count = session.query(func.sum(Cookie.quantity)).scalar()\n",
    "print(inv_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5,)\n"
     ]
    }
   ],
   "source": [
    "rec_count = session.query(func.count(Cookie.cookie_name)).first()\n",
    "print(rec_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['inventory_count']\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "rec_count = session.query(func.count(Cookie.cookie_name) \\\n",
    "                          .label('inventory_count')).first()\n",
    "print(rec_count.keys())\n",
    "print(rec_count.inventory_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n"
     ]
    }
   ],
   "source": [
    "record = session.query(Cookie).filter(Cookie.cookie_name == 'chocolate chip').first()\n",
    "print(record)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cookie(cookie_name='chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50)\n"
     ]
    }
   ],
   "source": [
    "record = session.query(Cookie).filter_by(cookie_name='chocolate chip').first()\n",
    "print(record)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip\n",
      "dark chocolate chip\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie).filter(Cookie.cookie_name.like('%chocolate%'))\n",
    "for record in query:                       \n",
    "    print(record.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'chocolate chip', u'SKU-CC01')\n",
      "(u'dark chocolate chip', u'SKU-CC02')\n",
      "(u'molasses', u'SKU-MOL01')\n",
      "(u'peanut butter', u'SKU-PB01')\n",
      "(u'oatmeal raisin', u'SKU-EWW01')\n"
     ]
    }
   ],
   "source": [
    "results = session.query(Cookie.cookie_name, 'SKU-' + Cookie.cookie_sku).all()\n",
    "for row in results:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chocolate chip - 6.00\n",
      "dark chocolate chip - 0.75\n",
      "molasses - 0.80\n",
      "peanut butter - 6.00\n",
      "oatmeal raisin - 100.00\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import cast\n",
    "query = session.query(Cookie.cookie_name,\n",
    "                      cast((Cookie.quantity * Cookie.unit_cost), \n",
    "                           Numeric(12,2)).label('inv_cost'))\n",
    "for result in query:\n",
    "    print('{} - {}'.format(result.cookie_name, result.inv_cost))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "peanut butter\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import and_, or_, not_\n",
    "query = session.query(Cookie).filter(\n",
    "    Cookie.quantity > 23,\n",
    "    Cookie.unit_cost < 0.40\n",
    ")\n",
    "for result in query:\n",
    "    print(result.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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",
    "query = session.query(Cookie).filter(\n",
    "    or_(\n",
    "        Cookie.quantity.between(10, 50),\n",
    "        Cookie.cookie_name.contains('chip')\n",
    "    )\n",
    ")\n",
    "for result in query:\n",
    "    print(result.cookie_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "132\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie)\n",
    "cc_cookie = query.filter(Cookie.cookie_name == \"chocolate chip\").first()\n",
    "cc_cookie.quantity = cc_cookie.quantity + 120\n",
    "session.commit()\n",
    "print(cc_cookie.quantity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie)\n",
    "query = query.filter(Cookie.cookie_name == \"chocolate chip\")\n",
    "query.update({Cookie.quantity: Cookie.quantity - 20})\n",
    "\n",
    "cc_cookie = query.first()\n",
    "print(cc_cookie.quantity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie)\n",
    "query = query.filter(Cookie.cookie_name == \"dark chocolate chip\")\n",
    "dcc_cookie = query.one()\n",
    "session.delete(dcc_cookie)\n",
    "session.commit()\n",
    "dcc_cookie = query.first()\n",
    "print(dcc_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Cookie)\n",
    "query = query.filter(Cookie.cookie_name == \"molasses\")\n",
    "query.delete()\n",
    "mol_cookie = query.first()\n",
    "print(mol_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cookiemon = User(username='cookiemon', \n",
    "                 email_address='mon@cookie.com', \n",
    "                 phone='111-111-1111', \n",
    "                 password='password')\n",
    "cakeeater = User(username='cakeeater', \n",
    "                 email_address='cakeeater@cake.com', \n",
    "                 phone='222-222-2222', \n",
    "                 password='password')\n",
    "pieperson = User(username='pieperson', \n",
    "                 email_address='person@pie.com', \n",
    "                 phone='333-333-3333', \n",
    "                 password='password')\n",
    "session.add(cookiemon)\n",
    "session.add(cakeeater)\n",
    "session.add(pieperson)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "o1 = Order()\n",
    "o1.user = cookiemon\n",
    "session.add(o1)\n",
    "\n",
    "cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"chocolate chip\").one()\n",
    "line1 = LineItem(cookie=cc, quantity=2, extended_cost=1.00)\n",
    "\n",
    "pb = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"peanut butter\").one()\n",
    "line2 = LineItem(quantity=12, extended_cost=3.00)\n",
    "line2.cookie = pb \n",
    "line2.order = o1\n",
    "\n",
    "o1.line_items.append(line1)\n",
    "o1.line_items.append(line2)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "o2 = Order()\n",
    "o2.user = cakeeater\n",
    "\n",
    "cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"chocolate chip\").one()\n",
    "line1 = LineItem(cookie=cc, quantity=24, extended_cost=12.00)\n",
    "\n",
    "oat = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                   \"oatmeal raisin\").one()\n",
    "line2 = LineItem(cookie=oat, quantity=6, extended_cost=6.00)\n",
    "\n",
    "o2.line_items.append(line1)\n",
    "o2.line_items.append(line2)\n",
    "\n",
    "session.add(o2)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, u'cookiemon', u'111-111-1111', u'peanut butter', 12, Decimal('3.00')), (1, u'cookiemon', u'111-111-1111', u'chocolate chip', 2, Decimal('1.00'))]\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Order.order_id, User.username, User.phone,\n",
    "                      Cookie.cookie_name, LineItem.quantity,\n",
    "                      LineItem.extended_cost)\n",
    "query = query.join(User).join(LineItem).join(Cookie)\n",
    "results = query.filter(User.username == 'cookiemon').all()\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'cakeeater', 1)\n",
      "(u'cookiemon', 1)\n",
      "(u'pieperson', 0)\n"
     ]
    }
   ],
   "source": [
    "query = session.query(User.username, func.count(Order.order_id))\n",
    "query = query.outerjoin(Order).group_by(User.username)\n",
    "for row in query:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "class Employee(Base):\n",
    "    __tablename__ = 'employees'\n",
    "    \n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    manager_id = Column(Integer(), ForeignKey('employees.id'))\n",
    "    name = Column(String(255), nullable=False)\n",
    "    \n",
    "    manager = relationship(\"Employee\", backref=backref('reports'), remote_side=[id])\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "marsha = Employee(name='Marsha')\n",
    "fred = Employee(name='Fred')\n",
    "marsha.reports.append(fred)\n",
    "session.add(marsha)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fred\n"
     ]
    }
   ],
   "source": [
    "for report in marsha.reports:\n",
    "    print(report.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'cakeeater', 1)\n",
      "(u'cookiemon', 1)\n",
      "(u'pieperson', 0)\n"
     ]
    }
   ],
   "source": [
    "query = session.query(User.username, func.count(Order.order_id))\n",
    "query = query.outerjoin(Order).group_by(User.username)\n",
    "for row in query:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')),\n",
       " (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_orders_by_customer(cust_name):\n",
    "    query = session.query(Order.order_id, User.username, User.phone,\n",
    "                      Cookie.cookie_name, LineItem.quantity,\n",
    "                      LineItem.extended_cost)\n",
    "    query = query.join(User).join(LineItem).join(Cookie)\n",
    "    results = query.filter(User.username == cust_name).all()\n",
    "    return results\n",
    "\n",
    "get_orders_by_customer('cakeeater')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(2, u'cakeeater', u'222-222-2222')]\n",
      "[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')), (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]\n",
      "[]\n",
      "[(2, u'cakeeater', u'222-222-2222')]\n",
      "[(2, u'cakeeater', u'222-222-2222', u'chocolate chip', 24, Decimal('12.00')), (2, u'cakeeater', u'222-222-2222', u'oatmeal raisin', 6, Decimal('6.00'))]\n"
     ]
    }
   ],
   "source": [
    "def get_orders_by_customer(cust_name, shipped=None, details=False):\n",
    "    query = session.query(Order.order_id, User.username, User.phone)\n",
    "    query = query.join(User)\n",
    "    if details:\n",
    "        query = query.add_columns(Cookie.cookie_name, LineItem.quantity,\n",
    "                          LineItem.extended_cost)\n",
    "        query = query.join(LineItem).join(Cookie)\n",
    "    if shipped is not None:\n",
    "        query = query.filter(Order.shipped == shipped)\n",
    "    results = query.filter(User.username == cust_name).all()\n",
    "    return results\n",
    "\n",
    "print(get_orders_by_customer('cakeeater'))\n",
    "\n",
    "print(get_orders_by_customer('cakeeater', details=True))\n",
    "\n",
    "print(get_orders_by_customer('cakeeater', shipped=True))\n",
    "\n",
    "print(get_orders_by_customer('cakeeater', shipped=False))\n",
    "\n",
    "print(get_orders_by_customer('cakeeater', shipped=False, details=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[User(username='cookiemon', email_address='mon@cookie.com', phone='111-111-1111', password='password')]\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import text\n",
    "query = session.query(User).filter(text(\"username='cookiemon'\"))\n",
    "print(query.all())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}