Pony ORM demo

pony demo.ipynb

{
 "metadata": {
  "name": "Pony Demo"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from decimal import Decimal\n",
      "from pony.orm import *\n",
      "\n",
      "# hard coded path \n",
      "db = Database(\"sqlite\", \"/Users/cm022291/demo.sqlite\", create_db=True)\n",
      "\n",
      "class Customer(db.Entity):\n",
      "    id = PrimaryKey(int, auto=True)\n",
      "    name = Required(unicode)\n",
      "    email = Required(unicode, unique=True)\n",
      "    orders = Set(\"Order\")\n",
      "\n",
      "class Order(db.Entity):\n",
      "    id = PrimaryKey(int, auto=True)\n",
      "    total_price = Required(Decimal)\n",
      "    customer = Required(Customer)\n",
      "    items = Set(\"OrderItem\")\n",
      "\n",
      "class Product(db.Entity):\n",
      "    id = PrimaryKey(int, auto=True)\n",
      "    name = Required(unicode)\n",
      "    price = Required(Decimal)\n",
      "    items = Set(\"OrderItem\")\n",
      "\n",
      "class OrderItem(db.Entity):\n",
      "    quantity = Required(int, default=1)\n",
      "    order = Required(Order)\n",
      "    product = Required(Product)\n",
      "    PrimaryKey(order, product)\n",
      "\n",
      "sql_debug(True)\n",
      "db.generate_mapping(create_tables=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def populate_database():\n",
      "    # drop the tables\n",
      "    db.drop_all_tables(with_all_data=True)\n",
      "    \n",
      "    # (re)create all the tables\n",
      "    db.create_tables()\n",
      "    \n",
      "    c1 = Customer(name='John Smith', email='[email protected]')\n",
      "    c2 = Customer(name='Matthew Reed', email='[email protected]')\n",
      "    c3 = Customer(name='Chuan Qin', email='[email protected]')\n",
      "    c4 = Customer(name='Rebecca Lawson', email='[email protected]')\n",
      "    c5 = Customer(name='Oliver Blakey', email='[email protected]')\n",
      "\n",
      "    p1 = Product(name='Kindle Fire HD', price=Decimal('284.00'))\n",
      "    p2 = Product(name='Apple iPad with Retina Display', price=Decimal('478.50'))\n",
      "    p3 = Product(name='SanDisk Cruzer 16 GB USB Flash Drive', price=Decimal('9.99'))\n",
      "    p4 = Product(name='Kingston DataTraveler 16GB USB 2.0', price=Decimal('9.98'))\n",
      "    p5 = Product(name='Samsung 840 Series 120GB SATA III SSD', price=Decimal('98.95'))\n",
      "    p6 = Product(name='Crucial m4 256GB SSD SATA 6Gb/s', price=Decimal('188.67'))\n",
      "\n",
      "    o1 = Order(customer=c1, total_price=Decimal('292.00'))\n",
      "    OrderItem(order=o1, product=p1)\n",
      "    OrderItem(order=o1, product=p4, quantity=2)\n",
      "\n",
      "    o2 = Order(customer=c1, total_price=Decimal('478.50'))\n",
      "    OrderItem(order=o2, product=p2)\n",
      "\n",
      "    o3 = Order(customer=c2, total_price=Decimal('680.50'))\n",
      "    OrderItem(order=o3, product=p2)\n",
      "    OrderItem(order=o3, product=p4, quantity=2)\n",
      "    OrderItem(order=o3, product=p6)\n",
      "\n",
      "    o4 = Order(customer=c3, total_price=Decimal('99.80'))\n",
      "    OrderItem(order=o4, product=p4, quantity=10)\n",
      "\n",
      "    o5 = Order(customer=c4, total_price=Decimal('722.00'))\n",
      "    OrderItem(order=o5, product=p1)\n",
      "    OrderItem(order=o5, product=p2)\n",
      "\n",
      "    commit()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "populate_database()\n",
      "\n",
      "# show me all the customers, I need to bug them with an email\n",
      "select(c for c in Customer).show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "id|name          |email               \n",
        "--+--------------+--------------------\n",
        "1 |John Smith    |[email protected]    \n",
        "2 |Matthew Reed  |[email protected] \n",
        "3 |Chuan Qin     |[email protected]\n",
        "4 |Rebecca Lawson|[email protected] \n",
        "5 |Oliver Blakey |[email protected]  \n"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# show me the inexpensive products, we need to raise those prices\n",
      "select(p for p in Product if p.price < Decimal(100)).show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "id|name                                 |price\n",
        "--+-------------------------------------+-----\n",
        "3 |SanDisk Cruzer 16 GB USB Flash Drive |9.99 \n",
        "4 |Kingston DataTraveler 16GB USB 2.0   |9.98 \n",
        "5 |Samsung 840 Series 120GB SATA III SSD|98.95\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# special today, for customers with R names\n",
      "select(c for c in Customer if c.name.startswith('R')).show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "id|name          |email              \n",
        "--+--------------+-------------------\n",
        "4 |Rebecca Lawson|[email protected]\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Show me who is buying lots of stuff!\n",
      "select(o for o in Order).order_by(desc(Order.total_price)).show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "id|total_price|customer   \n",
        "--+-----------+-----------\n",
        "5 |722.00     |Customer[4]\n",
        "3 |680.50     |Customer[2]\n",
        "2 |478.50     |Customer[1]\n",
        "1 |292.00     |Customer[1]\n",
        "4 |99.80      |Customer[3]\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Lets make this fun (join some tables)\n",
      "select(o for o in Order for c in Customer if o.customer.name == 'John Smith' or o.customer.email == '[email protected]' ).show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "id|total_price|customer   \n",
        "--+-----------+-----------\n",
        "1 |292.00     |Customer[1]\n",
        "2 |478.50     |Customer[1]\n",
        "5 |722.00     |Customer[4]\n"
       ]
      }
     ],
     "prompt_number": 27
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# http://ponyorm.com/"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}