{
 "metadata": {
  "name": "SQLAlchemyDemo"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "import datetime\nfrom sqlalchemy.engine import create_engine\nfrom sqlalchemy.ext.declarative.api import declarative_base\nfrom sqlalchemy.orm import relationship\nfrom sqlalchemy.orm.session import Session\nfrom sqlalchemy.schema import Column, ForeignKey\nfrom sqlalchemy.sql.expression import case\nfrom sqlalchemy.sql.functions import count\nfrom sqlalchemy.types import Integer, String, DateTime",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "Base = declarative_base()\nclass Book(Base):\n    __tablename__ = 'book'\n    id = Column(Integer, primary_key=True)\n    title = Column(String)\n    publication_date = Column(DateTime)\n    pages_count = Column(Integer)\n    author_id = Column(Integer, ForeignKey('author.id'))\n\nclass Author(Base):\n    __tablename__ = 'author'\n    id = Column(Integer, primary_key=True)\n    name = Column(String)\n    books = relationship(Book, backref=\"author\")\n",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "db_uri = \"sqlite:///\"\nengine = create_engine(db_uri, echo=False)\nBase.metadata.create_all(engine)",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "session = Session(bind=engine)",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "insert_stmt = Book.__table__.insert()\nprint insert_stmt",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "INSERT INTO book (id, title, publication_date, pages_count, author_id) VALUES (:id, :title, :publication_date, :pages_count, :author_id)\n"
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "data = {'title': 'Essential SQLAlchemy', 'publication_date': datetime.datetime.strptime('2010-05-05', '%Y-%m-%d'),\n        'pages_count': 240}\nengine.connect().execute(insert_stmt, data)",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 12,
       "text": "<sqlalchemy.engine.result.ResultProxy at 0x4c87f90>"
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "session = Session(bind=engine)\nq = session.query(Book).filter(Book.title == 'Essential SQLAlchemy')\nprint q",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "SELECT book.id AS book_id, book.title AS book_title, book.publication_date AS book_publication_date, book.pages_count AS book_pages_count, book.author_id AS book_author_id \nFROM book \nWHERE book.title = :title_1\n"
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "book = q.one()\nprint (book.id, book.title)",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "(1, u'Essential SQLAlchemy')\n"
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "book.__dict__",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "pyout",
       "prompt_number": 15,
       "text": "{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState at 0x4c87570>,\n 'author_id': None,\n 'id': 1,\n 'pages_count': 240,\n 'publication_date': datetime.datetime(2010, 5, 5, 0, 0),\n 'title': u'Essential SQLAlchemy'}"
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "author = Author(name='Rick Copeland')\nauthor.books.append(book)\nprint author.books",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "[<__main__.Book object at 0x04C7EAB0>]\n"
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "session.add(book)\nsession.flush()",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "is_novel_column = case([(Book.pages_count>200, 1)], else_=0)\nprint is_novel_column",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "CASE WHEN (book.pages_count > :pages_count_1) THEN :param_1 ELSE :param_2 END\n"
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "novel_query = session.query(is_novel_column.label('is_alias'), count()).\\\n    group_by(is_novel_column).\\\n    order_by(is_novel_column)\nprint novel_query",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "SELECT CASE WHEN (book.pages_count > :pages_count_1) THEN :param_1 ELSE :param_2 END AS is_alias, count(*) AS count_1 \nFROM book GROUP BY CASE WHEN (book.pages_count > :pages_count_1) THEN :param_1 ELSE :param_2 END ORDER BY CASE WHEN (book.pages_count > :pages_count_1) THEN :param_1 ELSE :param_2 END\n"
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "print novel_query.all()",
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": "[(1, 1)]\n"
      }
     ],
     "prompt_number": 24
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": "session.close()",
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 25
    }
   ],
   "metadata": {}
  }
 ]
}