python/python_exercises_with_answers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises: Connecting to PostgreSQL with Python\n",
    "\n",
    "There may be other ways to solve these exercises.  They are aimed at getting you to think about the interplay between Python and a SQL database -- not reviewing the basic syntax of calling a database from Python.  So they'll require some thought and possibly a little more research/reading.  Try them with a partner or group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise: Create and Populate Tables\n",
    "\n",
    "Connect to a database where you have permission to create tables.\n",
    "\n",
    "Create three tables with appropriate columns:\n",
    "\n",
    "* `person`: at least an ID and name, maybe other characteristics of a person\n",
    "* `relationship`: links people together and labels it with a relationship type\n",
    "* `relationship_type`: a table defining the allowed set of relationship types in the `relationship` table\n",
    "\n",
    "Populate the tables with information about your friends and/or family.  Hint: think about how you want to handle IDs for people so that you can use them in the relationship table.  Hint 2: think about how to make it clear in the relationship table what the direction of the relationship is (e.g. who is the child and who is the parent?).\n",
    "\n",
    "Print out sentences describing the family relationships."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Solution\n",
    "\n",
    "There are obviously multiple ways to do this.  Here is one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(dbname=\"\", host=\"\", user=\"\", password=\"\") # fill in details\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create tables\n",
    "cur.execute(\"create table person (id serial primary key, name text not null);\") # have to create before relationship\n",
    "cur.execute(\"\"\"create table relationship_type (\n",
    "               type text primary key);\"\"\") ## have to create before relationship below\n",
    "cur.execute(\"\"\"create table relationship (\n",
    "            id serial primary key, \n",
    "            subject int references person(id),\n",
    "            predicate int references person(id),\n",
    "            relationship text references relationship_type(type));\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Populate tables.  One option is to create a dict to store the auto generated IDs for the people to use later.  This isn't very efficient, but works fine for moderately sized databases where you want to keep all of this information in memory in Python anyway.  You could also define ids yourself, but this can get tricky to keep track of across multiple sessions of working with a database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "family = {x:None for x in ['Christina','Casey','Henry','Jessica','Denise','Bob']}\n",
    "for person in family:\n",
    "    # insert\n",
    "    cur.execute(\"insert into person (name) values (%s);\", [person])\n",
    "    # retrieve ID\n",
    "    cur.execute(\"select id from person where name=%s;\", [person])\n",
    "    family[person] = cur.fetchone()[0]\n",
    "    \n",
    "# commit\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define relationship types.  There's no reason you have to use an underscore in the relationship type strings -- you could use a space.  The use of the underscore comes from experience with categorical variables in data analysis in other contexts.  \n",
    "\n",
    "You have to populate the `relationship_type` table before `relationship`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for rtype in ['spouse_of','parent_of','sibling_of','child_of']:\n",
    "    cur.execute(\"insert into relationship_type values (%s);\", [rtype])\n",
    "\n",
    "# commit\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# not complete set of relationships, but we can do some both ways\n",
    "relations = [(family['Christina'], family['Casey'], 'spouse_of'),\n",
    "    (family['Christina'], family['Henry'], 'parent_of'),\n",
    "    (family['Casey'], family['Henry'], 'parent_of'),\n",
    "    (family['Henry'], family['Christina'], 'child_of'),\n",
    "    (family['Henry'], family['Casey'], 'child_of'),\n",
    "    (family['Christina'], family['Jessica'], 'sibling_of'),\n",
    "    (family['Christina'], family['Denise'], 'child_of'),\n",
    "    (family['Christina'], family['Bob'], 'child_of'),\n",
    "    (family['Jessica'], family['Denise'], 'child_of'),\n",
    "    (family['Jessica'], family['Bob'], 'child_of')]\n",
    "for relation in relations:\n",
    "    cur.execute(\"\"\"insert into relationship (subject, predicate, relationship) \n",
    "                values (%s, %s, %s);\"\"\", relation)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You could also use [`executemany()`](http://initd.org/psycopg/docs/cursor.html#cursor.executemany) above, but it isn't faster than a loop.\n",
    "\n",
    "Note that instead of manually entering each relationship both ways, you could set up [triggers](https://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) in the database to do this.  This would take some work to set up (you'd need to define the opposite of each relationship type), but it's possible.\n",
    "\n",
    "Look at results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"select a.name, b.name, relationship from person a, person b, relationship \n",
    "                where a.id=subject and b.id=predicate;\"\"\")\n",
    "for row in cur.fetchall():\n",
    "    print(\"{} is the {} {}.\".format(row[0], row[2].replace(\"_\", \" \"), row[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise: Selecting Random Data\n",
    "\n",
    "One thing that isn't easy to do with SQL is selecting random rows.  There are functions to generate random values, but generating a new random column on a large table, and then sorting by that column (or computing the max value and then selecting an observation) is costly.  This is one scenario when working with a database from Python is useful.\n",
    "\n",
    "Use the code below to create a table in the database.  Then figure out how to select 3 random rows from that table (as if you didn't have access to the code or values that created the table).  Do this without reading the entire table into Python.  Hint: you'll probably want to use some combination of sorting the table, limiting the number of rows you retrieve, and offsetting results (for a refresher, learn more [here](http://www.postgresqltutorial.com/postgresql-limit/) or [here](https://www.tutorialspoint.com/postgresql/postgresql_limit_clause.htm))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import string\n",
    "import random\n",
    "\n",
    "ids = random.sample(list(range(1000)), 100)\n",
    "\n",
    "conn = psycopg2.connect(dbname=\"\", host=\"\", user=\"\", password=\"\") ## connect to a database where you can write\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"\"\"create table patient (\n",
    "                id int primary key,\n",
    "                name text not null);\"\"\")\n",
    "for i in ids:\n",
    "    cur.execute(\"insert into patient values (%s, %s)\", (i, ''.join(random.sample(string.ascii_letters, 5))))\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(dbname=\"\", host=\"\", user=\"\", password=\"\") ## connect to a database where you can write\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"select * from patient;\")\n",
    "for row in cur.fetchmany(5):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First get the number of rows in the table.  Then select 3 random values between 0 and the number of rows - 1. Then for each, execute a query to get that row from the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur.execute(\"select count(*) from patient;\")\n",
    "count = cur.fetchone()[0]\n",
    "selection = random.sample(list(range(count)), 3)  ## sample between 0 and count to get the row offset\n",
    "for val in selection:\n",
    "    cur.execute(\"\"\"select * from patient \n",
    "                    order by id  -- important so that we get rows in the same order each query\n",
    "                    limit 1 -- we just need one row\n",
    "                    offset %s;\"\"\", [val]) # use the offset to determine which row\n",
    "    print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An alternative approach, which could work well if the table isn't too big, is to retrieve all of the IDs, and then randomly sample the IDs, and retrieve just those rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur.execute(\"select id from patient;\")\n",
    "ids = [x[0] for x in cur.fetchall()]\n",
    "selection = random.sample(ids, 3)\n",
    "for val in selection:\n",
    "    cur.execute(\"\"\"select * from patient \n",
    "                    where id = %s;\"\"\", [val]) # use the offset to determine which row\n",
    "    print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (conda p3)",
   "language": "python",
   "name": "p3"
  },
  "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}