Key Word(s): Databases, SQL

Download Notebook

Lecture 22

Thursday, November 19th, 2019

Databases II

SQLite Exercises

Today you will work with the candidates and contributors datasets to create a database in Python using SQLite. This is a hands-on lecture. The result will be a Python file.

The exercises will consist of a sequence of steps to help illustrate basic commands.

Exercise Deliverables

  1. Make a copy of this notebook and call it L22_Exercises.ipynb.
  2. For each step in this lecture notebook, there are instructions labeled "Do the following:". Put all the code from those instructions in a single cell immediately following the instructions. It should look like a Python script. You must comment where appropriate to demonstrate that you understand what you are doing.
    • Note: To get the pandas tables to display in a cell, use display().
  3. Save and close your database. Be sure to upload your database with the lecture exercises. You must name your database L22DB.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

Setting the Stage

You should import sqlite3 again like last time.

In [ ]:
import sqlite3

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.

In [ ]:
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)

from IPython.display import display

Now we create the tables in the database (just like last time).

In [ ]:
db = sqlite3.connect('L22DB_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()

Step 1

Read candidates.txt and contributors.txt and insert their values into the respective tables.

In [ ]:
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)
In [ ]:
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)

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.

In [ ]:
def viz_tables(cols, query):
    q = cursor.execute(query).fetchall()
    framelist = dict()
    for i, col_name in enumerate(cols):
        framelist[col_name] = [row[i] for row in q]
    return pd.DataFrame.from_dict(framelist)

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.

In [ ]:
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
query = '''SELECT * FROM candidates'''
viz_tables(candidate_cols, query)

Step 2: Various Queries

We can query our database for entries with certain characteristics. For example, we can query the candidates table for entries whose middle name fields are not empty.

In [ ]:
query = '''SELECT * FROM candidates WHERE middle_init <> ""'''
viz_tables(candidate_cols, query)

We can also see how many entries satisfy the query:

In [ ]:
print("{} candidates have a middle initial.".format(viz_tables(candidate_cols, query).shape[0]))

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" or "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.

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.

In [ ]:
query = '''SELECT * FROM candidates ORDER BY id DESC'''
viz_tables(candidate_cols, query)

Do the following sorts on the contributors table:

  • Sort the candidates table by last_name.
  • Sort the contributors table 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

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).

In [ ]:
query = '''SELECT last_name, party FROM candidates'''
viz_tables(['last_name', 'party'], query)

Using the DISTINCT clause, you remove duplicate rows.

In [ ]:
query = '''SELECT DISTINCT party FROM candidates'''
viz_tables(['party'], query)

Do the following:

  • Get the first and last name of contributors. Make sure each row has distinct values.

Step 5: Altering Tables

The ALTER clause allows us to modify tables in our database. Here, we add a new column to our candidates table called full_name.

In [ ]:
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''')

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.

In [ ]:
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)

Here's another update, this time on an existing column.

In [ ]:
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 = "Romney"'''
cursor.execute(update)
viz_tables(candidate_cols, query)

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$.

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.

In [ ]:
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'''
viz_tables(contributor_cols, function)

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: 
    "SELECT state,SUM(amount) FROM contributors GROUP BY state"

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:

deletion = '''DELETE FROM table_name WHERE condition'''

Do the following:

  • Delete rows in the contributors table with last name "Ahrens".

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.

In [ ]:
query = '''SELECT * FROM candidates LIMIT 3'''
viz_tables(candidate_cols, query)
In [ ]:
query = '''SELECT * FROM candidates LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)
In [ ]:
query = '''SELECT * FROM candidates ORDER BY last_name LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)

Do the following:

  • Query and display the ten most generous donors.
  • Query and display the ten least generous donors who donated a positive amount of money (since the data we have has some negative numbers in it...).

Save

Don't forget to save all of these changes to your database using db.commit(). Before closing shop, be sure to close the database connection with db.close().