Intro to SQLAlchemy Core.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:469402dd46371ed4d992cd7aa042e503414e83e8bfd4812c5aca6990ddd6d4c0"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Initializing\n",
      "\n",
      "* First we import SQLAlchemy create_engine function, which tells us what dialect and connection parameters to use\n",
      "* Next we create an engine to an in memory SQLite database"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from sqlalchemy import create_engine\n",
      "engine = create_engine('sqlite:///:memory:')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 23
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Building Tables\n",
      "It\u2019s not like a normal class definition, but a more list a list of table columns.  The metadata is used as a catalog to provide a mapping between the actual tables and our pythonic representation of them.\n",
      "\n",
      "* First we import the components we need from SQLAlchemy\n",
      "* Next we define our actors table\n",
      "* Finally we define our roles table\n",
      "\n",
      "This doesn't do us much good since there is not association between the engine we established above, and the tables objects we created."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey\n",
      "metadata = MetaData()\n",
      "actors = Table('actors', metadata,\n",
      "    Column('id', Integer, primary_key=True),\n",
      "    Column('name', String),\n",
      "    Column('fullname', String),\n",
      "    Column('body_count', Integer)\n",
      ")\n",
      "roles = Table('roles', metadata,\n",
      "    Column('id', Integer, primary_key=True),\n",
      "    Column('actor_id', None, ForeignKey('actors.id')),\n",
      "    Column('character_name', String, nullable=False)\n",
      ")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 24
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Assocating MetaData to the Engine\n",
      "We take the MetaData object we created early, and bind it to the engine as see below.  This will create our tables in the actually database.  This is conditional by default, will not attempt to recreate tables already present in the target database. NOTE: It doesn't check to make sure they are the same just that an existing table is already present."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "metadata.create_all(engine)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 25
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Table Objects\n",
      "We can look at the tables attributes, and use this to reference parts of them later. In this example, we're looking at the columns of the table.  This can also be abbreviated as just actors.c.items()"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "actors.columns.items()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 26,
       "text": [
        "[('id',\n",
        "  Column('id', Integer(), table=<actors>, primary_key=True, nullable=False)),\n",
        " ('name', Column('name', String(), table=<actors>)),\n",
        " ('fullname', Column('fullname', String(), table=<actors>)),\n",
        " ('body_count', Column('body_count', Integer(), table=<actors>))]"
       ]
      }
     ],
     "prompt_number": 26
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Opening a Connection\n",
      "We're going to open a connection to that engine so we can make queries etc."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn = engine.connect()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 27
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Single Insert\n",
      "\n",
      "* First we're going to build an insert statement for putting a single record in our actors table\n",
      "* Then, we're excute that statement and store the result\n",
      "* Next, we'll get the primary key of that record back"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ins = actors.insert().values(name='Graham', fullname='Graham Chapman', body_count=3)\n",
      "result = conn.execute(ins)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.inserted_primary_key"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 29,
       "text": [
        "[1]"
       ]
      }
     ],
     "prompt_number": 29
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Looking at the output\n",
      "Below, we're going to print the SQL statement as build via the dialect we choose above, and then print the params from that statement"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print str(ins)\n",
      "ins.compile().params"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "INSERT INTO actors (name, fullname, body_count) VALUES (:name, :fullname, :body_count)\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 30,
       "text": [
        "{'body_count': 3, 'fullname': 'Graham Chapman', 'name': 'Graham'}"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Insert some more test data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ins = actors.insert().values(name='John', fullname='John Cleese', body_count=2)\n",
      "result = conn.execute(ins)\n",
      "ins = actors.insert().values(name='Terry', fullname='Terry Gilliam', body_count=6)\n",
      "result = conn.execute(ins)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 31
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Multiple (Bulk) Insert\n",
      "This let's us bulk insert data into our table.  Transactions can be manually started, but by default wrap each execute."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "results = conn.execute(roles.insert(), [ \n",
      "    {'actor_id': 1, 'character_name' : 'King Arthur'},\n",
      "    {'actor_id': 1, 'character_name' : 'Voice of God'},\n",
      "    {'actor_id': 2, 'character_name' : 'Sir Lancelot'},\n",
      "    {'actor_id': 2, 'character_name' : 'Black Knight'},\n",
      "    {'actor_id': 3, 'character_name' : 'Patsy'},\n",
      "    {'actor_id': 3, 'character_name' : 'Sir Bors'},\n",
      "])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 32
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "results.rowcount"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 33,
       "text": [
        "6"
       ]
      }
     ],
     "prompt_number": 33
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Updating Records\n",
      "When looking at updating data we often want to do it to just a particular record, or a certain group of records.  The use of the ``.where()`` clause let's us do just that.  Then we supply a list of values to update."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stmt = actors.update().where(actors.c.name == 'Graham').values(name='Gram')\n",
      "\n",
      "result = conn.execute(stmt)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 34
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result.rowcount"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 35,
       "text": [
        "1"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Deleting Records\n",
      "Same concept as update above, except it removes the record."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = conn.execute(actors.delete().where(actors.c.name == 'Terry'))\n",
      "result.rowcount\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 36,
       "text": [
        "1"
       ]
      }
     ],
     "prompt_number": 36
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Selecting Records\n",
      "So unlike the other statements that were clauses off of the table object, this time we're going to use a new select stmt and operate on the table that way.\n",
      "You can use a clause off the table object; however, this is more commonly used as you'll normally be joining data and doing other things."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from sqlalchemy.sql import select\n",
      "s = select([actors.c.name, actors.c.fullname])\n",
      "result = conn.execute(s)\n",
      "for row in result:\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'Gram', u'Graham Chapman')\n",
        "(u'John', u'John Cleese')\n"
       ]
      }
     ],
     "prompt_number": 37
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Ordering\n",
      "Here we're sorting by the name in descending alphabetical order. ``.asc()`` will do the accending order"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stmt = select([actors.c.name]).order_by(actors.c.name.desc())\n",
      "conn.execute(stmt).fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 45,
       "text": [
        "[(u'John',), (u'Gram',)]"
       ]
      }
     ],
     "prompt_number": 45
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Limiting\n",
      "Here we are limiting the query to return only one record and offset it by 1. So it should be the second record in the table.  Notice the use of the ``.first()`` clause on the statement execution.  This gives us back the first result instead of the list we had been dealing with previously."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stmt = select([actors.c.name, actors.c.fullname]).limit(1).offset(1)\n",
      "conn.execute(stmt).first()  "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 46,
       "text": [
        "(u'John', u'John Cleese')"
       ]
      }
     ],
     "prompt_number": 46
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Counting\n",
      "* First we import ``func``\n",
      "* Next we build a select statement that counts the actors in our table\n",
      "* Finally, we execute the statement, and since this returns only a number I use the ``scalar()`` method to return to get back just the number. It actually returns the first column of the first row."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from sqlalchemy.sql import func\n",
      "stmt = select([func.count(actors)])\n",
      "conn.execute(stmt).scalar()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 51,
       "text": [
        "2"
       ]
      }
     ],
     "prompt_number": 51
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Summing\n",
      "Here we are building a select statement that counts the actors in our table, and sums their body counts"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stmt = select([func.count(actors), func.sum(actors.c.body_count)])\n",
      "conn.execute(stmt).first()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 55,
       "text": [
        "(2, 5)"
       ]
      }
     ],
     "prompt_number": 55
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Joins\n",
      "Now let's join the actors and the roles tables on the ID of the actor."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s = select([actors, roles]).where(actors.c.id == roles.c.actor_id)\n",
      "for row in conn.execute(s):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(1, u'Gram', u'Graham Chapman', 3, 1, 1, u'King Arthur')\n",
        "(1, u'Gram', u'Graham Chapman', 3, 2, 1, u'Voice of God')\n",
        "(2, u'John', u'John Cleese', 2, 3, 2, u'Sir Lancelot')\n",
        "(2, u'John', u'John Cleese', 2, 4, 2, u'Black Knight')\n"
       ]
      }
     ],
     "prompt_number": 56
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Grouping\n",
      "We can also use the ``.join()`` clause on the table object to join to tables, and then group them to get a count of roles by actor."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "stmt = select([actors.c.name, func.count(roles.c.id)]).\\\n",
      "    select_from(actors.join(roles)).\\\n",
      "    group_by(actors.c.name)\n",
      "conn.execute(stmt).fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 57,
       "text": [
        "[(u'Gram', 2), (u'John', 2)]"
       ]
      }
     ],
     "prompt_number": 57
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Filtering\n",
      "\n",
      "* First, let's pull in some additional parts of the sqlalchemy.sql module. NOTE: we're only using ``and_()`` in this example, but they all work the same way and can be nested.\n",
      "* Next we're going to use the ``.where()`` clause and nest an ``and_()`` cause inside of it."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from sqlalchemy.sql import and_, or_, not_\n",
      "stmt = select([actors.c.name, roles.c.character_name]).\\\n",
      "    where(\n",
      "        and_(\n",
      "          actors.c.name.like('Gra%'),\n",
      "          roles.c.character_name.like('Vo%'),\n",
      "          actors.c.id == roles.c.actor_id\n",
      "        )\n",
      "    )\n",
      "conn.execute(stmt).fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 22,
       "text": [
        "[(u'Graham', u'Voice of God')]"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 53
    }
   ],
   "metadata": {}
  }
 ]
}