ch08b.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "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 Table, Column, Integer, Numeric, String, DateTime, ForeignKey, Boolean, CheckConstraint\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",
    "    __table_args__ = (CheckConstraint('quantity >= 0', name='quantity_positive'),)\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 __init__(self, name, recipe_url=None, sku=None, quantity=0, unit_cost=0.00):\n",
    "        self.cookie_name = name\n",
    "        self.cookie_recipe_url = recipe_url\n",
    "        self.cookie_sku = sku\n",
    "        self.quantity = quantity\n",
    "        self.unit_cost = unit_cost\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 __init__(self, username, email_address, phone, password):\n",
    "        self.username = username\n",
    "        self.email_address = email_address\n",
    "        self.phone = phone\n",
    "        self.password = password\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": [
    "cookiemon = User('cookiemon', 'mon@cookie.com', '111-111-1111', 'password')\n",
    "cc = Cookie('chocolate chip', 'http://some.aweso.me/cookie/recipe.html', 'CC01', 12, 0.50)\n",
    "dcc = Cookie('dark chocolate chip',\n",
    "             'http://some.aweso.me/cookie/recipe_dark.html',\n",
    "             'CC02',\n",
    "             1,\n",
    "             0.75)\n",
    "session.add(cookiemon)\n",
    "session.add(cc)\n",
    "session.add(dcc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py:322: 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",
      "  d[coltype] = rp = d['impl'].result_processor(dialect, coltype)\n"
     ]
    }
   ],
   "source": [
    "o1 = Order()\n",
    "o1.user = cookiemon\n",
    "session.add(o1)\n",
    "\n",
    "line1 = LineItem(order=o1, cookie=cc, quantity=9, extended_cost=4.50)\n",
    "\n",
    "\n",
    "session.add(line1)\n",
    "session.commit()\n",
    "o2 = Order()\n",
    "o2.user = cookiemon\n",
    "session.add(o2)\n",
    "\n",
    "line1 = LineItem(order=o2, cookie=cc, quantity=2, extended_cost=1.50)\n",
    "line2 = LineItem(order=o2, cookie=dcc, quantity=9, extended_cost=6.75)\n",
    "\n",
    "\n",
    "session.add(line1)\n",
    "session.add(line2)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def ship_it(order_id):\n",
    "    order = session.query(Order).get(order_id)\n",
    "    for li in order.line_items:\n",
    "        li.cookie.quantity = li.cookie.quantity - li.quantity\n",
    "        session.add(li.cookie)\n",
    "    order.shipped = True\n",
    "    session.add(order)\n",
    "    session.commit()\n",
    "    print(\"shipped order ID: {}\".format(order_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shipped order ID: 1\n",
      "[(u'chocolate chip', 3), (u'dark chocolate chip', 1)]\n"
     ]
    }
   ],
   "source": [
    "ship_it(1)\n",
    "print(session.query(Cookie.cookie_name, Cookie.quantity).all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "(IntegrityError) CHECK constraint failed: quantity_positive u'UPDATE cookies SET quantity=? WHERE cookies.cookie_id = ?' (-8, 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-7-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-5-c442ae46326c>\u001b[0m in \u001b[0;36mship_it\u001b[0;34m(order_id)\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0morder\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[0m\n\u001b[1;32m      7\u001b[0m     \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0madd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morder\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m     \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      9\u001b[0m     \u001b[0;32mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"shipped order ID: {}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morder_id\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-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mcommit\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    786\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0msa_exc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInvalidRequestError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"No transaction is begun.\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    787\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 788\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtransaction\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    789\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    790\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mprepare\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\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-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mcommit\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    382\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_assert_active\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mprepared_ok\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[1;32m    383\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_state\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mPREPARED\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 384\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prepare_impl\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    385\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    386\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_parent\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0mNone\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnested\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_prepare_impl\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    362\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_is_clean\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    363\u001b[0m                     \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 364\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    365\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    366\u001b[0m                 raise exc.FlushError(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mflush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   1983\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1984\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1985\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobjects\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1986\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1987\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_flush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   2101\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2102\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2103\u001b[0;31m                 \u001b[0mtransaction\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_capture_exception\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[0m\u001b[1;32m   2104\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2105\u001b[0m     def is_modified(self, instance, include_collections=True,\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc\u001b[0m in \u001b[0;36m__exit__\u001b[0;34m(self, type_, value, traceback)\u001b[0m\n\u001b[1;32m     58\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[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mNone\u001b[0m   \u001b[0;31m# remove potential circular references\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m             \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\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[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     61\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mNone\u001b[0m   \u001b[0;31m# remove potential circular references\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_flush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   2065\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2066\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2067\u001b[0;31m                 \u001b[0mflush_context\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2068\u001b[0m             \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2069\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    370\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdependencies\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    371\u001b[0m                     postsort_actions):\n\u001b[0;32m--> 372\u001b[0;31m                 \u001b[0mrec\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    373\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    374\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mfinalize_flush_changes\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\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-dev/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, uow)\u001b[0m\n\u001b[1;32m    524\u001b[0m                              uow.states_for_mapper_hierarchy(\n\u001b[1;32m    525\u001b[0m                                  self.mapper, False, False),\n\u001b[0;32m--> 526\u001b[0;31m                              \u001b[0muow\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    527\u001b[0m                              )\n\u001b[1;32m    528\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/persistence.pyc\u001b[0m in \u001b[0;36msave_obj\u001b[0;34m(base_mapper, states, uowtransaction, single)\u001b[0m\n\u001b[1;32m     58\u001b[0m             _emit_update_statements(base_mapper, uowtransaction,\n\u001b[1;32m     59\u001b[0m                                     \u001b[0mcached_connections\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m                                     mapper, table, update)\n\u001b[0m\u001b[1;32m     61\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/persistence.pyc\u001b[0m in \u001b[0;36m_emit_update_statements\u001b[0;34m(base_mapper, uowtransaction, cached_connections, mapper, table, update)\u001b[0m\n\u001b[1;32m    516\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    517\u001b[0m             \u001b[0mc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcached_connections\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 518\u001b[0;31m                 \u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\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    519\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    520\u001b[0m         _postfetch(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/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    839\u001b[0m                 type(object))\n\u001b[1;32m    840\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 841\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    842\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    843\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-dev/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    320\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    321\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--> 322\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    323\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    324\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-dev/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    936\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    937\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 938\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    939\u001b[0m         )\n\u001b[1;32m    940\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-dev/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   1068\u001b[0m                 \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1069\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1070\u001b[0;31m                 context)\n\u001b[0m\u001b[1;32m   1071\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1072\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-dev/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   1269\u001b[0m                 util.raise_from_cause(\n\u001b[1;32m   1270\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1271\u001b[0;31m                     \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1272\u001b[0m                 )\n\u001b[1;32m   1273\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-dev/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-dev/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   1061\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1062\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1063\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1064\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   1065\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/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    440\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    441\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--> 442\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    443\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    444\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: (IntegrityError) CHECK constraint failed: quantity_positive u'UPDATE cookies SET quantity=? WHERE cookies.cookie_id = ?' (-8, 2)"
     ]
    }
   ],
   "source": [
    "ship_it(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "InvalidRequestError",
     "evalue": "This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (IntegrityError) CHECK constraint failed: quantity_positive u'UPDATE cookies SET quantity=? WHERE cookies.cookie_id = ?' (-8, 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInvalidRequestError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-8-90b93364fb2d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCookie\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcookie_name\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[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36mall\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2321\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2322\u001b[0m         \"\"\"\n\u001b[0;32m-> 2323\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2324\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2325\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0m_generative\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_no_clauseelement_condition\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m__iter__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2439\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_populate_existing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2440\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2441\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_and_instances\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcontext\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2442\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2443\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_from_session\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\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-dev/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m_execute_and_instances\u001b[0;34m(self, querycontext)\u001b[0m\n\u001b[1;32m   2452\u001b[0m             \u001b[0mmapper\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_mapper_zero_or_none\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2453\u001b[0m             \u001b[0mclause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mquerycontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2454\u001b[0;31m             close_with_result=True)\n\u001b[0m\u001b[1;32m   2455\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2456\u001b[0m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquerycontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_params\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m_connection_from_session\u001b[0;34m(self, **kw)\u001b[0m\n\u001b[1;32m   2443\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_from_session\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2444\u001b[0m         conn = self.session.connection(\n\u001b[0;32m-> 2445\u001b[0;31m             **kw)\n\u001b[0m\u001b[1;32m   2446\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execution_options\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2447\u001b[0m             \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mconnection\u001b[0;34m(self, mapper, clause, bind, close_with_result, execution_options, **kw)\u001b[0m\n\u001b[1;32m    878\u001b[0m         return self._connection_for_bind(bind,\n\u001b[1;32m    879\u001b[0m                                          \u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 880\u001b[0;31m                                          execution_options=execution_options)\n\u001b[0m\u001b[1;32m    881\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    882\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_for_bind\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\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-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_connection_for_bind\u001b[0;34m(self, engine, execution_options, **kw)\u001b[0m\n\u001b[1;32m    883\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtransaction\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    884\u001b[0m             return self.transaction._connection_for_bind(\n\u001b[0;32m--> 885\u001b[0;31m                 engine, execution_options)\n\u001b[0m\u001b[1;32m    886\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    887\u001b[0m             \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_connection_for_bind\u001b[0;34m(self, bind, execution_options)\u001b[0m\n\u001b[1;32m    303\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    304\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_for_bind\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbind\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 305\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_assert_active\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    306\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    307\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mbind\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connections\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book-dev/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_assert_active\u001b[0;34m(self, prepared_ok, rollback_ok, deactive_ok, closed_msg)\u001b[0m\n\u001b[1;32m    212\u001b[0m                         \u001b[0;34m\"first issue Session.rollback().\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    213\u001b[0m                         \u001b[0;34m\" Original exception was: %s\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 214\u001b[0;31m                         \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rollback_exception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    215\u001b[0m                     )\n\u001b[1;32m    216\u001b[0m                 \u001b[0;32melif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mdeactive_ok\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInvalidRequestError\u001b[0m: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (IntegrityError) CHECK constraint failed: quantity_positive u'UPDATE cookies SET quantity=? WHERE cookies.cookie_id = ?' (-8, 2)"
     ]
    }
   ],
   "source": [
    "print(session.query(Cookie.cookie_name, Cookie.quantity).all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(u'chocolate chip', 3), (u'dark chocolate chip', 1)]\n"
     ]
    }
   ],
   "source": [
    "session.rollback()\n",
    "print(session.query(Cookie.cookie_name, Cookie.quantity).all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.exc import IntegrityError\n",
    "def ship_it(order_id):\n",
    "    order = session.query(Order).get(order_id)\n",
    "    for li in order.line_items:\n",
    "        li.cookie.quantity = li.cookie.quantity - li.quantity\n",
    "        session.add(li.cookie)\n",
    "    order.shipped = True\n",
    "    session.add(order)\n",
    "    try:\n",
    "        session.commit()\n",
    "        print(\"shipped order ID: {}\".format(order_id))\n",
    "    except IntegrityError as error:\n",
    "        print('ERROR: {!s}'.format(error.orig))\n",
    "        session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ERROR: CHECK constraint failed: quantity_positive\n"
     ]
    }
   ],
   "source": [
    "ship_it(2)"
   ]
  },
  {
   "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
}