SQL_in_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Writing SQL Queries in Python: The Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We started you off by having you write SQL queries in PostgreSQL's interactive shell (`psql`). Which is cool, but you're probably wondering: \"Hey how do I actually get information from a SQL database into my Python programs? That's where I can do some real damage.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python PostgreSQL libraries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to connect to a SQL database and make queries in Python, we need to use a *database adapter library*. Such libraries provide the functionality needed to connect to a database, send SQL statements, and receive responses, taking all necessary steps to ensure that (e.g.) SQL types are converted safely to Python types, etc.\n",
    "\n",
    "Each RDBMS has several different adapter libraries. For this tutorial, we're going to use a library called [pg8000](https://github.com/mfenniak/pg8000). There are a number of other PostgreSQL adapter libraries; the reason we're using pg8000 is that it's easy to install, as it has no external dependencies. [psycopg2](http://initd.org/psycopg/docs/index.html) is another popular library for accessing PostgreSQL in Python. It's harder to install but offers significant performance advantages over pg8000. The good news is that nearly *all* of the database adapter libraries in Python conform to the [DB-API specification](https://www.python.org/dev/peps/pep-0249/), so once you've learned one, you should be able to switch over to the others with relative ease.\n",
    "\n",
    "This library assumes that you've completed the tutorial in the [SQL introduction](SQL_notes.md) notes, and that you have a PostgreSQL server with a copy of the [MONDIAL database](http://www.dbis.informatik.uni-goettingen.de/Mondial/) running on your computer on the default port."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Installing pg8000\n",
    "\n",
    "The pg8000 library can be installed with `pip`. (If you're using a version of Python that doesn't include `pip`, make sure to [install pip first](https://pip.pypa.io/en/stable/installing/). You may want to create a virtual environment first, to isolate different projects from upgrades and uninstalls.) In OSX and other UNIX-like operating systems, type the following on the command line:\n",
    "\n",
    "    pip3 install pg8000\n",
    "    \n",
    "(This should also work if you've installed Python on Windows using the instructions in class. If you're using a different Python packaging system, you're on your own!)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting to a database with pg8000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When using a SQL server from Python, you'll be working with several different kinds of objects:\n",
    "\n",
    "* A *connection object*, which gives you access to the server; and\n",
    "* *Cursor objects*, which you use to make SQL queries and retrieve data  returned from those queries.\n",
    "\n",
    "To create a connection object, call `pg8000`'s `connect()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pg8000.core.Connection'>\n"
     ]
    }
   ],
   "source": [
    "import pg8000\n",
    "conn = pg8000.connect(database=\"mondial\")\n",
    "print(type(conn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `connect()` function takes a number of named parameters. The only one we're using here is `database`, which specifies which database to connect to. If we were attempting to connect to a PostgreSQL server on someone else's machine, we might need to use the `host` and `port` parameters. [Consult the documentation](http://pythonhosted.org/pg8000/dbapi.html#pg8000.connect) for more information."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Making a query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have an open connection, let's attempt to query the database. To perform a query, we first need a cursor object, which we can create using the connection object's `.cursor()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pg8000.core.Cursor'>\n"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "print(type(cursor))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The cursor object has several methods of interest to us. The first and most important is `.execute()`, which takes a SQL statement (in a Python string) as a parameter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT name, length FROM river WHERE length > 4000\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.execute()` performs the query, but doesn't evaluate to anything. After calling `.execute()`, you can call the cursor's `.fetchone()` method to get the first row returned from the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Irtysch', Decimal('4248')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subsequent requests to `.fetchone()` will return subsequent rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Jenissej', Decimal('4092')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To retrieve all of the rows returned from a query, you can use the cursor object in a `for` loop, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Irtysch', Decimal('4248')]\n",
      "['Jenissej', Decimal('4092')]\n",
      "['Lena', Decimal('4400')]\n",
      "['Hwangho', Decimal('4845')]\n",
      "['Jangtse', Decimal('6380')]\n",
      "['Mekong', Decimal('4350')]\n",
      "['Missouri', Decimal('4130')]\n",
      "['Niger', Decimal('4184')]\n",
      "['Zaire', Decimal('4374')]\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT name, length FROM river WHERE length > 4000\")\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calling the `.fetchone()` method, or iterating over the cursor object, yields a series of lists, with one element in the tuple per field requested in the query."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interpolating values in a query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's say that we're starting with data from some other source, say, a list of cities whose population we're interested in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cities_of_interest = ['Paris', 'Nairobi', 'Buenos Aires', 'Kyoto']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we want to perform queries on the MONDIAL database to get the population for each of these cities. Somehow, we need to *build* a series of SQL queries in Python that include the names in the list.\n",
    "\n",
    "You might think that you could simply do something like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT population FROM city WHERE name = 'Paris'\n"
     ]
    }
   ],
   "source": [
    "query = \"SELECT population FROM city WHERE name = '\" + cities_of_interest[0] + \"'\";\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks good, until you have a name with problematic punctuation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT population FROM city WHERE name = 'Martha's Vineyard'\n"
     ]
    }
   ],
   "source": [
    "problematic_city = \"Martha's Vineyard\"\n",
    "query = \"SELECT population FROM city WHERE name = '\" + problematic_city + \"'\"\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See the trouble? The apostrophe in the name of the city made its way into our query string. This query would be a syntax error in SQL, since SQL will believe the string to have ended at the apostrophe in `Martha's`. Troublesome!\n",
    "\n",
    "To solve this problem, the cursor object's `.execute()` method comes with a built-in means of interpolating values into queries. Simply put `%s` in your query string wherever you want to insert a value, and then pass as a second parameter to `.execute()` a list of values that you want to be included in the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT population FROM city WHERE name = %s\",\n",
    "              [\"Martha's Vineyard\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pg8000 will take care of the nasty business of quoting your string for you, and you'll be protected from [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection).\n",
    "\n",
    "Here's a complete example, iterating over a list of cities and getting the population for each one:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Paris 2249975\n",
      "Nairobi 3133518\n",
      "Buenos Aires 2768772\n",
      "Kyoto 1382113\n"
     ]
    }
   ],
   "source": [
    "for city_name in cities_of_interest:\n",
    "    cursor.execute(\"SELECT population FROM city WHERE name = %s\",\n",
    "                   [city_name])\n",
    "    population = cursor.fetchone()[0] # fetchone() returns a tuple w/1 val\n",
    "    print(city_name, population)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example: Percentages to population\n",
    "\n",
    "Let's do a quick example of using Python to perform calculations on results from SQL queries performed in Python. Here's what we'll do: given a particular country and a religion in that country, find the *number of adherents* of that religion in the country.\n",
    "\n",
    "> There's nothing in this example that you couldn't do with a sophisticated SQL query. But there's nothing wrong with solving a problem like this in Python! It might not end up being as fast or efficient as doing it all in SQL, but sometimes it's helpful to work through these problems as a series of discrete steps rather than as a monolithic query.\n",
    "\n",
    "First, we'll set two variables for the country and religion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "country = \"United States\"\n",
    "religion = \"Muslim\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we'll select the relevant data from the `country` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \"SELECT code, population FROM country WHERE name = %s\"\n",
    "cursor.execute(query, [country])\n",
    "country_info = cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point, `country_info` will be a list of values corresponding to the columns in the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['USA', Decimal('318857056')]\n"
     ]
    }
   ],
   "source": [
    "print(country_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's break that out into separate variables, for clarity:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "country_code = country_info[0]\n",
    "country_pop = country_info[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is that weird `Decimal` thing anyway? Well, it's a special type of Python object that can represent numerical data with the same precision as the SQL `numeric` type. It looks weird when you `print` it, but it actually behaves just like a regular number in other contexts:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "637714112\n"
     ]
    }
   ],
   "source": [
    "print(country_info[2] * 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the data for the country, let's find all of the religion records for that country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = \"SELECT percentage FROM religion WHERE country = %s AND name = %s\"\n",
    "cursor.execute(query, [country_code, religion])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query is a bit different from the others in that we've used *two* placeholders in the query instead of just one. This means that we also need to include two expressions inside the square brackets in the `.execute()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "religion_info = cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the variable `religion_info` contains a list with a single element: the percentage of adherents of that religion in the given country. Let's store that in a separate variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.006\n"
     ]
    }
   ],
   "source": [
    "percentage = religion_info[0] / 100\n",
    "print(percentage)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we'll get the number of adherents by multiplying the population of the country by the religion percentage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1913142.336\n"
     ]
    }
   ],
   "source": [
    "print(percentage * country_pop)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's nearly two million Muslims living in the USA! Let's put the whole thing into a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def religion_pop(country, religion):\n",
    "    query = \"SELECT code, population FROM country WHERE name = %s\"\n",
    "    cursor.execute(query, [country])\n",
    "    country_info = cursor.fetchone()\n",
    "    country_code = country_info[0]\n",
    "    country_pop = country_info[1]\n",
    "    query = \"SELECT percentage FROM religion WHERE country = %s AND name = %s\"\n",
    "    cursor.execute(query, [country_code, religion])\n",
    "    religion_info = cursor.fetchone()\n",
    "    percentage = religion_info[0] / 100\n",
    "    return percentage * country_pop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function takes a country and a religion as parameters, and returns the number of adherents of the religion in that country:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14977437.504\n",
      "641056.54\n",
      "56606.890\n"
     ]
    }
   ],
   "source": [
    "print(religion_pop(\"Canada\", \"Roman Catholic\"))\n",
    "print(religion_pop(\"United Kingdom\", \"Hindu\"))\n",
    "print(religion_pop(\"Lithuania\", \"Protestant\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Coping with errors\n",
    "\n",
    "pg8000 is very persnickety about errors. If you have a syntax error, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "('ERROR', '42601', 'syntax error at or near \"SMELLECT\"', '1', 'scan.l', '1053', 'scanner_yyerror', '', '')",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, cursor, operation, vals)\u001b[0m\n\u001b[1;32m   1896\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1897\u001b[0;31m             \u001b[0mps\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcache\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ps'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1898\u001b[0m             \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mps\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mps\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: ('SMELLECT * FORM cheese WERE stink > 15 ODOR DESC', ())",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-17-4f6c94103f37>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"SMELLECT * FORM cheese WERE stink > 15 ODOR DESC\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, args, stream)\u001b[0m\n\u001b[1;32m    905\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0min_transaction\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mautocommit\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    906\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"begin transaction\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 907\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0moperation\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    908\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    909\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, cursor, operation, vals)\u001b[0m\n\u001b[1;32m   1944\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1945\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1946\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhandle_messages\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1947\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1948\u001b[0m             \u001b[0;31m# We've got row_desc that allows us to identify what we're\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mhandle_messages\u001b[0;34m(self, cursor)\u001b[0m\n\u001b[1;32m   2092\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2093\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merror\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2094\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merror\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2095\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2096\u001b[0m     \u001b[0;31m# Byte1('C') - Identifies the message as a close command.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: ('ERROR', '42601', 'syntax error at or near \"SMELLECT\"', '1', 'scan.l', '1053', 'scanner_yyerror', '', '')"
     ]
    }
   ],
   "source": [
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SMELLECT * FORM cheese WERE stink > 15 ODOR DESC\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... you'll get a syntax error as expected. But then subsequent attempts to use the cursor will frustratingly fail:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "('ERROR', '25P02', 'current transaction is aborted, commands ignored until end of transaction block', 'postgres.c', '1283', 'exec_parse_message', '', '')",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, cursor, operation, vals)\u001b[0m\n\u001b[1;32m   1896\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1897\u001b[0;31m             \u001b[0mps\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcache\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ps'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1898\u001b[0m             \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mps\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mps\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: (\"SELECT population FROM city WHERE name = 'Paris'\", ())",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-18-83a95cc0b980>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"SELECT population FROM city WHERE name = 'Paris'\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, args, stream)\u001b[0m\n\u001b[1;32m    905\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0min_transaction\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mautocommit\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    906\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"begin transaction\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 907\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0moperation\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    908\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    909\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_c\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, cursor, operation, vals)\u001b[0m\n\u001b[1;32m   1944\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1945\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1946\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhandle_messages\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1947\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1948\u001b[0m             \u001b[0;31m# We've got row_desc that allows us to identify what we're\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/allison/Dropbox/projects/lede/databases/env/lib/python3.5/site-packages/pg8000/core.py\u001b[0m in \u001b[0;36mhandle_messages\u001b[0;34m(self, cursor)\u001b[0m\n\u001b[1;32m   2092\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2093\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merror\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2094\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merror\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2095\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2096\u001b[0m     \u001b[0;31m# Byte1('C') - Identifies the message as a close command.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: ('ERROR', '25P02', 'current transaction is aborted, commands ignored until end of transaction block', 'postgres.c', '1283', 'exec_parse_message', '', '')"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT population FROM city WHERE name = 'Paris'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The way to fix this problem is to close the connection and re-open it, or simply call the connection object's `rollback` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now your queries can proceed as planned:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Decimal('2249975')]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT population FROM city WHERE name = 'Paris'\")\n",
    "cursor.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More information\n",
    "\n",
    "For more information, consult [pg8000's documentation](http://pythonhosted.org/pg8000/)."
   ]
  }
 ],
 "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
}