Homework_5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Homework #5\n",
    "\n",
    "This homework presents a sophisticated scenario in which you must design a SQL schema, insert data into it, and issue queries against it.\n",
    "\n",
    "## The scenario\n",
    "\n",
    "In the year 20XX, I have won the lottery and decided to leave my programming days behind me in order to pursue my true calling as a [cat cafe](https://en.wikipedia.org/wiki/Cat_caf%C3%A9) tycoon. [This webpage](http://static.decontextualize.com/cats.html) lists the locations of my cat cafes and all the cats that are currently in residence at these cafes.\n",
    "\n",
    "I'm interested in doing more detailed analysis of my cat cafe holdings and the cats that are currently being cared for by my cafes. For this reason, I've hired *you* to convert this HTML page into a workable SQL database. (Why don't I just do it myself? Because I am far too busy hanging out with adorable cats in all of my beautiful, beautiful cat cafes.)\n",
    "\n",
    "Specifically, I want to know the answers to the following questions:\n",
    "\n",
    "* What's the name of the youngest cat at any location?\n",
    "* In which zip codes can I find a lilac-colored tabby?\n",
    "* What's the average weight of cats currently residing at any location (grouped by location)?\n",
    "* Which location has the most cats with tortoiseshell coats?\n",
    "\n",
    "Because I'm not paying you very much, and because I am a merciful person who has considerable experience in these matters, I've decided to *write the queries for you*. (See below.) Your job is just to scrape the data from the web page, create the appropriate tables in PostgreSQL, and insert the data into those tables.\n",
    "\n",
    "Before you continue, scroll down to \"The Queries\" below to examine the queries as I wrote them.\n",
    "\n",
    "## Problem set #1: Scraping the data\n",
    "\n",
    "Your first goal is to create two data structures, both lists of dictionaries: one for the list of locations and one for the list of cats. You'll get these from scraping two `<table>` tags in the HTML: the first table has a class of `cafe-list`, the second has a class of `cat-list`.\n",
    "\n",
    "Before you do anything else, though, execute the following cell to import Beautiful Soup and create a BeautifulSoup object with the content of the web page:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "from urllib.request import urlopen\n",
    "html = urlopen(\"http://static.decontextualize.com/cats.html\").read()\n",
    "document = BeautifulSoup(html, \"html.parser\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's tackle the list of cafes first. In the cell below, write some code that creates a list of dictionaries with information about each cafe, assigning it to the variable `cafe_list`. I've written some of the code for you; you just need to fill in the rest. The list should end up looking like this:\n",
    "\n",
    "```\n",
    "[{'name': 'Hang In There', 'zip': '11237'},\n",
    " {'name': 'Independent Claws', 'zip': '11201'},\n",
    " {'name': 'Paws and Play', 'zip': '11215'},\n",
    " {'name': 'Tall Tails', 'zip': '11222'},\n",
    " {'name': 'Cats Meow', 'zip': '11231'}]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cafe_list = list()\n",
    "cafe_table = document.find('table', {'class': 'cafe-list'})\n",
    "tbody = cafe_table.find('tbody')\n",
    "for tr_tag in tbody.find_all('tr'):\n",
    "    pass # replace \"pass\" with your code\n",
    "cafe_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! In the following cell, write some code that creates a list of cats from the `<table>` tag on the page, storing them as a list of dictionaries in a variable called `cat_list`. Again, I've written a bit of the code for you. Expected output:\n",
    "\n",
    "```\n",
    "[{'birthdate': '2015-05-20',\n",
    "  'color': 'black',\n",
    "  'locations': ['Paws and Play', 'Independent Claws*'],\n",
    "  'name': 'Sylvester',\n",
    "  'pattern': 'colorpoint',\n",
    "  'weight': 10.46},\n",
    " {'birthdate': '2000-01-03',\n",
    "  'color': 'cinnamon',\n",
    "  'locations': ['Independent Claws*'],\n",
    "  'name': 'Jasper',\n",
    "  'pattern': 'solid',\n",
    "  'weight': 8.06},\n",
    " {'birthdate': '2006-02-27',\n",
    "  'color': 'brown',\n",
    "  'locations': ['Independent Claws*'],\n",
    "  'name': 'Luna',\n",
    "  'pattern': 'tortoiseshell',\n",
    "  'weight': 10.88},\n",
    "[...many records omitted for brevity...]\n",
    " {'birthdate': '1999-01-09',\n",
    "  'color': 'white',\n",
    "  'locations': ['Cats Meow*', 'Independent Claws', 'Tall Tails'],\n",
    "  'name': 'Lafayette',\n",
    "  'pattern': 'tortoiseshell',\n",
    "  'weight': 9.3}]\n",
    "```\n",
    "\n",
    "Note: Observe the data types of the values in each dictionary! Make sure to explicitly convert values retrieved from `.string` attributes of Beautiful Soup tag objects to `str`s using the `str()` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cat_list = list()\n",
    "cat_table = document.find('table', {'class': 'cat-list'})\n",
    "tbody = cat_table.find('tbody')\n",
    "for tr_tag in tbody.find_all('tr'):\n",
    "    cat_dict = {}\n",
    "    # your code here\n",
    "    cat_list.append(cat_dict)\n",
    "cat_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem set #2: Designing the schema\n",
    "\n",
    "Before you do anything else, use `psql` to create a new database for this homework assignment using the following command:\n",
    "\n",
    "    CREATE DATABASE catcafes;\n",
    "    \n",
    "In the following cell, connect to the database using `pg8000`. (You may need to provide additional arguments to the `.connect()` method, depending on the distribution of PostgreSQL you're using.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pg8000\n",
    "conn = pg8000.connect(database=\"catcafes\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's a cell you can run if something goes wrong and you need to rollback the current query session:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the cell below, you're going to create *three* tables, necessary to represent the data you scraped above. I've given the basic framework of the Python code and SQL statements to create these tables. I've given the entire `CREATE TABLE` statement for the `cafe` table, but for the other two, you'll need to supply the field names and the data types for each column. If you're unsure what to call the fields, or what fields should be in the tables, consult the queries in \"The Queries\" below. Hints:\n",
    "\n",
    "* Many of these fields will be `varchar`s. Don't worry too much about how many characters you need—it's okay just to eyeball it.\n",
    "* Feel free to use a `varchar` type to store the `birthdate` field. No need to dig too deep into PostgreSQL's date types for this particular homework assignment.\n",
    "* Cats and locations are in a *many-to-many* relationship. You'll need to create a linking table to represent this relationship. (That's why there's space for you to create *three* tables.)\n",
    "* The linking table will need a field to keep track of whether or not a particular cafe is the \"current\" cafe for a given cat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE cafe (\n",
    "  id serial,\n",
    "  name varchar(40),\n",
    "  zip varchar(5)\n",
    ")\n",
    "\"\"\")\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE cat (\n",
    "  ... fill in with your fields and types ...\n",
    ")\n",
    "\"\"\")\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE cat_cafe (\n",
    "  ... fill in with your fields and types ...\n",
    ")\n",
    "\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After executing the above cell, issuing a `\\d` command in `psql` should yield something that looks like the following:\n",
    "\n",
    "```\n",
    "             List of relations\n",
    " Schema |    Name     |   Type   |  Owner  \n",
    "--------+-------------+----------+---------\n",
    " public | cafe        | table    | allison\n",
    " public | cafe_id_seq | sequence | allison\n",
    " public | cat         | table    | allison\n",
    " public | cat_cafe    | table    | allison\n",
    " public | cat_id_seq  | sequence | allison\n",
    "(5 rows)\n",
    "```\n",
    "\n",
    "If something doesn't look right, you can always use the `DROP TABLE` command to drop the tables and start again. (You can also issue a `DROP DATABASE catcafes` command to drop the database altogether.) Don't worry if it takes a few tries to get it right—happens to the best and most expert among us. You'll probably have to drop the database and start again from scratch several times while completing this homework.\n",
    "\n",
    "> Note: If you try to issue a `DROP TABLE` or `DROP DATABASE` command and `psql` seems to hang forever, it could be that PostgreSQL is waiting for current connections to close before proceeding with your command. To fix this, create a cell with the code `conn.close()` in your notebook and execute it. After the `DROP` commands have completed, make sure to run the cell containing the `pg8000.connect()` call again."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem set #3: Inserting the data\n",
    "\n",
    "In the cell below, I've written the code to insert the cafes into the `cafe` table, using data from the `cafe_list` variable that we made earlier. If the code you wrote to create that table was correct, the following cell should execute without error or incident. Execute it before you continue."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cafe_name_id_map = {}\n",
    "for item in cafe_list:\n",
    "    cursor.execute(\"INSERT INTO cafe (name, zip) VALUES (%s, %s) RETURNING id\",\n",
    "                  [str(item['name']), str(item['zip'])])\n",
    "    rowid = cursor.fetchone()[0]\n",
    "    cafe_name_id_map[str(item['name'])] = rowid\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issuing `SELECT * FROM cafe` in the `psql` client should yield something that looks like this:\n",
    "\n",
    "```\n",
    " id |       name        |  zip  \n",
    "----+-------------------+-------\n",
    "  1 | Hang In There     | 11237\n",
    "  2 | Independent Claws | 11201\n",
    "  3 | Paws and Play     | 11215\n",
    "  4 | Tall Tails        | 11222\n",
    "  5 | Cats Meow         | 11231\n",
    "(5 rows)\n",
    "```\n",
    "\n",
    "(The `id` values may be different depending on how many times you've cleaned the table out with `DELETE`.)\n",
    "\n",
    "Note that the code in the cell above created a dictionary called `cafe_name_id_map`. What's in it? Let's see:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Cats Meow': 5,\n",
       " 'Hang In There': 1,\n",
       " 'Independent Claws': 2,\n",
       " 'Paws and Play': 3,\n",
       " 'Tall Tails': 4}"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cafe_name_id_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dictionary maps the *name of the cat cafe to its ID in the database*. You'll need these values later when you're adding records to the linking table (`cat_cafe`).\n",
    "\n",
    "Now the tricky part. (Yes, believe it or not, *this* is the tricky part. The other stuff has all been easy by comparison.) In the cell below, write the Python code to insert each cat's data from the `cat_list` variable (created in Problem Set #1) into the `cat` table. The code should *also* insert the relevant data into the `cat_cafe` table. Hints:\n",
    "\n",
    "* You'll need to get the `id` of each cat record using the `RETURNING` clause of the `INSERT` statement and the `.fetchone()` method of the cursor object.\n",
    "* How do you know whether or not the current location is the \"active\" location for a particular cat? The page itself contains some explanatory text that might be helpful here. You might need to use some string checking and manipulation functions in order to make this determination and transform the string as needed.\n",
    "* The linking table stores an ID only for both the cat and the cafe. Use the `cafe_name_id_map` dictionary to get the `id` of the cafes inserted earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for cat in cat_list:\n",
    "    pass # replace pass with your code. it will be a LOT of code!\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Issuing a `SELECT * FROM cat LIMIT 10` in `psql` should yield something that looks like this:\n",
    "\n",
    "```\n",
    " id |   name    | birthdate  | weight |  color   |    pattern    \n",
    "----+-----------+------------+--------+----------+---------------\n",
    "  1 | Sylvester | 2015-05-20 |  10.46 | black    | colorpoint\n",
    "  2 | Jasper    | 2000-01-03 |   8.06 | cinnamon | solid\n",
    "  3 | Luna      | 2006-02-27 |  10.88 | brown    | tortoiseshell\n",
    "  4 | Georges   | 2015-08-13 |   9.40 | white    | tabby\n",
    "  5 | Millie    | 2003-09-13 |   9.27 | red      | bicolor\n",
    "  6 | Lisa      | 2009-07-30 |   8.84 | cream    | colorpoint\n",
    "  7 | Oscar     | 2011-12-15 |   8.44 | cream    | solid\n",
    "  8 | Scaredy   | 2015-12-30 |   8.83 | lilac    | tabby\n",
    "  9 | Charlotte | 2013-10-16 |   9.54 | blue     | tabby\n",
    " 10 | Whiskers  | 2011-02-07 |   9.47 | white    | colorpoint\n",
    "(10 rows)\n",
    "```\n",
    "\n",
    "And a `SELECT * FROM cat_cafe LIMIT 10` in `psql` should look like this:\n",
    "\n",
    "```\n",
    " cat_id | cafe_id | active \n",
    "--------+---------+--------\n",
    "      1 |       3 | f\n",
    "      1 |       2 | t\n",
    "      2 |       2 | t\n",
    "      3 |       2 | t\n",
    "      4 |       4 | t\n",
    "      4 |       1 | f\n",
    "      5 |       3 | t\n",
    "      6 |       1 | t\n",
    "      7 |       1 | t\n",
    "      7 |       5 | f\n",
    "(10 rows)\n",
    "```\n",
    "\n",
    "Again, the exact values for the ID columns might be different, depending on how many times you've deleted and dropped the tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Queries\n",
    "\n",
    "Okay. To verify your work, run the following queries and check their output. If you've correctly scraped the data and imported it into SQL, running the cells should produce exactly the expected output, as indicated. If not, then you performed one of the steps above incorrectly; check your work and try again. (Note: Don't modify these cells, just run them! This homework was about *scraping* and *inserting* data, not querying it.)\n",
    "\n",
    "### What's the name of the youngest cat at any location?\n",
    "\n",
    "Expected output: `Scaredy`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scaredy\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT max(birthdate) FROM cat\")\n",
    "birthdate = cursor.fetchone()[0]\n",
    "cursor.execute(\"SELECT name FROM cat WHERE birthdate = %s\", [birthdate])\n",
    "print(cursor.fetchone()[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### In which zip codes can I find a lilac-colored tabby?\n",
    "\n",
    "Expected output: 11237, 11215"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11237, 11215\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"SELECT DISTINCT(cafe.zip)\n",
    "    FROM cat\n",
    "    JOIN cat_cafe ON cat.id = cat_cafe.cat_id\n",
    "    JOIN cafe ON cafe.id = cat_cafe.cafe_id\n",
    "    WHERE cat.color = 'lilac' AND cat.pattern = 'tabby' AND cat_cafe.active = true\n",
    "\"\"\")\n",
    "print(', '.join([x[0] for x in cursor.fetchall()]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What's the average weight of cats currently residing at all locations?\n",
    "\n",
    "Expected output:\n",
    "\n",
    "```\n",
    "Independent Claws: 9.33\n",
    "Paws and Play: 9.28\n",
    "Tall Tails: 9.82\n",
    "Hang In There: 9.25\n",
    "Cats Meow: 9.76\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Independent Claws: 9.33\n",
      "Paws and Play: 9.28\n",
      "Tall Tails: 9.82\n",
      "Hang In There: 9.25\n",
      "Cats Meow: 9.76\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    SELECT cafe.name, avg(cat.weight)\n",
    "    FROM cat\n",
    "    JOIN cat_cafe ON cat.id = cat_cafe.cat_id\n",
    "    JOIN cafe ON cafe.id = cat_cafe.cafe_id\n",
    "    WHERE cat_cafe.active = true\n",
    "    GROUP BY cafe.name\n",
    "    \"\"\")\n",
    "for rec in cursor.fetchall():\n",
    "    print(rec[0]+\":\", \"%0.2f\" % rec[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Which location has the most cats with tortoiseshell coats?\n",
    "\n",
    "Expected output: `Independent Claws`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Independent Claws\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    SELECT cafe.name\n",
    "    FROM cat\n",
    "    JOIN cat_cafe ON cat.id = cat_cafe.cat_id\n",
    "    JOIN cafe ON cafe.id = cat_cafe.cafe_id\n",
    "    WHERE cat_cafe.active = true AND cat.pattern = 'tortoiseshell'\n",
    "    GROUP BY cafe.name\n",
    "    ORDER BY count(cat.name) DESC\n",
    "    LIMIT 1\n",
    "\"\"\")\n",
    "print(cursor.fetchone()[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Did they all work? Great job! You're done."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}