06-CRUD using SQLAlchemy ORM.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/pp/tuts/sqlalchemy/env/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
      "  \"\"\")\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\n",
    "    Column, DateTime, ForeignKey, Numeric, SmallInteger, CheckConstraint\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import relationship\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "engine = create_engine(\"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts\")\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class Customer(Base):\n",
    "    __tablename__ = 'customers'\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    first_name = Column(String(100), nullable=False)\n",
    "    last_name = Column(String(100), nullable=False)\n",
    "    username = Column(String(50), nullable=False)\n",
    "    email = Column(String(200), nullable=False)\n",
    "    address = Column(String(200), nullable=False)\n",
    "    town = Column(String(50), nullable=False)\n",
    "    created_on = Column(DateTime(), default=datetime.now)\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    "    orders = relationship(\"Order\", backref='customer')\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"<Customer:{0}-{1}>\".format(self.id, self.username)\n",
    "\n",
    "\n",
    "class Item(Base):\n",
    "    __tablename__ = 'items'\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    name = Column(String(200), nullable=False)\n",
    "    cost_price =  Column(Numeric(10, 2), nullable=False)\n",
    "    selling_price = Column(Numeric(10, 2), nullable=False)\n",
    "    quantity = Column(SmallInteger(), nullable=False)\n",
    "#     orders = relationship(\"Order\", backref='customer')\n",
    "\n",
    "    def __repr__(self):\n",
    "        return \"<Item:{0}-{1}>\".format(self.id, self.name)    \n",
    "    \n",
    "    __table_args__ = (\n",
    "        CheckConstraint('quantity > 0', name='quantity_check'),\n",
    "    )\n",
    "    \n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    customer_id = Column(Integer(), ForeignKey('customers.id'))\n",
    "    date_placed = Column(DateTime(), default=datetime.now, nullable=False)\n",
    "    date_shipped = Column(DateTime())\n",
    "#     items = relationship(\"OrderLine\")\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"<Order:{0}>\".format(self.id)\n",
    "    \n",
    "\n",
    "class OrderLine(Base):\n",
    "    __tablename__ = 'order_lines'\n",
    "    id =  Column(Integer(), primary_key=True)\n",
    "    order_id = Column(Integer(), ForeignKey('orders.id'))\n",
    "    item_id = Column(Integer(), ForeignKey('items.id'))\n",
    "    quantity = Column(SmallInteger())\n",
    "    order = relationship(\"Order\", backref='order_lines')\n",
    "    item = relationship(\"Item\")\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"<OrderLine:{0}>\".format(self.id)    \n",
    "\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Base.metadata.drop_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker, Session\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(<Customer:None-johngreen>, <Customer:None-katwilson>)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = Customer(first_name = 'John', \n",
    "              last_name = 'Green', \n",
    "              username = 'johngreen', \n",
    "              email = 'johngreen@mail.com', \n",
    "              address = '164 Hidden Valley Road',\n",
    "              town = 'Norfolk'\n",
    "             )\n",
    "\n",
    "c2 = Customer(    \n",
    "            first_name = 'Katherine',\n",
    "            last_name = 'Wilson',\n",
    "            username = 'katwilson',\n",
    "            email = 'katwilson@gmail.com',\n",
    "            address = '4685 West Side Avenue',\n",
    "            town = 'Peterbrugh'    \n",
    "             )\n",
    "\n",
    "c1, c2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('John', 'Green')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "('Katherine', 'Wilson')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.first_name, c1.last_name\n",
    "c2.first_name, c2.last_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add(c1)\n",
    "session.add(c2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(None, None)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.id, c2.id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add_all([c1, c2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IdentitySet([<Customer:None-johngreen>, <Customer:None-katwilson>])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 2)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.id, c2.id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "([], [])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.orders, c2.orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "c3 = Customer(\n",
    "            first_name = \"John\", \n",
    "            last_name = \"Lara\", \n",
    "            username = \"johnlara\", \n",
    "            email = \"johnlara@mail.com\", \n",
    "            address = \"3073 Derek Drive\",\n",
    "            town = \"Norfolk\"\n",
    ")\n",
    "\n",
    "c4 = Customer(          \n",
    "            first_name = \"Sarah\", \n",
    "            last_name = \"Tomlin\", \n",
    "            username = \"sarahtomlin\", \n",
    "            email = \"sarahtomlin@mail.com\",\n",
    "            address = \"3572 Poplar Avenue\",\n",
    "            town = \"Norfolk\"        \n",
    ")\n",
    "\n",
    "c5 = Customer(first_name = 'Toby', \n",
    "              last_name = 'Miller', \n",
    "              username = 'tmiller', \n",
    "              email = 'tmiller@example.com', \n",
    "              address = '1662 Kinney Street',\n",
    "              town = 'Wolfden'\n",
    "             )\n",
    "\n",
    "c6 = Customer(first_name = 'Scott', \n",
    "              last_name = 'Harvey', \n",
    "              username = 'scottharvey', \n",
    "              email = 'scottharvey@example.com', \n",
    "              address = '424 Patterson Street',\n",
    "              town = 'Beckinsdale'\n",
    "             )\n",
    "\n",
    "session.add_all([c3, c4, c5, c6])\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "i1 = Item(name = 'Chair', cost_price = 9.21, selling_price = 10.81, quantity = 5)\n",
    "i2 = Item(name = 'Pen', cost_price = 3.45, selling_price = 4.51, quantity = 3)\n",
    "i3 = Item(name = 'Headphone', cost_price = 15.52, selling_price = 16.81, quantity = 50)\n",
    "i4 = Item(name = 'Travel Bag', cost_price = 20.1, selling_price = 24.21, quantity = 50)\n",
    "i5 = Item(name = 'Keyboard', cost_price = 20.1, selling_price = 22.11, quantity = 50)\n",
    "i6 = Item(name = 'Monitor', cost_price = 200.14, selling_price = 212.89, quantity = 50)\n",
    "i7 = Item(name = 'Watch', cost_price = 100.58, selling_price = 104.41, quantity = 50)\n",
    "i8 = Item(name = 'Water Bottle', cost_price = 20.89, selling_price = 25, quantity = 50)\n",
    "\n",
    "session.add_all([i1, i2, i3, i4, i5, i6, i7, i8])\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IdentitySet([<OrderLine:None>, <OrderLine:None>, <Order:None>, <OrderLine:None>, <OrderLine:None>, <Order:None>])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o1 = Order(customer = c1)\n",
    "o2 = Order(customer = c1)\n",
    "\n",
    "order_line1 = OrderLine(order = o1, item = i1, quantity =  3)\n",
    "order_line2 = OrderLine(order = o1, item = i2, quantity =  2)\n",
    "order_line3 = OrderLine(order = o2, item = i1, quantity =  1)\n",
    "order_line4 = OrderLine(order = o2, item = i2, quantity =  4)\n",
    "\n",
    "session.add_all([o1, o2])\n",
    "\n",
    "session.new\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "o3 = Order(customer = c1)\n",
    "\n",
    "orderline1 = OrderLine(item = i1, quantity = 5)\n",
    "orderline2 = OrderLine(item = i2, quantity = 10)\n",
    "\n",
    "o3.order_lines.append(orderline1)\n",
    "o3.order_lines.append(orderline2)\n",
    "\n",
    "session.add_all([o3])\n",
    "\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Order:1>, <Order:2>, <Order:3>]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Customer:1-johngreen>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "o1.customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "([<OrderLine:1>, <OrderLine:2>], [<OrderLine:3>, <OrderLine:4>])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.orders[0].order_lines, c1.orders[1].order_lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, <Item:1-Chair>, 3)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(2, <Item:2-Pen>, 2)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(3, <Item:1-Chair>, 1)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(4, <Item:2-Pen>, 4)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for ol in c1.orders[0].order_lines:\n",
    "    ol.id, ol.item, ol.quantity\n",
    "    \n",
    "print('-------')\n",
    "    \n",
    "for ol in c1.orders[1].order_lines:\n",
    "    ol.id, ol.item, ol.quantity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying Data "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### all() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>,\n",
       " <Customer:2-katwilson>,\n",
       " <Customer:3-johnlara>,\n",
       " <Customer:4-sarahtomlin>,\n",
       " <Customer:5-tmiller>,\n",
       " <Customer:6-scottharvey>]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:1-Chair>,\n",
       " <Item:2-Pen>,\n",
       " <Item:3-Headphone>,\n",
       " <Item:4-Travel Bag>,\n",
       " <Item:5-Keyboard>,\n",
       " <Item:6-Monitor>,\n",
       " <Item:7-Watch>,\n",
       " <Item:8-Water Bottle>]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Order:1>, <Order:2>, <Order:3>]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).all()\n",
    "session.query(Order).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 John\n",
      "2 Katherine\n",
      "3 John\n",
      "4 Sarah\n",
      "5 Toby\n",
      "6 Scott\n"
     ]
    }
   ],
   "source": [
    "q = session.query(Customer)\n",
    "\n",
    "for c in q:\n",
    "    print(c.id, c.first_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'John'),\n",
       " (2, 'Katherine'),\n",
       " (3, 'John'),\n",
       " (4, 'Sarah'),\n",
       " (5, 'Toby'),\n",
       " (6, 'Scott')]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer.id, Customer.first_name).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### count() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).count() # get the total number of records in the customers table\n",
    "session.query(Item).count()  # get the total number of records in the items table\n",
    "session.query(Order).count()  # get the total number of records in the orders table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### first() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Customer:1-johngreen>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Item:1-Chair>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Order:1>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).first()\n",
    "session.query(Item).first()\n",
    "session.query(Order).first()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### get() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Customer:1-johngreen>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Item:1-Chair>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).get(1)\n",
    "session.query(Item).get(1)\n",
    "session.query(Order).get(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### filter() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).filter(Customer.first_name == 'John').all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name = %(first_name_1)s\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer).filter(Customer.first_name == 'John'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).filter(Customer.id <= 5, Customer.town == \"Norfolk\").all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      "WHERE customers.id <= %(id_1)s AND customers.town LIKE %(town_1)s\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer).filter(Customer.id <= 5, Customer.town.like(\"Nor%\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>,\n",
       " <Customer:2-katwilson>,\n",
       " <Customer:3-johnlara>,\n",
       " <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import or_, and_, not_\n",
    "\n",
    "# find all customers who either live in Peterbrugh or Norfolk\n",
    "\n",
    "session.query(Customer).filter(or_(\n",
    "    Customer.town == 'Peterbrugh', \n",
    "    Customer.town == 'Norfolk'\n",
    ")).all()\n",
    "\n",
    "\n",
    "# find all customers whose first name is John and live in Norfolk\n",
    "\n",
    "session.query(Customer).filter(and_(\n",
    "    Customer.first_name == 'John', \n",
    "    Customer.town == 'Norfolk'\n",
    ")).all()\n",
    "\n",
    "\n",
    "# find all johns who don't live in Peterbrugh\n",
    "\n",
    "session.query(Customer).filter(and_(\n",
    "    Customer.first_name == 'John', \n",
    "    not_(\n",
    "        Customer.town == 'Peterbrugh', \n",
    "    )\n",
    ")).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IS NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Order:1>, <Order:2>, <Order:3>]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Order).filter(Order.date_shipped == None).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IS NOT NULL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Order).filter(Order.date_shipped != None).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:4-sarahtomlin>, <Customer:5-tmiller>]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).filter(Customer.first_name.in_(['Toby', 'Sarah'])).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT IN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>,\n",
       " <Customer:2-katwilson>,\n",
       " <Customer:3-johnlara>,\n",
       " <Customer:6-scottharvey>]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).filter(Customer.first_name.notin_(['Toby', 'Sarah'])).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### BETWEEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:3-Headphone>,\n",
       " <Item:4-Travel Bag>,\n",
       " <Item:5-Keyboard>,\n",
       " <Item:8-Water Bottle>]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(Item.cost_price.between(10, 50)).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT BETWEEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:1-Chair>, <Item:2-Pen>, <Item:6-Monitor>, <Item:7-Watch>]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LIKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:1-Chair>, <Item:6-Monitor>]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(Item.name.like(\"%r\")).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:7-Watch>, <Item:8-Water Bottle>]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(Item.name.ilike(\"w%\")).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NOT LIKE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:1-Chair>,\n",
       " <Item:2-Pen>,\n",
       " <Item:3-Headphone>,\n",
       " <Item:4-Travel Bag>,\n",
       " <Item:5-Keyboard>,\n",
       " <Item:6-Monitor>]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(not_(Item.name.like(\"W%\"))).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## limit() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:2-katwilson>]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Customer:2-katwilson>, <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).limit(2).all()\n",
    "session.query(Customer).filter(Customer.address.ilike(\"%avenue\")).limit(2).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      " LIMIT %(param_1)s\n",
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      "WHERE customers.address ILIKE %(address_1)s \n",
      " LIMIT %(param_1)s\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer).limit(2))\n",
    "print(session.query(Customer).filter(Customer.address.ilike(\"%avenue\")).limit(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### offset() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>, <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).limit(2).offset(2).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers \n",
      " LIMIT %(param_1)s OFFSET %(param_2)s\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer).limit(2).offset(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### order_by() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:7-Watch>, <Item:8-Water Bottle>]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Item:8-Water Bottle>, <Item:7-Watch>]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).all()\n",
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).order_by(Item.cost_price).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item:7-Watch>, <Item:8-Water Bottle>]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import desc\n",
    "session.query(Item).filter(Item.name.ilike(\"wa%\")).order_by(desc(Item.cost_price)).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### join() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer).join(Order).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT customers.id AS customers_id, customers.first_name AS customers_first_name, customers.last_name AS customers_last_name, customers.username AS customers_username, customers.email AS customers_email, customers.address AS customers_address, customers.town AS customers_town, customers.created_on AS customers_created_on, customers.updated_on AS customers_updated_on \n",
      "FROM customers JOIN orders ON customers.id = orders.customer_id\n"
     ]
    }
   ],
   "source": [
    "print(session.query(Customer).join(Order))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'johngreen', 1), (1, 'johngreen', 2), (1, 'johngreen', 3)]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Customer.id, Customer.username, Order.id).join(Order).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('John', 'Chair', Decimal('10.81'), 3), ('John', 'Pen', Decimal('4.51'), 2)]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(\n",
    "    Customer.first_name, \n",
    "    Item.name, \n",
    "    Item.selling_price, \n",
    "    OrderLine.quantity\n",
    ").join(Order).join(OrderLine).join(Item).filter(\n",
    "    Customer.first_name == 'John',\n",
    "    Customer.last_name == 'Green',\n",
    "    Order.id == 1,\n",
    ").all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### outerjoin() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('John', 3),\n",
       " ('Katherine', None),\n",
       " ('Toby', None),\n",
       " ('Scott', None),\n",
       " ('Sarah', None),\n",
       " ('John', None)]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(        \n",
    "    Customer.first_name,\n",
    "    Order.id,\n",
    ").outerjoin(Order).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('John', 3),\n",
       " ('Katherine', None),\n",
       " ('Toby', None),\n",
       " ('Scott', None),\n",
       " ('Sarah', None),\n",
       " ('John', None)]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(        \n",
    "    Customer.first_name,\n",
    "    Order.id,\n",
    ").outerjoin(Order, full=True).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### group_by() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import func\n",
    "\n",
    "session.query(func.count(Customer.id)).join(Order).filter(\n",
    "    Customer.first_name == 'John',\n",
    "    Customer.last_name == 'Green',    \n",
    ").group_by(Customer.id).scalar()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### having() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3, 'Norfolk')]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find the number of customers lives in each town\n",
    "\n",
    "session.query(\n",
    "    func.count(\"*\").label('town_count'),    \n",
    "    Customer.town\n",
    ").group_by(Customer.town).having(func.count(\"*\") > 2).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Norfolk'),\n",
       " ('Peterbrugh'),\n",
       " ('Norfolk'),\n",
       " ('Norfolk'),\n",
       " ('Wolfden'),\n",
       " ('Beckinsdale')]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[('Peterbrugh'), ('Beckinsdale'), ('Wolfden'), ('Norfolk')]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[(4, 6)]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import distinct\n",
    "\n",
    "session.query(Customer.town).filter(Customer.id  < 10).all()\n",
    "session.query(Customer.town).filter(Customer.id  < 10).distinct().all()\n",
    "\n",
    "session.query(        \n",
    "    func.count(distinct(Customer.town)),\n",
    "    func.count(Customer.town)\n",
    ").all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Casting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3,\n",
       "  Decimal('3.14'),\n",
       "  datetime.datetime(2010, 12, 1, 0, 0),\n",
       "  datetime.date(2010, 12, 1))]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import cast, Date, union\n",
    "\n",
    "session.query(\n",
    "    cast(func.pi(), Integer),\n",
    "    cast(func.pi(), Numeric(10,2)),\n",
    "    cast(\"2010-12-01\", DateTime),\n",
    "    cast(\"2010-12-01\", Date),\n",
    ").all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2, 'Pen'),\n",
       " (4, 'Travel Bag'),\n",
       " (3, 'Headphone'),\n",
       " (5, 'Keyboard'),\n",
       " (7, 'Watch'),\n",
       " (8, 'Water Bottle')]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = session.query(Item.id, Item.name).filter(Item.name.like(\"Wa%\"))\n",
    "s2 = session.query(Item.id, Item.name).filter(Item.name.like(\"%e%\"))\n",
    "s1.union(s2).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(7, 'Watch'),\n",
       " (8, 'Water Bottle'),\n",
       " (2, 'Pen'),\n",
       " (3, 'Headphone'),\n",
       " (4, 'Travel Bag'),\n",
       " (5, 'Keyboard'),\n",
       " (8, 'Water Bottle')]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.union_all(s2).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = session.query(Item).get(8)\n",
    "i.selling_price = 25.91\n",
    "session.add(i)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(\n",
    "    Item.name.ilike(\"W%\")\n",
    ").update({\"quantity\": 60}, synchronize_session='fetch')\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Item:6-Monitor>"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i = session.query(Item).filter(Item.name == 'Monitor').one()\n",
    "i\n",
    "session.delete(i)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Item).filter(\n",
    "    Item.name.ilike(\"W%\")\n",
    ").delete(synchronize_session='fetch')\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Raw Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "[<Customer:3-johnlara>, <Customer:1-johngreen>, <Customer:4-sarahtomlin>]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import text\n",
    "\n",
    "session.query(Customer).filter(text(\"first_name = 'John'\")).all()\n",
    "\n",
    "session.query(Customer).filter(text(\"town like 'Nor%'\")).all()\n",
    "\n",
    "session.query(Customer).filter(text(\"town like 'Nor%'\")).order_by(text(\"first_name, id desc\")).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import update\n",
    "from sqlalchemy.exc import IntegrityError\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "def dispatch_order(order_id):\n",
    "\n",
    "    # check whether order_id is valid or not\n",
    "    order = session.query(Order).get(order_id)\n",
    "    \n",
    "    if not order:\n",
    "        raise ValueError(\"Invalid order id: {}.\".format(order_id))    \n",
    "        \n",
    "    if order.date_shipped:\n",
    "        print(\"Order already shipped.\")\n",
    "        return\n",
    "\n",
    "    try:\n",
    "        for i in order.order_lines:\n",
    "            i.item.quantity = i.item.quantity - i.quantity            \n",
    "        \n",
    "        order.date_shipped = datetime.now()                            \n",
    "        session.commit()\n",
    "        print(\"Transaction completed.\")\n",
    "\n",
    "    except IntegrityError as e:\n",
    "        print(e)\n",
    "        print(\"Rolling back ...\")\n",
    "        session.rollback()\n",
    "        print(\"Transaction failed.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transaction completed.\n"
     ]
    }
   ],
   "source": [
    "dispatch_order(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(psycopg2.IntegrityError) new row for relation \"items\" violates check constraint \"quantity_check\"\n",
      "DETAIL:  Failing row contains (2, Pen, 3.45, 4.51, -3).\n",
      " [SQL: 'UPDATE items SET quantity=%(quantity)s WHERE items.id = %(items_id)s'] [parameters: ({'quantity': 1, 'items_id': 1}, {'quantity': -3, 'items_id': 2})] (Background on this error at: http://sqlalche.me/e/gkpj)\n",
      "Rolling back ...\n",
      "Transaction failed.\n"
     ]
    }
   ],
   "source": [
    "dispatch_order(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}