HTML_to_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HTML to SQL\n",
    "\n",
    "In this tutorial, we'll take the result of scraping a web page and make a SQL database from it. Over the course of the tutorial, I'll show you how to scrape a page, make Python data structure from it, devise a SQL schema for the data, and then use Python (with pg8000) to insert the data into a PostgreSQL database.\n",
    "\n",
    "## Introducing Menupages\n",
    "\n",
    "As a target for this exercise, we'll pick some HTML that presents a moderate challenge: [the Morningside Heights listing from Menupages](http://www.menupages.com/restaurants/all-areas/morningside-heights/all-cuisines/). (Menupages is now owned by and largely superceded by Seamless, but in my formative period as a New Yorker in the middle of the first decade of the 2000s it was *the place* to go for information about where to get food in Manhattan. Menupages doesn't make its data available in a non-HTML format, so if we want to get our hands on the data we need to scrape it from the website.\n",
    "\n",
    "To make things easier on the Menupages server, and so that this tutorial doesn't break when/if Menupages changes their layout, I've made a copy of the Morningside Heights directory HTML and uploaded it to Github [here](https://raw.githubusercontent.com/ledeprogram/data-and-databases/master/menupages-morningside-heights.html). We'll download and use this file locally instead of fetching it from Menupages directly.\n",
    "\n",
    "> Note: If you're trying to reproduce my results from the Menupages site itself, note that I slightly modified the source code to correct some bad HTML in the `<thead>` tag, which was preventing `html.parser` from parsing the page into a tree properly.\n",
    "\n",
    "## Making the data structure\n",
    "\n",
    "The data structure that we'll want to end up with as a sort of \"intermediary\" between the HTML and SQL is a list of dictionaries, with each dictionary having keys and values corresponding to the data for each restauraunt. Eyeballing the page, I'd say that the data structure we'd want to end up with might look like this:\n",
    "\n",
    "    [\n",
    "        {'name': '176 Presbyterian Deli',\n",
    "         'cuisines': [],\n",
    "         'price': None},\n",
    "        {'name': 'Amir's Grill',\n",
    "         'cuisines': ['Middle Eastern'],\n",
    "         'price': None}\n",
    "        {'name': 'Artopolis Espresso Cafe',\n",
    "         'cuisines': ['Coffee & Tea', 'Sandwiches', 'Crepes'],\n",
    "         'price': '$'}\n",
    "        },\n",
    "        ... more of these probably ...\n",
    "    ]\n",
    "    \n",
    "A data structure like this clearly separates out all of the individual pieces of data available to us in the HTML, and allows us to easily transform the data into a different format (e.g., CSV, SQL, etc.).\n",
    "\n",
    "> Note: This isn't a \"standard\" format or anything—it's just something I made up, based on what is present in the HTML. I might decide later to revise what the data structure looks like, if I find out something about the data that wasn't immediately apparent from just looking at the rendered HTML. It's worth noting that there's no automated tool to \"force\" the HTML into this data format. (In fact, that's the whole point of this tutorial.)\n",
    "\n",
    "## Scraping the HTML\n",
    "\n",
    "By prodding at the source code with Developer Tools, I isolated the important part of the HTML to this little bit here:\n",
    "\n",
    "![screenshot of dev tools on menupages](http://static.decontextualize.com/snaps/menupages-devtools.png)\n",
    "\n",
    "At first glance: There's a table with class `search-results` that has a `<tbody>` tag that is the parent tag for a series of `<tr>` tags that represent each restaurant, row-by-row. Inside each `<tr>` tag, there are a number of `<td>`s that look useful: one with class `name-address` that appears to have the name and address of the restaurant; another with class `price` that has the price of the restaurant. The \"cuisines\" of the restaurant can be obtained either from the `name-address` `<td>`, or from a mysterious `<td>` without a class that appears to be in the table but not displayed anywhere. (That's weird, but not unheard of: it's likely hidden by a CSS class, and the data is probably present only to be used by some Javascript on the page.)\n",
    "\n",
    "As a first attempt, let's just try to print the *name* of every restaurant. First, we'll load the data and create a Beautiful Soup object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "\n",
    "raw_html = open(\"menupages-morningside-heights.html\").read()\n",
    "soup = BeautifulSoup(raw_html, \"html.parser\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we'll find that `<table>` tag and iterate through its child `<tr>`s:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Milano Market\n",
      "Massawa\n",
      "China Place\n",
      "Subsconscious\n",
      "Famous Famiglia\n",
      "Kitchenette\n",
      "V & T Pizza\n",
      "New Aroma\n",
      "Peking Garden\n",
      "Tom's Restaurant\n",
      "Pisticci\n",
      "Deluxe\n",
      "Toast\n",
      "Tom's Delicious Pizza\n",
      "West Place\n",
      "Che' Bella Pizza\n",
      "Ajanta\n",
      "Panino Sportivo Roma\n",
      "Max Soha\n",
      "Strokos Pizza\n",
      "Camille's\n",
      "Amsterdam Restaurant\n",
      "Nussbaum & Wu\n",
      "Amir's Grill\n",
      "M2M - Morning To Midnight\n",
      "The Mill\n",
      "Le Monde\n",
      "Melba's\n",
      "Chuck E Cheese's\n",
      "Haagen-Dazs\n",
      "Oren's\n",
      "Dinosaur Bar-B-Que\n",
      "Symposium Greek Restaurant\n",
      "Koronet Pizza\n",
      "The Heights Bar & Grill\n",
      "Cafe Nana\n",
      "Hamilton Deli\n",
      "Community Food & Juice\n",
      "Haakon's Hall\n",
      "El Porton\n",
      "Brad's\n",
      "Mel's Burger Bar\n",
      "Bettolona\n",
      "Five Guys\n",
      "Nikko\n",
      "Falafel on Broadway\n",
      "Sushi Sushi\n",
      "Insomnia Cookies\n",
      "Cafe Amrita\n",
      "Pinkberry\n",
      "Artopolis Espresso Cafe\n",
      "Max Caffe\n",
      "Chipotle\n",
      "Chokolat Patisserie\n",
      "Chokolat Patisserie\n",
      "Joe's G-H Deli\n",
      "Joe the Art of Coffee\n",
      "Levain Bakery\n",
      "Silvana\n",
      "Bier International\n",
      "Vegenation\n",
      "Flat Top\n",
      "Kuro Kuma\n",
      "Vinateria\n",
      "Henan Cart\n",
      "Vine\n",
      "El Paso Truck\n",
      "Mama's Fried Chicken and Pizza\n",
      "Chapati House\n",
      "Dig Inn Seasonal Market\n",
      "Uncle Luoyang\n",
      "Pita Grill\n",
      "Kissaten Jin\n",
      "Serengeti Teas & Spices\n",
      "Lighthouse Fish Market & Restaurant\n",
      "Lolo's Seafood Shack\n",
      "Harmony\n",
      "Bernheim & Schwartz\n",
      "BTH (By The Hudson)\n",
      "Empanadas Monumental\n",
      "Friedman's\n",
      "176 Presbyterian Deli\n",
      "Nacho Mama's\n",
      "Streetbird Rotisserie\n",
      "Colum Chinese Restaurant\n",
      "Flacoz Tacoz\n",
      "sweetgreen\n",
      "La Salle Dumpling Room\n"
     ]
    }
   ],
   "source": [
    "search_table = soup.find('table', {'class': 'search-results'}) # the search-results table\n",
    "table_body = search_table.find('tbody') # get only the table body\n",
    "for tr_tag in table_body.find_all('tr'):\n",
    "    name_address_tag = tr_tag.find('td', {'class': 'name-address'})\n",
    "    a_tag = name_address_tag.find('a')\n",
    "    print(a_tag.string)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, that looks okay so far! Let's continue the work. The next easiest-hanging fruit is the price. Looking through the HTML, we can see that the price is recorded in one of two ways: either as a `N/A` string inside the `price`-classed `<td>` tag, or as a string inside of a `span` that is the direct descendent of that tag. We'll check for both."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Milano Market - 2\n",
      "Massawa - None\n",
      "China Place - None\n",
      "Subsconscious - None\n",
      "Famous Famiglia - None\n",
      "Kitchenette - None\n",
      "V & T Pizza - None\n",
      "New Aroma - 1\n",
      "Peking Garden - 1\n",
      "Tom's Restaurant - 1\n",
      "Pisticci - 1\n",
      "Deluxe - 1\n",
      "Toast - 2\n",
      "Tom's Delicious Pizza - 2\n",
      "West Place - 1\n",
      "Che' Bella Pizza - None\n",
      "Ajanta - None\n",
      "Panino Sportivo Roma - 2\n",
      "Max Soha - 2\n",
      "Strokos Pizza - 3\n",
      "Camille's - 1\n",
      "Amsterdam Restaurant - 1\n",
      "Nussbaum & Wu - None\n",
      "Amir's Grill - 1\n",
      "M2M - Morning To Midnight - 1\n",
      "The Mill - 2\n",
      "Le Monde - 1\n",
      "Melba's - 1\n",
      "Chuck E Cheese's - None\n",
      "Haagen-Dazs - 1\n",
      "Oren's - None\n",
      "Dinosaur Bar-B-Que - 3\n",
      "Symposium Greek Restaurant - None\n",
      "Koronet Pizza - 1\n",
      "The Heights Bar & Grill - 1\n",
      "Cafe Nana - None\n",
      "Hamilton Deli - None\n",
      "Community Food & Juice - None\n",
      "Haakon's Hall - None\n",
      "El Porton - None\n",
      "Brad's - 1\n",
      "Mel's Burger Bar - 2\n",
      "Bettolona - 1\n",
      "Five Guys - 1\n",
      "Nikko - 2\n",
      "Falafel on Broadway - 1\n",
      "Sushi Sushi - 1\n",
      "Insomnia Cookies - 1\n",
      "Cafe Amrita - 1\n",
      "Pinkberry - 2\n",
      "Artopolis Espresso Cafe - 1\n",
      "Max Caffe - 2\n",
      "Chipotle - 1\n",
      "Chokolat Patisserie - 5\n",
      "Chokolat Patisserie - 1\n",
      "Joe's G-H Deli - 2\n",
      "Joe the Art of Coffee - 1\n",
      "Levain Bakery - 2\n",
      "Silvana - 1\n",
      "Bier International - 1\n",
      "Vegenation - 1\n",
      "Flat Top - 4\n",
      "Kuro Kuma - 1\n",
      "Vinateria - 2\n",
      "Henan Cart - 1\n",
      "Vine - 2\n",
      "El Paso Truck - 1\n",
      "Mama's Fried Chicken and Pizza - 1\n",
      "Chapati House - 2\n",
      "Dig Inn Seasonal Market - 1\n",
      "Uncle Luoyang - 1\n",
      "Pita Grill - 2\n",
      "Kissaten Jin - 1\n",
      "Serengeti Teas & Spices - 2\n",
      "Lighthouse Fish Market & Restaurant - 3\n",
      "Lolo's Seafood Shack - 2\n",
      "Harmony - 1\n",
      "Bernheim & Schwartz - 2\n",
      "BTH (By The Hudson) - 3\n",
      "Empanadas Monumental - None\n",
      "Friedman's - None\n",
      "176 Presbyterian Deli - None\n",
      "Nacho Mama's - None\n",
      "Streetbird Rotisserie - None\n",
      "Colum Chinese Restaurant - None\n",
      "Flacoz Tacoz - None\n",
      "sweetgreen - None\n",
      "La Salle Dumpling Room - None\n"
     ]
    }
   ],
   "source": [
    "search_table = soup.find('table', {'class': 'search-results'}) # the search-results table\n",
    "table_body = search_table.find('tbody') # get only the table body\n",
    "for tr_tag in table_body.find_all('tr'):\n",
    "    name_address_tag = tr_tag.find('td', {'class': 'name-address'})\n",
    "    a_tag = name_address_tag.find('a')\n",
    "    name = a_tag.string\n",
    "    price_tag = tr_tag.find('td', {'class': 'price'})\n",
    "    price_span_tag = price_tag.find('span')\n",
    "    if price_span_tag:\n",
    "        price = price_span_tag.string\n",
    "    else:\n",
    "        price = None\n",
    "    print(name, \"-\", price)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point, the code is getting a little bit ugly, so I'm going to try to compartmentalize it a bit by moving some of the code for extracting information from the `<tr>` tags into separate functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_name(tr_tag):\n",
    "    name_address_tag = tr_tag.find('td', {'class': 'name-address'})\n",
    "    a_tag = name_address_tag.find('a')\n",
    "    return str(a_tag.string) # call str() to convert to regular string, not bs4 navigable string type\n",
    "    \n",
    "def get_price(tr_tag):\n",
    "    price_tag = tr_tag.find('td', {'class': 'price'})\n",
    "    price_span_tag = price_tag.find('span')\n",
    "    if price_span_tag:\n",
    "        return int(price_span_tag.string)\n",
    "    else:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can rewrite our loop, but in a much cleaner way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Milano Market - 2\n",
      "Massawa - 0\n",
      "China Place - 0\n",
      "Subsconscious - 0\n",
      "Famous Famiglia - 0\n",
      "Kitchenette - 0\n",
      "V & T Pizza - 0\n",
      "New Aroma - 1\n",
      "Peking Garden - 1\n",
      "Tom's Restaurant - 1\n",
      "Pisticci - 1\n",
      "Deluxe - 1\n",
      "Toast - 2\n",
      "Tom's Delicious Pizza - 2\n",
      "West Place - 1\n",
      "Che' Bella Pizza - 0\n",
      "Ajanta - 0\n",
      "Panino Sportivo Roma - 2\n",
      "Max Soha - 2\n",
      "Strokos Pizza - 3\n",
      "Camille's - 1\n",
      "Amsterdam Restaurant - 1\n",
      "Nussbaum & Wu - 0\n",
      "Amir's Grill - 1\n",
      "M2M - Morning To Midnight - 1\n",
      "The Mill - 2\n",
      "Le Monde - 1\n",
      "Melba's - 1\n",
      "Chuck E Cheese's - 0\n",
      "Haagen-Dazs - 1\n",
      "Oren's - 0\n",
      "Dinosaur Bar-B-Que - 3\n",
      "Symposium Greek Restaurant - 0\n",
      "Koronet Pizza - 1\n",
      "The Heights Bar & Grill - 1\n",
      "Cafe Nana - 0\n",
      "Hamilton Deli - 0\n",
      "Community Food & Juice - 0\n",
      "Haakon's Hall - 0\n",
      "El Porton - 0\n",
      "Brad's - 1\n",
      "Mel's Burger Bar - 2\n",
      "Bettolona - 1\n",
      "Five Guys - 1\n",
      "Nikko - 2\n",
      "Falafel on Broadway - 1\n",
      "Sushi Sushi - 1\n",
      "Insomnia Cookies - 1\n",
      "Cafe Amrita - 1\n",
      "Pinkberry - 2\n",
      "Artopolis Espresso Cafe - 1\n",
      "Max Caffe - 2\n",
      "Chipotle - 1\n",
      "Chokolat Patisserie - 5\n",
      "Chokolat Patisserie - 1\n",
      "Joe's G-H Deli - 2\n",
      "Joe the Art of Coffee - 1\n",
      "Levain Bakery - 2\n",
      "Silvana - 1\n",
      "Bier International - 1\n",
      "Vegenation - 1\n",
      "Flat Top - 4\n",
      "Kuro Kuma - 1\n",
      "Vinateria - 2\n",
      "Henan Cart - 1\n",
      "Vine - 2\n",
      "El Paso Truck - 1\n",
      "Mama's Fried Chicken and Pizza - 1\n",
      "Chapati House - 2\n",
      "Dig Inn Seasonal Market - 1\n",
      "Uncle Luoyang - 1\n",
      "Pita Grill - 2\n",
      "Kissaten Jin - 1\n",
      "Serengeti Teas & Spices - 2\n",
      "Lighthouse Fish Market & Restaurant - 3\n",
      "Lolo's Seafood Shack - 2\n",
      "Harmony - 1\n",
      "Bernheim & Schwartz - 2\n",
      "BTH (By The Hudson) - 3\n",
      "Empanadas Monumental - 0\n",
      "Friedman's - 0\n",
      "176 Presbyterian Deli - 0\n",
      "Nacho Mama's - 0\n",
      "Streetbird Rotisserie - 0\n",
      "Colum Chinese Restaurant - 0\n",
      "Flacoz Tacoz - 0\n",
      "sweetgreen - 0\n",
      "La Salle Dumpling Room - 0\n"
     ]
    }
   ],
   "source": [
    "search_table = soup.find('table', {'class': 'search-results'}) # the search-results table\n",
    "table_body = search_table.find('tbody') # get only the table body\n",
    "for tr_tag in table_body.find_all('tr'):\n",
    "    name = get_name(tr_tag)\n",
    "    price = get_price(tr_tag)\n",
    "    print(name, \"-\", price)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting the \"cuisines\" is a bit more difficult. I think we'll try to use the mysterious `<td>` tag that doesn't have a class. In order to use this, we have to do something we haven't done yet, which is grab a matching element *by position* instead of by class. This function takes the `<tr>` tag object and gets the content of the fifth (index 4) `<td>` tag, splitting it on the string `\", \"`, so that it returns a list of strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_cuisines(tr_tag):\n",
    "    cuisine_tag = tr_tag.find_all('td')[4]\n",
    "    if cuisine_tag.string:\n",
    "        return cuisine_tag.string.split(', ')\n",
    "    else:\n",
    "        return []"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(I checked to see if the `.string` attribute is actually present, because in some cases the given `<td>` is empty.) Here's the revised loop making use of this function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Milano Market - 2 - ['deli', 'sandwiches']\n",
      "Massawa - 0 - ['ethiopian', 'african']\n",
      "China Place - 0 - ['chinese', 'japanese', 'sushi']\n",
      "Subsconscious - 0 - ['cheese-steaks', 'deli', 'sandwiches', 'salads']\n",
      "Famous Famiglia - 0 - ['italian', 'pizza']\n",
      "Kitchenette - 0 - ['bakery-pastries', 'american', 'desserts']\n",
      "V & T Pizza - 0 - ['italian', 'pizza']\n",
      "New Aroma - 1 - ['chinese']\n",
      "Peking Garden - 1 - ['chinese']\n",
      "Tom's Restaurant - 1 - ['diner']\n",
      "Pisticci - 1 - ['italian']\n",
      "Deluxe - 1 - ['diner', 'american']\n",
      "Toast - 2 - ['american', 'bar-food']\n",
      "Tom's Delicious Pizza - 2 - ['italian', 'pizza']\n",
      "West Place - 1 - ['chinese']\n",
      "Che' Bella Pizza - 0 - ['italian', 'pizza']\n",
      "Ajanta - 0 - ['indian']\n",
      "Panino Sportivo Roma - 2 - ['italian', 'coffee-tea', 'sandwiches']\n",
      "Max Soha - 2 - ['italian']\n",
      "Strokos Pizza - 3 - ['deli', 'pizza', 'sandwiches', 'chicken']\n",
      "Camille's - 1 - ['american']\n",
      "Amsterdam Restaurant - 1 - ['american-new', 'tapas']\n",
      "Nussbaum & Wu - 0 - ['deli', 'sandwiches', 'bagels', 'salads']\n",
      "Amir's Grill - 1 - ['middle-eastern']\n",
      "M2M - Morning To Midnight - 1 - ['japanese', 'sushi', 'deli', 'sandwiches']\n",
      "The Mill - 2 - ['korean']\n",
      "Le Monde - 1 - ['french', 'bistro']\n",
      "Melba's - 1 - ['american-new', 'soul-food']\n",
      "Chuck E Cheese's - 0 - ['pizza', 'american']\n",
      "Haagen-Dazs - 1 - ['desserts']\n",
      "Oren's - 0 - ['coffee-tea', 'sandwiches']\n",
      "Dinosaur Bar-B-Que - 3 - ['barbecue', 'soul-food']\n",
      "Symposium Greek Restaurant - 0 - ['greek', 'vegetarian']\n",
      "Koronet Pizza - 1 - ['pizza']\n",
      "The Heights Bar & Grill - 1 - ['southwestern', 'tapas', 'bar-food']\n",
      "Cafe Nana - 0 - ['middle-eastern', 'kosher']\n",
      "Hamilton Deli - 0 - ['deli', 'sandwiches']\n",
      "Community Food & Juice - 0 - ['american-new', 'smoothies-juices', 'vegetarian', 'healthy', 'localorganic']\n",
      "Haakon's Hall - 0 - ['american', 'scandinavian']\n",
      "El Porton - 0 - ['mexican']\n",
      "Brad's - 1 - ['coffee-tea']\n",
      "Mel's Burger Bar - 2 - ['burgers', 'bar-food']\n",
      "Bettolona - 1 - ['italian', 'pizza', 'localorganic']\n",
      "Five Guys - 1 - ['burgers', 'hot-dogs']\n",
      "Nikko - 2 - ['chinese', 'japanese', 'sushi', 'teahouses']\n",
      "Falafel on Broadway - 1 - ['middle-eastern']\n",
      "Sushi Sushi - 1 - ['japanese', 'sushi']\n",
      "Insomnia Cookies - 1 - ['bakery-pastries', 'desserts']\n",
      "Cafe Amrita - 1 - ['coffee-tea', 'sandwiches']\n",
      "Pinkberry - 2 - ['desserts']\n",
      "Artopolis Espresso Cafe - 1 - ['coffee-tea', 'sandwiches', 'crepes']\n",
      "Max Caffe - 2 - ['coffee-tea', 'sandwiches']\n",
      "Chipotle - 1 - ['mexican']\n",
      "Chokolat Patisserie - 5 - ['bakery-pastries', 'desserts']\n",
      "Chokolat Patisserie - 1 - ['bakery-pastries', 'coffee-tea', 'desserts']\n",
      "Joe's G-H Deli - 2 - ['deli', 'sandwiches']\n",
      "Joe the Art of Coffee - 1 - ['coffee-tea']\n",
      "Levain Bakery - 2 - ['bakery-pastries', 'desserts']\n",
      "Silvana - 1 - ['middle-eastern', 'vegetarian']\n",
      "Bier International - 1 - ['german', 'eclectic', 'bar-food']\n",
      "Vegenation - 1 - ['indian', 'vegetarian']\n",
      "Flat Top - 4 - ['american-new', 'bistro']\n",
      "Kuro Kuma - 1 - ['coffee-tea']\n",
      "Vinateria - 2 - ['american-new']\n",
      "Henan Cart - 1 - ['chinese']\n",
      "Vine - 2 - ['japanese', 'sushi', 'thai']\n",
      "El Paso Truck - 1 - ['mexican']\n",
      "Mama's Fried Chicken and Pizza - 1 - ['pizza', 'chicken']\n",
      "Chapati House - 2 - ['indian']\n",
      "Dig Inn Seasonal Market - 1 - ['american', 'localorganic']\n",
      "Uncle Luoyang - 1 - ['chinese']\n",
      "Pita Grill - 2 - ['middle-eastern']\n",
      "Kissaten Jin - 1 - ['japanese']\n",
      "Serengeti Teas & Spices - 2 - ['coffee-tea', 'teahouses']\n",
      "Lighthouse Fish Market & Restaurant - 3 - ['seafood', 'wings', 'fish--chips']\n",
      "Lolo's Seafood Shack - 2 - ['seafood']\n",
      "Harmony - 1 - ['sandwiches', 'salads', 'teahouses']\n",
      "Bernheim & Schwartz - 2 - ['american', 'gastropub']\n",
      "BTH (By The Hudson) - 3 - ['american-new']\n",
      "Empanadas Monumental - 0 - []\n",
      "Friedman's - 0 - []\n",
      "176 Presbyterian Deli - 0 - []\n",
      "Nacho Mama's - 0 - []\n",
      "Streetbird Rotisserie - 0 - []\n",
      "Colum Chinese Restaurant - 0 - []\n",
      "Flacoz Tacoz - 0 - []\n",
      "sweetgreen - 0 - []\n",
      "La Salle Dumpling Room - 0 - []\n"
     ]
    }
   ],
   "source": [
    "search_table = soup.find('table', {'class': 'search-results'}) # the search-results table\n",
    "table_body = search_table.find('tbody') # get only the table body\n",
    "for tr_tag in table_body.find_all('tr'):\n",
    "    name = get_name(tr_tag)\n",
    "    price = get_price(tr_tag)\n",
    "    cuisines = get_cuisines(tr_tag)\n",
    "    print(name, \"-\", price, \"-\", cuisines)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nice! Now we're going to rework the code so that instead of just printing stuff out, we'll make a list of dictionaries containing the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'cuisines': ['deli', 'sandwiches'], 'name': 'Milano Market', 'price': 2},\n",
       " {'cuisines': ['ethiopian', 'african'], 'name': 'Massawa', 'price': 0},\n",
       " {'cuisines': ['chinese', 'japanese', 'sushi'],\n",
       "  'name': 'China Place',\n",
       "  'price': 0},\n",
       " {'cuisines': ['cheese-steaks', 'deli', 'sandwiches', 'salads'],\n",
       "  'name': 'Subsconscious',\n",
       "  'price': 0},\n",
       " {'cuisines': ['italian', 'pizza'], 'name': 'Famous Famiglia', 'price': 0},\n",
       " {'cuisines': ['bakery-pastries', 'american', 'desserts'],\n",
       "  'name': 'Kitchenette',\n",
       "  'price': 0},\n",
       " {'cuisines': ['italian', 'pizza'], 'name': 'V & T Pizza', 'price': 0},\n",
       " {'cuisines': ['chinese'], 'name': 'New Aroma', 'price': 1},\n",
       " {'cuisines': ['chinese'], 'name': 'Peking Garden', 'price': 1},\n",
       " {'cuisines': ['diner'], 'name': \"Tom's Restaurant\", 'price': 1},\n",
       " {'cuisines': ['italian'], 'name': 'Pisticci', 'price': 1},\n",
       " {'cuisines': ['diner', 'american'], 'name': 'Deluxe', 'price': 1},\n",
       " {'cuisines': ['american', 'bar-food'], 'name': 'Toast', 'price': 2},\n",
       " {'cuisines': ['italian', 'pizza'],\n",
       "  'name': \"Tom's Delicious Pizza\",\n",
       "  'price': 2},\n",
       " {'cuisines': ['chinese'], 'name': 'West Place', 'price': 1},\n",
       " {'cuisines': ['italian', 'pizza'], 'name': \"Che' Bella Pizza\", 'price': 0},\n",
       " {'cuisines': ['indian'], 'name': 'Ajanta', 'price': 0},\n",
       " {'cuisines': ['italian', 'coffee-tea', 'sandwiches'],\n",
       "  'name': 'Panino Sportivo Roma',\n",
       "  'price': 2},\n",
       " {'cuisines': ['italian'], 'name': 'Max Soha', 'price': 2},\n",
       " {'cuisines': ['deli', 'pizza', 'sandwiches', 'chicken'],\n",
       "  'name': 'Strokos Pizza',\n",
       "  'price': 3},\n",
       " {'cuisines': ['american'], 'name': \"Camille's\", 'price': 1},\n",
       " {'cuisines': ['american-new', 'tapas'],\n",
       "  'name': 'Amsterdam Restaurant',\n",
       "  'price': 1},\n",
       " {'cuisines': ['deli', 'sandwiches', 'bagels', 'salads'],\n",
       "  'name': 'Nussbaum & Wu',\n",
       "  'price': 0},\n",
       " {'cuisines': ['middle-eastern'], 'name': \"Amir's Grill\", 'price': 1},\n",
       " {'cuisines': ['japanese', 'sushi', 'deli', 'sandwiches'],\n",
       "  'name': 'M2M - Morning To Midnight',\n",
       "  'price': 1},\n",
       " {'cuisines': ['korean'], 'name': 'The Mill', 'price': 2},\n",
       " {'cuisines': ['french', 'bistro'], 'name': 'Le Monde', 'price': 1},\n",
       " {'cuisines': ['american-new', 'soul-food'], 'name': \"Melba's\", 'price': 1},\n",
       " {'cuisines': ['pizza', 'american'], 'name': \"Chuck E Cheese's\", 'price': 0},\n",
       " {'cuisines': ['desserts'], 'name': 'Haagen-Dazs', 'price': 1},\n",
       " {'cuisines': ['coffee-tea', 'sandwiches'], 'name': \"Oren's\", 'price': 0},\n",
       " {'cuisines': ['barbecue', 'soul-food'],\n",
       "  'name': 'Dinosaur Bar-B-Que',\n",
       "  'price': 3},\n",
       " {'cuisines': ['greek', 'vegetarian'],\n",
       "  'name': 'Symposium Greek Restaurant',\n",
       "  'price': 0},\n",
       " {'cuisines': ['pizza'], 'name': 'Koronet Pizza', 'price': 1},\n",
       " {'cuisines': ['southwestern', 'tapas', 'bar-food'],\n",
       "  'name': 'The Heights Bar & Grill',\n",
       "  'price': 1},\n",
       " {'cuisines': ['middle-eastern', 'kosher'], 'name': 'Cafe Nana', 'price': 0},\n",
       " {'cuisines': ['deli', 'sandwiches'], 'name': 'Hamilton Deli', 'price': 0},\n",
       " {'cuisines': ['american-new',\n",
       "   'smoothies-juices',\n",
       "   'vegetarian',\n",
       "   'healthy',\n",
       "   'localorganic'],\n",
       "  'name': 'Community Food & Juice',\n",
       "  'price': 0},\n",
       " {'cuisines': ['american', 'scandinavian'],\n",
       "  'name': \"Haakon's Hall\",\n",
       "  'price': 0},\n",
       " {'cuisines': ['mexican'], 'name': 'El Porton', 'price': 0},\n",
       " {'cuisines': ['coffee-tea'], 'name': \"Brad's\", 'price': 1},\n",
       " {'cuisines': ['burgers', 'bar-food'], 'name': \"Mel's Burger Bar\", 'price': 2},\n",
       " {'cuisines': ['italian', 'pizza', 'localorganic'],\n",
       "  'name': 'Bettolona',\n",
       "  'price': 1},\n",
       " {'cuisines': ['burgers', 'hot-dogs'], 'name': 'Five Guys', 'price': 1},\n",
       " {'cuisines': ['chinese', 'japanese', 'sushi', 'teahouses'],\n",
       "  'name': 'Nikko',\n",
       "  'price': 2},\n",
       " {'cuisines': ['middle-eastern'], 'name': 'Falafel on Broadway', 'price': 1},\n",
       " {'cuisines': ['japanese', 'sushi'], 'name': 'Sushi Sushi', 'price': 1},\n",
       " {'cuisines': ['bakery-pastries', 'desserts'],\n",
       "  'name': 'Insomnia Cookies',\n",
       "  'price': 1},\n",
       " {'cuisines': ['coffee-tea', 'sandwiches'], 'name': 'Cafe Amrita', 'price': 1},\n",
       " {'cuisines': ['desserts'], 'name': 'Pinkberry', 'price': 2},\n",
       " {'cuisines': ['coffee-tea', 'sandwiches', 'crepes'],\n",
       "  'name': 'Artopolis Espresso Cafe',\n",
       "  'price': 1},\n",
       " {'cuisines': ['coffee-tea', 'sandwiches'], 'name': 'Max Caffe', 'price': 2},\n",
       " {'cuisines': ['mexican'], 'name': 'Chipotle', 'price': 1},\n",
       " {'cuisines': ['bakery-pastries', 'desserts'],\n",
       "  'name': 'Chokolat Patisserie',\n",
       "  'price': 5},\n",
       " {'cuisines': ['bakery-pastries', 'coffee-tea', 'desserts'],\n",
       "  'name': 'Chokolat Patisserie',\n",
       "  'price': 1},\n",
       " {'cuisines': ['deli', 'sandwiches'], 'name': \"Joe's G-H Deli\", 'price': 2},\n",
       " {'cuisines': ['coffee-tea'], 'name': 'Joe the Art of Coffee', 'price': 1},\n",
       " {'cuisines': ['bakery-pastries', 'desserts'],\n",
       "  'name': 'Levain Bakery',\n",
       "  'price': 2},\n",
       " {'cuisines': ['middle-eastern', 'vegetarian'], 'name': 'Silvana', 'price': 1},\n",
       " {'cuisines': ['german', 'eclectic', 'bar-food'],\n",
       "  'name': 'Bier International',\n",
       "  'price': 1},\n",
       " {'cuisines': ['indian', 'vegetarian'], 'name': 'Vegenation', 'price': 1},\n",
       " {'cuisines': ['american-new', 'bistro'], 'name': 'Flat Top', 'price': 4},\n",
       " {'cuisines': ['coffee-tea'], 'name': 'Kuro Kuma', 'price': 1},\n",
       " {'cuisines': ['american-new'], 'name': 'Vinateria', 'price': 2},\n",
       " {'cuisines': ['chinese'], 'name': 'Henan Cart', 'price': 1},\n",
       " {'cuisines': ['japanese', 'sushi', 'thai'], 'name': 'Vine', 'price': 2},\n",
       " {'cuisines': ['mexican'], 'name': 'El Paso Truck', 'price': 1},\n",
       " {'cuisines': ['pizza', 'chicken'],\n",
       "  'name': \"Mama's Fried Chicken and Pizza\",\n",
       "  'price': 1},\n",
       " {'cuisines': ['indian'], 'name': 'Chapati House', 'price': 2},\n",
       " {'cuisines': ['american', 'localorganic'],\n",
       "  'name': 'Dig Inn Seasonal Market',\n",
       "  'price': 1},\n",
       " {'cuisines': ['chinese'], 'name': 'Uncle Luoyang', 'price': 1},\n",
       " {'cuisines': ['middle-eastern'], 'name': 'Pita Grill', 'price': 2},\n",
       " {'cuisines': ['japanese'], 'name': 'Kissaten Jin', 'price': 1},\n",
       " {'cuisines': ['coffee-tea', 'teahouses'],\n",
       "  'name': 'Serengeti Teas & Spices',\n",
       "  'price': 2},\n",
       " {'cuisines': ['seafood', 'wings', 'fish--chips'],\n",
       "  'name': 'Lighthouse Fish Market & Restaurant',\n",
       "  'price': 3},\n",
       " {'cuisines': ['seafood'], 'name': \"Lolo's Seafood Shack\", 'price': 2},\n",
       " {'cuisines': ['sandwiches', 'salads', 'teahouses'],\n",
       "  'name': 'Harmony',\n",
       "  'price': 1},\n",
       " {'cuisines': ['american', 'gastropub'],\n",
       "  'name': 'Bernheim & Schwartz',\n",
       "  'price': 2},\n",
       " {'cuisines': ['american-new'], 'name': 'BTH (By The Hudson)', 'price': 3},\n",
       " {'cuisines': [], 'name': 'Empanadas Monumental', 'price': 0},\n",
       " {'cuisines': [], 'name': \"Friedman's\", 'price': 0},\n",
       " {'cuisines': [], 'name': '176 Presbyterian Deli', 'price': 0},\n",
       " {'cuisines': [], 'name': \"Nacho Mama's\", 'price': 0},\n",
       " {'cuisines': [], 'name': 'Streetbird Rotisserie', 'price': 0},\n",
       " {'cuisines': [], 'name': 'Colum Chinese Restaurant', 'price': 0},\n",
       " {'cuisines': [], 'name': 'Flacoz Tacoz', 'price': 0},\n",
       " {'cuisines': [], 'name': 'sweetgreen', 'price': 0},\n",
       " {'cuisines': [], 'name': 'La Salle Dumpling Room', 'price': 0}]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants = []\n",
    "search_table = soup.find('table', {'class': 'search-results'}) # the search-results table\n",
    "table_body = search_table.find('tbody') # get only the table body\n",
    "for tr_tag in table_body.find_all('tr'):\n",
    "    name = get_name(tr_tag)\n",
    "    price = get_price(tr_tag)\n",
    "    cuisines = get_cuisines(tr_tag)\n",
    "    rest_dict = {'name': name, 'price': price, 'cuisines': cuisines}\n",
    "    restaurants.append(rest_dict)\n",
    "restaurants"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now `restaurants` is a *list of dictionaries*, which is the data structure we were aiming for from the beginning."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Back to SQL\n",
    "\n",
    "In order to store this data in SQL, we first need to create the data schema. By this I mean: we need to decide which tables are needed, and what the data types will be for those tables.\n",
    "\n",
    "So what tables do we need? Clearly we need a table for restaurants, since that's what the data represents. The restaurant table will need at least a field for name and price. The question of how to store the cuisine types is a little bit different: it's an example of a many-to-many relationship. Each restaurant can have zero or more cuisine types, and each cuisine type can be associated with more than one restaurant.\n",
    "\n",
    "The best way to represent a many-to-many relationship in SQL is with a linking table, and this instance will be no different! Because the cuisine types themselves don't have any extra data associated with them, we can safely store a string for the type in the linking table itself, instead of creating a second table for cuisine types.\n",
    "\n",
    "Here's what I propose for the schemas of the two tables:\n",
    "\n",
    "The `restaurant` table:\n",
    "\n",
    "| column | type |\n",
    "| ------ | ---- |\n",
    "| id | serial |\n",
    "| name | varchar(80) |\n",
    "| price | int |\n",
    "\n",
    "The `cuisine` table:\n",
    "\n",
    "| column | type |\n",
    "| ------ | ---- |\n",
    "| restaurant_id | int |\n",
    "| kind | varchar(40) |\n",
    "\n",
    "The `serial` type is a special PostgreSQL type that automatically creates an incrementing unique integer in that field. This is great for fields that function as unique identifiers where you don't want to keep track of the number yourself."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the tables\n",
    "\n",
    "At this point, you can open up `psql` and create a new database to contain the restaurant data. We'll call it `menupages`:\n",
    "\n",
    "    allison=# create database menupages;\n",
    "    CREATE DATABASE\n",
    "\n",
    "Connect to the database like so:\n",
    "\n",
    "    \\c menupages\n",
    "    \n",
    "We'll use the [`CREATE TABLE`](https://www.postgresql.org/docs/current/static/sql-createtable.html) SQL command to create the tables. Here's the `CREATE TABLE` for restaurants:\n",
    "\n",
    "    CREATE TABLE restaurant (\n",
    "      id serial,\n",
    "      name varchar(80),\n",
    "      price int\n",
    "    );\n",
    "    \n",
    "And the `CREATE TABLE` for cuisines:\n",
    "\n",
    "    CREATE TABLE cuisine (\n",
    "      restaurant_id int,\n",
    "      kind varchar(40)\n",
    "    );\n",
    "    \n",
    "After executing these `CREATE TABLE` statements, running `\\d` should show something like this:\n",
    "\n",
    "```\n",
    "menupages=# \\d\n",
    "                List of relations\n",
    " Schema |       Name        |   Type   |  Owner  \n",
    "--------+-------------------+----------+---------\n",
    " public | cuisine           | table    | allison\n",
    " public | restaurant        | table    | allison\n",
    " public | restaurant_id_seq | sequence | allison\n",
    "(3 rows)\n",
    "```\n",
    "\n",
    "(The `restaurant_id_seq` entry is something that PostgreSQL adds automatically to keep track of the `serial` type.)\n",
    "\n",
    "> Note: You can also issue the `CREATE TABLE` statements in Python using pg8000 if you want! Generally I find it helpful to do the database \"setup\" steps like creating databases and creating tables in `psql`, where it's easy to use commands like `\\d` to see your work, and use Python only for the steps that require working with the data itself.\n",
    "\n",
    "### Inserting data into SQL\n",
    "\n",
    "Now we can take the data in our list of dictionaries and add it to SQL. We'll do this by connecting to the database with pg8000 and using the `INSERT` statement to add records to the tables. The code in the following cell connects to PostgreSQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pg8000\n",
    "conn = pg8000.connect(database=\"menupages\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here's a cell that calls the connection's `.rollback()` method, just in case you need to back out of an error:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The process of inserting a record into the database from Python is a little bit complicated. First, we need to create a cursor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, we'll write a string that contains the `INSERT INTO` SQL statement. The `INSERT INTO` statement looks like this, schematically (fill in the lower-case \n",
    "\n",
    "    INSERT INTO table_name (col1, col2, col3, col4, ...)\n",
    "    VALUES (value1, value2, value3, val4, ...)\n",
    "    RETURNING rval1, rval2, ...\n",
    "    \n",
    "... where:\n",
    "\n",
    "* `table_name` is the name of your table\n",
    "* `colN`: one or more comma-separated column names\n",
    "* `valueN`: one or more comma-separated values to insert into the columns (in same order as `colN`)\n",
    "* `rvalN`: values to *return* from the insert process\n",
    "\n",
    "The following statement inserts a new row into the `restaurant` table with the name `Test Restaurant` and price `3`, returning the `id` field:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_insert = \"INSERT INTO restaurant (name, price) VALUES ('Test Restaurant', 3) RETURNING id\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll execute this statement with `cursor.execute()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(test_insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `cursor.fetchone()`, we can get back the value for the `id` field, which was automatically created by the database because the column's type is `serial`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "629\n"
     ]
    }
   ],
   "source": [
    "rowid = cursor.fetchone()[0] # `fetchone()` always returns a list of values, even if there's only one column\n",
    "print(rowid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The value for `id` will start with 0 and increment once for every record you add to the table. Note that the number keeps growing *even if you've deleted rows from the table* (which is why the number you see above is greater than 0: I've been messing with this table a lot, making mistakes, deleting records, etc.).\n",
    "\n",
    "Changes you make to the database don't take effect until you've executed the `.commit()` method of the connection object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll do a quick check to ensure that the record did indeed get added to the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[629, 'Test Restaurant', 3]\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM restaurant\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks good! We got back the row that we just created.\n",
    "\n",
    "For more information on `INSERT`, visit the [official PostgreSQL documentation](https://www.postgresql.org/docs/current/static/sql-insert.html).\n",
    "\n",
    "### Parameters\n",
    "\n",
    "If you're including data from another source in a SQL statement, you should use *parameters* instead of attempting to construct the query yourself. This saves you from accidentally introducing errors into your program if the data you want to insert uses special SQL characters. To use parameters, we'll replace the hard-coded data items in the SQL statement we wrote above with the special replacement character sequence `%s`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "insert_statement = \"INSERT INTO restaurant (name, price) VALUES (%s, %s) RETURNING id\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, when calling `.execute()` on the cursor object, we include the values we want to be included in the query as a second parameter, in a list, in the order we want them to replace the `%s` sequences in the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "630\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(insert_statement, [\"Another Test Restaurant\", 4])\n",
    "rowid = cursor.fetchone()[0]\n",
    "conn.commit()\n",
    "print(rowid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that this variety of \"parameters\" is a special syntax supported only by the `cursor.execute()` function. (It bears superficial similarity to [Python string formatting](https://pyformat.info/), but is actually its own beast entirely.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding the cuisines\n",
    "\n",
    "Once we've added a restaurant, we'll also want to add the cuisines associated with the restaurant. Eventually we'll do this for *all* of the restaurants in a loop, but for now we're just going to add a few \"test cuisines\" as an example.\n",
    "\n",
    "The trick with the cuisines is that they need to be associated with the *ID of the restaurant*. Which means we need to insert the restaurant first, remember its row ID, and then insert the cuisine records with that ID. We added a restaurant in the example above and captured its row ID in the variable `rowid`. We'll use that variable below to insert the cuisine records:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cuisine_insert = \"INSERT INTO cuisine (restaurant_id, kind) VALUES (%s, %s)\"\n",
    "cursor.execute(cuisine_insert, [rowid, \"Fondue\"])\n",
    "cursor.execute(cuisine_insert, [rowid, \"Casseroles\"])\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that in this case, we don't need the `RETURNING` clause in the `INSERT` statement, because we don't need any data back from SQL after the row has been inserted (the `cuisine` table doesn't have a serial `id` field, so there's no information to get back).\n",
    "\n",
    "Now a `SELECT` should return the records we just added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[630, 'Fondue']\n",
      "[630, 'Casseroles']\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM cuisine\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we should be able to do a join to get the name of the restaurant along with the cuisine:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Another Test Restaurant', 'Fondue']\n",
      "['Another Test Restaurant', 'Casseroles']\n"
     ]
    }
   ],
   "source": [
    "cuisine_join = \"\"\"SELECT restaurant.name, cuisine.kind\n",
    "FROM cuisine JOIN restaurant ON restaurant.id = cuisine.restaurant_id\n",
    "\"\"\"\n",
    "cursor.execute(cuisine_join)\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning out the test data\n",
    "\n",
    "We've been issuing \"test\" queries just to get the syntax right, but we don't want those records to remain in the database. To get rid of them, we'll use the `DELETE FROM` SQL statement. The `DELETE FROM` statement works like this:\n",
    "\n",
    "    DELETE FROM table WHERE expression;\n",
    "    \n",
    "... where `table` is the name of the table that you want to delete rows from, and `expression` is a SQL expression that defines which rows you want to delete. So for example, to delete the row that we just added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"DELETE FROM restaurant WHERE id = %s\", [rowid])\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, querying for all of the records in that table should not include the row we just added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[629, 'Test Restaurant', 3]\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SELECT * FROM restaurant\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to delete *all* the rows from a table, you can just leave the `WHERE` clause off of the `DELETE FROM` statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"DELETE FROM restaurant\") # delete all records in the table\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, the `SELECT` statement should return no rows at all:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SELECT * FROM restaurant\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nothing gets displayed because there were no records returned. In other words, the table is perfectly clean. Let's clean out the records from the `cuisine` table while we're at it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"DELETE FROM cuisine\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For more information on the `DELETE` statement, [see the PostgreSQL documentation](https://www.postgresql.org/docs/current/static/sql-delete.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inserting real data\n",
    "\n",
    "Okay, now we're on track to being able to actually put some of our *real data* into the table. To do this, we'll issue *one insert statement* for each dictionary in the list `restaurants`, using the name and price from each dictionary, saving the relevant row ID. In another `for` loop, we'll add a record to the `cuisine` table for each cuisine in the list that is the value for the dictionary's `cuisines` key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n",
    "for item in restaurants:\n",
    "    cursor.execute(\n",
    "        \"INSERT INTO restaurant (name, price) VALUES (%s, %s) RETURNING id\",\n",
    "        [item['name'], item['price']])\n",
    "    rowid = cursor.fetchone()[0]\n",
    "    for cuisine_name in item['cuisines']:\n",
    "        cursor.execute(\n",
    "            \"INSERT INTO cuisine (restaurant_id, kind) VALUES (%s, %s)\",\n",
    "            [rowid, cuisine_name])\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That probably happened *really fast* on your computer—so fast that maybe you think nothing happened at all. Let's run a `SELECT` statement to verify that the records made it into the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[631, 'Milano Market', 2]\n",
      "[632, 'Massawa', 0]\n",
      "[633, 'China Place', 0]\n",
      "[634, 'Subsconscious', 0]\n",
      "[635, 'Famous Famiglia', 0]\n",
      "[636, 'Kitchenette', 0]\n",
      "[637, 'V & T Pizza', 0]\n",
      "[638, 'New Aroma', 1]\n",
      "[639, 'Peking Garden', 1]\n",
      "[640, \"Tom's Restaurant\", 1]\n",
      "[641, 'Pisticci', 1]\n",
      "[642, 'Deluxe', 1]\n",
      "[643, 'Toast', 2]\n",
      "[644, \"Tom's Delicious Pizza\", 2]\n",
      "[645, 'West Place', 1]\n",
      "[646, \"Che' Bella Pizza\", 0]\n",
      "[647, 'Ajanta', 0]\n",
      "[648, 'Panino Sportivo Roma', 2]\n",
      "[649, 'Max Soha', 2]\n",
      "[650, 'Strokos Pizza', 3]\n",
      "[651, \"Camille's\", 1]\n",
      "[652, 'Amsterdam Restaurant', 1]\n",
      "[653, 'Nussbaum & Wu', 0]\n",
      "[654, \"Amir's Grill\", 1]\n",
      "[655, 'M2M - Morning To Midnight', 1]\n",
      "[656, 'The Mill', 2]\n",
      "[657, 'Le Monde', 1]\n",
      "[658, \"Melba's\", 1]\n",
      "[659, \"Chuck E Cheese's\", 0]\n",
      "[660, 'Haagen-Dazs', 1]\n",
      "[661, \"Oren's\", 0]\n",
      "[662, 'Dinosaur Bar-B-Que', 3]\n",
      "[663, 'Symposium Greek Restaurant', 0]\n",
      "[664, 'Koronet Pizza', 1]\n",
      "[665, 'The Heights Bar & Grill', 1]\n",
      "[666, 'Cafe Nana', 0]\n",
      "[667, 'Hamilton Deli', 0]\n",
      "[668, 'Community Food & Juice', 0]\n",
      "[669, \"Haakon's Hall\", 0]\n",
      "[670, 'El Porton', 0]\n",
      "[671, \"Brad's\", 1]\n",
      "[672, \"Mel's Burger Bar\", 2]\n",
      "[673, 'Bettolona', 1]\n",
      "[674, 'Five Guys', 1]\n",
      "[675, 'Nikko', 2]\n",
      "[676, 'Falafel on Broadway', 1]\n",
      "[677, 'Sushi Sushi', 1]\n",
      "[678, 'Insomnia Cookies', 1]\n",
      "[679, 'Cafe Amrita', 1]\n",
      "[680, 'Pinkberry', 2]\n",
      "[681, 'Artopolis Espresso Cafe', 1]\n",
      "[682, 'Max Caffe', 2]\n",
      "[683, 'Chipotle', 1]\n",
      "[684, 'Chokolat Patisserie', 5]\n",
      "[685, 'Chokolat Patisserie', 1]\n",
      "[686, \"Joe's G-H Deli\", 2]\n",
      "[687, 'Joe the Art of Coffee', 1]\n",
      "[688, 'Levain Bakery', 2]\n",
      "[689, 'Silvana', 1]\n",
      "[690, 'Bier International', 1]\n",
      "[691, 'Vegenation', 1]\n",
      "[692, 'Flat Top', 4]\n",
      "[693, 'Kuro Kuma', 1]\n",
      "[694, 'Vinateria', 2]\n",
      "[695, 'Henan Cart', 1]\n",
      "[696, 'Vine', 2]\n",
      "[697, 'El Paso Truck', 1]\n",
      "[698, \"Mama's Fried Chicken and Pizza\", 1]\n",
      "[699, 'Chapati House', 2]\n",
      "[700, 'Dig Inn Seasonal Market', 1]\n",
      "[701, 'Uncle Luoyang', 1]\n",
      "[702, 'Pita Grill', 2]\n",
      "[703, 'Kissaten Jin', 1]\n",
      "[704, 'Serengeti Teas & Spices', 2]\n",
      "[705, 'Lighthouse Fish Market & Restaurant', 3]\n",
      "[706, \"Lolo's Seafood Shack\", 2]\n",
      "[707, 'Harmony', 1]\n",
      "[708, 'Bernheim & Schwartz', 2]\n",
      "[709, 'BTH (By The Hudson)', 3]\n",
      "[710, 'Empanadas Monumental', 0]\n",
      "[711, \"Friedman's\", 0]\n",
      "[712, '176 Presbyterian Deli', 0]\n",
      "[713, \"Nacho Mama's\", 0]\n",
      "[714, 'Streetbird Rotisserie', 0]\n",
      "[715, 'Colum Chinese Restaurant', 0]\n",
      "[716, 'Flacoz Tacoz', 0]\n",
      "[717, 'sweetgreen', 0]\n",
      "[718, 'La Salle Dumpling Room', 0]\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SELECT * FROM restaurant\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hey, there they all are! And the cuisines too!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[631, 'deli']\n",
      "[631, 'sandwiches']\n",
      "[632, 'ethiopian']\n",
      "[632, 'african']\n",
      "[633, 'chinese']\n",
      "[633, 'japanese']\n",
      "[633, 'sushi']\n",
      "[634, 'cheese-steaks']\n",
      "[634, 'deli']\n",
      "[634, 'sandwiches']\n",
      "[634, 'salads']\n",
      "[635, 'italian']\n",
      "[635, 'pizza']\n",
      "[636, 'bakery-pastries']\n",
      "[636, 'american']\n",
      "[636, 'desserts']\n",
      "[637, 'italian']\n",
      "[637, 'pizza']\n",
      "[638, 'chinese']\n",
      "[639, 'chinese']\n",
      "[640, 'diner']\n",
      "[641, 'italian']\n",
      "[642, 'diner']\n",
      "[642, 'american']\n",
      "[643, 'american']\n",
      "[643, 'bar-food']\n",
      "[644, 'italian']\n",
      "[644, 'pizza']\n",
      "[645, 'chinese']\n",
      "[646, 'italian']\n",
      "[646, 'pizza']\n",
      "[647, 'indian']\n",
      "[648, 'italian']\n",
      "[648, 'coffee-tea']\n",
      "[648, 'sandwiches']\n",
      "[649, 'italian']\n",
      "[650, 'deli']\n",
      "[650, 'pizza']\n",
      "[650, 'sandwiches']\n",
      "[650, 'chicken']\n",
      "[651, 'american']\n",
      "[652, 'american-new']\n",
      "[652, 'tapas']\n",
      "[653, 'deli']\n",
      "[653, 'sandwiches']\n",
      "[653, 'bagels']\n",
      "[653, 'salads']\n",
      "[654, 'middle-eastern']\n",
      "[655, 'japanese']\n",
      "[655, 'sushi']\n",
      "[655, 'deli']\n",
      "[655, 'sandwiches']\n",
      "[656, 'korean']\n",
      "[657, 'french']\n",
      "[657, 'bistro']\n",
      "[658, 'american-new']\n",
      "[658, 'soul-food']\n",
      "[659, 'pizza']\n",
      "[659, 'american']\n",
      "[660, 'desserts']\n",
      "[661, 'coffee-tea']\n",
      "[661, 'sandwiches']\n",
      "[662, 'barbecue']\n",
      "[662, 'soul-food']\n",
      "[663, 'greek']\n",
      "[663, 'vegetarian']\n",
      "[664, 'pizza']\n",
      "[665, 'southwestern']\n",
      "[665, 'tapas']\n",
      "[665, 'bar-food']\n",
      "[666, 'middle-eastern']\n",
      "[666, 'kosher']\n",
      "[667, 'deli']\n",
      "[667, 'sandwiches']\n",
      "[668, 'american-new']\n",
      "[668, 'smoothies-juices']\n",
      "[668, 'vegetarian']\n",
      "[668, 'healthy']\n",
      "[668, 'localorganic']\n",
      "[669, 'american']\n",
      "[669, 'scandinavian']\n",
      "[670, 'mexican']\n",
      "[671, 'coffee-tea']\n",
      "[672, 'burgers']\n",
      "[672, 'bar-food']\n",
      "[673, 'italian']\n",
      "[673, 'pizza']\n",
      "[673, 'localorganic']\n",
      "[674, 'burgers']\n",
      "[674, 'hot-dogs']\n",
      "[675, 'chinese']\n",
      "[675, 'japanese']\n",
      "[675, 'sushi']\n",
      "[675, 'teahouses']\n",
      "[676, 'middle-eastern']\n",
      "[677, 'japanese']\n",
      "[677, 'sushi']\n",
      "[678, 'bakery-pastries']\n",
      "[678, 'desserts']\n",
      "[679, 'coffee-tea']\n",
      "[679, 'sandwiches']\n",
      "[680, 'desserts']\n",
      "[681, 'coffee-tea']\n",
      "[681, 'sandwiches']\n",
      "[681, 'crepes']\n",
      "[682, 'coffee-tea']\n",
      "[682, 'sandwiches']\n",
      "[683, 'mexican']\n",
      "[684, 'bakery-pastries']\n",
      "[684, 'desserts']\n",
      "[685, 'bakery-pastries']\n",
      "[685, 'coffee-tea']\n",
      "[685, 'desserts']\n",
      "[686, 'deli']\n",
      "[686, 'sandwiches']\n",
      "[687, 'coffee-tea']\n",
      "[688, 'bakery-pastries']\n",
      "[688, 'desserts']\n",
      "[689, 'middle-eastern']\n",
      "[689, 'vegetarian']\n",
      "[690, 'german']\n",
      "[690, 'eclectic']\n",
      "[690, 'bar-food']\n",
      "[691, 'indian']\n",
      "[691, 'vegetarian']\n",
      "[692, 'american-new']\n",
      "[692, 'bistro']\n",
      "[693, 'coffee-tea']\n",
      "[694, 'american-new']\n",
      "[695, 'chinese']\n",
      "[696, 'japanese']\n",
      "[696, 'sushi']\n",
      "[696, 'thai']\n",
      "[697, 'mexican']\n",
      "[698, 'pizza']\n",
      "[698, 'chicken']\n",
      "[699, 'indian']\n",
      "[700, 'american']\n",
      "[700, 'localorganic']\n",
      "[701, 'chinese']\n",
      "[702, 'middle-eastern']\n",
      "[703, 'japanese']\n",
      "[704, 'coffee-tea']\n",
      "[704, 'teahouses']\n",
      "[705, 'seafood']\n",
      "[705, 'wings']\n",
      "[705, 'fish--chips']\n",
      "[706, 'seafood']\n",
      "[707, 'sandwiches']\n",
      "[707, 'salads']\n",
      "[707, 'teahouses']\n",
      "[708, 'american']\n",
      "[708, 'gastropub']\n",
      "[709, 'american-new']\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SELECT * FROM cuisine\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fun SQL queries with the data\n",
    "\n",
    "Now that we've successfully imported the data into SQL, we can do fun SQL stuff with it. First, let's get the names of all the pizza restaurants:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Famous Famiglia\n",
      "V & T Pizza\n",
      "Tom's Delicious Pizza\n",
      "Che' Bella Pizza\n",
      "Strokos Pizza\n",
      "Chuck E Cheese's\n",
      "Koronet Pizza\n",
      "Bettolona\n",
      "Mama's Fried Chicken and Pizza\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"SELECT r.name\n",
    "FROM restaurant r JOIN cuisine c ON c.restaurant_id = r.id\n",
    "WHERE c.kind = 'pizza'\"\"\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's how to find the number of restaurants for each kind of cuisine, as long as that kind of cuisine has more than one restaurant listed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sandwiches - 13 restaurant(s)\n",
      "coffee-tea - 10 restaurant(s)\n",
      "pizza - 9 restaurant(s)\n",
      "italian - 8 restaurant(s)\n",
      "american - 8 restaurant(s)\n",
      "deli - 7 restaurant(s)\n",
      "desserts - 7 restaurant(s)\n",
      "chinese - 7 restaurant(s)\n",
      "japanese - 6 restaurant(s)\n",
      "american-new - 6 restaurant(s)\n",
      "sushi - 5 restaurant(s)\n",
      "bakery-pastries - 5 restaurant(s)\n",
      "middle-eastern - 5 restaurant(s)\n",
      "bar-food - 4 restaurant(s)\n",
      "vegetarian - 4 restaurant(s)\n",
      "teahouses - 3 restaurant(s)\n",
      "localorganic - 3 restaurant(s)\n",
      "indian - 3 restaurant(s)\n",
      "mexican - 3 restaurant(s)\n",
      "salads - 3 restaurant(s)\n",
      "soul-food - 2 restaurant(s)\n",
      "tapas - 2 restaurant(s)\n",
      "seafood - 2 restaurant(s)\n",
      "diner - 2 restaurant(s)\n",
      "chicken - 2 restaurant(s)\n",
      "bistro - 2 restaurant(s)\n",
      "burgers - 2 restaurant(s)\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"SELECT kind, count(kind)\n",
    "FROM cuisine\n",
    "GROUP BY kind\n",
    "HAVING count(kind) > 1\n",
    "ORDER BY count(cuisine) DESC\"\"\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item[0], \"-\", item[1], \"restaurant(s)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's find the average price for each kind of cuisine. Note that we're specifically excluding records from the restaurant table whose value is `0`, since we used `0` in that field to indicate that the price was unknown:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "barbecue - 3.0\n",
      "fish--chips - 3.0\n",
      "wings - 3.0\n",
      "seafood - 2.5\n",
      "bistro - 2.5\n",
      "bakery-pastries - 2.25\n",
      "american-new - 2.2\n",
      "desserts - 2.0\n",
      "thai - 2.0\n",
      "gastropub - 2.0\n",
      "korean - 2.0\n",
      "chicken - 2.0\n",
      "soul-food - 2.0\n",
      "deli - 2.0\n",
      "teahouses - 1.6666666666666667\n",
      "sandwiches - 1.6666666666666667\n",
      "italian - 1.6\n",
      "pizza - 1.6\n",
      "bar-food - 1.5\n",
      "burgers - 1.5\n",
      "sushi - 1.5\n",
      "indian - 1.5\n",
      "american - 1.4\n",
      "japanese - 1.4\n",
      "coffee-tea - 1.3333333333333333\n",
      "middle-eastern - 1.25\n",
      "chinese - 1.1666666666666667\n",
      "french - 1.0\n",
      "tapas - 1.0\n",
      "hot-dogs - 1.0\n",
      "southwestern - 1.0\n",
      "crepes - 1.0\n",
      "mexican - 1.0\n",
      "salads - 1.0\n",
      "eclectic - 1.0\n",
      "vegetarian - 1.0\n",
      "diner - 1.0\n",
      "localorganic - 1.0\n",
      "german - 1.0\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"SELECT c.kind, avg(r.price)\n",
    "FROM cuisine c JOIN restaurant r ON c.restaurant_id = r.id\n",
    "WHERE r.price > 0\n",
    "GROUP BY c.kind\n",
    "ORDER BY avg(r.price) DESC\"\"\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item[0], \"-\", float(item[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using Python it's easy to chain two `SELECT` statements together to find, e.g., the most expensive New American restaurant:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Flat Top\n"
     ]
    }
   ],
   "source": [
    "# get the price of the most expensive restaurant\n",
    "cursor.execute(\"\"\"SELECT max(r.price)\n",
    "FROM restaurant r JOIN cuisine c ON r.id = c.restaurant_id\n",
    "WHERE c.kind = 'american-new'\"\"\")\n",
    "price = cursor.fetchall()[0][0] # get first column from first row\n",
    "\n",
    "# retrieve the restaurant with that price (n.b. there might be more than one!)\n",
    "cursor.execute(\"\"\"SELECT r.name\n",
    "FROM restaurant r JOIN cuisine c ON r.id = c.restaurant_id\n",
    "WHERE c.kind = 'american-new' AND r.price = %s\"\"\", [price])\n",
    "print(cursor.fetchall()[0][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Updating records\n",
    "\n",
    "You might be wondering, \"what if I make a mistake when importing my data? What then? Do I just need to drop all the tables and start again?! That seems wasteful.\" And indeed, in my experience when you're working with data sets of small to medium size, it usually makes sense to think of data import as one monolithic operation that either succeeds or fails. When you're working with larger data, where the import process might take hours or days, you may want to \"clean up\" a record without re-running the entire import process. Or you may want to change a whole series of records that have incorrect or inconsistent data. For this, there's the `UPDATE` statement.\n",
    "\n",
    "The `UPDATE` statement is sort of like a cross between a `SELECT` statement and an `INSERT` statement. You specify which fields you want to update, and what values you want to update them with, and then you specify which records should be updated in this manner. The syntax looks like this:\n",
    "\n",
    "    UPDATE table\n",
    "    SET col1 = val1, col2 = val2, col3 = val3\n",
    "    WHERE expr\n",
    "    \n",
    "... where `table` is the name of the table whose records you want to update; `colN` and `valN` are the column names whose values you want to update and the values you want those columns to be set to (respectively); and `expr` is a SQL expression specifying which records should be updated. There needs to be at least one column and value to set, but there can also be as many more as you want.\n",
    "\n",
    "So, for example, let's say that \"Brad's\" changed its name to \"Sad Coffee Shack\" as we want to update the `restaurants` table to reflect this fact. The update statement might look something like this, supplying the parameter placeholders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "update_name = \"\"\"UPDATE restaurant\n",
    "SET name = %s\n",
    "WHERE name = %s\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the cell below, I execute the statement with the needed parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.execute(update_name, [\"Sad Coffee Shack\", \"Brad's\"])\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should be able to see the results when, e.g., selecting all of the restaurants that have `coffee-tea` listed as a kind of cuisine:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Panino Sportivo Roma\n",
      "Oren's\n",
      "Cafe Amrita\n",
      "Artopolis Espresso Cafe\n",
      "Max Caffe\n",
      "Chokolat Patisserie\n",
      "Joe the Art of Coffee\n",
      "Kuro Kuma\n",
      "Serengeti Teas & Spices\n",
      "Sad Coffee Shack\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"\"\"SELECT r.name\n",
    "FROM restaurant r JOIN cuisine c ON c.restaurant_id = r.id\n",
    "WHERE c.kind = 'coffee-tea'\"\"\")\n",
    "for item in cursor.fetchall():\n",
    "    print(item[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `UPDATE` statement is considerably more powerful than I've let on here. For more information on the `UPDATE` statement, see [the PostgreSQL documentation](https://www.postgresql.org/docs/current/static/sql-update.html)."
   ]
  }
 ],
 "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
}