<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro to SQL - The Hacker Within (2018-02-21)\n",
    "This tutorial will show you the basics of SQL:\n",
    "* creating a table\n",
    "* inserting rows\n",
    "* querying with conditions\n",
    "* grouping and ordering\n",
    "* joining tables with a common field\n",
    "* aggregating rows using COUNT, MAX, etc.\n",
    "* alternates to SQL like pandas and Django\n",
    "* indexing frequently queried columns for performance\n",
    "* alter existing table to add, drop, or rename a column\n",
    "* drop a table\n",
    "* vacuum a database\n",
    "\n",
    "## SQLite\n",
    "We are going to use [Python](https://docs.python.org/3/library/sqlite3.html) because it comes with the popular [SQLite](https://sqlite.org/index.html) (_aka_ [`etilqs`](https://www.google.com/search?q=etilqs)) relational database builtin. \n",
    "\n",
    "## Requirements\n",
    "The following examples assume you have Python 3 installed on your computer. Any distribution will do. If you have [Anaconda](https://www.anaconda.com/distribution/) or [Miniconda](https://conda.io/miniconda.html), you can create a new conda environment with the requirements for this tutorial from a terminal. For example with miniconda on Mac OS X, you might use the following to activate the root conda environment and create a new one called \"py36-thw-sql\".\n",
    "\n",
    "    ~$ source ~/miniconda/bin/activate\n",
    "    (root) ~$ conda create -n py36-thw-sql python=3.6.3 psycopg2 django pandas\n",
    "\n",
    "Or on Windows with Anaconda, you might use the following in a CMD terminal.\n",
    "\n",
    "    ~> %LOCALAPPDATA%\\Continuum\\anaconda3\\Scripts\\activate\n",
    "    (root) ~> conda create -n py36-thw-sql python=3.6.3 psycopg2 django pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python DB-API 2.0\n",
    "[PEP 249](https://www.python.org/dev/peps/pep-0249/) specifies a `connection` to a database, and a `cursor` from the connection to execute SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "import io  # we'll need this way later\n",
    "import os\n",
    "import sqlite3  # this is the module that binds to SQLite\n",
    "import numpy as np  # never know when you might need NumPy, oh, right, always!\n",
    "import pandas as pd  # you'll see why we can use this later\n",
    "\n",
    "DBFILE = 'sqlite3.db'  # this will be our database\n",
    "BASEDIR = %pwd  # os.path.abspath(os.path.dirname(__file__))\n",
    "DBPATH = os.path.join(BASEDIR, DBFILE)\n",
    "\n",
    "# we may need to delete the existing file first\n",
    "if os.path.exists(DBPATH):\n",
    "    os.remove(DBPATH)\n",
    "\n",
    "# open a connection to the database for this tutorial\n",
    "conn = sqlite3.connect(DBPATH)\n",
    "\n",
    "# get a cursor to the database\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notes\n",
    "If a file with the same name doesn't already exist, then this creates a new database otherwise it connects to the existing database contained in the file. You can also use ':memory:' to create an \"in-memory\" database that has no file, but then you can't connect to that from another process.\n",
    "\n",
    "We'll have to close the connection and cursor later. Next time we could use a [`with` context to automatically close the connection](https://docs.python.org/3.6/library/sqlite3.html#using-the-connection-as-a-context-manager).\n",
    "\n",
    "    with sqlite3.connect('sqlite3.db') as conn:\n",
    "        cur = conn.execute('SQL QUERY ...')  # e.g.: 'SELECT * FROM table_name;'\n",
    "        output = cur.fetchall()  # get the results\n",
    "\n",
    "Closing the connection automatically closes the cursor. [Other bindings may offer similar context managers.](http://initd.org/psycopg/docs/usage.html#with-statement) to commit and close changes or rollback changes and raise an exception."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating tables\n",
    "A relational database or SQL database is a tabular structure consisting of rows of data with columns of fields. The [data definition language or DDL](https://en.wikipedia.org/wiki/Data_definition_language) used to create the table is the same language used to query it, called [SQL or Structured Query Language](https://en.wikipedia.org/wiki/SQL).\n",
    "\n",
    "Although [the basic SQL commands](https://sqlite.org/lang.html) are nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/sql.html), the data types may be different. [SQLite only has 5 datatypes](https://sqlite.org/datatype3.html): `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`. For [boolean, use integer zero for false, and one for true](https://sqlite.org/datatype3.html#boolean_datatype). For [dates and times use text and ISO8601](https://sqlite.org/datatype3.html#date_and_time_datatype), _e.g._: `\"2018-02-21T17:05-0800\"`. By comparison, [PostgreSQL has too many to list here](https://www.postgresql.org/docs/10/static/datatype.html) including booleans, date, time, arrays, JSON, etc.\n",
    "\n",
    "## `CREATE`\n",
    "The [basic SQL command to create a table](https://sqlite.org/lang_createtable.html) is\n",
    "\n",
    "    CREATE TABLE <table_name> (<field_name> <TYPE> <CONSTRAINTS>, <field_name> <TYPE> <CONSTRAINTS>, <CONSTRAINTS>, ...);\n",
    "\n",
    "Some languages enforce the semicolon, some don't. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-basics.html).\n",
    "\n",
    "### Constraints, Defaults, and Options\n",
    "Constraints are optional and set conditions, limitations, or options for columns and the table. The most common constraints are: `PRIMARY KEY`, `UNIQUE`, `NOT NULL`, `DEFAULT`, `FOREIGN KEY`, `REFERENCES`, etc. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-constraints.html).\n",
    "\n",
    "#### `PRIMARY KEY`\n",
    "The most important of these is `PRIMARY KEY` which is equivalent to `UNIQUE NOT NULL`. A [primary key](https://www.postgresql.org/docs/10/static/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS) is a unique references that identifies each record in the table. Although it is not required, every table should have a primary key. Only one primary key is allowed, and it can be constructed from multiple columns, `PRIMARY KEY (<field_A>, <field_B)`, to create a unique together, non-null identifier. [In SQLite, if missing then a integer primary key named, `rowid`, is created by default](https://sqlite.org/lang_createtable.html#rowid). Also in SQLite, any integer primary key is automatically incremented, so [the _AUTOINCREMENT_ command is usually **not** needed](https://sqlite.org/autoinc.html). [In PostgreSQL the `SERIAL` command is used](https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL) to create a corresponding [sequence](https://www.postgresql.org/docs/10/static/functions-sequence.html) for the primary key.\n",
    "\n",
    "## Practice\n",
    "The other constraints and options are also important, but we'll discover those as we learn. Let's create some simple databases with fictitious data to practice. Imagine you are testing several different materials with different properties $\\alpha$ and $\\beta$ under different stresses like different temperatures and  light intensity and changing thickness. How would you organize this data? Take a moment to design a schema or structure for your data. The schema consists of the column names and data types and the column and table constraints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use Python triple quoted strings to span multiple lines, but use single quotes, since SQL only uses double quotes\n",
    "\n",
    "# first create a materials table\n",
    "cur.execute('''CREATE TABLE materials (\n",
    "    material_id TEXT PRIMARY KEY,\n",
    "    long_name TEXT UNIQUE NOT NULL,\n",
    "    alpha REAL NOT NULL,\n",
    "    beta REAL NOT NULL,\n",
    "    material_type TEXT NOT NULL\n",
    ")''')\n",
    "conn.commit()\n",
    "# if you don't commit the changes, they won't be written to the file, and won't be visible to other connections\n",
    "\n",
    "# then create an experiments table\n",
    "cur.execute('''CREATE TABLE experiments (\n",
    "    experiment_id INTEGER PRIMARY KEY,\n",
    "    temperature REAL DEFAULT 298.15,\n",
    "    irradiance REAL DEFAULT 1000.0,\n",
    "    uv_filter INTEGER DEFAULT 0,\n",
    "    material_id NOT NULL REFERENCES materials ON UPDATE CASCADE ON DELETE CASCADE,\n",
    "    thickness REAL DEFAULT 0.005,\n",
    "    UNIQUE (temperature, irradiance, uv_filter, material_id)\n",
    ")''')\n",
    "conn.commit()\n",
    "\n",
    "# and finally create a trials table\n",
    "cur.execute('''CREATE TABLE trials (\n",
    "    trial_id INTEGER PRIMARY KEY,\n",
    "    experiment_id NOT NULL REFERENCES experiments ON UPDATE CASCADE ON DELETE CASCADE,\n",
    "    results BLOB NOT NULL,\n",
    "    duration REAL NOT NULL,\n",
    "    avg_temperature REAL NOT NULL,\n",
    "    std_temperature REAL NOT NULL,\n",
    "    avg_irradiance REAL NOT NULL,\n",
    "    std_irradiance REAL NOT NULL,\n",
    "    init_visible_transmittance REAL NOT NULL,\n",
    "    final_visible_transmittance REAL NOT NULL,\n",
    "    notes TEXT\n",
    ")''')\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `FOREIGN KEY`\n",
    "A [foreign key constraint](https://sqlite.org/foreignkeys.html) creates a relationship between two tables. The `FOREIGN KEY` is implied when the `REFERENCES` column constraint is applied. In the experiments table above, the column constraint on `material_id` is the same as adding this table constriant:\n",
    "\n",
    "    FOREIGN KEY (material_id) REFERENCES materials (material_id)\n",
    "\n",
    "Specifying the referenced column in the table constraint isn't necessary, and if omitted defaults to the primary key of the referenced table. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-constraints.html#DDL-CONSTRAINTS-FK).\n",
    "\n",
    "You can use the same name for the foreign key and it's related field, but it may make joining tables more difficult because you will need to use the table name to avoid an ambigous column name. _E.G._: you can use `trials.experiment_id` and `experiments.experiment_id` to differentiate between them. You can also use `AS` to create a temporary name like `trials.experiment_id AS experiment`. Or you could just use different names for the foreign key and it's related field like `FOREIGN KEY (material) REFERENCES materials (material_id)` and then there's no ambiguity. Your call.\n",
    "\n",
    "### `DELETE` and `UPDATE`\n",
    "What happens if the reference of a foreign key is deleted or updated? That's up to you: in SQLite the default is to do nothing, but typically you want the action to cascade. Add the desired [`ON DELETE` or `ON UPDATE` action](https://sqlite.org/foreignkeys.html#fk_actions) to the constraint.\n",
    "\n",
    "\n",
    "### Bonus Questions\n",
    "1. What is the difference between a column constraint and a table constraint?\n",
    "2. What [other table constraint](https://www.postgresql.org/docs/9.1/static/ddl-constraints.html#AEN2496) is in the experiments table?\n",
    "3. What other constraints or defaults are applied in the tables?\n",
    "4. What part of the materials table schema is fragile and can be improved?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `INSERT`\n",
    "The [basic SQL command to put data into a table is](https://sqlite.org/lang_insert.html)\n",
    "\n",
    "    INSERT INTO <table_name> (<field_name>, <field_name>, ...) VALUES (<value>, <value>, ...)\n",
    "\n",
    "[Other relational databases use the same SQL syntax.](https://www.postgresql.org/docs/current/static/dml-insert.html)\n",
    "\n",
    "Let's add some pretend data to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a EVA as a material\n",
    "cur.execute('INSERT INTO materials VALUES (\"EVA\", \"ethylene vinyl acetate\", 0.123, 4.56, \"polymer\")')\n",
    "conn.commit()  # you must commit for it to become permanent\n",
    "cur.rowcount  # tells you how many rows written, sometimes, it's quirky"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Placeholders\n",
    "You can use placeholders to loop over insert statements to add multiple records.\n",
    "\n",
    ">*WARNING*: Never use string formatters to in lieu of placeholders or you may be subject to [a SQL injection attack](https://xkcd.com/327/).\n",
    "\n",
    "SQLite uses `?` but [other relational databases may use `%s`](http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries) or another placeholder.\n",
    "\n",
    "Also, in `sqlite3` [`executemany`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany) is a convenient shortcut, [but it may not be convenient for all database bindings](http://initd.org/psycopg/docs/extras.html#fast-exec)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rowcount = 2\n"
     ]
    }
   ],
   "source": [
    "# add some more fake materials\n",
    "fake_materials = [\n",
    "    ('PMMC', 'poly methyl methacrylate', 0.789, 10.11, 'polymer'),\n",
    "    ('KBr', 'potassium bromide', 1.213, 14.15, 'crystal')\n",
    "]\n",
    "for mat in fake_materials:\n",
    "    # must have same number of place holders as values\n",
    "    cur.execute('INSERT INTO materials VALUES (?, ?, ?, ?, ?)', mat)  # use place holders\n",
    "conn.commit()  # you can commit all of the changes at the end of the loop\n",
    "\n",
    "# use the executemany shortcut\n",
    "fake_materials = [\n",
    "    ('SiO2', 'silicon dioxide', 1.617, 18.19, 'crystal'),\n",
    "    ('CaF2', 'calcium flouride', 2.0, 21.22, 'crystal')\n",
    "]\n",
    "cur.executemany('INSERT INTO materials VALUES (?, ?, ?, ?, ?)', fake_materials)\n",
    "conn.commit()\n",
    "print('rowcount = %d' % cur.rowcount)  # with executemany, cur.rowcount shows total number of rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `DELETE` and `UPDATE`\n",
    "Oops I made a mistake. How do I fix it? The opposite of `INSERT` is [`DELETE`](https://sqlite.org/lang_delete.html). But don't throw the baby out with the bathwater, you can also [`UPDATE`] a record. [Other relational databases use the same SQL syntax to manipulate data](https://www.postgresql.org/docs/10/static/dml.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('DELETE FROM materials WHERE material_id = \"SiO2\"')\n",
    "cur.execute('UPDATE materials SET alpha=1.23E-4, beta=8.910E+11 WHERE material_id = \"CaF2\"')\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Queries\n",
    "The way you [select](https://sqlite.org/lang_select.html) data is by executing queries. [The language is the same for all relational databases.](https://www.postgresql.org/docs/10/static/queries.html) The star `*` means select all columns, or you can give the columns explicitly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('EVA', 'ethylene vinyl acetate', 0.123, 4.56, 'polymer'),\n",
       " ('PMMC', 'poly methyl methacrylate', 0.789, 10.11, 'polymer'),\n",
       " ('KBr', 'potassium bromide', 1.213, 14.15, 'crystal'),\n",
       " ('CaF2', 'calcium flouride', 0.000123, 891000000000.0, 'crystal')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM materials')\n",
    "cur.fetchall()  # fetch all the results of the query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conditions\n",
    "You can limit a query using [`WHERE`](https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-FROM) and [`LIMIT`](https://www.postgresql.org/docs/10/static/queries-limit.html). You can combine `WHERE` with a conditional expression, `IN` to check a set, or `LIKE` to compare with strings. Use `AND` and `OR` to combine conditions.\n",
    "\n",
    "### Python DB-API Cursor Methods\n",
    "The Python DB-API cursor can be used as an iterator or you can call it's [fetch methods](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.fetchone)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EVA is ethylene vinyl acetate\n",
      "PMMC is poly methyl methacrylate\n"
     ]
    }
   ],
   "source": [
    "# limit the query using WHERE and LIMIT\n",
    "cur.execute('SELECT material_id, long_name FROM materials WHERE alpha < 1 LIMIT 2')\n",
    "for c in cur: print('{} is {}'.format(*c))  # user the cursor as an iterator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('EVA', None), ('PMMC', None)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "materials_list = (\"EVA\", \"PMMC\")\n",
    "cur.execute('SELECT alpha, beta FROM materials WHERE material_type')\n",
    "[(mat, cur.fetchone()) for mat in materials_list]  # use the cursor fetchone() method to get next item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregates\n",
    "Your query can aggregate results like `AVG`, `SUM`, `COUNT`, `MAX`, `MIN`, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4,)\n"
     ]
    }
   ],
   "source": [
    "cur.execute('SELECT COUNT(*) FROM materials')\n",
    "print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `GROUP BY`\n",
    "You can group queries by a column or a condition such as an expression, `IN`, or `LIKE`, if your selection is an aggregate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('crystal', 2, 0.6065615000000001, 891000000000.0),\n",
       " ('polymer', 2, 0.456, 10.11)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT material_type, COUNT(*), AVG(alpha), MAX(beta) FROM materials GROUP BY material_type')\n",
    "cur.fetchmany(2)  # use fetchmany() with size parameter, just for fun"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Practice\n",
    "Add a fictitious experiment schedule and doctor up some data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use defaults, let primary key auto-increment, just supply material ID\n",
    "cur.execute('INSERT INTO experiments (material_id) VALUES (\"EVA\")')  # use defaults, \n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sqlite3.IntegrityError: %s NOT NULL constraint failed: experiments.material_id\n"
     ]
    }
   ],
   "source": [
    "# set up a test matrix for EVA\n",
    "temp = range(300, 400, 25)\n",
    "irrad = range(400, 800, 100)\n",
    "try:\n",
    "    for T in temp:\n",
    "        for E in irrad:\n",
    "            cur.execute('INSERT INTO experiments (temperature, irradiance) VALUES (?, ?)', (T, E))\n",
    "except sqlite3.IntegrityError as exc:\n",
    "    print('sqlite3.IntegrityError: %s', exc)\n",
    "\n",
    "# Oops! We forgot to specify the material, there is not default, and it is constrained as NOT NULL!\n",
    "conn.rollback()  # undo any changes\n",
    "try:\n",
    "    for T in temp:\n",
    "        for E in irrad:\n",
    "            cur.execute('INSERT INTO experiments (temperature, irradiance, material_id) VALUES (?, ?, \"EVA\")', (T, E))\n",
    "except sqlite3.IntegrityError as exc:\n",
    "    print(exc)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 298.15, 1000.0, 0, 'EVA', 0.005),\n",
       " (2, 300.0, 400.0, 0, 'EVA', 0.005),\n",
       " (3, 300.0, 500.0, 0, 'EVA', 0.005),\n",
       " (4, 300.0, 600.0, 0, 'EVA', 0.005),\n",
       " (5, 300.0, 700.0, 0, 'EVA', 0.005),\n",
       " (6, 325.0, 400.0, 0, 'EVA', 0.005),\n",
       " (7, 325.0, 500.0, 0, 'EVA', 0.005),\n",
       " (8, 325.0, 600.0, 0, 'EVA', 0.005),\n",
       " (9, 325.0, 700.0, 0, 'EVA', 0.005),\n",
       " (10, 350.0, 400.0, 0, 'EVA', 0.005),\n",
       " (11, 350.0, 500.0, 0, 'EVA', 0.005),\n",
       " (12, 350.0, 600.0, 0, 'EVA', 0.005),\n",
       " (13, 350.0, 700.0, 0, 'EVA', 0.005),\n",
       " (14, 375.0, 400.0, 0, 'EVA', 0.005),\n",
       " (15, 375.0, 500.0, 0, 'EVA', 0.005),\n",
       " (16, 375.0, 600.0, 0, 'EVA', 0.005),\n",
       " (17, 375.0, 700.0, 0, 'EVA', 0.005)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this list is hard to read\n",
    "list(cur.execute('SELECT * FROM experiments'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temperature</th>\n",
       "      <th>irradiance</th>\n",
       "      <th>uv_filter</th>\n",
       "      <th>material_id</th>\n",
       "      <th>thickness</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>experiment_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>298.15</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>300.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>300.00</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>300.00</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>325.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>325.00</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>325.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>325.00</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>350.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>350.00</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>350.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>350.00</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>375.00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>375.00</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>375.00</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>375.00</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               temperature  irradiance  uv_filter material_id  thickness\n",
       "experiment_id                                                           \n",
       "1                   298.15      1000.0          0         EVA      0.005\n",
       "2                   300.00       400.0          0         EVA      0.005\n",
       "3                   300.00       500.0          0         EVA      0.005\n",
       "4                   300.00       600.0          0         EVA      0.005\n",
       "5                   300.00       700.0          0         EVA      0.005\n",
       "6                   325.00       400.0          0         EVA      0.005\n",
       "7                   325.00       500.0          0         EVA      0.005\n",
       "8                   325.00       600.0          0         EVA      0.005\n",
       "9                   325.00       700.0          0         EVA      0.005\n",
       "10                  350.00       400.0          0         EVA      0.005\n",
       "11                  350.00       500.0          0         EVA      0.005\n",
       "12                  350.00       600.0          0         EVA      0.005\n",
       "13                  350.00       700.0          0         EVA      0.005\n",
       "14                  375.00       400.0          0         EVA      0.005\n",
       "15                  375.00       500.0          0         EVA      0.005\n",
       "16                  375.00       600.0          0         EVA      0.005\n",
       "17                  375.00       700.0          0         EVA      0.005"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# not only is Pandas much nicer, it also executes queries!\n",
    "pd.read_sql('SELECT * FROM experiments', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `ORDER BY`\n",
    "Does what it says; order the query results by a column. Default is ascending, but use `ASC` or `DESC` to change the order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temperature</th>\n",
       "      <th>irradiance</th>\n",
       "      <th>uv_filter</th>\n",
       "      <th>material_id</th>\n",
       "      <th>thickness</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>experiment_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>300.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>325.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>350.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>375.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               temperature  irradiance  uv_filter material_id  thickness\n",
       "experiment_id                                                           \n",
       "5                    300.0       700.0          0         EVA      0.005\n",
       "9                    325.0       700.0          0         EVA      0.005\n",
       "13                   350.0       700.0          0         EVA      0.005\n",
       "17                   375.0       700.0          0         EVA      0.005"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Python's SQLite let's you use either '==' or '=', but I think SQL only allows '=', okay?\n",
    "pd.read_sql('SELECT * FROM experiments WHERE irradiance = 700 ORDER BY temperature', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>temperature</th>\n",
       "      <th>irradiance</th>\n",
       "      <th>uv_filter</th>\n",
       "      <th>material_id</th>\n",
       "      <th>thickness</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>experiment_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>375.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>375.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>375.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>375.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0</td>\n",
       "      <td>EVA</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               temperature  irradiance  uv_filter material_id  thickness\n",
       "experiment_id                                                           \n",
       "17                   375.0       700.0          0         EVA      0.005\n",
       "16                   375.0       600.0          0         EVA      0.005\n",
       "15                   375.0       500.0          0         EVA      0.005\n",
       "14                   375.0       400.0          0         EVA      0.005"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# descending order\n",
    "pd.read_sql('SELECT * FROM experiments WHERE temperature = 375 ORDER BY irradiance DESC', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Dr. Data](http://www.startrek.com/database_article/data)\n",
    "![Dr. Data](http://www.startrek.com/uploads/assets/db_articles/26da32597d9bd37fde9da22660aa524f24fd725c.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dr. Data\n",
    "start_time, end_time = '2018-02-21T17:00-0800', '2018-02-21T18:30-0800'\n",
    "timestamps = pd.DatetimeIndex(start=start_time, end=end_time, freq='T')\n",
    "# use http://poquitopicante.blogspot.com/2016/11/panda-pop.html to help you recall what offset alias to use\n",
    "size = len(timestamps)\n",
    "data = {\n",
    "    'temperature': np.random.randn(size) + 298.15,\n",
    "    'irradiance': np.random.randn(size) + 1000,\n",
    "    'visible_transmittance': np.logspace(np.log10(0.9), np.log10(0.8), size) + np.random.randn(size) / 100\n",
    "}\n",
    "results = pd.DataFrame(data, index=timestamps)\n",
    "duration = (results.index[-1] - results.index[0]).value  # [ns]\n",
    "avg_temperature = results.temperature.mean()  # [K]\n",
    "std_temperature = results.temperature.std()  # [K]\n",
    "avg_irradiance = results.irradiance.mean()  # [W/m^2]\n",
    "std_irradiance = results.irradiance.std()  # [W/m^2]\n",
    "init_visible_transmittance = results.visible_transmittance[start_time]\n",
    "final_visible_transmittance = results.visible_transmittance[end_time]\n",
    "values = (1, results.to_csv(), duration, avg_temperature, std_temperature,\n",
    "          avg_irradiance, std_irradiance, init_visible_transmittance, final_visible_transmittance,\n",
    "         'this is doctored data')\n",
    "cur.execute('''INSERT INTO trials (\n",
    "    experiment_id, results, duration, avg_temperature, std_temperature,\n",
    "    avg_irradiance, std_irradiance, init_visible_transmittance,\n",
    "    final_visible_transmittance, notes\n",
    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', values)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>irradiance</th>\n",
       "      <th>temperature</th>\n",
       "      <th>visible_transmittance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:00:00-08:00</th>\n",
       "      <td>1000.081842</td>\n",
       "      <td>297.612852</td>\n",
       "      <td>0.910691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:01:00-08:00</th>\n",
       "      <td>1000.871228</td>\n",
       "      <td>296.636944</td>\n",
       "      <td>0.902736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:02:00-08:00</th>\n",
       "      <td>999.704325</td>\n",
       "      <td>296.934144</td>\n",
       "      <td>0.880882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:03:00-08:00</th>\n",
       "      <td>1000.953634</td>\n",
       "      <td>297.200167</td>\n",
       "      <td>0.891295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:04:00-08:00</th>\n",
       "      <td>1000.631334</td>\n",
       "      <td>297.750583</td>\n",
       "      <td>0.905726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:05:00-08:00</th>\n",
       "      <td>999.688538</td>\n",
       "      <td>297.643966</td>\n",
       "      <td>0.886238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:06:00-08:00</th>\n",
       "      <td>1001.116272</td>\n",
       "      <td>300.001588</td>\n",
       "      <td>0.909086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:07:00-08:00</th>\n",
       "      <td>1000.060724</td>\n",
       "      <td>298.319699</td>\n",
       "      <td>0.893735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:08:00-08:00</th>\n",
       "      <td>1000.860010</td>\n",
       "      <td>298.435405</td>\n",
       "      <td>0.872477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:09:00-08:00</th>\n",
       "      <td>1000.498578</td>\n",
       "      <td>296.689734</td>\n",
       "      <td>0.877053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:10:00-08:00</th>\n",
       "      <td>1001.345200</td>\n",
       "      <td>296.809218</td>\n",
       "      <td>0.891337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:11:00-08:00</th>\n",
       "      <td>1000.235921</td>\n",
       "      <td>297.759992</td>\n",
       "      <td>0.897618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:12:00-08:00</th>\n",
       "      <td>1001.902433</td>\n",
       "      <td>298.030807</td>\n",
       "      <td>0.881457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:13:00-08:00</th>\n",
       "      <td>999.548980</td>\n",
       "      <td>298.981068</td>\n",
       "      <td>0.888009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:14:00-08:00</th>\n",
       "      <td>999.889200</td>\n",
       "      <td>297.736104</td>\n",
       "      <td>0.877405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:15:00-08:00</th>\n",
       "      <td>998.827514</td>\n",
       "      <td>297.128743</td>\n",
       "      <td>0.883828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:16:00-08:00</th>\n",
       "      <td>1000.034731</td>\n",
       "      <td>297.628159</td>\n",
       "      <td>0.893295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:17:00-08:00</th>\n",
       "      <td>999.958014</td>\n",
       "      <td>298.120556</td>\n",
       "      <td>0.879693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:18:00-08:00</th>\n",
       "      <td>1000.681469</td>\n",
       "      <td>298.517194</td>\n",
       "      <td>0.876718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:19:00-08:00</th>\n",
       "      <td>999.220348</td>\n",
       "      <td>299.680959</td>\n",
       "      <td>0.883759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:20:00-08:00</th>\n",
       "      <td>999.567604</td>\n",
       "      <td>298.360048</td>\n",
       "      <td>0.868437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:21:00-08:00</th>\n",
       "      <td>1001.067439</td>\n",
       "      <td>296.906150</td>\n",
       "      <td>0.882149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:22:00-08:00</th>\n",
       "      <td>1000.621196</td>\n",
       "      <td>298.871270</td>\n",
       "      <td>0.874125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:23:00-08:00</th>\n",
       "      <td>999.905744</td>\n",
       "      <td>299.097761</td>\n",
       "      <td>0.881978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:24:00-08:00</th>\n",
       "      <td>999.366200</td>\n",
       "      <td>298.732733</td>\n",
       "      <td>0.876780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:25:00-08:00</th>\n",
       "      <td>999.691182</td>\n",
       "      <td>298.144174</td>\n",
       "      <td>0.877093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:26:00-08:00</th>\n",
       "      <td>999.016193</td>\n",
       "      <td>298.894197</td>\n",
       "      <td>0.875367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:27:00-08:00</th>\n",
       "      <td>998.416556</td>\n",
       "      <td>297.814813</td>\n",
       "      <td>0.868949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:28:00-08:00</th>\n",
       "      <td>1002.595668</td>\n",
       "      <td>298.454622</td>\n",
       "      <td>0.865083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 17:29:00-08:00</th>\n",
       "      <td>1001.286565</td>\n",
       "      <td>300.865097</td>\n",
       "      <td>0.847962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:01:00-08:00</th>\n",
       "      <td>998.519212</td>\n",
       "      <td>299.170132</td>\n",
       "      <td>0.844087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:02:00-08:00</th>\n",
       "      <td>1000.524464</td>\n",
       "      <td>299.297063</td>\n",
       "      <td>0.850804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:03:00-08:00</th>\n",
       "      <td>1000.230786</td>\n",
       "      <td>298.205787</td>\n",
       "      <td>0.814171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:04:00-08:00</th>\n",
       "      <td>998.918075</td>\n",
       "      <td>297.683320</td>\n",
       "      <td>0.829430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:05:00-08:00</th>\n",
       "      <td>998.783564</td>\n",
       "      <td>296.635687</td>\n",
       "      <td>0.820429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:06:00-08:00</th>\n",
       "      <td>1000.661528</td>\n",
       "      <td>295.858623</td>\n",
       "      <td>0.809932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:07:00-08:00</th>\n",
       "      <td>999.183372</td>\n",
       "      <td>297.665386</td>\n",
       "      <td>0.810741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:08:00-08:00</th>\n",
       "      <td>1001.407083</td>\n",
       "      <td>298.357840</td>\n",
       "      <td>0.818922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:09:00-08:00</th>\n",
       "      <td>1001.401620</td>\n",
       "      <td>297.069370</td>\n",
       "      <td>0.818102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:10:00-08:00</th>\n",
       "      <td>1000.313572</td>\n",
       "      <td>297.750229</td>\n",
       "      <td>0.815229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:11:00-08:00</th>\n",
       "      <td>998.067068</td>\n",
       "      <td>300.672723</td>\n",
       "      <td>0.818853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:12:00-08:00</th>\n",
       "      <td>1000.412690</td>\n",
       "      <td>299.595845</td>\n",
       "      <td>0.827210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:13:00-08:00</th>\n",
       "      <td>999.932441</td>\n",
       "      <td>298.539169</td>\n",
       "      <td>0.821970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:14:00-08:00</th>\n",
       "      <td>1000.121614</td>\n",
       "      <td>296.118869</td>\n",
       "      <td>0.826828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:15:00-08:00</th>\n",
       "      <td>1000.587600</td>\n",
       "      <td>297.693499</td>\n",
       "      <td>0.818136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:16:00-08:00</th>\n",
       "      <td>999.551818</td>\n",
       "      <td>299.593113</td>\n",
       "      <td>0.815307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:17:00-08:00</th>\n",
       "      <td>1001.398281</td>\n",
       "      <td>298.286372</td>\n",
       "      <td>0.814115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:18:00-08:00</th>\n",
       "      <td>1000.706561</td>\n",
       "      <td>296.897137</td>\n",
       "      <td>0.827031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:19:00-08:00</th>\n",
       "      <td>1000.820852</td>\n",
       "      <td>298.461707</td>\n",
       "      <td>0.833830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:20:00-08:00</th>\n",
       "      <td>1001.036305</td>\n",
       "      <td>298.048856</td>\n",
       "      <td>0.809816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:21:00-08:00</th>\n",
       "      <td>1000.409047</td>\n",
       "      <td>295.667157</td>\n",
       "      <td>0.809794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:22:00-08:00</th>\n",
       "      <td>999.233165</td>\n",
       "      <td>297.710249</td>\n",
       "      <td>0.802135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:23:00-08:00</th>\n",
       "      <td>999.352135</td>\n",
       "      <td>297.918112</td>\n",
       "      <td>0.803824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:24:00-08:00</th>\n",
       "      <td>1000.322697</td>\n",
       "      <td>298.401682</td>\n",
       "      <td>0.811700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:25:00-08:00</th>\n",
       "      <td>999.450538</td>\n",
       "      <td>298.701086</td>\n",
       "      <td>0.803430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:26:00-08:00</th>\n",
       "      <td>1000.417054</td>\n",
       "      <td>299.167490</td>\n",
       "      <td>0.807146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:27:00-08:00</th>\n",
       "      <td>1001.359744</td>\n",
       "      <td>298.201252</td>\n",
       "      <td>0.806142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:28:00-08:00</th>\n",
       "      <td>999.383056</td>\n",
       "      <td>299.168673</td>\n",
       "      <td>0.802074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:29:00-08:00</th>\n",
       "      <td>1000.645755</td>\n",
       "      <td>297.703946</td>\n",
       "      <td>0.801625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-21 18:30:00-08:00</th>\n",
       "      <td>1000.388472</td>\n",
       "      <td>297.204252</td>\n",
       "      <td>0.814248</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            irradiance  temperature  visible_transmittance\n",
       "2018-02-21 17:00:00-08:00  1000.081842   297.612852               0.910691\n",
       "2018-02-21 17:01:00-08:00  1000.871228   296.636944               0.902736\n",
       "2018-02-21 17:02:00-08:00   999.704325   296.934144               0.880882\n",
       "2018-02-21 17:03:00-08:00  1000.953634   297.200167               0.891295\n",
       "2018-02-21 17:04:00-08:00  1000.631334   297.750583               0.905726\n",
       "2018-02-21 17:05:00-08:00   999.688538   297.643966               0.886238\n",
       "2018-02-21 17:06:00-08:00  1001.116272   300.001588               0.909086\n",
       "2018-02-21 17:07:00-08:00  1000.060724   298.319699               0.893735\n",
       "2018-02-21 17:08:00-08:00  1000.860010   298.435405               0.872477\n",
       "2018-02-21 17:09:00-08:00  1000.498578   296.689734               0.877053\n",
       "2018-02-21 17:10:00-08:00  1001.345200   296.809218               0.891337\n",
       "2018-02-21 17:11:00-08:00  1000.235921   297.759992               0.897618\n",
       "2018-02-21 17:12:00-08:00  1001.902433   298.030807               0.881457\n",
       "2018-02-21 17:13:00-08:00   999.548980   298.981068               0.888009\n",
       "2018-02-21 17:14:00-08:00   999.889200   297.736104               0.877405\n",
       "2018-02-21 17:15:00-08:00   998.827514   297.128743               0.883828\n",
       "2018-02-21 17:16:00-08:00  1000.034731   297.628159               0.893295\n",
       "2018-02-21 17:17:00-08:00   999.958014   298.120556               0.879693\n",
       "2018-02-21 17:18:00-08:00  1000.681469   298.517194               0.876718\n",
       "2018-02-21 17:19:00-08:00   999.220348   299.680959               0.883759\n",
       "2018-02-21 17:20:00-08:00   999.567604   298.360048               0.868437\n",
       "2018-02-21 17:21:00-08:00  1001.067439   296.906150               0.882149\n",
       "2018-02-21 17:22:00-08:00  1000.621196   298.871270               0.874125\n",
       "2018-02-21 17:23:00-08:00   999.905744   299.097761               0.881978\n",
       "2018-02-21 17:24:00-08:00   999.366200   298.732733               0.876780\n",
       "2018-02-21 17:25:00-08:00   999.691182   298.144174               0.877093\n",
       "2018-02-21 17:26:00-08:00   999.016193   298.894197               0.875367\n",
       "2018-02-21 17:27:00-08:00   998.416556   297.814813               0.868949\n",
       "2018-02-21 17:28:00-08:00  1002.595668   298.454622               0.865083\n",
       "2018-02-21 17:29:00-08:00  1001.286565   300.865097               0.847962\n",
       "...                                ...          ...                    ...\n",
       "2018-02-21 18:01:00-08:00   998.519212   299.170132               0.844087\n",
       "2018-02-21 18:02:00-08:00  1000.524464   299.297063               0.850804\n",
       "2018-02-21 18:03:00-08:00  1000.230786   298.205787               0.814171\n",
       "2018-02-21 18:04:00-08:00   998.918075   297.683320               0.829430\n",
       "2018-02-21 18:05:00-08:00   998.783564   296.635687               0.820429\n",
       "2018-02-21 18:06:00-08:00  1000.661528   295.858623               0.809932\n",
       "2018-02-21 18:07:00-08:00   999.183372   297.665386               0.810741\n",
       "2018-02-21 18:08:00-08:00  1001.407083   298.357840               0.818922\n",
       "2018-02-21 18:09:00-08:00  1001.401620   297.069370               0.818102\n",
       "2018-02-21 18:10:00-08:00  1000.313572   297.750229               0.815229\n",
       "2018-02-21 18:11:00-08:00   998.067068   300.672723               0.818853\n",
       "2018-02-21 18:12:00-08:00  1000.412690   299.595845               0.827210\n",
       "2018-02-21 18:13:00-08:00   999.932441   298.539169               0.821970\n",
       "2018-02-21 18:14:00-08:00  1000.121614   296.118869               0.826828\n",
       "2018-02-21 18:15:00-08:00  1000.587600   297.693499               0.818136\n",
       "2018-02-21 18:16:00-08:00   999.551818   299.593113               0.815307\n",
       "2018-02-21 18:17:00-08:00  1001.398281   298.286372               0.814115\n",
       "2018-02-21 18:18:00-08:00  1000.706561   296.897137               0.827031\n",
       "2018-02-21 18:19:00-08:00  1000.820852   298.461707               0.833830\n",
       "2018-02-21 18:20:00-08:00  1001.036305   298.048856               0.809816\n",
       "2018-02-21 18:21:00-08:00  1000.409047   295.667157               0.809794\n",
       "2018-02-21 18:22:00-08:00   999.233165   297.710249               0.802135\n",
       "2018-02-21 18:23:00-08:00   999.352135   297.918112               0.803824\n",
       "2018-02-21 18:24:00-08:00  1000.322697   298.401682               0.811700\n",
       "2018-02-21 18:25:00-08:00   999.450538   298.701086               0.803430\n",
       "2018-02-21 18:26:00-08:00  1000.417054   299.167490               0.807146\n",
       "2018-02-21 18:27:00-08:00  1001.359744   298.201252               0.806142\n",
       "2018-02-21 18:28:00-08:00   999.383056   299.168673               0.802074\n",
       "2018-02-21 18:29:00-08:00  1000.645755   297.703946               0.801625\n",
       "2018-02-21 18:30:00-08:00  1000.388472   297.204252               0.814248\n",
       "\n",
       "[91 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the blob, is it really there?\n",
    "cur.execute('SELECT results FROM trials WHERE trial_id = 1')\n",
    "trial1_results = cur.fetchone()\n",
    "pd.read_csv(io.StringIO(trial1_results[0]), index_col=0)\n",
    "# yay! it works!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `JOIN`\n",
    "The foreign keys relate tables, but how do we use this relation? By joining the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add the results for experiment 17: T=375[K], E=700[W/m^2]\n",
    "experiment_id, temperature, irradiance = list(cur.execute(\n",
    "    'SELECT experiment_id, temperature, irradiance FROM experiments WHERE (temperature = 375 AND irradiance = 700)'\n",
    "))[0]\n",
    "start_time, end_time = '2018-02-28T17:00-0800', '2018-02-28T18:30-0800'\n",
    "timestamps = pd.DatetimeIndex(start=start_time, end=end_time, freq='T')\n",
    "# use http://poquitopicante.blogspot.com/2016/11/panda-pop.html to help you recall what offset alias to use\n",
    "size = len(timestamps)\n",
    "data = {\n",
    "    'temperature': np.random.randn(size) + temperature,\n",
    "    'irradiance': np.random.randn(size) + irradiance,\n",
    "    'visible_transmittance': np.logspace(np.log10(0.9), np.log10(0.7), size) + np.random.randn(size) / 100\n",
    "}\n",
    "results = pd.DataFrame(data, index=timestamps)\n",
    "duration = (results.index[-1] - results.index[0]).value  # [ns]\n",
    "avg_temperature = results.temperature.mean()  # [K]\n",
    "std_temperature = results.temperature.std()  # [K]\n",
    "avg_irradiance = results.irradiance.mean()  # [W/m^2]\n",
    "std_irradiance = results.irradiance.std()  # [W/m^2]\n",
    "init_visible_transmittance = results.visible_transmittance[start_time]\n",
    "final_visible_transmittance = results.visible_transmittance[end_time]\n",
    "values = (experiment_id, results.to_csv(), duration, avg_temperature, std_temperature,\n",
    "          avg_irradiance, std_irradiance, init_visible_transmittance, final_visible_transmittance,\n",
    "         'this is doctored data')\n",
    "cur.execute('''INSERT INTO trials (\n",
    "    experiment_id, results, duration, avg_temperature, std_temperature,\n",
    "    avg_irradiance, std_irradiance, init_visible_transmittance,\n",
    "    final_visible_transmittance, notes\n",
    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', values)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>experiment</th>\n",
       "      <th>init_visible_transmittance</th>\n",
       "      <th>final_visible_transmittance</th>\n",
       "      <th>material</th>\n",
       "      <th>temperature</th>\n",
       "      <th>irradiance</th>\n",
       "      <th>alpha</th>\n",
       "      <th>beta</th>\n",
       "      <th>material_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trial_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0.910691</td>\n",
       "      <td>0.814248</td>\n",
       "      <td>EVA</td>\n",
       "      <td>298.15</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.123</td>\n",
       "      <td>4.56</td>\n",
       "      <td>polymer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17</td>\n",
       "      <td>0.881748</td>\n",
       "      <td>0.695605</td>\n",
       "      <td>EVA</td>\n",
       "      <td>375.00</td>\n",
       "      <td>700.0</td>\n",
       "      <td>0.123</td>\n",
       "      <td>4.56</td>\n",
       "      <td>polymer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          experiment  init_visible_transmittance  final_visible_transmittance  \\\n",
       "trial_id                                                                        \n",
       "1                  1                    0.910691                     0.814248   \n",
       "2                 17                    0.881748                     0.695605   \n",
       "\n",
       "         material  temperature  irradiance  alpha  beta material_type  \n",
       "trial_id                                                               \n",
       "1             EVA       298.15      1000.0  0.123  4.56       polymer  \n",
       "2             EVA       375.00       700.0  0.123  4.56       polymer  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('''\n",
    "SELECT\n",
    "    trial_id, trials.experiment_id AS experiment, init_visible_transmittance, final_visible_transmittance,\n",
    "    experiments.material_id AS material, temperature, irradiance, alpha, beta, material_type\n",
    "FROM trials\n",
    "JOIN experiments ON experiments.experiment_id = experiment\n",
    "JOIN materials ON materials.material_id = material\n",
    "''', conn, index_col='trial_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Epilogue\n",
    "Unfortunately that's all we have time for, but we covered a lot, even if we didn't get this far. If there's anything I hope you learned from this it's that:\n",
    "\n",
    "1. You can read the SQLite and PostgreSQL manuals or use [StackOverflow](https://stackoverflow.com/questions/tagged/SQL) to teach yourself SQL. It's not rocket science. I was a mechanical engineer, and I learned it.\n",
    "2. The basics of making a table, inserting data, and conducting queries.\n",
    "3. How to interact programmatically with a database.\n",
    "\n",
    "But there's still so much to learn! Hopefully you will continue on your own and try some of these intereting topics.\n",
    "1. Use what you've learned on a more advanced database management system like PostgreSQL, MySQL, or MS SQL Server.\n",
    "2. Use an Object Relational Mapper (ORM) like Django or SQLAlchemy to simplify your workflow, by creating and manipulating data in native Python.\n",
    "3. Explore a NoSQL database like Cassandra or MongoDB and see if the flexible structure and large scale cluster computing allow you to explore big data with tools like [Spark](http://spark.apache.org/) and [Hadoop](http://hadoop.apache.org/).\n",
    "\n",
    "Did you learn about everything you expected today? What did wish we had covered? Leave your comments or improve this tutorial by sending a PR to [The Hacker Within, Berkeley](https://github.com/thehackerwithin/berkeley).\n",
    "\n",
    "Thanks!"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}<commit_msg>fix typo in materials_list query<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro to SQL - The Hacker Within (2018-02-21)\n",
    "This tutorial will show you the basics of SQL:\n",
    "* creating a table\n",
    "* inserting rows\n",
    "* querying with conditions\n",
    "* grouping and ordering\n",
    "* joining tables with a common field\n",
    "* aggregating rows using COUNT, MAX, etc.\n",
    "* alternates to SQL like pandas and Django\n",
    "* indexing frequently queried columns for performance\n",
    "* alter existing table to add, drop, or rename a column\n",
    "* drop a table\n",
    "* vacuum a database\n",
    "\n",
    "## SQLite\n",
    "We are going to use [Python](https://docs.python.org/3/library/sqlite3.html) because it comes with the popular [SQLite](https://sqlite.org/index.html) (_aka_ [`etilqs`](https://www.google.com/search?q=etilqs)) relational database builtin. \n",
    "\n",
    "## Requirements\n",
    "The following examples assume you have Python 3 installed on your computer. Any distribution will do. If you have [Anaconda](https://www.anaconda.com/distribution/) or [Miniconda](https://conda.io/miniconda.html), you can create a new conda environment with the requirements for this tutorial from a terminal. For example with miniconda on Mac OS X, you might use the following to activate the root conda environment and create a new one called \"py36-thw-sql\".\n",
    "\n",
    "    ~$ source ~/miniconda/bin/activate\n",
    "    (root) ~$ conda create -n py36-thw-sql python=3.6.3 psycopg2 django pandas\n",
    "\n",
    "Or on Windows with Anaconda, you might use the following in a CMD terminal.\n",
    "\n",
    "    ~> %LOCALAPPDATA%\\Continuum\\anaconda3\\Scripts\\activate\n",
    "    (root) ~> conda create -n py36-thw-sql python=3.6.3 psycopg2 django pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python DB-API 2.0\n",
    "[PEP 249](https://www.python.org/dev/peps/pep-0249/) specifies a `connection` to a database, and a `cursor` from the connection to execute SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "import io  # we'll need this way later\n",
    "import os\n",
    "import sqlite3  # this is the module that binds to SQLite\n",
    "import numpy as np  # never know when you might need NumPy, oh, right, always!\n",
    "import pandas as pd  # you'll see why we can use this later\n",
    "\n",
    "DBFILE = 'sqlite3.db'  # this will be our database\n",
    "BASEDIR = %pwd  # os.path.abspath(os.path.dirname(__file__))\n",
    "DBPATH = os.path.join(BASEDIR, DBFILE)\n",
    "\n",
    "# we may need to delete the existing file first\n",
    "if os.path.exists(DBPATH):\n",
    "    os.remove(DBPATH)\n",
    "\n",
    "# open a connection to the database for this tutorial\n",
    "conn = sqlite3.connect(DBPATH)\n",
    "\n",
    "# get a cursor to the database\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notes\n",
    "If a file with the same name doesn't already exist, then this creates a new database otherwise it connects to the existing database contained in the file. You can also use ':memory:' to create an \"in-memory\" database that has no file, but then you can't connect to that from another process.\n",
    "\n",
    "We'll have to close the connection and cursor later. Next time we could use a [`with` context to automatically close the connection](https://docs.python.org/3.6/library/sqlite3.html#using-the-connection-as-a-context-manager).\n",
    "\n",
    "    with sqlite3.connect('sqlite3.db') as conn:\n",
    "        cur = conn.execute('SQL QUERY ...')  # e.g.: 'SELECT * FROM table_name;'\n",
    "        output = cur.fetchall()  # get the results\n",
    "\n",
    "Closing the connection automatically closes the cursor. [Other bindings may offer similar context managers.](http://initd.org/psycopg/docs/usage.html#with-statement) to commit and close changes or rollback changes and raise an exception."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating tables\n",
    "A relational database or SQL database is a tabular structure consisting of rows of data with columns of fields. The [data definition language or DDL](https://en.wikipedia.org/wiki/Data_definition_language) used to create the table is the same language used to query it, called [SQL or Structured Query Language](https://en.wikipedia.org/wiki/SQL).\n",
    "\n",
    "Although [the basic SQL commands](https://sqlite.org/lang.html) are nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/sql.html), the data types may be different. [SQLite only has 5 datatypes](https://sqlite.org/datatype3.html): `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`. For [boolean, use integer zero for false, and one for true](https://sqlite.org/datatype3.html#boolean_datatype). For [dates and times use text and ISO8601](https://sqlite.org/datatype3.html#date_and_time_datatype), _e.g._: `\"2018-02-21T17:05-0800\"`. By comparison, [PostgreSQL has too many to list here](https://www.postgresql.org/docs/10/static/datatype.html) including booleans, date, time, arrays, JSON, etc.\n",
    "\n",
    "## `CREATE`\n",
    "The [basic SQL command to create a table](https://sqlite.org/lang_createtable.html) is\n",
    "\n",
    "    CREATE TABLE <table_name> (<field_name> <TYPE> <CONSTRAINTS>, <field_name> <TYPE> <CONSTRAINTS>, <CONSTRAINTS>, ...);\n",
    "\n",
    "Some languages enforce the semicolon, some don't. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-basics.html).\n",
    "\n",
    "### Constraints, Defaults, and Options\n",
    "Constraints are optional and set conditions, limitations, or options for columns and the table. The most common constraints are: `PRIMARY KEY`, `UNIQUE`, `NOT NULL`, `DEFAULT`, `FOREIGN KEY`, `REFERENCES`, etc. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-constraints.html).\n",
    "\n",
    "#### `PRIMARY KEY`\n",
    "The most important of these is `PRIMARY KEY` which is equivalent to `UNIQUE NOT NULL`. A [primary key](https://www.postgresql.org/docs/10/static/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS) is a unique references that identifies each record in the table. Although it is not required, every table should have a primary key. Only one primary key is allowed, and it can be constructed from multiple columns, `PRIMARY KEY (<field_A>, <field_B)`, to create a unique together, non-null identifier. [In SQLite, if missing then a integer primary key named, `rowid`, is created by default](https://sqlite.org/lang_createtable.html#rowid). Also in SQLite, any integer primary key is automatically incremented, so [the _AUTOINCREMENT_ command is usually **not** needed](https://sqlite.org/autoinc.html). [In PostgreSQL the `SERIAL` command is used](https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL) to create a corresponding [sequence](https://www.postgresql.org/docs/10/static/functions-sequence.html) for the primary key.\n",
    "\n",
    "## Practice\n",
    "The other constraints and options are also important, but we'll discover those as we learn. Let's create some simple databases with fictitious data to practice. Imagine you are testing several different materials with different properties $\\alpha$ and $\\beta$ under different stresses like different temperatures and  light intensity and changing thickness. How would you organize this data? Take a moment to design a schema or structure for your data. The schema consists of the column names and data types and the column and table constraints."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use Python triple quoted strings to span multiple lines, but use single quotes, since SQL only uses double quotes\n",
    "\n",
    "# first create a materials table\n",
    "cur.execute('''CREATE TABLE materials (\n",
    "    material_id TEXT PRIMARY KEY,\n",
    "    long_name TEXT UNIQUE NOT NULL,\n",
    "    alpha REAL NOT NULL,\n",
    "    beta REAL NOT NULL,\n",
    "    material_type TEXT NOT NULL\n",
    ")''')\n",
    "conn.commit()\n",
    "# if you don't commit the changes, they won't be written to the file, and won't be visible to other connections\n",
    "\n",
    "# then create an experiments table\n",
    "cur.execute('''CREATE TABLE experiments (\n",
    "    experiment_id INTEGER PRIMARY KEY,\n",
    "    temperature REAL DEFAULT 298.15,\n",
    "    irradiance REAL DEFAULT 1000.0,\n",
    "    uv_filter INTEGER DEFAULT 0,\n",
    "    material_id NOT NULL REFERENCES materials ON UPDATE CASCADE ON DELETE CASCADE,\n",
    "    thickness REAL DEFAULT 0.005,\n",
    "    UNIQUE (temperature, irradiance, uv_filter, material_id)\n",
    ")''')\n",
    "conn.commit()\n",
    "\n",
    "# and finally create a trials table\n",
    "cur.execute('''CREATE TABLE trials (\n",
    "    trial_id INTEGER PRIMARY KEY,\n",
    "    experiment_id NOT NULL REFERENCES experiments ON UPDATE CASCADE ON DELETE CASCADE,\n",
    "    results BLOB NOT NULL,\n",
    "    duration REAL NOT NULL,\n",
    "    avg_temperature REAL NOT NULL,\n",
    "    std_temperature REAL NOT NULL,\n",
    "    avg_irradiance REAL NOT NULL,\n",
    "    std_irradiance REAL NOT NULL,\n",
    "    init_visible_transmittance REAL NOT NULL,\n",
    "    final_visible_transmittance REAL NOT NULL,\n",
    "    notes TEXT\n",
    ")''')\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `FOREIGN KEY`\n",
    "A [foreign key constraint](https://sqlite.org/foreignkeys.html) creates a relationship between two tables. The `FOREIGN KEY` is implied when the `REFERENCES` column constraint is applied. In the experiments table above, the column constraint on `material_id` is the same as adding this table constriant:\n",
    "\n",
    "    FOREIGN KEY (material_id) REFERENCES materials (material_id)\n",
    "\n",
    "Specifying the referenced column in the table constraint isn't necessary, and if omitted defaults to the primary key of the referenced table. The syntax is nearly the same for [other relational databases](https://www.postgresql.org/docs/10/static/ddl-constraints.html#DDL-CONSTRAINTS-FK).\n",
    "\n",
    "You can use the same name for the foreign key and it's related field, but it may make joining tables more difficult because you will need to use the table name to avoid an ambigous column name. _E.G._: you can use `trials.experiment_id` and `experiments.experiment_id` to differentiate between them. You can also use `AS` to create a temporary name like `trials.experiment_id AS experiment`. Or you could just use different names for the foreign key and it's related field like `FOREIGN KEY (material) REFERENCES materials (material_id)` and then there's no ambiguity. Your call.\n",
    "\n",
    "### `DELETE` and `UPDATE`\n",
    "What happens if the reference of a foreign key is deleted or updated? That's up to you: in SQLite the default is to do nothing, but typically you want the action to cascade. Add the desired [`ON DELETE` or `ON UPDATE` action](https://sqlite.org/foreignkeys.html#fk_actions) to the constraint.\n",
    "\n",
    "\n",
    "### Bonus Questions\n",
    "1. What is the difference between a column constraint and a table constraint?\n",
    "2. What [other table constraint](https://www.postgresql.org/docs/9.1/static/ddl-constraints.html#AEN2496) is in the experiments table?\n",
    "3. What other constraints or defaults are applied in the tables?\n",
    "4. What part of the materials table schema is fragile and can be improved?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `INSERT`\n",
    "The [basic SQL command to put data into a table is](https://sqlite.org/lang_insert.html)\n",
    "\n",
    "    INSERT INTO <table_name> (<field_name>, <field_name>, ...) VALUES (<value>, <value>, ...)\n",
    "\n",
    "[Other relational databases use the same SQL syntax.](https://www.postgresql.org/docs/current/static/dml-insert.html)\n",
    "\n",
    "Let's add some pretend data to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a EVA as a material\n",
    "cur.execute('INSERT INTO materials VALUES (\"EVA\", \"ethylene vinyl acetate\", 0.123, 4.56, \"polymer\")')\n",
    "conn.commit()  # you must commit for it to become permanent\n",
    "cur.rowcount  # tells you how many rows written, sometimes, it's quirky"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Placeholders\n",
    "You can use placeholders to loop over insert statements to add multiple records.\n",
    "\n",
    ">*WARNING*: Never use string formatters to in lieu of placeholders or you may be subject to [a SQL injection attack](https://xkcd.com/327/).\n",
    "\n",
    "SQLite uses `?` but [other relational databases may use `%s`](http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries) or another placeholder.\n",
    "\n",
    "Also, in `sqlite3` [`executemany`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany) is a convenient shortcut, [but it may not be convenient for all database bindings](http://initd.org/psycopg/docs/extras.html#fast-exec)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rowcount = 2\n"
     ]
    }
   ],
   "source": [
    "# add some more fake materials\n",
    "fake_materials = [\n",
    "    ('PMMC', 'poly methyl methacrylate', 0.789, 10.11, 'polymer'),\n",
    "    ('KBr', 'potassium bromide', 1.213, 14.15, 'crystal')\n",
    "]\n",
    "for mat in fake_materials:\n",
    "    # must have same number of place holders as values\n",
    "    cur.execute('INSERT INTO materials VALUES (?, ?, ?, ?, ?)', mat)  # use place holders\n",
    "conn.commit()  # you can commit all of the changes at the end of the loop\n",
    "\n",
    "# use the executemany shortcut\n",
    "fake_materials = [\n",
    "    ('SiO2', 'silicon dioxide', 1.617, 18.19, 'crystal'),\n",
    "    ('CaF2', 'calcium flouride', 2.0, 21.22, 'crystal')\n",
    "]\n",
    "cur.executemany('INSERT INTO materials VALUES (?, ?, ?, ?, ?)', fake_materials)\n",
    "conn.commit()\n",
    "print('rowcount = %d' % cur.rowcount)  # with executemany, cur.rowcount shows total number of rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `DELETE` and `UPDATE`\n",
    "Oops I made a mistake. How do I fix it? The opposite of `INSERT` is [`DELETE`](https://sqlite.org/lang_delete.html). But don't throw the baby out with the bathwater, you can also [`UPDATE`] a record. [Other relational databases use the same SQL syntax to manipulate data](https://www.postgresql.org/docs/10/static/dml.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('DELETE FROM materials WHERE material_id = \"SiO2\"')\n",
    "cur.execute('UPDATE materials SET alpha=1.23E-4, beta=8.910E+11 WHERE material_id = \"CaF2\"')\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Queries\n",
    "The way you [select](https://sqlite.org/lang_select.html) data is by executing queries. [The language is the same for all relational databases.](https://www.postgresql.org/docs/10/static/queries.html) The star `*` means select all columns, or you can give the columns explicitly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('EVA', 'ethylene vinyl acetate', 0.123, 4.56, 'polymer'),\n",
       " ('PMMC', 'poly methyl methacrylate', 0.789, 10.11, 'polymer'),\n",
       " ('KBr', 'potassium bromide', 1.213, 14.15, 'crystal'),\n",
       " ('CaF2', 'calcium flouride', 0.000123, 891000000000.0, 'crystal')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM materials')\n",
    "cur.fetchall()  # fetch all the results of the query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conditions\n",
    "You can limit a query using [`WHERE`](https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-FROM) and [`LIMIT`](https://www.postgresql.org/docs/10/static/queries-limit.html). You can combine `WHERE` with a conditional expression, `IN` to check a set, or `LIKE` to compare with strings. Use `AND` and `OR` to combine conditions.\n",
    "\n",
    "### Python DB-API Cursor Methods\n",
    "The Python DB-API cursor can be used as an iterator or you can call it's [fetch methods](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.fetchone)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EVA is ethylene vinyl acetate\n",
      "PMMC is poly methyl methacrylate\n"
     ]
    }
   ],
   "source": [
    "# limit the query using WHERE and LIMIT\n",
    "cur.execute('SELECT material_id, long_name FROM materials WHERE alpha < 1 LIMIT 2')\n",
    "for c in cur: print('{} is {}'.format(*c))  # user the cursor as an iterator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "no such column: materials_list",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-8-79c291e0993d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mmaterials_list\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\"EVA\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"PMMC\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'SELECT alpha, beta FROM materials WHERE materials_list'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmat\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchone\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mmat\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mmaterials_list\u001b[0m\u001b[0;34m]\u001b[0m  \u001b[0;31m# use the cursor fetchone() method to get next item\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: no such column: materials_list"
     ]
    }
   ],
   "source": [
    "materials_list = (\"EVA\", \"PMMC\")\n",
    "cur.execute('SELECT alpha, beta FROM materials WHERE materials_list')\n",
    "[(mat, cur.fetchone()) for mat in materials_list]  # use the cursor fetchone() method to get next item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregates\n",
    "Your query can aggregate results like `AVG`, `SUM`, `COUNT`, `MAX`, `MIN`, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('SELECT COUNT(*) FROM materials')\n",
    "print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `GROUP BY`\n",
    "You can group queries by a column or a condition such as an expression, `IN`, or `LIKE`, if your selection is an aggregate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('SELECT material_type, COUNT(*), AVG(alpha), MAX(beta) FROM materials GROUP BY material_type')\n",
    "cur.fetchmany(2)  # use fetchmany() with size parameter, just for fun"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Practice\n",
    "Add a fictitious experiment schedule and doctor up some data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use defaults, let primary key auto-increment, just supply material ID\n",
    "cur.execute('INSERT INTO experiments (material_id) VALUES (\"EVA\")')  # use defaults, \n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set up a test matrix for EVA\n",
    "temp = range(300, 400, 25)\n",
    "irrad = range(400, 800, 100)\n",
    "try:\n",
    "    for T in temp:\n",
    "        for E in irrad:\n",
    "            cur.execute('INSERT INTO experiments (temperature, irradiance) VALUES (?, ?)', (T, E))\n",
    "except sqlite3.IntegrityError as exc:\n",
    "    print('sqlite3.IntegrityError: %s', exc)\n",
    "\n",
    "# Oops! We forgot to specify the material, there is not default, and it is constrained as NOT NULL!\n",
    "conn.rollback()  # undo any changes\n",
    "try:\n",
    "    for T in temp:\n",
    "        for E in irrad:\n",
    "            cur.execute('INSERT INTO experiments (temperature, irradiance, material_id) VALUES (?, ?, \"EVA\")', (T, E))\n",
    "except sqlite3.IntegrityError as exc:\n",
    "    print(exc)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this list is hard to read\n",
    "list(cur.execute('SELECT * FROM experiments'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# not only is Pandas much nicer, it also executes queries!\n",
    "pd.read_sql('SELECT * FROM experiments', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `ORDER BY`\n",
    "Does what it says; order the query results by a column. Default is ascending, but use `ASC` or `DESC` to change the order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python's SQLite let's you use either '==' or '=', but I think SQL only allows '=', okay?\n",
    "pd.read_sql('SELECT * FROM experiments WHERE irradiance = 700 ORDER BY temperature', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# descending order\n",
    "pd.read_sql('SELECT * FROM experiments WHERE temperature = 375 ORDER BY irradiance DESC', conn, index_col='experiment_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### [Dr. Data](http://www.startrek.com/database_article/data)\n",
    "![Dr. Data](http://www.startrek.com/uploads/assets/db_articles/26da32597d9bd37fde9da22660aa524f24fd725c.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dr. Data\n",
    "start_time, end_time = '2018-02-21T17:00-0800', '2018-02-21T18:30-0800'\n",
    "timestamps = pd.DatetimeIndex(start=start_time, end=end_time, freq='T')\n",
    "# use http://poquitopicante.blogspot.com/2016/11/panda-pop.html to help you recall what offset alias to use\n",
    "size = len(timestamps)\n",
    "data = {\n",
    "    'temperature': np.random.randn(size) + 298.15,\n",
    "    'irradiance': np.random.randn(size) + 1000,\n",
    "    'visible_transmittance': np.logspace(np.log10(0.9), np.log10(0.8), size) + np.random.randn(size) / 100\n",
    "}\n",
    "results = pd.DataFrame(data, index=timestamps)\n",
    "duration = (results.index[-1] - results.index[0]).value  # [ns]\n",
    "avg_temperature = results.temperature.mean()  # [K]\n",
    "std_temperature = results.temperature.std()  # [K]\n",
    "avg_irradiance = results.irradiance.mean()  # [W/m^2]\n",
    "std_irradiance = results.irradiance.std()  # [W/m^2]\n",
    "init_visible_transmittance = results.visible_transmittance[start_time]\n",
    "final_visible_transmittance = results.visible_transmittance[end_time]\n",
    "values = (1, results.to_csv(), duration, avg_temperature, std_temperature,\n",
    "          avg_irradiance, std_irradiance, init_visible_transmittance, final_visible_transmittance,\n",
    "         'this is doctored data')\n",
    "cur.execute('''INSERT INTO trials (\n",
    "    experiment_id, results, duration, avg_temperature, std_temperature,\n",
    "    avg_irradiance, std_irradiance, init_visible_transmittance,\n",
    "    final_visible_transmittance, notes\n",
    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', values)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# check the blob, is it really there?\n",
    "cur.execute('SELECT results FROM trials WHERE trial_id = 1')\n",
    "trial1_results = cur.fetchone()\n",
    "pd.read_csv(io.StringIO(trial1_results[0]), index_col=0)\n",
    "# yay! it works!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `JOIN`\n",
    "The foreign keys relate tables, but how do we use this relation? By joining the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add the results for experiment 17: T=375[K], E=700[W/m^2]\n",
    "experiment_id, temperature, irradiance = list(cur.execute(\n",
    "    'SELECT experiment_id, temperature, irradiance FROM experiments WHERE (temperature = 375 AND irradiance = 700)'\n",
    "))[0]\n",
    "start_time, end_time = '2018-02-28T17:00-0800', '2018-02-28T18:30-0800'\n",
    "timestamps = pd.DatetimeIndex(start=start_time, end=end_time, freq='T')\n",
    "# use http://poquitopicante.blogspot.com/2016/11/panda-pop.html to help you recall what offset alias to use\n",
    "size = len(timestamps)\n",
    "data = {\n",
    "    'temperature': np.random.randn(size) + temperature,\n",
    "    'irradiance': np.random.randn(size) + irradiance,\n",
    "    'visible_transmittance': np.logspace(np.log10(0.9), np.log10(0.7), size) + np.random.randn(size) / 100\n",
    "}\n",
    "results = pd.DataFrame(data, index=timestamps)\n",
    "duration = (results.index[-1] - results.index[0]).value  # [ns]\n",
    "avg_temperature = results.temperature.mean()  # [K]\n",
    "std_temperature = results.temperature.std()  # [K]\n",
    "avg_irradiance = results.irradiance.mean()  # [W/m^2]\n",
    "std_irradiance = results.irradiance.std()  # [W/m^2]\n",
    "init_visible_transmittance = results.visible_transmittance[start_time]\n",
    "final_visible_transmittance = results.visible_transmittance[end_time]\n",
    "values = (experiment_id, results.to_csv(), duration, avg_temperature, std_temperature,\n",
    "          avg_irradiance, std_irradiance, init_visible_transmittance, final_visible_transmittance,\n",
    "         'this is doctored data')\n",
    "cur.execute('''INSERT INTO trials (\n",
    "    experiment_id, results, duration, avg_temperature, std_temperature,\n",
    "    avg_irradiance, std_irradiance, init_visible_transmittance,\n",
    "    final_visible_transmittance, notes\n",
    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', values)\n",
    "conn.commit()  # commit! commit! commit!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql('''\n",
    "SELECT\n",
    "    trial_id, trials.experiment_id AS experiment, init_visible_transmittance, final_visible_transmittance,\n",
    "    experiments.material_id AS material, temperature, irradiance, alpha, beta, material_type\n",
    "FROM trials\n",
    "JOIN experiments ON experiments.experiment_id = experiment\n",
    "JOIN materials ON materials.material_id = material\n",
    "''', conn, index_col='trial_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Epilogue\n",
    "Unfortunately that's all we have time for, but we covered a lot, even if we didn't get this far. If there's anything I hope you learned from this it's that:\n",
    "\n",
    "1. You can read the SQLite and PostgreSQL manuals or use [StackOverflow](https://stackoverflow.com/questions/tagged/SQL) to teach yourself SQL. It's not rocket science. I was a mechanical engineer, and I learned it.\n",
    "2. The basics of making a table, inserting data, and conducting queries.\n",
    "3. How to interact programmatically with a database.\n",
    "\n",
    "But there's still so much to learn! Hopefully you will continue on your own and try some of these intereting topics.\n",
    "1. Use what you've learned on a more advanced database management system like PostgreSQL, MySQL, or MS SQL Server.\n",
    "2. Use an Object Relational Mapper (ORM) like Django or SQLAlchemy to simplify your workflow, by creating and manipulating data in native Python.\n",
    "3. Explore a NoSQL database like Cassandra or MongoDB and see if the flexible structure and large scale cluster computing allow you to explore big data with tools like [Spark](http://spark.apache.org/) and [Hadoop](http://hadoop.apache.org/).\n",
    "\n",
    "Did you learn about everything you expected today? What did wish we had covered? Leave your comments or improve this tutorial by sending a PR to [The Hacker Within, Berkeley](https://github.com/thehackerwithin/berkeley).\n",
    "\n",
    "Thanks!"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
{'fraction_non_alphanumeric': 0.19186166268435362, 'fraction_numerical': 0.08000329570734119, 'mean_word_length': 2.0158094173189216, 'pattern_counts': {'":': 439, '<': 1231, '<?xml version=': 0, '>': 1234, 'https://': 80, 'lorem ipsum': 0, 'www.': 38, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1442721', 'n_tokens_mistral': 40443, 'n_tokens_neox': 35174, 'n_words': 7353}