<jupyter_start><jupyter_text>1. SQLite- Is a relational database based on SQL language but optimized for use in small environments There are a couple of ways you could choose to create databases for your projects. 1.1 You could use SQLite's commands to store your parsed texts. For example:<jupyter_code>import sqlite3

connection = sqlite3.connect("mydatabase.db") # you can also call it mydatabase.sqlite

cursor.execute("CREATE TABLE hearing(id INTEGER PRIMARY KEY, title TEXT);")

cursor.execute("INSERT INTO hearing VALUES (1, 'congressional hearing');")

cursor.execute("INSERT INTO hearing VALUES (2, 'senate hearing');")

cursor.commit()

cursor.close()<jupyter_output><empty_output><jupyter_text>1.2  Store your parsed data into CSV files and then convert the CSV files to an SQlite file [Recommended] . Have a look at the CSV files we will use for our experiment today. We can use pandas to get the files and have a peek at what is in each of them.We have 4 files. You can think of them as tables.HearingSpeechSpeakerPerson<jupyter_code>import pandas as pd
hearings = pd.read_csv("csv/hearing.csv")
hearings.head()
speech = pd.read_csv("csv/speech.csv")
speech.head()
speaker = pd.read_csv("csv/speaker.csv")
speaker.head()
person = pd.read_csv("csv/person.csv")
person.head()<jupyter_output><empty_output><jupyter_text>But how do we create CSV files?There are two ways. 1.2.1 Use the datascience module  [Recommended]First thing you need to do is install the datascience module on your terminal<jupyter_code>pip install datascience<jupyter_output><empty_output><jupyter_text>import datascience as dsCreate a table with the desired column namescolumns = ("hearing_id", "committee_id", "subcommittee_id","hearing_title", "is_appropriation", "is_nomination", "date","url","file","extent")hearing_table = ds.Table(columns)Add data to the table. Direct your parser to store data as an array and them add that array into your table.row = ["1", "1", "1", "Example hearing", "1", "0", "2018/01/01", "someurl.gov", None, None]hearing_table = hearing_table.with_row(row)Save the data as a csv filehearing_table.to_csv("sample/hearing_csv_made_using_ds.csv")<jupyter_code>Read the file and see that we've added a new row into the csv file<jupyter_output><empty_output><jupyter_text>person = pd.read_csv("sample/hearing_csv_made_using_ds.csv")person.head()<jupyter_code>### Quiz: What happens when you add an empty array to the table?<jupyter_output><empty_output><jupyter_text>enter your answer here.<jupyter_code>### 1.2.2 Using python's CSV writer<jupyter_output><empty_output><jupyter_text>import csvwith open('sample/hearing_csv_made_using_csv.csv', 'w+') as csvfile:    writer = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)    columns = ("hearing_id", "committee_id", "subcommittee_id","hearing_title", "is_appropriation", "is_nomination", "date","url","file","extent")    writer.writerow(columns)    row = ["1", "1", "1", "Second example hearing", "1", "0", "2018/01/01", "someurl.gov", None, None]    writer.writerow(row) person = pd.read_csv("sample/hearing_csv_made_using_csv.csv")person.head()<jupyter_code>## 2. Now you have your CSV files, what next?
it's now time to create your sqlite database.
There are a couple of ways to do it.
1. Using pandas which is recommended. We will cover this next.

2. You can use the csvs-to-sqlite  library. Install it by running the following lines. then create your sqlite database as follows.<jupyter_output><empty_output><jupyter_text>pip install csvs-to-sqlite csvs-to-sqlite hearing.csv  mydatabase.dbcsvs-to-sqlite speech.csv   mydatabase.dbcsvs-to-sqlite speaker.csv  mydatabase.dbcsvs-to-sqlite person.csv   mydatabase.db<jupyter_code>Let's see how we could use pandas to do this.<jupyter_output><empty_output><jupyter_text>import sqlite3import pandas as pdconnection = sqlite3.connect("mydatabase.sqlite")  create your database here.hearing = pd.read_csv("csv/hearing.csv")hearing.to_sql("hearing", connection, if_exists='append', index=False)speech = pd.read_csv("csv/speech.csv")speech.to_sql("speech", connection, if_exists='append', index=False)speaker = pd.read_csv("csv/speaker.csv")speaker.to_sql("speaker", connection, if_exists='append', index=False)person = pd.read_csv("csv/person.csv")person.to_sql("person", connection, if_exists='append', index=False)<jupyter_code>Now let's read  the contents we have saved.
How many speakers are there?<jupyter_output><empty_output><jupyter_text>cursor = connection.cursor()cursor.execute("SELECT count(*) FROM speaker;")no_of_speakers = cursor.fetchall()print(no_of_speakers)connection.close()  Always a good idea to close the connection<jupyter_code>### Quiz: How many hearings are there?<jupyter_output><empty_output><jupyter_text>Answer here<jupyter_code># Flask

Flask is a lightweight framework fro writing web applications.It's 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.<jupyter_output><empty_output><jupyter_text>pip install Flask<jupyter_code>## 3. Let's create a simple Flask app

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.

Open the app.py file and you'll see the following code.<jupyter_output><empty_output><jupyter_text>!/usr/bin/env pythonimport flask Create the application.app = flask.Flask(__name__)@app.route('/')def index():    return flask.render_template('index.html')if __name__ == '__main__':    app.debug=True    app.run()<jupyter_code>Run<jupyter_output><empty_output><jupyter_text>python app.py<jupyter_code>Then load http://127.0.0.1:5000/on your browser<jupyter_output><empty_output><jupyter_text>Quiz: What do you see when you load the link on your browser?<jupyter_code># answer<jupyter_output><empty_output><jupyter_text>4. Let's pull some data from the DB and access it from the browser Add this to the top of the app.py page<jupyter_code>from flask import Response
import sqlite3
import json<jupyter_output><empty_output><jupyter_text>Add this in the body of the text after hello function<jupyter_code>@app.route('/speakers')
def speakers():
    connection = sqlite3.connect("mydatabase.sqlite") 
    cursor = connection.cursor()
    cursor.execute("SELECT surname FROM speaker;")
    speakers = cursor.fetchall()
    return Response(json.dumps(speakers), mimetype='application/json')<jupyter_output><empty_output><jupyter_text>Quiz: What is the role of @app.route?<jupyter_code>#answer<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1100835322195704, 'fraction_numerical': 0.009099045346062052, 'mean_word_length': 6.5, 'pattern_counts': {'":': 0, '<': 73, '<?xml version=': 0, '>': 73, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '556535', 'license': 'Apache-2.0', 'n_tokens_mistral': 2214, 'n_tokens_neox': 2093, 'n_words': 636, 'path': 'SQLite & Flask/SQLite3 and Flask.ipynb', 'repo_name': 'Goodly/LTD', 'size': '6704'}