REA demo

{
 "metadata": {
  "name": "",
  "signature": "sha256:4c29a1ff7ca0f7527fdf8246baed59e51d769e2a4e8365475b522e91736e250c"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Let's demonstrate how to render a mass of JS into a Python string, convert that string into a dictionary, loop over the dictionary to create a sqlite3 database and then make some queries on that data. \n",
      "\n",
      "### drg 5/2014"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import re\n",
      "import simplejson as json\n",
      "from pprint import pprint"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## grab the Javascript and put it in a Python string"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "js = \"\"\"[{id:\"1\",Xid:\"12602\",agentName:\"Rappaport, Kurt\",company:\"WESTSIDE ESTATE AGENCY INC.\",address:\"210 N. CANON DRIVE, BEVERLY HILLS, 90210\",unitVol:\"20\",dollarVol:\"312556308\",approxRevenue:\"0\",contactPhone:\"(310) 247-7770\",contactEmail:\"kr@weahomes.com\"},\n",
      "{id:\"2\",Xid:\"7202\",agentName:\"Purkiss, Wes & Chris\",company:\"HILL TOP REAL ESTATE\",address:\"223 N. GLENDORA AVE. STE. B, GLENDORA, 91741\",unitVol:\"21\",dollarVol:\"252760250\",approxRevenue:\"0\",contactPhone:\"(626) 852-3870\",contactEmail:\"wes@hilltoprealestate.com\"},\n",
      "{id:\"3\",Xid:\"7684\",agentName:\"Watts, Polly\",company:\"POLLY WATTS\",address:\"319 MAIN ST., EL SEGUNDO, 90245\",unitVol:\"697\",dollarVol:\"198778543\",approxRevenue:\"0\",contactPhone:\"(866) 640-3040\",contactEmail:\"pwatts@wedgewood-inc.com\"},\n",
      "{id:\"4\",Xid:\"16839\",agentName:\"Offer, David\",company:\"PRUDENTIAL CA - BRENTWOOD\",address:\"11677 SAN VICENTE BL. #307, LOS ANGELES, 90049\",unitVol:\"32\",dollarVol:\"174591023\",approxRevenue:\"0\",contactPhone:\"(310) 207-7080\",contactEmail:\"doffer@davidoffer.com\"},\n",
      "{id:\"5\",Xid:\"1723\",agentName:\"Mills, Jade\",company:\"COLDWELL BANKER-BH SOUTH\",address:\"166 N. CANON DR., BEVERLY HILLS, 90210\",unitVol:\"31\",dollarVol:\"160488140\",approxRevenue:\"0\",contactPhone:\"(310) 273-3113\",contactEmail:\"homes@jademills.com\"},\n",
      "]\"\"\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## wrap quotes around the keys"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# this is a dictionary with all the transformations we wish to make.\n",
      "\n",
      "xform = {'\\'':'-',\n",
      " '{id:':'{\"id\":',\n",
      " 'Xid:':'\"Xid\":',\n",
      " 'agentName:':'\"agentName\":',\n",
      " 'company:':'\"company\":',\n",
      " 'address:':'\"address\":',\n",
      " 'unitVol:':'\"unitVol\":',\n",
      " 'dollarVol:':'\"dollarVol\":',\n",
      " 'approxRevenue:':'\"approxRevenue\":',\n",
      " 'contactPhone:':'\"contactPhone\":',\n",
      " 'contactEmail:':'\"contactEmail\":',\n",
      " '\\\\n':'',\n",
      " ',]':']'\n",
      "}\n",
      "\n",
      "# first, copy this string\n",
      "bigform=js\n",
      "\n",
      "# now run through every line of the string and do the transformation\n",
      "\n",
      "for eachone in xform:\n",
      "    bigform = re.sub(eachone, xform[eachone], bigform)\n",
      "\n",
      "# it's easiest to remove the last comma with the stream editor\n",
      "bigform=bigform.replace(',]',']')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## load the string into a Python dictionary and print it out"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "bigdict = json.loads(bigform)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## now you can stuff it into a sqlite database"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3\n",
      "conn = sqlite3.connect('rea.db')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c = conn.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# you will only need to create the table once\n",
      "c.execute('''CREATE TABLE listings\n",
      "             (Xid int, address text, agentName text, dollarVol int, company text)''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for lstng in bigdict:\n",
      "    Xid=int(lstng['Xid'])\n",
      "    dollarVol=int(lstng['dollarVol'])\n",
      "    exc = \"INSERT INTO listings VALUES (%d,'%s','%s',%d,'%s')\" %(Xid,lstng['address'],lstng['agentName'],dollarVol,lstng['company'])\n",
      "    # Insert a row of data\n",
      "    try:\n",
      "        c.execute(exc)\n",
      "    except:\n",
      "        print \"barf!: %s\" %(exc)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# do some sql\n",
      "\n",
      "c.execute(\"SELECT * FROM listings WHERE agentName = 'Alosi, Sharon'\")\n",
      "print c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(13706, u'755 E. ROUTE 66, GLENDORA, 91740', u'Alosi, Sharon', 142539900, u'PODLEY PROPERTIES')]\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# get all agents with the last name \"Smith\"\n",
      "c.execute(\"SELECT * FROM listings WHERE agentName LIKE 'Smith%'\")\n",
      "print c.fetchall()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(9769, u'11726 SAN VICENTE BLVD. #350, LOS ANGELES, 90049', u'Smith, F. Ron', 42483726, u'PARTNERS TRUST BRENTWOOD')]\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# get the big sellers\n",
      "\n",
      "c.execute(\"SELECT * FROM listings WHERE dollarVol > 142539900\")\n",
      "pprint( c.fetchall() )"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(12602,\n",
        "  u'210 N. CANON DRIVE, BEVERLY HILLS, 90210',\n",
        "  u'Rappaport, Kurt',\n",
        "  312556308,\n",
        "  u'WESTSIDE ESTATE AGENCY INC.'),\n",
        " (7202,\n",
        "  u'223 N. GLENDORA AVE. STE. B, GLENDORA, 91741',\n",
        "  u'Purkiss, Wes & Chris',\n",
        "  252760250,\n",
        "  u'HILL TOP REAL ESTATE'),\n",
        " (7684,\n",
        "  u'319 MAIN ST., EL SEGUNDO, 90245',\n",
        "  u'Watts, Polly',\n",
        "  198778543,\n",
        "  u'POLLY WATTS'),\n",
        " (16839,\n",
        "  u'11677 SAN VICENTE BL. #307, LOS ANGELES, 90049',\n",
        "  u'Offer, David',\n",
        "  174591023,\n",
        "  u'PRUDENTIAL CA - BRENTWOOD'),\n",
        " (1723,\n",
        "  u'166 N. CANON DR., BEVERLY HILLS, 90210',\n",
        "  u'Mills, Jade',\n",
        "  160488140,\n",
        "  u'COLDWELL BANKER-BH SOUTH'),\n",
        " (77278,\n",
        "  u'1524 ABBOT KINNEY, VENICE, 90291',\n",
        "  u'Pardee, Tamra (tami)',\n",
        "  156508306,\n",
        "  u'PARDEE PROPERTIES, INC.')]\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## delete the data and start again"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print \"I just deleted\", c.execute(\"delete from listings\").rowcount, \"rows\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "I just deleted 250 rows\n"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 36
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}