SQLite Notebook.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# SQLite Notebook"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Before to Start: Importing the Libraries and Packages and Checking the Versions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%matplotlib inline\n",
      "import os\n",
      "import math\n",
      "import IPython\n",
      "import sqlite3 as sql3\n",
      "import time as t\n",
      "import numpy as np\n",
      "import pandas as pd\n",
      "from sys import version\n",
      "import matplotlib.pyplot as plt\n",
      "import matplotlib as mpl \n",
      "#os.chdir('C:\\\\Users\\\\Suso')\n",
      "print '='*70\n",
      "print 'Python version:     ' + version\n",
      "print 'Numpy version:      ' + np.__version__\n",
      "print 'Pandas version:     ' + pd.__version__\n",
      "print 'Matplotlib ver:     ' + mpl.__version__\n",
      "print 'SQLite version:     ' + sql3.version\n",
      "print 'IPython version:    ' + IPython.__version__\n",
      "direct = %pwd\n",
      "print 'Working directory:  ' + direct\n",
      "print '='*70\n",
      "now = t.asctime()\n",
      "print 'Today is ' + now + ' ... AND WE ARE READY TO GO!! '\n",
      "print '='*70"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "======================================================================\n",
        "Python version:     2.7.5 |Anaconda 1.8.0 (32-bit)| (default, Jul  1 2013, 12:41:55) [MSC v.1500 32 bit (Intel)]\n",
        "Numpy version:      1.7.1\n",
        "Pandas version:     0.12.0\n",
        "Matplotlib ver:     1.3.1\n",
        "SQLite version:     2.6.0\n",
        "IPython version:    1.1.0\n",
        "Working directory:  C:\\Users\\Suso\n",
        "======================================================================\n",
        "Today is Tue Dec 17 15:20:56 2013 ... AND WE ARE READY TO GO!! \n",
        "======================================================================\n"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## A Quick Introduction to SQLite with Python"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "os.unlink('data.db')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "- **First**, connect to the database using the database library's `connect` method. \n",
      "- **Second**, get a `cursor` which will let us execute SQL commands\n",
      "- **Third**, We can now execute any SQL commands that we want in the database using the cursor's `execute` method. Querying the database simply involves writing the appropriate SQL and placing it inside a string in the `execute` method call."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "db = sql3.connect('data.db')\n",
      "\n",
      "with db:\n",
      "    c = db.cursor()\n",
      "    c.execute('SELECT SQLITE_VERSION()')\n",
      "    data = c.fetchone()\n",
      "    print \"SQLite version: %s\" % data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "SQLite version: 3.6.21\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c = db.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('CREATE TABLE test (i INTEGER, j TEXT)')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "<sqlite3.Cursor at 0x5c13960>"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "n = 5\n",
      "m = 'some text' \n",
      "\n",
      "c.execute('INSERT INTO test(i,j) VALUES (?,?)', (n,m))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "<sqlite3.Cursor at 0x5c13960>"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "n = 100\n",
      "m = 'more text' \n",
      "\n",
      "c.execute('INSERT INTO test(i,j) VALUES (?,?)', (n,m))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "<sqlite3.Cursor at 0x5c13960>"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('SELECT * FROM test')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "<sqlite3.Cursor at 0x5c13960>"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "results = c.fetchall()\n",
      "print results"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(5, u'some text'), (100, u'more text')]\n"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for (i,j) in results:\n",
      "    print i,j"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "5 some text\n",
        "100 more text\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('SELECT * FROM test WHERE i=5')\n",
      "print c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(5, u'some text')]\n"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('UPDATE test SET j=\\'yet more test\\'WHERE i=5')\n",
      "c.execute('SELECT * FROM test')\n",
      "print c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(5, u'yet more test'), (100, u'more text')]\n"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('DELETE FROM test WHERE i=5')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "<sqlite3.Cursor at 0x5c13960>"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.execute('SELECT * FROM test')\n",
      "print c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(100, u'more text')]\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## SQLite Basic Tutorial\n",
      "\n",
      "### 1. Inserting and Quering Data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# os.unlink('test.db')\n",
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()\n",
      "    cur.execute(\"DROP TABLE IF EXISTS Cars\")    \n",
      "    cur.execute(\"CREATE TABLE Cars(Id INT, Name TEXT, Price INT)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(1,'Audi',52642)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(2,'Mercedes',57127)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(3,'Skoda',9000)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(4,'Volvo',29000)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(5,'Bentley',350000)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(6,'Citroen',21000)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(7,'Hummer',41400)\")\n",
      "    cur.execute(\"INSERT INTO Cars VALUES(8,'Volkswagen',21600)\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 25
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "In Python, we can use the `fetchall()` method to fetch all the records in the table:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "cur = con.cursor()\n",
      "\n",
      "cur.execute('SELECT * FROM Cars')\n",
      "\n",
      "rows = cur.fetchall()\n",
      "for row in rows:\n",
      "    print row\n",
      "    \n",
      "# or, you can do also: \n",
      "# print cur.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(1, u'Audi', 52642)\n",
        "(2, u'Mercedes', 57127)\n",
        "(3, u'Skoda', 9000)\n",
        "(4, u'Volvo', 29000)\n",
        "(5, u'Bentley', 350000)\n",
        "(6, u'Citroen', 21000)\n",
        "(7, u'Hummer', 41400)\n",
        "(8, u'Volkswagen', 21600)\n"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Or, alternatively, to get the results into Python we then use either the `fetchone()` method to fetch one record at a time (it returns None when there are no more records to fetch so that you know when to stop)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "cur = con.cursor()\n",
      "\n",
      "cur.execute('SELECT * FROM Cars')\n",
      "record = cur.fetchone()\n",
      "\n",
      "while record:\n",
      "    print record\n",
      "    record = cur.fetchone()\n",
      "    \n",
      "    "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(1, u'Audi', 52642)\n",
        "(2, u'Mercedes', 57127)\n",
        "(3, u'Skoda', 9000)\n",
        "(4, u'Volvo', 29000)\n",
        "(5, u'Bentley', 350000)\n",
        "(6, u'Citroen', 21000)\n",
        "(7, u'Hummer', 41400)\n",
        "(8, u'Volkswagen', 21600)\n"
       ]
      }
     ],
     "prompt_number": 29
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Another possiblity ..."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:    \n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    cur.execute(\"SELECT * FROM Cars\")\n",
      "    \n",
      "    rows = cur.fetchall()\n",
      "    \n",
      "    for row in rows:\n",
      "        print row[0], row[1], row[2]\n",
      "        \n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1 Audi 52642\n",
        "2 Mercedes 57127\n",
        "3 Skoda 9000\n",
        "4 Volvo 29000\n",
        "5 Bentley 350000\n",
        "6 Citroen 21000\n",
        "7 Hummer 41400\n",
        "8 Volkswagen 21600\n"
       ]
      }
     ],
     "prompt_number": 33
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "A technically better version of the previous code to retrive data is"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    cur.execute(\"SELECT * FROM Cars\")\n",
      "\n",
      "    while True:\n",
      "      \n",
      "        row = cur.fetchone()\n",
      "        \n",
      "        if row == None:\n",
      "            break\n",
      "            \n",
      "        print row[0], row[1], row[2]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "1 Audi 52642\n",
        "2 Mercedes 57127\n",
        "3 Skoda 9000\n",
        "4 Volvo 29000\n",
        "5 Bentley 350000\n",
        "6 Citroen 21000\n",
        "7 Hummer 41400\n",
        "8 Volkswagen 21600\n"
       ]
      }
     ],
     "prompt_number": 32
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We are going to create the same table. This time using the convenience `executemany()` method."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cars = (\n",
      "    (1, 'Audi', 52642),\n",
      "    (2, 'Mercedes', 57127),\n",
      "    (3, 'Skoda', 9000),\n",
      "    (4, 'Volvo', 29000),\n",
      "    (5, 'Bentley', 350000),\n",
      "    (6, 'Hummer', 41400),\n",
      "    (7, 'Volkswagen', 21600)\n",
      ")\n",
      "\n",
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    # This script drops a Cars table if it exists and (re)creates it.\n",
      "    cur.execute(\"DROP TABLE IF EXISTS Cars\")\n",
      "    cur.execute(\"CREATE TABLE Cars(Id INT, Name TEXT, Price INT)\")\n",
      "    # The first SQL statement drops the Cars table, if it exists. \n",
      "    # The second SQL statement creates the Cars table.\n",
      "    cur.executemany(\"INSERT INTO Cars VALUES(?, ?, ?)\", cars)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 23
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Another way to create our Cars table: We commit the changes manually and provide our own **error handling**.\n",
      "In the script below we re-create the Cars table using the `executescript()` method"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "try:\n",
      "    con = sql3.connect('test.db')\n",
      "\n",
      "    cur = con.cursor()  \n",
      "    \n",
      "    # The executescript() method allows us to execute the whole SQL code in one step.\n",
      "    \n",
      "    cur.executescript(\"\"\"\n",
      "        DROP TABLE IF EXISTS Cars;\n",
      "        CREATE TABLE Cars(Id INT, Name TEXT, Price INT);\n",
      "        INSERT INTO Cars VALUES(1,'Audi',52642);\n",
      "        INSERT INTO Cars VALUES(2,'Mercedes',57127);\n",
      "        INSERT INTO Cars VALUES(3,'Skoda',9000);\n",
      "        INSERT INTO Cars VALUES(4,'Volvo',29000);\n",
      "        INSERT INTO Cars VALUES(5,'Bentley',350000);\n",
      "        INSERT INTO Cars VALUES(6,'Citroen',21000);\n",
      "        INSERT INTO Cars VALUES(7,'Hummer',41400);\n",
      "        INSERT INTO Cars VALUES(8,'Volkswagen',21600);\n",
      "        \"\"\")\n",
      "\n",
      "    con.commit()\n",
      "    \n",
      "except lite.Error, e:\n",
      "    \n",
      "    if con:\n",
      "        con.rollback()\n",
      "        \n",
      "    print \"Error %s:\" % e.args[0]\n",
      "    sys.exit(1)\n",
      "    \n",
      "finally:\n",
      "    \n",
      "    if con:\n",
      "        con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 24
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##2. Parameterized queries"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "When we use parameterized queries, we use placeholders instead of directly writing the values into the statements. Parameterized queries increase security and performance.\n",
      "\n",
      "The Python **SQLite3** module supports two types of placeholders. Question marks and named placeholders."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uId = 1\n",
      "uPrice = 62300 \n",
      "\n",
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "\n",
      "    cur = con.cursor()    \n",
      "\n",
      "    cur.execute(\"UPDATE Cars SET Price=? WHERE Id=?\", (uPrice, uId))        \n",
      "    con.commit()\n",
      "    \n",
      "    print \"Number of rows updated: %d\" % cur.rowcount"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Number of rows updated: 1\n"
       ]
      }
     ],
     "prompt_number": 36
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The second example uses parameterized statements with named placeholders:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "uId = 4\n",
      "\n",
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "\n",
      "    cur = con.cursor()    \n",
      "\n",
      "    cur.execute(\"SELECT Name, Price FROM Cars WHERE Id=:Id\", \n",
      "        {\"Id\": uId})        \n",
      "    con.commit()\n",
      "    \n",
      "    row = cur.fetchone()\n",
      "    print row[0], row[1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Volvo 29000\n"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##3. Metadata"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metadata is information about the data in the database. Metadata in a SQLite contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.\n",
      "\n",
      "Metadata in SQLite can be obtained using the PRAGMA command. SQLite objects may have attributes, which are metadata. Finally, we can also obtain specific metatada from querying the SQLite system sqlite_master table."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    \n",
      "    cur.execute('PRAGMA table_info(Cars)')\n",
      "    \n",
      "    data = cur.fetchall()\n",
      "    \n",
      "    for d in data:\n",
      "        print d[0], d[1], d[2]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0 Id INT\n",
        "1 Name TEXT\n",
        "2 Price INT\n"
       ]
      }
     ],
     "prompt_number": 38
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Next we will print all rows from the Cars table with their column names."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    cur.execute('SELECT * FROM Cars')\n",
      "    \n",
      "    col_names = [cn[0] for cn in cur.description]\n",
      "    \n",
      "    rows = cur.fetchall()\n",
      "    \n",
      "    print \"%-5s %-15s %s\" % (col_names[0], col_names[1], col_names[2])\n",
      "\n",
      "    for row in rows:    \n",
      "        print \"%-5s %-15s %s\" % row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Id    Name            Price\n",
        "1     Audi            62300\n",
        "2     Mercedes        57127\n",
        "3     Skoda           9000\n",
        "4     Volvo           29000\n",
        "5     Bentley         350000\n",
        "6     Citroen         21000\n",
        "7     Hummer          41400\n",
        "8     Volkswagen      21600\n"
       ]
      }
     ],
     "prompt_number": 46
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Another example related to the metadata, we list all tables in the test.db database."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sql3.connect('test.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
      "\n",
      "    rows = cur.fetchall()\n",
      "\n",
      "    for row in rows:\n",
      "        print row[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Cars\n"
       ]
      }
     ],
     "prompt_number": 49
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##4. Export and Import of Data"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can dump data in an SQL format to create a simple backup of our database tables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cars = (\n",
      "    (1, 'Audi', 52643),\n",
      "    (2, 'Mercedes', 57642),\n",
      "    (3, 'Skoda', 9000),\n",
      "    (4, 'Volvo', 29000),\n",
      "    (5, 'Bentley', 350000),\n",
      "    (6, 'Hummer', 41400),\n",
      "    (7, 'Volkswagen', 21600)\n",
      ")\n",
      "\n",
      "# The data from the table is being written to the file:\n",
      "\n",
      "def writeData(data):\n",
      "    \n",
      "    f = open('cars.sql', 'w')\n",
      "    \n",
      "    with f:\n",
      "        f.write(data)\n",
      "\n",
      "# We create a temporary table in the memory: \n",
      "\n",
      "con = sql3.connect(':memory:')\n",
      "\n",
      "# These lines create a Cars table, insert values and delete rows, \n",
      "# where the Price is less than 30000 units.\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()\n",
      "    \n",
      "    cur.execute(\"DROP TABLE IF EXISTS Cars\")\n",
      "    cur.execute(\"CREATE TABLE Cars(Id INT, Name TEXT, Price INT)\")\n",
      "    cur.executemany(\"INSERT INTO Cars VALUES(?, ?, ?)\", cars)\n",
      "    cur.execute(\"DELETE FROM Cars WHERE Price < 30000\")\n",
      "    \n",
      "    # The con.iterdump() returns an iterator to dump the database \n",
      "    # in an SQL text format. The built-in join() function takes \n",
      "    # the iterator and joins all the strings in the iterator separated \n",
      "    # by a new line. This data is written to the cars.sql file in \n",
      "    # the writeData() function.\n",
      "    \n",
      "    data = '\\n'.join(con.iterdump())\n",
      "    \n",
      "    writeData(data)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 51
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "BEGIN TRANSACTION;\n",
        "CREATE TABLE Cars(Id INT, Name TEXT, Price INT);\n",
        "INSERT INTO \"Cars\" VALUES(1,'Audi',52643);\n",
        "INSERT INTO \"Cars\" VALUES(2,'Mercedes',57642);\n",
        "INSERT INTO \"Cars\" VALUES(5,'Bentley',350000);\n",
        "INSERT INTO \"Cars\" VALUES(6,'Hummer',41400);\n",
        "COMMIT;\n"
       ]
      }
     ],
     "prompt_number": 53
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now we are going to perform a reverse operation. We will import the dumped table back into memory."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def readData():\n",
      "    \n",
      "    f = open('cars.sql', 'r')\n",
      "    \n",
      "    with f:\n",
      "        data = f.read()\n",
      "        return data\n",
      "        \n",
      "\n",
      "con = sql3.connect(':memory:')\n",
      "\n",
      "with con:   \n",
      "\n",
      "    cur = con.cursor()\n",
      "    \n",
      "    sql_query = readData()\n",
      "    cur.executescript(sql_query)\n",
      "    \n",
      "    cur.execute(\"SELECT * FROM Cars\")\n",
      "    \n",
      "    rows = cur.fetchall()\n",
      "    \n",
      "    for row in rows:\n",
      "        print row    "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(1, u'Audi', 52643)\n",
        "(2, u'Mercedes', 57642)\n",
        "(5, u'Bentley', 350000)\n",
        "(6, u'Hummer', 41400)\n"
       ]
      }
     ],
     "prompt_number": 55
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##5. Transactions"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the **SQL** statements in a transaction can be either all committed to the database or all rolled back.\n",
      "\n",
      "In **SQLite**, any command other than the `SELECT` will start an implicit transaction. Also, within a transaction a command like `CREATE TABLE` ..., `VACUUM`, `PRAGMA`, will commit previous changes before executing.\n",
      "\n",
      "Manual transactions are started with the `BEGIN TRANSACTION` statement and finished with the `COMMIT` or `ROLLBACK` statements.\n",
      "\n",
      "**SQLite** supports three non-standard transaction levels. `DEFERRED`, `IMMEDIATE` and `EXCLUSIVE`. SQLite Python module also supports an autocommit mode, where all changes to the tables are immediately effective."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# We create a friends table and try to fill it with data. However, the data is not commited...\n",
      "# because the commit() menhod is commented. \n",
      "# If we uncomment the line, the line will be written to the table:\n",
      "\n",
      "#import sqlite3 as sql\n",
      "\n",
      "try:\n",
      "    con = sql3.connect('test.db')\n",
      "    cur = con.cursor()    \n",
      "    cur.execute(\"DROP TABLE IF EXISTS Friends\")\n",
      "    cur.execute(\"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)\")\n",
      "    cur.execute(\"INSERT INTO Friends(Name) VALUES ('Tom')\")\n",
      "    cur.execute(\"INSERT INTO Friends(Name) VALUES ('Rebecca')\")\n",
      "    cur.execute(\"INSERT INTO Friends(Name) VALUES ('Jim')\")\n",
      "    cur.execute(\"INSERT INTO Friends(Name) VALUES ('Robert')\")\n",
      "    \n",
      "#---> con.commit()\n",
      "            \n",
      "except sql3.error, e:\n",
      "    \n",
      "    if con:\n",
      "        con.rollback()\n",
      "    \n",
      "    print \"Error %s:\" % e.args[0]\n",
      "    sys.exit(1)\n",
      "    \n",
      "finally:\n",
      "    \n",
      "    if con:\n",
      "        con.close() "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 66
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Code Example 04: A Data Base of Movies"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The `pandas.io.sql` module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. These wrappers only support the Python database adapters which respect the Python DB-API.\n",
      "\n",
      "Let us use, in the following example, a list of the 10.000 movies made since 1950 with the most IMDB user ratings. Download the data at http://bit.ly/cs109_imdb and save it as text file in your working directory."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "names = ['imdbID', 'title', 'year', 'score', 'votes', 'runtime', 'genres']\n",
      "movies = pd.read_csv('imdb_top_10000.txt', delimiter='\\t', names = names).dropna()\n",
      "\n",
      "print movies.head()\n",
      "\n",
      "clean_runtime = [float(r.split(' ')[0]) for r in movies.runtime]\n",
      "movies['runtime'] = clean_runtime\n",
      "\n",
      "# determine the unique genres\n",
      "\n",
      "genres = set()\n",
      "\n",
      "for m in movies.genres:\n",
      "    genres.update(g for g in m.split('|'))\n",
      "\n",
      "genres = sorted(genres)\n",
      "\n",
      "# make a column for each genre\n",
      "\n",
      "for genre in genres:\n",
      "    movies[genre] = [genre in movie.split('|') for movie in movies.genres]\n",
      "         \n",
      "movies['title'] = [t[0:-7] for t in movies.title]\n",
      "\n",
      "print movies.head()  # print the first 10 rows"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "      imdbID                            title  year  score   votes    runtime  \\\n",
        "0  tt0111161  The Shawshank Redemption (1994)  1994    9.2  619479  142 mins.   \n",
        "1  tt0110912              Pulp Fiction (1994)  1994    9.0  490065  154 mins.   \n",
        "2  tt0137523                Fight Club (1999)  1999    8.8  458173  139 mins.   \n",
        "3  tt0133093                The Matrix (1999)  1999    8.7  448114  136 mins.   \n",
        "4  tt1375666                 Inception (2010)  2010    8.9  385149  148 mins.   \n",
        "\n",
        "                             genres  \n",
        "0                       Crime|Drama  \n",
        "1                    Crime|Thriller  \n",
        "2            Drama|Mystery|Thriller  \n",
        "3           Action|Adventure|Sci-Fi  \n",
        "4  Action|Adventure|Sci-Fi|Thriller  \n",
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 5 entries, 0 to 4\n",
        "Data columns (total 31 columns):\n",
        "imdbID        5  non-null values\n",
        "title         5  non-null values\n",
        "year          5  non-null values\n",
        "score         5  non-null values\n",
        "votes         5  non-null values\n",
        "runtime       5  non-null values\n",
        "genres        5  non-null values\n",
        "Action        5  non-null values\n",
        "Adult         5  non-null values\n",
        "Adventure     5  non-null values\n",
        "Animation     5  non-null values\n",
        "Biography     5  non-null values\n",
        "Comedy        5  non-null values\n",
        "Crime         5  non-null values\n",
        "Drama         5  non-null values\n",
        "Family        5  non-null values\n",
        "Fantasy       5  non-null values\n",
        "Film-Noir     5  non-null values\n",
        "History       5  non-null values\n",
        "Horror        5  non-null values\n",
        "Music         5  non-null values\n",
        "Musical       5  non-null values\n",
        "Mystery       5  non-null values\n",
        "News          5  non-null values\n",
        "Reality-TV    5  non-null values\n",
        "Romance       5  non-null values\n",
        "Sci-Fi        5  non-null values\n",
        "Sport         5  non-null values\n",
        "Thriller      5  non-null values\n",
        "War           5  non-null values\n",
        "Western       5  non-null values\n",
        "dtypes: bool(24), float64(2), int64(2), object(3)"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n"
       ]
      }
     ],
     "prompt_number": 112
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "\n",
      "# to load Dataframes into a SQl dataBase we need something from pandas\n",
      "from pandas.io import sql\n",
      "\n",
      "\n",
      "# Create your connection.\n",
      "cnx = sql3.connect('movies.db')\n",
      "\n",
      "# Load the DataFrame in SQLite3\n",
      "sql.write_frame(movies, name='movies', con=cnx)\n",
      "\n",
      "# Retriving the data from SQLite3\n",
      "# p1 = sql.read_frame('SELECT * FROM movies', cnx)\n",
      "\n",
      "p3 = sql.read_frame('SELECT * FROM movies WHERE year=2001', cnx)\n",
      "\n",
      "p3.shape"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 114,
       "text": [
        "(353, 31)"
       ]
      }
     ],
     "prompt_number": 114
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cnx = sql3.connect('movies.db')\n",
      "\n",
      "with con:\n",
      "    \n",
      "    cur = con.cursor()    \n",
      "    \n",
      "    cur.execute('PRAGMA table_info(Movies)')\n",
      "    \n",
      "    data = cur.fetchall()\n",
      "    \n",
      "    for d in data:\n",
      "        print d[0], d[1], d[2]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 98
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Code Example 05: Time Series Storage in a Data Base\n",
      "\n",
      "Functions from `pandas.io.data` extract data from various Internet sources into a DataFrame. Currently the following sources are supported:\n",
      "   \n",
      "   - **Yahoo! Finance** with `web.DataReader(ticker,'yahoo', start, end)`\n",
      "   - **Google Finance** with `web.DataReader(ticker,'google', start, end)`\n",
      "   - **St. Louis FED (FRED)** with `web.DataReader('GDP', 'fred', start, end)`\n",
      "   - **Kenneth French\u2019s** data library with `web.DataReader(\"5_Industry_Portfolios\", \"famafrench\")`\n",
      "\n",
      "It should be noted, that various sources support different kinds of data, so not all sources implement the same methods and the data elements returned might also differ."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Download data from yahoo\n",
      "import pandas.io.data as web\n",
      "\n",
      "start = pd.datetime(2013, 1, 1)\n",
      "end = pd.datetime(2013, 12, 1)\n",
      "\n",
      "# f=web.DataReader(\"F\", 'yahoo', start, end)\n",
      "\n",
      "all_data = {}\n",
      "\n",
      "for ticker in ['AAPL', 'GOOG', 'MSFT', 'DELL', 'GS', 'MS', 'BAC']:\n",
      "    all_data[ticker] = web.DataReader(ticker,'yahoo', start, end)\n",
      "\n",
      "# create a data frame\n",
      "# prices = pd.DataFrame({tic: data['Adj Close'] for tic, data in all_data.iteritems()}).dropna()\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# create a data frame\n",
      "prices = pd.DataFrame({tic: data['Adj Close'] for tic, data in all_data.iteritems()})\n",
      "prices['Dates']=prices.index()\n",
      "print prices.describe()\n",
      "print '='*100\n",
      "print prices.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "             AAPL         BAC        DELL         GOOG          GS          MS        MSFT\n",
        "count  231.000000  231.000000  209.000000   231.000000  231.000000  231.000000  231.000000\n",
        "mean   458.926234   13.238095   13.385981   866.051082  154.092944   25.145498   31.558009\n",
        "std     39.097435    1.215939    0.664270    81.248638    8.998906    2.959034    3.395749\n",
        "min    383.180000   11.000000   10.500000   702.870000  129.240000   19.420000   25.680000\n",
        "25%    427.175000   12.125000   13.190000   806.525000  147.780000   22.660000   27.755000\n",
        "50%    447.730000   13.280000   13.570000   871.980000  155.660000   25.250000   32.290000\n",
        "75%    492.085000   14.310000   13.800000   896.380000  161.740000   27.240000   34.155000\n",
        "max    556.070000   15.870000   14.260000  1063.110000  169.190000   31.540000   38.130000\n",
        "====================================================================================================\n",
        "              AAPL    BAC   DELL    GOOG      GS     MS   MSFT\n",
        "Date                                                          \n",
        "2013-01-02  535.58  11.99  10.50  723.25  129.95  19.46  26.81\n",
        "2013-01-03  528.82  11.92  10.75  723.67  129.24  19.42  26.45\n",
        "2013-01-04  514.09  12.07  10.78  737.97  132.76  20.03  25.95\n",
        "2013-01-07  511.06  12.05  10.87  734.75  132.51  19.64  25.91\n",
        "2013-01-08  512.44  11.94  10.59  733.30  131.32  19.49  25.77\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Notice that writing your DataFrame into a database works only with SQLite. \n",
      "# Moreover, the index will currently be dropped, therefore first, we have \n",
      "# to move it as column\n",
      "\n",
      "prices['Dates']=prices.index[:]\n",
      "prices.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>AAPL</th>\n",
        "      <th>BAC</th>\n",
        "      <th>DELL</th>\n",
        "      <th>GOOG</th>\n",
        "      <th>GS</th>\n",
        "      <th>MS</th>\n",
        "      <th>MSFT</th>\n",
        "      <th>Dates</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Date</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2013-01-02</th>\n",
        "      <td> 535.58</td>\n",
        "      <td> 11.99</td>\n",
        "      <td> 10.50</td>\n",
        "      <td> 723.25</td>\n",
        "      <td> 129.95</td>\n",
        "      <td> 19.46</td>\n",
        "      <td> 26.81</td>\n",
        "      <td>2013-01-02 00:00:00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-03</th>\n",
        "      <td> 528.82</td>\n",
        "      <td> 11.92</td>\n",
        "      <td> 10.75</td>\n",
        "      <td> 723.67</td>\n",
        "      <td> 129.24</td>\n",
        "      <td> 19.42</td>\n",
        "      <td> 26.45</td>\n",
        "      <td>2013-01-03 00:00:00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-04</th>\n",
        "      <td> 514.09</td>\n",
        "      <td> 12.07</td>\n",
        "      <td> 10.78</td>\n",
        "      <td> 737.97</td>\n",
        "      <td> 132.76</td>\n",
        "      <td> 20.03</td>\n",
        "      <td> 25.95</td>\n",
        "      <td>2013-01-04 00:00:00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-07</th>\n",
        "      <td> 511.06</td>\n",
        "      <td> 12.05</td>\n",
        "      <td> 10.87</td>\n",
        "      <td> 734.75</td>\n",
        "      <td> 132.51</td>\n",
        "      <td> 19.64</td>\n",
        "      <td> 25.91</td>\n",
        "      <td>2013-01-07 00:00:00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-08</th>\n",
        "      <td> 512.44</td>\n",
        "      <td> 11.94</td>\n",
        "      <td> 10.59</td>\n",
        "      <td> 733.30</td>\n",
        "      <td> 131.32</td>\n",
        "      <td> 19.49</td>\n",
        "      <td> 25.77</td>\n",
        "      <td>2013-01-08 00:00:00</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 29,
       "text": [
        "              AAPL    BAC   DELL    GOOG      GS     MS   MSFT  \\\n",
        "Date                                                             \n",
        "2013-01-02  535.58  11.99  10.50  723.25  129.95  19.46  26.81   \n",
        "2013-01-03  528.82  11.92  10.75  723.67  129.24  19.42  26.45   \n",
        "2013-01-04  514.09  12.07  10.78  737.97  132.76  20.03  25.95   \n",
        "2013-01-07  511.06  12.05  10.87  734.75  132.51  19.64  25.91   \n",
        "2013-01-08  512.44  11.94  10.59  733.30  131.32  19.49  25.77   \n",
        "\n",
        "                         Dates  \n",
        "Date                            \n",
        "2013-01-02 2013-01-02 00:00:00  \n",
        "2013-01-03 2013-01-03 00:00:00  \n",
        "2013-01-04 2013-01-04 00:00:00  \n",
        "2013-01-07 2013-01-07 00:00:00  \n",
        "2013-01-08 2013-01-08 00:00:00  "
       ]
      }
     ],
     "prompt_number": 29
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# To load DataFrames into a SQLite DataBase we need something from pandas\n",
      "# that transforms DataFrames into tables and back \n",
      "\n",
      "from pandas.io import sql\n",
      "\n",
      "# Create your connection\n",
      "\n",
      "cnx = sql3.connect('prices.db')\n",
      "\n",
      "# Load the DataFrame in SQLite3\n",
      "cur = cnx.cursor()    \n",
      "cur.execute(\"DROP TABLE IF EXISTS prices\")\n",
      "\n",
      "sql.write_frame(prices, name='prices', con = cnx)\n",
      "       \n",
      "# Retriving the data from SQLite3\n",
      "# apple = sql.read_frame(\"SELECT AAPL FROM prices\", cnx)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 30
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cnx = sql3.connect('prices.db')\n",
      "\n",
      "with cnx:\n",
      "    \n",
      "    cur = cnx.cursor()    \n",
      "    cur.execute('PRAGMA table_info(prices)')\n",
      "    \n",
      "    table = cur.fetchall()\n",
      "    \n",
      "    for d in table:\n",
      "        print d[0], d[1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0 AAPL\n",
        "1 BAC\n",
        "2 DELL\n",
        "3 GOOG\n",
        "4 GS\n",
        "5 MS\n",
        "6 MSFT\n",
        "7 Dates\n"
       ]
      }
     ],
     "prompt_number": 31
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Retriving the data from SQLite3\n",
      "\n",
      "from pandas.io import sql\n",
      "from pandas.lib import Timestamp\n",
      "\n",
      "cnx = sql3.connect('prices.db')\n",
      "allp =  sql.read_frame(\"SELECT * FROM prices\", cnx)\n",
      "apple2 = sql.read_frame(\"SELECT AAPL,Dates FROM prices\", cnx)\n",
      "\n",
      "# from pandas.lib import Timestamp\n",
      "apple2.Dates = apple2.Dates.apply(Timestamp)\n",
      "apple = apple2.set_index('Dates')\n",
      "\n",
      "\n",
      "start = pd.datetime(2013, 1, 1)\n",
      "end = pd.datetime(2013, 12, 1)\n",
      "rng = pd.bdate_range(start, end)\n",
      "\n",
      "# apple.set_index(rng)\n",
      "print allp.describe()\n",
      "apple.head(15)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "             AAPL         BAC        DELL         GOOG          GS          MS        MSFT\n",
        "count  231.000000  231.000000  209.000000   231.000000  231.000000  231.000000  231.000000\n",
        "mean   458.926234   13.238095   13.385981   866.051082  154.092944   25.145498   31.558009\n",
        "std     39.097435    1.215939    0.664270    81.248638    8.998906    2.959034    3.395749\n",
        "min    383.180000   11.000000   10.500000   702.870000  129.240000   19.420000   25.680000\n",
        "25%    427.175000   12.125000   13.190000   806.525000  147.780000   22.660000   27.755000\n",
        "50%    447.730000   13.280000   13.570000   871.980000  155.660000   25.250000   32.290000\n",
        "75%    492.085000   14.310000   13.800000   896.380000  161.740000   27.240000   34.155000\n",
        "max    556.070000   15.870000   14.260000  1063.110000  169.190000   31.540000   38.130000\n"
       ]
      },
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>AAPL</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Dates</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2013-01-02</th>\n",
        "      <td> 535.58</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-03</th>\n",
        "      <td> 528.82</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-04</th>\n",
        "      <td> 514.09</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-07</th>\n",
        "      <td> 511.06</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-08</th>\n",
        "      <td> 512.44</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-09</th>\n",
        "      <td> 504.43</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-10</th>\n",
        "      <td> 510.68</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-11</th>\n",
        "      <td> 507.55</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-14</th>\n",
        "      <td> 489.45</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-15</th>\n",
        "      <td> 474.01</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-16</th>\n",
        "      <td> 493.69</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-17</th>\n",
        "      <td> 490.36</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-18</th>\n",
        "      <td> 487.75</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-22</th>\n",
        "      <td> 492.40</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2013-01-23</th>\n",
        "      <td> 501.41</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 35,
       "text": [
        "              AAPL\n",
        "Dates             \n",
        "2013-01-02  535.58\n",
        "2013-01-03  528.82\n",
        "2013-01-04  514.09\n",
        "2013-01-07  511.06\n",
        "2013-01-08  512.44\n",
        "2013-01-09  504.43\n",
        "2013-01-10  510.68\n",
        "2013-01-11  507.55\n",
        "2013-01-14  489.45\n",
        "2013-01-15  474.01\n",
        "2013-01-16  493.69\n",
        "2013-01-17  490.36\n",
        "2013-01-18  487.75\n",
        "2013-01-22  492.40\n",
        "2013-01-23  501.41"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "allp.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>AAPL</th>\n",
        "      <th>BAC</th>\n",
        "      <th>C</th>\n",
        "      <th>DELL</th>\n",
        "      <th>GOOG</th>\n",
        "      <th>GS</th>\n",
        "      <th>MS</th>\n",
        "      <th>MSFT</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 535.58</td>\n",
        "      <td> 11.99</td>\n",
        "      <td> 41.22</td>\n",
        "      <td> 10.50</td>\n",
        "      <td> 723.25</td>\n",
        "      <td> 129.95</td>\n",
        "      <td> 19.46</td>\n",
        "      <td> 26.81</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 528.82</td>\n",
        "      <td> 11.92</td>\n",
        "      <td> 41.35</td>\n",
        "      <td> 10.75</td>\n",
        "      <td> 723.67</td>\n",
        "      <td> 129.24</td>\n",
        "      <td> 19.42</td>\n",
        "      <td> 26.45</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 514.09</td>\n",
        "      <td> 12.07</td>\n",
        "      <td> 42.39</td>\n",
        "      <td> 10.78</td>\n",
        "      <td> 737.97</td>\n",
        "      <td> 132.76</td>\n",
        "      <td> 20.03</td>\n",
        "      <td> 25.95</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 511.06</td>\n",
        "      <td> 12.05</td>\n",
        "      <td> 42.43</td>\n",
        "      <td> 10.87</td>\n",
        "      <td> 734.75</td>\n",
        "      <td> 132.51</td>\n",
        "      <td> 19.64</td>\n",
        "      <td> 25.91</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 512.44</td>\n",
        "      <td> 11.94</td>\n",
        "      <td> 42.42</td>\n",
        "      <td> 10.59</td>\n",
        "      <td> 733.30</td>\n",
        "      <td> 131.32</td>\n",
        "      <td> 19.49</td>\n",
        "      <td> 25.77</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 14,
       "text": [
        "     AAPL    BAC      C   DELL    GOOG      GS     MS   MSFT\n",
        "0  535.58  11.99  41.22  10.50  723.25  129.95  19.46  26.81\n",
        "1  528.82  11.92  41.35  10.75  723.67  129.24  19.42  26.45\n",
        "2  514.09  12.07  42.39  10.78  737.97  132.76  20.03  25.95\n",
        "3  511.06  12.05  42.43  10.87  734.75  132.51  19.64  25.91\n",
        "4  512.44  11.94  42.42  10.59  733.30  131.32  19.49  25.77"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "start = datetime(start)\n",
      "end = datetime(end)\n",
      "\n",
      "rng = date_range(start, end)\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "NameError",
       "evalue": "name 'datetime' is not defined",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
        "\u001b[1;32m<ipython-input-3-54d153a46814>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mstart\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstart\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mend\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mrng\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdate_range\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstart\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
        "\u001b[1;31mNameError\u001b[0m: name 'datetime' is not defined"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cur = cnx.cursor()    \n",
      "cur.execute(\"SELECT AAPL FROM prices\")\n",
      "cur.fetchall()\n",
      "del cur"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 183
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "whos"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Variable        Type          Data/Info\n",
        "---------------------------------------\n",
        "IPython         module        <module 'IPython' from 'C<...>es\\IPython\\__init__.pyc'>\n",
        "all_data        dict          n=8\n",
        "apple           DataFrame     <class 'pandas.core.frame<...>alues\\ndtypes: float64(1)\n",
        "c               Cursor        <sqlite3.Cursor object at 0x05C13960>\n",
        "cars            tuple         n=7\n",
        "clean_runtime   list          n=9999\n",
        "cn              tuple         n=7\n",
        "cnx             Connection    <sqlite3.Connection object at 0x07FA19B0>\n",
        "col_names       list          n=3\n",
        "con             Connection    <sqlite3.Connection object at 0x05C245C0>\n",
        "cur             Cursor        <sqlite3.Cursor object at 0x081BE8E0>\n",
        "d               tuple         n=6\n",
        "data            list          n=0\n",
        "db              Connection    <sqlite3.Connection object at 0x02F52AD0>\n",
        "direct          unicode       C:\\Users\\Suso\n",
        "end             datetime      2013-12-01 00:00:00\n",
        "genre           str           Western\n",
        "genres          list          n=24\n",
        "i               int           100\n",
        "j               unicode       more text\n",
        "m               str           Comedy|Drama\n",
        "math            module        <module 'math' (built-in)>\n",
        "movie           str           Comedy|Drama\n",
        "movies          DataFrame     <class 'pandas.core.frame<...>4(2), int64(2), object(3)\n",
        "mpl             module        <module 'matplotlib' from<...>matplotlib\\__init__.pyc'>\n",
        "n               int           100\n",
        "names           list          n=7\n",
        "now             str           Tue Dec 17 10:38:12 2013\n",
        "np              module        <module 'numpy' from 'C:\\<...>ages\\numpy\\__init__.pyc'>\n",
        "os              module        <module 'os' from 'C:\\Anaconda\\lib\\os.pyc'>\n",
        "p1              DataFrame     <class 'pandas.core.frame<...>(2), int64(26), object(3)\n",
        "p2              DataFrame     "
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<class 'pandas.core.frame<...>(2), int64(26), object(3)\n",
        "p3              DataFrame     <class 'pandas.core.frame<...>(2), int64(26), object(3)\n",
        "pd              module        <module 'pandas' from 'C:<...>ges\\pandas\\__init__.pyc'>\n",
        "plt             module        <module 'matplotlib.pyplo<...>s\\matplotlib\\pyplot.pyc'>\n",
        "prices          DataFrame     <class 'pandas.core.frame<...>alues\\ndtypes: float64(8)\n",
        "r               str           96 mins.\n",
        "readData        function      <function readData at 0x05C28BF0>\n",
        "record          NoneType      None\n",
        "results         list          n=2\n",
        "row             tuple         n=3\n",
        "rows            list          n=4\n",
        "sql             module        <module 'pandas.io.sql' f<...>kages\\pandas\\io\\sql.pyc'>\n",
        "sql3            module        <module 'sqlite3' from 'C<...>ib\\sqlite3\\__init__.pyc'>\n",
        "start           datetime      2013-01-01 00:00:00\n",
        "t               str           The Navigators (2001)\n",
        "ticker          str           C\n",
        "uId             int           1\n",
        "uPrice          int           62300\n",
        "version         str           2.7.5 |Anaconda 1.8.0 (32<...>SC v.1500 32 bit (Intel)]\n",
        "web             module        <module 'pandas.io.data' <...>ages\\pandas\\io\\data.pyc'>\n",
        "writeData       function      <function writeData at 0x05C136F0>\n",
        "xx              Cursor        <sqlite3.Cursor object at 0x07A7B060>\n"
       ]
      }
     ],
     "prompt_number": 181
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 170,
       "text": [
        "sqlite3.Cursor"
       ]
      }
     ],
     "prompt_number": 170
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}