LA 4 – Sharing your Research-Ready Database - SQL & Flask.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Phase IV: Storing and Sharing Your Research-Ready Database\n",
    "\n",
    "You have done a lot of heavy lifting and learned a lot. And you have a really useful set of data to show for it. Now the question becomes: how do you want tostore and share all this data? \n",
    "\n",
    "Throughout these tutorials, we have guided you step-by-step through the processes and code necessary to make your database research-ready. Here, our goals are more limited. Because there are dozens (or more) ways you could store and share your data, we aren't able to show you all of them. Instead, we will focus on a brief introduction into our choice, namely storing the data in an SQLite database and sharing it via a python server using a library called Flask. Again, this is just one approach that we found practical. It may also be applicable for many of you though certainly not all of you."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Creating a SQLite database\n",
    "\n",
    "In the last phase you learned to spread out your data into a series of tables. Each table with a more managable size than the alternative of storing it all in one gigantic table. One tool to manage such a relational database system is SQLite. SQLite is based on the omnipresent SQL language but optimized for use in (relatively) small environments.\n",
    "\n",
    "There are two ways to store our freshly-parsed text into an SQLite database that we will introduce here. The first is writing directly into an ever-growing SQLite database as you iterate through your documents. The second solution is to create CSV files as an intermediate storage, and only after completing the iterative parsing convert the completed CSV files into an SQLite database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option A: Extending the database as you iterate through the documents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To build your database, you could use SQLite's commands to store your parsed texts at the end of parsing each document. \n",
    "\n",
    "To do this, we first have to create a database that we can later write into. In our simple example here, we will create a database with one table only, called 'hearing'. We send all our commands using an object called 'cursor', the controller we can use to manipulate our database.\n",
    "\n",
    "Note that the commands insided the brackets of ``cursor.execute('...')`` are written in SQL. Click <a href=\"https://www.w3schools.com/sql/\" target=\"_blank\">here</a> for a very good hands-on SQL tutorial."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import sqlite3\n",
    "\n",
    "### creating a database, you can also call it 'mydatabase.sqlite'\n",
    "connection = sqlite3.connect(\"mydatabase.db\")\n",
    "\n",
    "### Adding a table 'hearing' with two columns ('id' which includes an integer number and 'title' which includes text)\n",
    "cursor.execute(\"CREATE TABLE hearing(id INTEGER PRIMARY KEY, title TEXT);\")\n",
    "\n",
    "###  storing and closing\n",
    "cursor.commit()\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have now created our simple SQLite databse including a rudimentary 'hearing' table. The plan now is to fill it consecutively with every document as soon as it has been parsed. \n",
    "\n",
    "To do that, we would go to the code position at the end of a document loop. There we add a few commands to reconnect to our SQLite database, take what has been found in the freshly-parsed document and add it to the hearing table. Before we turn to the next document, we store the updated datbase and close the connection again.\n",
    "\n",
    "The code passage would looks similar to what follows:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "connection = sqlite3.connect(\"mydatabase.db\")\n",
    "\n",
    "### Adding a new line with id '1' and title 'congressional hearing'\n",
    "cursor.execute(\"INSERT INTO hearing VALUES (1, 'congressional hearing');\")\n",
    "\n",
    "### Adding another line with id '2' and title 'senate hearing'\n",
    "cursor.execute(\"INSERT INTO hearing VALUES (2, 'senate hearing');\")\n",
    "\n",
    "cursor.commit()\n",
    "\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Option B: Store in CSV first, then convert it all in one go [Recommended]\n",
    "\n",
    "To see where this is going, let's have a look at a few sample CSV files that include text as parsed in Phase III.\n",
    "\n",
    "We have four files and you can think of them as four separate tables inside our relational database.\n",
    "\n",
    "Hearing\n",
    "\n",
    "Speech\n",
    "\n",
    "Speaker\n",
    "\n",
    "Person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "hearings = pd.read_csv(\"data/hearing.csv\")\n",
    "hearings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speech = pd.read_csv(\"data/speech.csv\")\n",
    "speech.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speaker = pd.read_csv(\"data/speaker.csv\")\n",
    "speaker.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "person = pd.read_csv(\"data/person.csv\")\n",
    "person.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Creating CSV files\n",
    "But how did we get to these CSVs in the first place. Again, there are various options. Our preferred one uses python's 'datascience' library (install it typing 'pip install datascience' into your terminal, if you have not done so already).\n",
    "\n",
    "Using the datascience library we can create a CSV file in three steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datascience as ds\n",
    "\n",
    "### Create a table with the desired column names\n",
    "columns = (\"hearing_id\", \"committee_id\", \"subcommittee_id\",\"hearing_title\", \"is_appropriation\", \"is_nomination\", \"date\",\"url\",\"file\",\"extent\")\n",
    "hearing_table = ds.Table(columns)\n",
    "\n",
    "### Add data to the table. Direct your parser to store data as an array and them add that array into your table.\n",
    "row = [\"1\", \"1\", \"1\", \"Example hearing\", \"1\", \"0\", \"2018/01/01\", \"someurl.gov\", None, None]\n",
    "hearing_table = hearing_table.with_row(row)\n",
    "\n",
    "### Save the data as a csv file\n",
    "hearing_table.to_csv(\"sample/hearing_csv_made_using_ds.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now read the file and see that we've added a new row into the csv file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "person = pd.read_csv(\"sample/hearing_csv_made_using_ds.csv\")\n",
    "person.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quiz: What happens when you add an empty array to the table?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Enter your answer here."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now you have your CSV files, what next?\n",
    "\n",
    "It's now time to create your SQLite database. Here, we will be using python library called 'pandas' to help us do the conversion.\n",
    "\n",
    "Before pandas can write into our database, we have to reconnect to our database first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "\n",
    "connection = sqlite3.connect(\"mydatabase.sqlite\")\n",
    "\n",
    "### Loading our table CSVs\n",
    "hearing = pd.read_csv(\"data/hearing.csv\")\n",
    "speech = pd.read_csv(\"data/speech.csv\")\n",
    "speaker = pd.read_csv(\"data/speaker.csv\")\n",
    "person = pd.read_csv(\"data/person.csv\")\n",
    "\n",
    "### Converting each table to SQL using the 'connection', \n",
    "### appending (rather than replacing) existing data and supplying the indexing ID (instead of having panda generate a new one)\n",
    "hearing.to_sql(\"hearing\", connection, if_exists='append', index=False)\n",
    "speech.to_sql(\"speech\", connection, if_exists='append', index=False)\n",
    "speaker.to_sql(\"speaker\", connection, if_exists='append', index=False)\n",
    "person.to_sql(\"person\", connection, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's read the contents we have added. Let's do this with a simply query. How many speakers are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()\n",
    "\n",
    "cursor.execute(\"SELECT count(*) FROM speaker;\")\n",
    "\n",
    "no_of_speakers = cursor.fetchall()\n",
    "\n",
    "print(no_of_speakers)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Quiz: How many hearings are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Try it here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Always a good idea to close a connection when done.\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Sharing your data online: Flask\n",
    "\n",
    "Flask is a lightweight framework for writing web applications. It is called 'lightweight' because it has little to no dependency on external libraries for it to function. You write the code in python and you can run your apps locally or on the server. To install Flask, run the following on your terminal:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "pip install Flask"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's create a simple Flask app\n",
    "\n",
    "When coding and running Flask, you can no longer use jupyter notebooks. So for this section, follow along the instructions to see how you can run an example of Flask.\n",
    "\n",
    "Using a text editor, open the app.py file in the root folder of our tutorial. There, you'll see the following code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!/usr/bin/env python\n",
    "\n",
    "import flask\n",
    "\n",
    "# Create the application.\n",
    "app = flask.Flask(__name__)\n",
    "\n",
    "@app.route('/')\n",
    "def index():\n",
    "    return flask.render_template('index.html')\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app.debug=True\n",
    "    app.run()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using your terminal, you can run this app by typing:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "python app.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then load 'http://127.0.0.1:5000/' on your browser where you will see rudimentary webapp."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's pull some data from the DB and access it from the browser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add this to the top of the app.py code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from flask import Response\n",
    "import sqlite3\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add this in the body of the text:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@app.route('/speakers')\n",
    "def speakers():\n",
    "    connection = sqlite3.connect(\"mydatabase.sqlite\") \n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(\"SELECT surname FROM speaker;\")\n",
    "    speakers = cursor.fetchall()\n",
    "    return Response(json.dumps(speakers), mimetype='application/json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now restart your app and see a rudimentary search function for this basic data.\n",
    "\n",
    "You already saw how little coding is necessary to get started. To move one, we recommend looking at <a href=\"https://pythonspot.com/flask-web-app-with-python/\" target=\"_blank\">this beginner's introduction</a>. Add in some HTML, or even <a href=\"https://bokeh.pydata.org/\" target=\"_blank\">Bokeh</a>, and things get pretty (as well as) useful very fast."
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}