2_Storing_Data_Module.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Storage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Text Files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The simplest way to store data is with a text file. [Reading and writing to a text file from Python is simple](http://www.pythonforbeginners.com/files/reading-and-writing-files-in-python), and those files can easily be opened with any text editor. A text file can take any form, but your file needs to be parsable. That means when fetching data from the file your python script needs to know where one data point ends and the next begins.  The standard convention for storing data is by comma sparated value (or CSVs). CSVs store data the same way an excel spreadsheet does, with rows and columns. Let's say you had three rows and three columns of integer values to store. You would store that data in a CSV as:\n",
    "````\n",
    "1,2,2\n",
    "3,2,1\n",
    "4,4,3\n",
    "````\n",
    "Rows are separated by a new line, and columns by commas. Knowing this simple convention makes it easy for a computer to parse the file so that it can then do something with the data.\n",
    "\n",
    "However, in some cases CSVs are actually a bad choice for storing data. Suppose that one of the fields contains names of bands:\n",
    "````\n",
    "ACDC,2,1\n",
    "Lady Gaga,4,3\n",
    "Peter,Paul and Marry,1,3\n",
    "````\n",
    "Are \"Peter\" and \"Paul and Marry\" two different columns? It's obvious to most people that they're not, but not at all obvious to a computer. If you're storing strings and there's chance that you may have commas in those strings, CSVs aren't going to work. Luckily, there are plenty of other more obscure characters that can be used to separatate values. If commas aren't a good choice, then you may choose to go with tabs (represented by \\t):\n",
    "````\n",
    "ACDC\\t2\\t1\n",
    "Lady Gaga\\t4\\t3\n",
    "Peter, Paul and Marry\\t1\\t3\n",
    "````\n",
    "\n",
    "You could write your own CSV Python parser, but a number of Python tools already exist to do this for you. Let's look at how you'd do this with pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "file_path = 'artists.csv'\n",
    "artists_df = pd.read_csv(file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>musicbrainz_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Scenic</td>\n",
       "      <td>a8e347d8-29d8-459c-a444-68febeb99c6b</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Our Family Name</td>\n",
       "      <td>fc93c97f-1b9b-49af-8370-7b36e29ad22e</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Khamelien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Death in the Park</td>\n",
       "      <td>000c60eb-a2c4-46be-9097-e603fd8795c6</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enter the collector</td>\n",
       "      <td>f50c0834-e2a7-4241-bd32-061d67560c41</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name                        musicbrainz_id   id\n",
       "0           The Scenic  a8e347d8-29d8-459c-a444-68febeb99c6b  500\n",
       "1      Our Family Name  fc93c97f-1b9b-49af-8370-7b36e29ad22e  710\n",
       "2            Khamelien                                   NaN  744\n",
       "3    Death in the Park  000c60eb-a2c4-46be-9097-e603fd8795c6  786\n",
       "4  enter the collector  f50c0834-e2a7-4241-bd32-061d67560c41  818"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artists_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you open artists.csv in a text editor, the first few lines look like this:\n",
    "````\n",
    "name,musicbrainz_id,id\n",
    "The Scenic,a8e347d8-29d8-459c-a444-68febeb99c6b,500\n",
    "Our Family Name,fc93c97f-1b9b-49af-8370-7b36e29ad22e,710\n",
    "Khamelien,,744\n",
    "````\n",
    "There are two things worth noting here:\n",
    "\n",
    "1. Khamelien doesn't have a musicbrainz id, but we still need to leave a space for it in the csv. Every row must have the same number of columns, and if we leave a field blank, Pandas will fill it in with the special NaN (Not a Number) value to denote that it's missing.\n",
    "2. By default, pandas assumes that the first line of a CSV contains the column names. If your csv does not have column names in the first line, you can set them yourself with the `names` parameter in the read_csv method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>musicbrainz_id</th>\n",
       "      <th>NBS_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Scenic</td>\n",
       "      <td>a8e347d8-29d8-459c-a444-68febeb99c6b</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Our Family Name</td>\n",
       "      <td>fc93c97f-1b9b-49af-8370-7b36e29ad22e</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Khamelien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Death in the Park</td>\n",
       "      <td>000c60eb-a2c4-46be-9097-e603fd8795c6</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enter the collector</td>\n",
       "      <td>f50c0834-e2a7-4241-bd32-061d67560c41</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name                        musicbrainz_id  NBS_id\n",
       "0           The Scenic  a8e347d8-29d8-459c-a444-68febeb99c6b     500\n",
       "1      Our Family Name  fc93c97f-1b9b-49af-8370-7b36e29ad22e     710\n",
       "2            Khamelien                                   NaN     744\n",
       "3    Death in the Park  000c60eb-a2c4-46be-9097-e603fd8795c6     786\n",
       "4  enter the collector  f50c0834-e2a7-4241-bd32-061d67560c41     818"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artists_df = pd.read_csv(\n",
    "    'artists_no_column_names.csv',\n",
    "    header=None,\n",
    "    names=['name','musicbrainz_id','NBS_id']\n",
    ")\n",
    "artists_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As mentioned before, separating strings with commas can be problematic. It is better to use tabs (\\t). We can use the same `read_csv` pandas method to open tab separated files, but in this case we need to specify that the separaters are tabs using the `sep` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>musicbrainz_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Scenic</td>\n",
       "      <td>a8e347d8-29d8-459c-a444-68febeb99c6b</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Our Family Name</td>\n",
       "      <td>fc93c97f-1b9b-49af-8370-7b36e29ad22e</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Khamelien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Death in the Park</td>\n",
       "      <td>000c60eb-a2c4-46be-9097-e603fd8795c6</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enter the collector</td>\n",
       "      <td>f50c0834-e2a7-4241-bd32-061d67560c41</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name                        musicbrainz_id   id\n",
       "0           The Scenic  a8e347d8-29d8-459c-a444-68febeb99c6b  500\n",
       "1      Our Family Name  fc93c97f-1b9b-49af-8370-7b36e29ad22e  710\n",
       "2            Khamelien                                   NaN  744\n",
       "3    Death in the Park  000c60eb-a2c4-46be-9097-e603fd8795c6  786\n",
       "4  enter the collector  f50c0834-e2a7-4241-bd32-061d67560c41  818"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artists_df = pd.read_csv('artists.tsv', sep='\\t')\n",
    "artists_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's suppose we've made some updates to the data, and now we want to save our results to a new csv."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>musicbrainz_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Scenic</td>\n",
       "      <td>a8e347d8-29d8-459c-a444-68febeb99c6b</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Our Family Name</td>\n",
       "      <td>fc93c97f-1b9b-49af-8370-7b36e29ad22e</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Khamelien</td>\n",
       "      <td>NaN</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Death in the Park</td>\n",
       "      <td>000c60eb-a2c4-46be-9097-e603fd8795c6</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Enter the Collector</td>\n",
       "      <td>f50c0834-e2a7-4241-bd32-061d67560c41</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name                        musicbrainz_id   id\n",
       "0           The Scenic  a8e347d8-29d8-459c-a444-68febeb99c6b  500\n",
       "1      Our Family Name  fc93c97f-1b9b-49af-8370-7b36e29ad22e  710\n",
       "2            Khamelien                                   NaN  744\n",
       "3    Death in the Park  000c60eb-a2c4-46be-9097-e603fd8795c6  786\n",
       "4  Enter the Collector  f50c0834-e2a7-4241-bd32-061d67560c41  818"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artists_df.loc[4,'name'] = 'Enter the Collector'\n",
    "artists_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "artists_df.to_csv('artists_updated.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the `to_csv` method, we write our results to the file `artists_updated.tsv` which can be opened with the `read_csv` method (using the sep='t' paramater) or with any text editor. Note: if we write to a file that already exists, `to_csv` will overwrite the old file with the new."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Text files work well for simple, relatively small, datasets that can live in one or two independant tables and don't need to be updated often. However when the data you're working with is either really big or you have multiple tables that are related to eachother, you might want to to start looking into other solutions. \n",
    "\n",
    "The most common tabular step up from CSVs are realtional databases that use SQL (Structured Query Language). Three advantages of a SQL database over text files are that:\n",
    "\n",
    "1. It's much easier to join two or more tables that share common columns (we'll see an example in a second)\n",
    "2. SQL is very efficient at searching through large data sets and pulling data from multiple tables matching a specific query criteria.\n",
    "3. SQL allows for easy inserting new rows or updating existing rows without needing to overwrite an entire dataset.\n",
    "\n",
    "There are several different implementations of SQL, and at most companies there is a person (or team of people) who manage the setup and maintenance of a SQL databse. Fortunately, the query languages across all implementations of SQL are very similar, so you don't necessarily need to know the nuts and bolts of how the data is stored to get the data you need.\n",
    "\n",
    "If you're new to SQL and don't have the resources to manage a production-level relational database, there's a free and easy to use SQL implementation called sqlite. It requires no setup and comes pre-installed with Python. sqlite is a standalone application that you can access [from the command line](https://sqlite.org/quickstart.html) by typing `sqlite3 /path/to/database` (where `/path/to/database` is the path to a database file. We'll be using the `nbs.db` database that's included in this repo in this tutorial. In your terminal window, change directories [cd] to where the `nbs.db` file is located and run\n",
    "````\n",
    "$ sqlite3 nbs.db\n",
    "````"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You should see a command prompt letting you know that you're using the sqlite command-line program. To see what tables are included in the nbs database use the `.tables` command:\n",
    "````\n",
    "sqlite> .tables\n",
    "artists              networks\n",
    "endpoints            x_artists_endpoints\n",
    "````\n",
    "and to see the columns and types for a table use the `.schema` command:\n",
    "````\n",
    "sqlite> .schema artists\n",
    "CREATE TABLE \"artists\" (\n",
    "\"index\" INTEGER,\n",
    "  \"name\" TEXT,\n",
    "  \"musicbrainz_id\" TEXT,\n",
    "  \"created_at\" TIMESTAMP,\n",
    "  \"updated_at\" TIMESTAMP,\n",
    "  \"deleted_at\" TIMESTAMP,\n",
    "  \"id\" INTEGER\n",
    ");\n",
    "CREATE INDEX \"ix_artists_index\"ON \"artists\" (\"index\");\n",
    "````\n",
    "Now let's query some data. To get the first five rows from the `artists` table, run the following from the command line:\n",
    "````\n",
    "sqlite> SELECT * FROM artists LIMIT 5;\n",
    "````\n",
    "The results should look like this:\n",
    "````\n",
    "The Scenic|a8e347d8-29d8-459c-a444-68febeb99c6b|2009-06-16 07:58:58|2015-05-19 12:28:17||500\n",
    "Our Family Name|fc93c97f-1b9b-49af-8370-7b36e29ad22e|2009-06-16 08:04:18|2014-08-27 09:04:37||710\n",
    "Khamelien||2009-06-16 08:05:08|2014-08-27 09:04:36||744\n",
    "Death in the Park|000c60eb-a2c4-46be-9097-e603fd8795c6|2009-06-16 08:06:10|2014-08-27 09:04:37||786\n",
    "enter the collector|f50c0834-e2a7-4241-bd32-061d67560c41|2009-06-16 08:06:54|2014-08-27 09:04:34||818\n",
    "````\n",
    "To get the first five rows that don't have a musicbrainz id:\n",
    "````\n",
    "sqlite> SELECT * FROM artists WHERE musicbrainz_id IS NULL LIMIT 5;\n",
    "Khamelien||2009-06-16 08:05:08|2014-08-27 09:04:36||744\n",
    "Shorelines End||2009-06-16 08:10:11|2014-08-27 09:04:32||952\n",
    "Dearth||2009-06-16 08:36:24|2014-08-27 09:51:29||1978\n",
    "Redink||2009-06-16 19:45:19|2014-08-27 09:59:37||4297\n",
    "The Atomic Ballroom Calamity||2009-06-16 19:47:00|2014-08-27 09:59:45||4359\n",
    "````\n",
    "and to find an artist named \"Redink\":\n",
    "````\n",
    "sqlite> SELECT * FROM artists WHERE name='The Good Ship';\n",
    "The Good Ship|efe7fd43-c3bb-4bb1-b306-8a92af4348c0|2009-07-09 05:56:18|2014-08-27 09:17:04||208860\n",
    "````\n",
    "\n",
    "In addition to the sqlite command-line tool, we can also query a sqlite database directly from Python. As with CSVs, there are a couple of tools for doing this, and we'll use a tool built directly into pandas. Because pandas is tabular, pulling data from sql and putting it directly into a dataframe is fairly simple.\n",
    "\n",
    "(Note that in the below query you will have to update the url to reflect the path to where nbs.db is stored on your computer.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are 4349 rows in the artists table\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>musicbrainz_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>deleted_at</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Scenic</td>\n",
       "      <td>a8e347d8-29d8-459c-a444-68febeb99c6b</td>\n",
       "      <td>2009-06-16 07:58:58</td>\n",
       "      <td>2015-05-19 12:28:17</td>\n",
       "      <td>None</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Our Family Name</td>\n",
       "      <td>fc93c97f-1b9b-49af-8370-7b36e29ad22e</td>\n",
       "      <td>2009-06-16 08:04:18</td>\n",
       "      <td>2014-08-27 09:04:37</td>\n",
       "      <td>None</td>\n",
       "      <td>710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Khamelien</td>\n",
       "      <td>None</td>\n",
       "      <td>2009-06-16 08:05:08</td>\n",
       "      <td>2014-08-27 09:04:36</td>\n",
       "      <td>None</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Death in the Park</td>\n",
       "      <td>000c60eb-a2c4-46be-9097-e603fd8795c6</td>\n",
       "      <td>2009-06-16 08:06:10</td>\n",
       "      <td>2014-08-27 09:04:37</td>\n",
       "      <td>None</td>\n",
       "      <td>786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enter the collector</td>\n",
       "      <td>f50c0834-e2a7-4241-bd32-061d67560c41</td>\n",
       "      <td>2009-06-16 08:06:54</td>\n",
       "      <td>2014-08-27 09:04:34</td>\n",
       "      <td>None</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name                        musicbrainz_id  \\\n",
       "0           The Scenic  a8e347d8-29d8-459c-a444-68febeb99c6b   \n",
       "1      Our Family Name  fc93c97f-1b9b-49af-8370-7b36e29ad22e   \n",
       "2            Khamelien                                  None   \n",
       "3    Death in the Park  000c60eb-a2c4-46be-9097-e603fd8795c6   \n",
       "4  enter the collector  f50c0834-e2a7-4241-bd32-061d67560c41   \n",
       "\n",
       "            created_at           updated_at deleted_at   id  \n",
       "0  2009-06-16 07:58:58  2015-05-19 12:28:17       None  500  \n",
       "1  2009-06-16 08:04:18  2014-08-27 09:04:37       None  710  \n",
       "2  2009-06-16 08:05:08  2014-08-27 09:04:36       None  744  \n",
       "3  2009-06-16 08:06:10  2014-08-27 09:04:37       None  786  \n",
       "4  2009-06-16 08:06:54  2014-08-27 09:04:34       None  818  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3 as lite\n",
    "import pandas as pd\n",
    "con = lite.connect('nbs.db')\n",
    "query = 'select * from artists'\n",
    "artists_df = pd.read_sql(query,con)\n",
    "con.close()\n",
    "print('there are %s rows in the artists table' % len(artists_df))\n",
    "artists_df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we've created a connection to the sqlite database using the sqlite3 library, and then passed a sql query and our sqlite connection object to the read_sql method to get the query results as a dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:** Try writing your own function to turn a sql query into a pandas dataframe. Your function will take a query string and a sqlite connection object and return a pandas dataframe. You can use any methods from the sqlite module, but use only the pandas dataframe constructor from the pandas library (pd.DataFrame()). \n",
    "\n",
    "*Hint: Here's one way to get column names from from a sqlite query: http://stackoverflow.com/questions/7831371/is-there-a-way-to-get-a-list-of-column-names-in-sqlite*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Non-tabular Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far, all of the ways we've looked at for storing data have assumed that our data is tabular, but sometimes data doesn't fit neatly into rows and columns. Suppose for instance that you had a list of arists and you wanted to store all of their EPs and all of the tracks on those EPs. It is *possible* to store this data is a relational database, but that's not necessarily the best or most intuitive way to do so. \n",
    "\n",
    "An alternative data structure uses key-value mappings. Storing EP data for the bands Green Day and Alkaline Trio in a key-value map might look like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ep_data = {\"Green Day\":{\n",
    "    \"EPs\":{\n",
    "        \"1,000 Hours\":{\n",
    "             \"release_year\":1989,\n",
    "             \"tracks\":[\"1,000 Hours\",\"1,000 Hours\",\"Only of You\",\"The One I Want\"]},\n",
    "        \"Slappy\":{\n",
    "             \"release_year\":1990,\n",
    "             \"tracks\":[\"Paper Lanterns\",\"Why do you Want Him?\",\"409 in Your Coffeemaker\",\"Knowledge\"]},\n",
    "        \"Tune In, Tokyo...\":{\n",
    "         \"release_year\":2001,\n",
    "         \"location\":\"Japan\"}\n",
    "        },\n",
    "    \"artist_id\":1},\n",
    " \"Alkaline Trio\":{\n",
    "    \"EPs\":{\n",
    "        \"For Your Lungs Only\":{\n",
    "             \"release_year\":1998,\n",
    "             \"tracks\":[\"Snake Oil Tanker\",\"Southern Rock\",\"Cooking Wine\",\"For Your Lungs Only\"]},\n",
    "        \"I Lied My Face Off\":{\n",
    "             \"release_year\":1999,\n",
    "             \"tracks\":[\"Goodbye Forever\",\"This Is Getting Over You\",\"Bleeder\",\"I Lied My Face Off\"]},\n",
    "        \"Broken Wing\":{\n",
    "             \"release_year\":2013,\n",
    "             \"tracks\":[\"Balanced On A Shelf\",\"Pocket Knife\",\"Broken Wing\",\"Sun Burns\"]}\n",
    "        },\n",
    "    \"artist_id\":2}}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The key-value pair data structure in Python is called a dictionary (and known as a JSON object in javascript, and goes by other names in other programming languages). Python dictionaries are very flexible. The key can be either a string or a number, and the value can be almost anything.\n",
    "\n",
    "If we want all of the metadata available for the Alkaline Trio album \"For Your Lungs Only\" we just pass in the keys for the nested set of dictionaries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'release_year': 1998,\n",
       " 'tracks': ['Snake Oil Tanker',\n",
       "  'Southern Rock',\n",
       "  'Cooking Wine',\n",
       "  'For Your Lungs Only']}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ep_data['Alkaline Trio']['EPs']['For Your Lungs Only']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and to get just the release year for that album:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1998"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ep_data['Alkaline Trio']['EPs']['For Your Lungs Only']['release_year']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To store a Python dictionary, we can convert to a JSON string and save to a text file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import json\n",
    "ep_data_str = json.dumps(ep_data)\n",
    "f = open('ep_data.json', 'w')\n",
    "f.write(ep_data_str)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After converting the dictionary to a string (using `json.dumps`), the above code block writes that string to the file `ep_data.json` (the last three lines)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MongoDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Storing key-value data as json files is fine for small data sets (less than a few Mb), but once your data grows larger than a 100 Mb, you may want to consider moving it into a queryable storage engine like MongoDB.\n",
    "\n",
    "MongoDB has a Python library and a good tutorial for getting started [link: http://api.mongodb.org/python/current/tutorial.html]. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}