ch03b.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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, CheckConstraint)\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",
    "    CheckConstraint('quantity >= 0', name='quantity_positive')\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()),\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)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import select, insert, update\n",
    "ins = insert(users).values(\n",
    "    username=\"cookiemon\",\n",
    "    email_address=\"mon@cookie.com\",\n",
    "    phone=\"111-111-1111\",\n",
    "    password=\"password\"\n",
    ")\n",
    "result = connection.execute(ins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = cookies.insert()\n",
    "inventory_list = [\n",
    "    {\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",
    "    {\n",
    "        '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",
    "    }\n",
    "]\n",
    "result = connection.execute(ins, inventory_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(orders).values(user_id=1, order_id=1)\n",
    "result = connection.execute(ins)\n",
    "ins = insert(line_items)\n",
    "order_items = [\n",
    "    {\n",
    "        'order_id': 1,\n",
    "        'cookie_id': 1,\n",
    "        'quantity': 9,\n",
    "        'extended_cost': 4.50\n",
    "    }\n",
    "]\n",
    "result = connection.execute(ins, order_items)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ins = insert(orders).values(user_id=1, order_id=2)\n",
    "result = connection.execute(ins)\n",
    "ins = insert(line_items)\n",
    "order_items = [\n",
    "    {\n",
    "        'order_id': 2,\n",
    "        'cookie_id': 2,\n",
    "        'quantity': 1,\n",
    "        'extended_cost': 1.50\n",
    "    },\n",
    "    {\n",
    "        'order_id': 2,\n",
    "        'cookie_id': 1,\n",
    "        'quantity': 4,\n",
    "        'extended_cost': 4.50\n",
    "    }\n",
    "]\n",
    "result = connection.execute(ins, order_items)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def ship_it(order_id):\n",
    "\n",
    "    s = select([line_items.c.cookie_id, line_items.c.quantity])\n",
    "    s = s.where(line_items.c.order_id == order_id)\n",
    "    cookies_to_ship = connection.execute(s)\n",
    "    for cookie in cookies_to_ship:\n",
    "        u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)\n",
    "        u = u.values(quantity = cookies.c.quantity - cookie.quantity)\n",
    "        result = connection.execute(u)\n",
    "    u = update(orders).where(orders.c.order_id == order_id)\n",
    "    u = u.values(shipped=True)\n",
    "    result = connection.execute(u)\n",
    "    print(\"Shipped order ID: {}\".format(order_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shipped order ID: 1\n"
     ]
    }
   ],
   "source": [
    "ship_it(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'chocolate chip', 3), (u'dark chocolate chip', 1)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "connection.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "(sqlite3.IntegrityError) CHECK constraint failed: quantity_positive [SQL: u'UPDATE cookies SET quantity=(cookies.quantity - ?) WHERE cookies.cookie_id = ?'] [parameters: (4, 1)]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-9-8a7f7805a7f6>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mship_it\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-6-a05ea8864be5>\u001b[0m in \u001b[0;36mship_it\u001b[0;34m(order_id)\u001b[0m\n\u001b[1;32m      7\u001b[0m         \u001b[0mu\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mupdate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcookies\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcookies\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcookie_id\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mcookie\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcookie_id\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m         \u001b[0mu\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mu\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquantity\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcookies\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquantity\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0mcookie\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquantity\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 9\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mu\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     10\u001b[0m     \u001b[0mu\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mupdate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morders\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morders\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0morder_id\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0morder_id\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     11\u001b[0m     \u001b[0mu\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mu\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mshipped\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object, *multiparams, **params)\u001b[0m\n\u001b[1;32m    912\u001b[0m                 type(object))\n\u001b[1;32m    913\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 914\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    915\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    916\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    321\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    322\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    324\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    325\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0munique_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0moptionaldict\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1008\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1009\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1010\u001b[0;31m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1011\u001b[0m         )\n\u001b[1;32m   1012\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1144\u001b[0m                 \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1145\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1146\u001b[0;31m                 context)\n\u001b[0m\u001b[1;32m   1147\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1148\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1339\u001b[0m                 util.raise_from_cause(\n\u001b[1;32m   1340\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1341\u001b[0;31m                     \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1342\u001b[0m                 )\n\u001b[1;32m   1343\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    197\u001b[0m             \u001b[0mexc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    198\u001b[0m         \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 199\u001b[0;31m         \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    200\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    201\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1137\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1138\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1139\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1140\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1141\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    448\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    449\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 450\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    451\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    452\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIntegrityError\u001b[0m: (sqlite3.IntegrityError) CHECK constraint failed: quantity_positive [SQL: u'UPDATE cookies SET quantity=(cookies.quantity - ?) WHERE cookies.cookie_id = ?'] [parameters: (4, 1)]"
     ]
    }
   ],
   "source": [
    "ship_it(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'chocolate chip', 3), (u'dark chocolate chip', 0)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "connection.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "u = update(cookies).where(cookies.c.cookie_name == \"dark chocolate chip\")\n",
    "u = u.values(quantity = 1)\n",
    "result = connection.execute(u)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.exc import IntegrityError\n",
    "def ship_it(order_id):\n",
    "    s = select([line_items.c.cookie_id, line_items.c.quantity])\n",
    "    s = s.where(line_items.c.order_id == order_id)\n",
    "    transaction = connection.begin()\n",
    "    cookies_to_ship = connection.execute(s).fetchall()\n",
    "    try:\n",
    "        for cookie in cookies_to_ship:\n",
    "            u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)\n",
    "            u = u.values(quantity = cookies.c.quantity-cookie.quantity)\n",
    "            result = connection.execute(u)\n",
    "        u = update(orders).where(orders.c.order_id == order_id)\n",
    "        u = u.values(shipped=True)\n",
    "        result = connection.execute(u)\n",
    "        print(\"Shipped order ID: {}\".format(order_id))\n",
    "        transaction.commit()\n",
    "    except IntegrityError as error:\n",
    "        transaction.rollback()\n",
    "        print(error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.IntegrityError) CHECK constraint failed: quantity_positive [SQL: u'UPDATE cookies SET quantity=(cookies.quantity - ?) WHERE cookies.cookie_id = ?'] [parameters: (4, 1)]\n"
     ]
    }
   ],
   "source": [
    "ship_it(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'chocolate chip', 3), (u'dark chocolate chip', 1)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([cookies.c.cookie_name, cookies.c.quantity])\n",
    "connection.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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
}