Lectures/Week-11-SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Spring 2018 Status: READY!\n",
    "# What is SQL?\n",
    "\n",
    "SQL, roughly, is a database language for creating queries and processing data in relational database systems. So very very roughly. In truth, it's actually 3 languages and in addition to those languages, there is usually an additional, proprietary aspect of it. This means that there are a lot of different _kinds_ of SQL. \n",
    "\n",
    "SQL began in 1970 with a paper called, \"A Relational Model of Data for Large Shared Data Banks\" from Edgar Codd. Codd's model united many of the different database systems that were around at the time. So, while there are tons of different kinds of SQL, they all share a common core. Strangely enough, other work Codd did even before SQL has recently begun to be used. Self-replicating computers - based on his dissertation - started to be used in 2010. \n",
    "\n",
    "You'll be using the Software Carpentry SQL lessons (http://swcarpentry.github.io/sql-novice-survey/) as the majority of your reference for this week.  The lecture notes here will be focused on how to process data with the SQLite3 program.\n",
    "\n",
    "# Why are we learning this?\n",
    "\n",
    "SQL is an incredibly common tool used across many platforms.  We're going to get a little taste of SQL with this week's lesson. This will be _just enough_ SQL to get started. \n",
    "\n",
    "SQL statements are very opqaue at first sight, but you can quickly understand them with a little tinkering. The goal of this lesson will be to experiment with SQL a bit and learn how to export processed data out of SQLite.\n",
    "\n",
    "We will not directly use SQL inside of Python but many of the ways you'll use it here can be replicated inside of Python."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL versus SQLite3\n",
    "\n",
    "SQL is just the general name of these languages.  There are many flavors of SQL, one of which is SQLite.  You may have heard of MySQL or Oracle SQL.  We'll be learning SQLite3.\n",
    "\n",
    "Meanwhile, we'll be using an application called SQLite3.  So you'll be using a mix of commands for SQLite3 and then writing SQL statements.\n",
    "\n",
    "* SQLite3 commands start with a period, e.g.\n",
    "    * `.headers on`\n",
    "    * `.mode column`\n",
    "    * `.help`\n",
    "* SQL statements do not start with a period and end with a semi-colon, e.g.\n",
    "    * `SELECT * FROM site;`\n",
    "    * `SELECT personal from person;`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading in data\n",
    "\n",
    "There are many ways to get data into the SQLite program, and you almost always do this with SQLite3 program commands. Once you have the data in, you've got database tables that can have SQL statements applied to them.  Remember that relational data is always in table format.  Refer to the lessons about this: http://swcarpentry.github.io/sql-novice-survey/01-select/\n",
    "\n",
    "For the purpose of this lesson, we're going to start with a pre-created data file:  survey.db.  You can download this file directly off the lessons page (http://swcarpentry.github.io/sql-novice-survey/).  This .db file has several tables loaded.  Since this is sort of compiled database file, we can just use .open to open it up.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".open survey.db\r\n"
     ]
    }
   ],
   "source": [
    ".open survey.db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we have our first two SQLite commands.  We know this because they start with periods.  `.open` will load a .db file into the system, which will go ahead and create all the tables of data that we need.  We can check that this worked via the `.tables` command, which will print out the names of each of the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".tables\r\n",
      "Person   Site     Survey   Visited\r\n"
     ]
    }
   ],
   "source": [
    ".tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can check that the tables all loaded correctly and what columns they have via `.schema`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".schema\r\n",
      "CREATE TABLE Person (id text, personal text, family text);\r\n",
      "CREATE TABLE Site (name text, lat real, long real);\r\n",
      "CREATE TABLE Visited (id text, site text, dated text);\r\n",
      "CREATE TABLE Survey (taken integer, person text, quant text, reading real);\r\n"
     ]
    }
   ],
   "source": [
    ".schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.schema` will print out the exact SQL statements to create empty tables of the same structure, so there's a lot of embedded metadata.  Take a look at the text inside the ().  `(id text, personal text, family text)` This is reporting first the name of the column and the data type for that column.  In this case, each of the columns is loaded is as regular text.\n",
    "\n",
    "Now we can start using some SQL statemenst on there to get some data out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select * from Person;\r\n",
      "dyer|William|Dyer\r\n",
      "pb|Frank|Pabodie\r\n",
      "lake|Anderson|Lake\r\n",
      "roe|Valentina|Roerich\r\n",
      "danforth|Frank|Danforth\r\n"
     ]
    }
   ],
   "source": [
    "select * from Person;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK, there's some data, but it's super ugly.  SQLite has some methods of making it more readable.  There are two commands that we want to use.\n",
    "\n",
    "* `.headers on` will show the column names\n",
    "* `.mode column` will change the column display to a pretty tab based thing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".headers on\r\n",
      ".mode column\r\n"
     ]
    }
   ],
   "source": [
    ".headers on\n",
    ".mode column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select * from Person;\r\n",
      "id          personal    family    \r\n",
      "----------  ----------  ----------\r\n",
      "dyer        William     Dyer      \r\n",
      "pb          Frank       Pabodie   \r\n",
      "lake        Anderson    Lake      \r\n",
      "roe         Valentina   Roerich   \r\n",
      "danforth    Frank       Danforth  \r\n"
     ]
    }
   ],
   "source": [
    "select * from Person;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's so much better!\n",
    "\n",
    "As you'll learn with the lesson, the * means that you want to select all the columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's talk about the assignment\n",
    "\n",
    "2 hour students will be crafting two statements and 4 hour students will be crafting 4 statements.  The assignmnet has some guidelines about what should be included in each SQL statement.  You'll have a deliverable with a narrative and the data files.  Export the select table results out to a CSV (with headers on, and in CSV mode) and submit the data file.  In your narrative, include a human explanation of what the table contains.\n",
    "\n",
    "* All queries containing calculations or functions within the select statements will need to rename the column to something appropriate\n",
    "* All queries should have at least one join\n",
    "* All queries should be selecting specific columns for a specific reason, so none of your queries should be \"select * from...\" style\n",
    "\n",
    "Using the rules above, pick either 2 or 4 (depending on your credit hour level) of the following:\n",
    "\n",
    "* where you are modifying a numerical value\n",
    "* where you are concatenating text data\n",
    "* where you are joining three tables\n",
    "* where you are aggregating data using group by with multiple groups.  So not counting all rows.\n",
    "\n",
    "You can have some overlap in each of these tasks if you'd like, but you've got to hit one of these elements in each."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting data\n",
    "\n",
    "Exporting the results of your select statements to files is not covered in the Software Carpentry lessons.  There are ways to use sqlite3 inside of Python, but we're going to use the tools built right into the SQLite3 program.  WARNING!  You must follow these directions in order and correctly. If things start looking funny, then you missed a step.\n",
    "\n",
    "The SQLite program doesn't exactly have an 'export to a file' type of option.  Instead, it changes where it writes all the output to.  By default, it writes all output to this thing called the \"standard out\".  Which, effectively, is your comptuer screen.  Let's explore this concept in Python for a moment. \n",
    "\n",
    "The vanilla version of the `print()` command in Python writes out whatever it is to your computer screen or console.  This information is ephemeral and goes away as soon as you rerun the program or shut down PyCharm.  This is writing out to the \"standard out\".  Meanwhile, we can do `print(something, file = out_file)` to divert the output text to a file instead.  Nothing will be printed out for us on our screen.  \n",
    "\n",
    "This `print(file=...)` versus `print()` utility in Python gives us very fine grained control over what is printed out to our screen versus what is printed out to a file.  SQLite has the choice of printing out to our screen OR printing out to a file.  This is pretty much binary.  Once you tell SQLite to write out to a file, then EVERYTHING after that will be written out to that file.  This is why turning it on and off again is so important, and why I stress following the order of operations very carefully.\n",
    "\n",
    "## The order of things\n",
    "\n",
    "Prepreparation:  play with the database and determine the SQL select statement that you want to run.  Once you're happy with the statement that you have and you want to write it out to a file, follow the following steps:\n",
    "\n",
    "* Determine the select statement you want to run for the file output.\n",
    "* Copy/paste your desired select statement somewhere in a plain text file. Use a text file in PyCharm, Notepad, Notepad ++, Sublime Text, Textwranger, etc.  Doesn't matter.  Don't use Word because it'll change your quotes.  \n",
    "* Now go back to your sqlite session.\n",
    "* execute:  `.mode csv`\n",
    "* execute: `.headers on` (I recommend that you always have this on in general, so you might skip this step if you already have it on)\n",
    "* execute `.output filename.csv` change 'filename' to a file name of choice\n",
    "* execute your SQL statement.  You get this one chance to get it right, else you'll need to delete the file and start over.  You'll get a bunch of data stacking up in the file if you run multiple select statements with the output going to a file name.\n",
    "* Reset the output back to standard out by executing: `.output stdout`\n",
    "* Now you can start back to figuring out your desired select statement for the next one.\n",
    "\n",
    "You should be able to download this file off of your pythonanywhere.com account."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### For your submission\n",
    "\n",
    "Have a word or text document that has:\n",
    "\n",
    "1. The SQL statement\n",
    "2. Human words about that SQL statement\n",
    "3. Your narrative about it\n",
    "\n",
    "Rinse and repeat for each statement.\n",
    "\n",
    "Then also attach to your moodle submission each data file that corresponds to the SQL statement you wrote."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}