<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Lecture 18
Wednesday, November 8th, 2017
Databases with SQlite
SQLite Exercises
Today you will work with the candidates and contributors datasets to create a database in Python using SQLite.
The exercises will consist of a sequence of steps to help illustrate basic commands.
<a id='deliverables'></a>
Exercise Deliverables
Create a Jupyter notebook called Exercises-Final.ipynb inside the L18 directory.  This is the one we will grade.
For each step in this lecture, there were instructions labeled "Do the following
Step1:  We will also use a basic a pandas feature to display tables in the database.  Although this lecture isn't on pandas, I will still have you use it a little bit.
Step2:  Now we create the tables in the database (just like last time).
Step3:  <a id='step_1'></a>
Step 1
Read candidates.txt and contributors.txt and insert their values into the respective tables.
Step4:  <a id='interlude'></a>
Interlude
Now that you have values in the tables of the database, it would be convenient to be able to visualize those tables in some way.  We'll write a little helper function to accomplish this.
Step5:  Here's how we can use our helper function.  It gives a pretty nice visualization of our table.  You should do the same thing with the contributors table.
Step6:  <a id='step_2'></a>
Step 2
Step7:  We can also see how many entries satisfy the query
Step8:  Do the following queries
Step9:  Do the following sorts on the contributors table
Step10:  Using the DISTINCT clause, you remove duplicate rows.
Step11:  Do the following
Step12:  What if we want to rename or delete a columm?  It can't be done with SQLite with a single command.  We need to follow some roundabout steps (see SQLite ALTER TABLE).  We won't consider this case at the moment.
For now, let's put a few commands together to populate the full_name column.
Step13:  Here's another update, this time on an existing column.
Step14:  Do the following
Step15:  Do the following
<ASSISTANT_TASK:>
Python Code:
import sqlite3
Explanation: Lecture 18
Wednesday, November 8th, 2017
Databases with SQlite
SQLite Exercises
Today you will work with the candidates and contributors datasets to create a database in Python using SQLite.
The exercises will consist of a sequence of steps to help illustrate basic commands.
<a id='deliverables'></a>
Exercise Deliverables
Create a Jupyter notebook called Exercises-Final.ipynb inside the L18 directory.  This is the one we will grade.
For each step in this lecture, there were instructions labeled "Do the following:".  Put all the code from those instructions in a single Jupyter notebook cell.  It should look like a Python script.  You must comment where appropriate to demonstrate that you understand what you are doing.
Save and close your database.  Be sure to upload your database with the lecture exercises.  You must name your database L18DB.sqlite.
Table of Contents
Setting the Stage
Step 1
Interlude: Not required but highly recommended.
Step 2
Step 3
Step 4
Step 5
Step 6
Step 7
Step 8
<a id='setting_the_stage'></a>
Setting the Stage
You should import sqlite3 again like last time.
End of explanation
import pandas as pd
pd.set_option('display.width', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.notebook_repr_html', True)
Explanation: We will also use a basic a pandas feature to display tables in the database.  Although this lecture isn't on pandas, I will still have you use it a little bit.
End of explanation
db = sqlite3.connect('L18DB_demo.sqlite')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS candidates")
cursor.execute("DROP TABLE IF EXISTS contributors")
cursor.execute("PRAGMA foreign_keys=1")
cursor.execute('''CREATE TABLE candidates (
               id INTEGER PRIMARY KEY NOT NULL, 
               first_name TEXT, 
               last_name TEXT, 
               middle_init TEXT, 
               party TEXT NOT NULL)''')
db.commit() # Commit changes to the database
cursor.execute('''CREATE TABLE contributors (
          id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
          last_name TEXT, 
          first_name TEXT, 
          middle_name TEXT, 
          street_1 TEXT, 
          street_2 TEXT, 
          city TEXT, 
          state TEXT, 
          zip TEXT, 
          amount REAL, 
          date DATETIME, 
          candidate_id INTEGER NOT NULL, 
          FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')
db.commit()
Explanation: Now we create the tables in the database (just like last time).
End of explanation
with open ("candidates.txt") as candidates:
    next(candidates) # jump over the header
    for line in candidates.readlines():
        cid, first_name, last_name, middle_name, party = line.strip().split('|')
        vals_to_insert = (int(cid), first_name, last_name, middle_name, party)
        cursor.execute('''INSERT INTO candidates 
                  (id, first_name, last_name, middle_init, party)
                  VALUES (?, ?, ?, ?, ?)''', vals_to_insert)
with open ("contributors.txt") as contributors:
    next(contributors)
    for line in contributors.readlines():
        cid, last_name, first_name, middle_name, street_1, street_2, \
            city, state, zip_code, amount, date, candidate_id = line.strip().split('|')
        vals_to_insert = (last_name, first_name, middle_name, street_1, street_2, 
                          city, state, int(zip_code), amount, date, candidate_id)
        cursor.execute('''INSERT INTO contributors (last_name, first_name, middle_name, 
                           street_1, street_2, city, state, zip, amount, date, candidate_id) 
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', vals_to_insert)
Explanation: <a id='step_1'></a>
Step 1
Read candidates.txt and contributors.txt and insert their values into the respective tables.
End of explanation
def viz_tables(cols, query):
    q = cursor.execute(query).fetchall()
    framelist = []
    for i, col_name in enumerate(cols):
        framelist.append((col_name, [col[i] for col in q]))
    return pd.DataFrame.from_items(framelist)
Explanation: <a id='interlude'></a>
Interlude
Now that you have values in the tables of the database, it would be convenient to be able to visualize those tables in some way.  We'll write a little helper function to accomplish this.
End of explanation
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
query = '''SELECT * FROM candidates'''
viz_tables(candidate_cols, query)
Explanation: Here's how we can use our helper function.  It gives a pretty nice visualization of our table.  You should do the same thing with the contributors table.
End of explanation
query = '''SELECT * FROM candidates WHERE middle_init <> ""'''
viz_tables(candidate_cols, query)
Explanation: <a id='step_2'></a>
Step 2:  Various Queries
We can query our database for entries with certain characteristics.  For example, we can query the candidates table for entries who's middle name fields are not empty.
End of explanation
print("{} candidates have a middle initial.".format(viz_tables(candidate_cols, query).shape[0]))
Explanation: We can also see how many entries satisfy the query:
End of explanation
query = '''SELECT * FROM candidates ORDER BY id DESC'''
viz_tables(candidate_cols, query)
Explanation: Do the following queries:
Display the contributors where the state is "PA"
Display the contributors where the amount contributed is greater than $\$1000.00$.
Display the contributors from "UT" where the amount contributed is greater than $\$1000.00$.
Display the contributors who didn't list their state
Hint:  Match state to the empty string
Display the contributors from "WA" and "PA"
Hint:  You will need to use IN ("WA", "PA") in your SELECT statement.
Display the contributors who contributed between $\$100.00$ and $\$200.00$.
Hint: You can use the BETWEEN 100.00 and 200.00 clause.
<a id='step_3'></a>
Step 3:  Sorting
It could be beneficial to sort by one of the attributes in the database.  The following cell contains a basic sorting demo.
End of explanation
query = '''SELECT last_name, party FROM candidates'''
viz_tables(['last_name', 'party'], query)
Explanation: Do the following sorts on the contributors table:
Sort the contributors table by last_name.
Sort by the amount in decending order where amount is restricted to be between $\$1000.00$ and $\$5000.00$.
Sort the contributors who donted between $\$1000.00$ and $\$5000.00$ by candidate_id and then by amount in descending order.
Hint:  Multiple orderings can be accomplished by separating requests after ORDER BY with commas.
e.g. ORDER BY amount ASC, last_name DESC
<a id='step_4'></a>
Step 4:  Selecting Columns
So far, we've been selecting all columns from a table (i.e. SELECT * FROM).  Often, we just want to select specific columns (e.g. SELECT amount FROM).
End of explanation
query = '''SELECT DISTINCT party FROM candidates'''
viz_tables(['party'], query)
Explanation: Using the DISTINCT clause, you remove duplicate rows.
End of explanation
cursor.execute('''ALTER TABLE candidates ADD COLUMN full_name TEXT''')
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
viz_tables(candidate_cols, '''SELECT * FROM candidates''')
Explanation: Do the following:
Get the first and last name of contributors.  Make sure each row has distinct values.
<a id='step_5'></a>
Step 5:  Altering Tables
The ALTER clause allows us to modify tables in our database.  Here, we had a new column to our candidates table called nick_name.
End of explanation
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")] # regenerate columns with full_name
query = '''SELECT id, last_name, first_name FROM candidates''' # Select a few columns
full_name_and_id = [(attr[1] + ", " + attr[2], attr[0]) for attr in cursor.execute(query).fetchall()] # List of tuples: (full_name, id)
update = '''UPDATE candidates SET full_name = ? WHERE id = ?''' # Update the table
for rows in full_name_and_id:
    cursor.execute(update, rows)
query = '''SELECT * FROM candidates'''
viz_tables(candidate_cols, query)
Explanation: What if we want to rename or delete a columm?  It can't be done with SQLite with a single command.  We need to follow some roundabout steps (see SQLite ALTER TABLE).  We won't consider this case at the moment.
For now, let's put a few commands together to populate the full_name column.
End of explanation
update = '''UPDATE candidates SET full_name = "Eventual Winner" WHERE last_name = "Obama"'''
cursor.execute(update)
update = '''UPDATE candidates SET full_name = "Eventual Loser" WHERE last_name = "McCain"'''
cursor.execute(update)
viz_tables(candidate_cols, query)
Explanation: Here's another update, this time on an existing column.
End of explanation
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")] # You've already done this part.  I just need to do it here b/c I haven't yet.
function = '''SELECT *, MAX(amount) AS max_amount FROM contributors'''
pcursor.execute(function)
Explanation: Do the following:
Add a new column to the contributors table called full_name.  The value in that column should be in the form last_name, first_name.
Change the value in the full_name column to the string "Too Much" if someone donated more than $\$1000.00$.
<a id='step_6'></a>
Step 6: Aggregation
You can perform some nice operations on the values in the database.  For example, you can compute the maximum, minimum, and sum of a set.  You can also count the number of items in a given set.  Here's a little example.  You can do the rest.
End of explanation
query = '''SELECT * FROM candidates LIMIT 3'''
viz_tables(candidate_cols, query)
query = '''SELECT * FROM candidates LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)
query = '''SELECT * FROM candidates ORDER BY last_name LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)
Explanation: Do the following:
Count how many donations there were above $\$1000.00$.
Calculate the average donation.
Calculate the average contribution from each state and display in a table.
Hint:  Use code that looks like: 
python
  "SELECT state,SUM(amount) FROM contributors GROUP BY state"
<a id='step_7'></a>
Step 7: DELETE
We have already noted that SQLite can't drop columns in a straightfoward manner.  However, it can delete rows quite simply.  Here's the syntax:
python
deletion = '''DELETE FROM table_name WHERE condition'''
Do the following:
Delete rows in the contributors table with last name "Ahrens".
<a id='step_8'></a>
Step 8:  LIMIT
The LIMIT clause offers convenient functionality.  It allows you to constrain the number of rows returned by your query.  It shows up in many guises.
End of explanation
<END_TASK>