{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 对象关系映射"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据库中的记录可以与一个 `Python` 对象对应。\n",
    "\n",
    "例如对于上一节中的数据库：\n",
    "\n",
    "Order|Date|Stock|Quantity|Price\n",
    "--|--|--|--|--\n",
    "A0001|2013-12-01|AAPL|1000|203.4\n",
    "A0002|2013-12-01|MSFT|1500|167.5\n",
    "A0003|2013-12-02|GOOG|1500|167.5\n",
    "\n",
    "可以用一个类来描述：\n",
    "\n",
    "Attr.|Method\n",
    "--|--\n",
    "Order id| Cost\n",
    "Date|\n",
    "Stock|\n",
    "Quant.|\n",
    "Price|\n",
    "\n",
    "可以使用 `sqlalchemy` 来实现这种对应："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, Date, Float, Integer, String\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    \n",
    "    order_id = Column(String, primary_key=True)\n",
    "    date = Column(Date)\n",
    "    symbol = Column(String)\n",
    "    quantity = Column(Integer)\n",
    "    price = Column(Float)\n",
    "    \n",
    "    def get_cost(self):\n",
    "        return self.quantity*self.price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "生成一个 `Order` 对象："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "order = Order(order_id='A0004', date=datetime.date.today(), symbol='MSFT', quantity=-1000, price=187.54)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "调用方法："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-187540.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order.get_cost()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用上一节生成的数据库产生一个 `session`："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "engine = create_engine(\"sqlite:///my_database.sqlite\")   # 相当于 connection\n",
    "Session = sessionmaker(bind=engine) # 相当于 cursor\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用这个 `session` 向数据库中添加刚才生成的对象："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.add(order)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "显示是否添加成功："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'A0001', u'2013-12-01', u'AAPL', 1000, 203.4)\n",
      "(u'A0002', u'2013-12-01', u'MSFT', 1500, 167.5)\n",
      "(u'A0003', u'2013-12-02', u'GOOG', 1500, 167.5)\n",
      "(u'A0004', u'2015-09-10', u'MSFT', -1000, 187.54)\n"
     ]
    }
   ],
   "source": [
    "for row in engine.execute(\"SELECT * FROM orders\"):\n",
    "    print row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用 `filter` 进行查询，返回的是 `Order` 对象的列表："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A0001 2013-12-01 203400.0\n"
     ]
    }
   ],
   "source": [
    "for order in session.query(Order).filter(Order.symbol==\"AAPL\"):\n",
    "    print order.order_id, order.date, order.get_cost()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "返回列表的第一个："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "order_2 = session.query(Order).filter(Order.order_id=='A0002').first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'MSFT'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_2.symbol"
   ]
  }
 ],
 "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
}