Key Word(s): SQL
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. For once, you may do these tasks in a Jupyter notebook.
The exercises will consist of a sequence of steps to help illustrate basic commands.
Exercise Deliverables¶
- Make a copy of this notebook and call it
L22_Exercises.ipynb.
- For each step in this lecture notebook, there are instructions labeled "Do the following:" (except for Step 1 and Interlude). 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()
.
- Note: To get the pandas tables to display in a cell, use
- Save and close your database. Be sure to upload your database with the lecture exercises. You must name your database
L21DB.sqlite
.
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.
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).
db = sqlite3.connect('L20DB_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()
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)
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.
def viz_tables(cols, query):
q = cursor.execute(query).fetchall()
framelist = dict()
for i, col_name in enumerate(cols):
framelist[col_name] = [col[i] for col 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.
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
query = '''SELECT * FROM candidates'''
display(viz_tables(candidate_cols, query))
id | first_name | last_name | middle_init | party | |
---|---|---|---|---|---|
0 | 16 | Mike | Huckabee | R | |
1 | 20 | Barack | Obama | D | |
2 | 22 | Rudolph | Giuliani | R | |
3 | 24 | Mike | Gravel | D | |
4 | 26 | John | Edwards | D | |
5 | 29 | Bill | Richardson | D | |
6 | 30 | Duncan | Hunter | R | |
7 | 31 | Dennis | Kucinich | D | |
8 | 32 | Ron | Paul | R | |
9 | 33 | Joseph | Biden | D | |
10 | 34 | Hillary | Clinton | R. | D |
11 | 35 | Mitt | Romney | R | |
12 | 36 | Samuel | Brownback | R | |
13 | 37 | John | McCain | R | |
14 | 38 | Tom | Tancredo | R | |
15 | 39 | Christopher | Dodd | J. | D |
16 | 41 | Fred | Thompson | D. | R |
Do the following:¶
Display the contributors table.
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]
query = '''SELECT * FROM contributors'''
viz_tables(contributor_cols, query)
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | Agee | Steven | 549 Laurel Branch Road | Floyd | VA | 24091 | 500.0 | 2007-06-30 | 16 | ||
1 | 2 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 250.0 | 2007-05-16 | 16 | ||
2 | 3 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 50.0 | 2007-06-18 | 16 | ||
3 | 4 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 100.0 | 2007-06-21 | 16 | ||
4 | 5 | Akin | Charles | 10187 Sugar Creek Road | Bentonville | AR | 72712 | 100.0 | 2007-06-16 | 16 | ||
5 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | ||
6 | 7 | Akin | Rebecca | 181 Baywood Lane | Monticello | AR | 71655 | 500.0 | 2007-05-18 | 16 | ||
7 | 8 | Aldridge | Brittni | 808 Capitol Square Place, SW | Washington | DC | 20024 | 250.0 | 2007-06-06 | 16 | ||
8 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | ||
9 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | ||
10 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | ||
11 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | ||
12 | 13 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 200.0 | 2007-06-12 | 16 | ||
13 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | ||
14 | 15 | Andres | Dale | 1160 Glen Oaks Drive | West Des Moines | IA | 50266 | 250.0 | 2007-06-06 | 16 | ||
15 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | ||
16 | 17 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 500.0 | 2007-04-08 | 16 | ||
17 | 18 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 100.0 | 2007-06-22 | 16 | ||
18 | 19 | Ardle | William | 412 Dakota Avenue | Springfield | OH | 45504 | 50.0 | 2007-06-28 | 16 | ||
19 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | ||
20 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | ||
21 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | ||
22 | 23 | Bancroft | David | 2934 Broderick Street | San Francisco | CA | 94123 | 250.0 | 2007-04-24 | 16 | ||
23 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | ||
24 | 25 | Barbee | John | 516 Kellyridge Drive | Apex | NC | 27502 | 500.0 | 2007-05-23 | 16 | ||
25 | 26 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 50.0 | 2007-07-30 | 20 | ||
26 | 27 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 25.0 | 2007-08-16 | 20 | ||
27 | 28 | Buckheit | Bruce | 8904 KAREN DR | FAIRFAX | VA | 220312731 | 100.0 | 2007-09-19 | 20 | ||
28 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | ||
29 | 30 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | -2300.0 | 2007-08-14 | 20 | ||
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
145 | 146 | ABDELLA | THOMAS | M. | 4231 MONUMENT WALL WAY #340 | FAIRFAX | VA | 220308440 | 50.0 | 2007-09-30 | 35 | |
146 | 147 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 100.0 | 2007-09-29 | 35 | |
147 | 148 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 50.0 | 2007-08-09 | 35 | |
148 | 149 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-09-15 | 35 | |
149 | 150 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-08-15 | 35 | |
150 | 151 | ABEDIN | ZAINUL | 715 N. CENTRAL AVENUE | SUITE 212 | GLENDALE | CA | 912031164 | 500.0 | 2008-01-21 | 37 | |
151 | 152 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 75.0 | 2008-01-08 | 37 | |
152 | 153 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 50.0 | 2008-01-08 | 37 | |
153 | 154 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 200.0 | 2008-01-31 | 37 | |
154 | 155 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 100.0 | 2008-01-08 | 37 | |
155 | 156 | ABBOTT | ROBERT | A. | 3061 LOREE ROAD | DECKERVILLE | MI | 484279763 | 500.0 | 2008-01-21 | 37 | |
156 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | |
157 | 158 | ABBOT | DAVID | M. | 56 SALEM STREET | ANDOVER | MA | 18102114 | 200.0 | 2008-01-21 | 37 | |
158 | 159 | ABBO | PAULINE | MORENCY | 10720 JACOB LANE | WHITE LAKE | MI | 483862274 | 35.0 | 2008-01-07 | 37 | |
159 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | |
160 | 161 | ABAIR | PETER | 40 EVANS STREET | WATERTOWN | MA | 24722150 | 25.0 | 2008-01-09 | 37 | ||
161 | 162 | ABACHERLI | SHIRLEY | M. | 29875 NEWPORT ROAD | MENIFEE | CA | 925849524 | 150.0 | 2008-01-28 | 37 | |
162 | 163 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 300.0 | 2008-01-30 | 37 | ||
163 | 164 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 410.0 | 2008-01-15 | 37 | ||
164 | 165 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 500.0 | 2008-01-09 | 37 | ||
165 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 | |
166 | 167 | ABEL | MARLING | L. | 14 HANGING MOSS LANE | GREENVILLE | SC | 296155069 | 100.0 | 2008-01-22 | 37 | |
167 | 168 | ABEL | RUDOLPH | 4532 OCEAN BLVD. | # 108 | SARASOTA | FL | 342421337 | 100.0 | 2008-01-08 | 37 | |
168 | 169 | ABELE | RODNEY | 3620 METAIRIE HEIGHTS AVENUE | METAIRIE | LA | 700021823 | 500.0 | 2008-01-15 | 37 | ||
169 | 170 | ABERCROMBIE | DENIS | 11811 WATER OAK CT | MAGNOLIA | TX | 773546270 | 500.0 | 2008-01-30 | 37 | ||
170 | 171 | ABESHAUS | MERRILL | M. | 1801 N. HEREFORD DRIVE | FLAGSTAFF | AZ | 860011121 | 120.0 | 2008-01-16 | 37 | |
171 | 172 | ABRAHAM | GEORGE | P.O. BOX 1504 | LAKE CHARLES | LA | 706021504 | 800.0 | 2008-01-17 | 37 | ||
172 | 173 | ABRAHAMSON | PETER | J. | 1030 W. ROSCOE STREET | CHICAGO | IL | 606572207 | 50.0 | 2008-01-25 | 37 | |
173 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 | |
174 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 |
175 rows × 12 columns
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.
query = '''SELECT * FROM candidates WHERE middle_init <> ""'''
display(viz_tables(candidate_cols, query))
id | first_name | last_name | middle_init | party | |
---|---|---|---|---|---|
0 | 34 | Hillary | Clinton | R. | D |
1 | 39 | Christopher | Dodd | J. | D |
2 | 41 | Fred | Thompson | D. | R |
We can also see how many entries satisfy the query:
print("{} candidates have a middle initial.".format(viz_tables(candidate_cols, query).shape[0]))
3 candidates have a middle initial.
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
- Hint: Match
- Display the contributors from "WA" and "PA"
- Hint: You will need to use
IN ("WA", "PA")
in yourSELECT
statement.
- Hint: You will need to use
- Display the contributors who contributed between $\$100.00$ and $\$200.00$.
- Hint: You can use the
BETWEEN 100.00 and 200.00
clause.
- Hint: You can use the
# State is PA
query = '''SELECT * FROM contributors WHERE state="PA"'''
display(viz_tables(contributor_cols, query))
# Contributed > $1000.0
query = '''SELECT * FROM contributors WHERE amount>1000.00'''
display(viz_tables(contributor_cols, query))
query = '''SELECT * FROM contributors WHERE state="UT" AND amount>1000.00'''
display(viz_tables(contributor_cols, query))
# Contributors who didn't list their state
query = '''SELECT * FROM contributors WHERE state =""'''
display(viz_tables(contributor_cols, query))
# Contributors from WA and PA
query = '''SELECT * FROM contributors WHERE state IN ('WA', 'PA')'''
display(viz_tables(contributor_cols, query))
# Contributors who contributed between $100.00 and $200.00
query = '''SELECT * FROM contributors WHERE amount BETWEEN 100.00 and 200.00'''
display(viz_tables(contributor_cols, query))
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 71 | BUCKLEY | WALTER | W. | 1635 COUNTRY ROAD | BETHLEHEM | PA | 180155718 | -100.0 | 2008-03-05 | 22 | |
1 | 72 | BUCKLEY | MARJORIE | B. | 1635 COUNTRY ROAD | BETHLEHEM | PA | 180155718 | -100.0 | 2008-03-05 | 22 | |
2 | 94 | Raught | Philip | M | 4714 Plum Way | Pittsburgh | PA | 15201 | -1046.0 | 2008-04-21 | 32 | |
3 | 95 | Ferrara | Judith | D | 1508 Waterford Road | Yardley | PA | 19067 | -1100.0 | 2008-04-21 | 32 | |
4 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | ||
1 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | ||
2 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | ||
3 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | ||
4 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | ||
5 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | ||
6 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | ||
7 | 34 | Buck | Blaine | M | 45 Eaton Ave | Camden | ME | 48431752 | 2300.0 | 2007-09-30 | 20 | |
8 | 46 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 1300.0 | 2007-08-09 | 20 | ||
9 | 136 | ABRAMOWITZ | NIRA | 411 HARBOR ROAD | SOUTHPORT | CT | 68901376 | 2300.0 | 2007-09-14 | 35 | ||
10 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | |
11 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | ||
1 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 126 | BOURNE | TRAVIS | LAGE KAART 77 | BRASSCHATT | 2930 | -500.0 | 2008-11-20 | 35 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 63 | BURKE | SUZANNE | M. | 3401 EVANSTON | SEATTLE | WA | 981038677 | -700.0 | 2008-03-05 | 22 | |
1 | 71 | BUCKLEY | WALTER | W. | 1635 COUNTRY ROAD | BETHLEHEM | PA | 180155718 | -100.0 | 2008-03-05 | 22 | |
2 | 72 | BUCKLEY | MARJORIE | B. | 1635 COUNTRY ROAD | BETHLEHEM | PA | 180155718 | -100.0 | 2008-03-05 | 22 | |
3 | 94 | Raught | Philip | M | 4714 Plum Way | Pittsburgh | PA | 15201 | -1046.0 | 2008-04-21 | 32 | |
4 | 95 | Ferrara | Judith | D | 1508 Waterford Road | Yardley | PA | 19067 | -1100.0 | 2008-04-21 | 32 | |
5 | 101 | Aaronson | Rebecca | 2000 Village Green Dr Apt 12 | Mill Creek | WA | 980125787 | 100.0 | 2008-02-08 | 34 | ||
6 | 107 | Aaronson | Rebecca | 2000 Village Green Dr Apt 12 | Mill Creek | WA | 980125787 | 100.0 | 2008-02-14 | 34 | ||
7 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 4 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 100.0 | 2007-06-21 | 16 | ||
1 | 5 | Akin | Charles | 10187 Sugar Creek Road | Bentonville | AR | 72712 | 100.0 | 2007-06-16 | 16 | ||
2 | 13 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 200.0 | 2007-06-12 | 16 | ||
3 | 18 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 100.0 | 2007-06-22 | 16 | ||
4 | 28 | Buckheit | Bruce | 8904 KAREN DR | FAIRFAX | VA | 220312731 | 100.0 | 2007-09-19 | 20 | ||
5 | 32 | Buck | Thomas | 4206 Terrace Street | Kansas City | MO | 64111 | 100.0 | 2007-09-25 | 20 | ||
6 | 33 | Buck | Jay | K. | 1855 Old Willow Rd Unit 322 | Northfield | IL | 600932918 | 200.0 | 2007-09-12 | 20 | |
7 | 38 | Bucher | Ida | M | 1400 Warnall Ave | Los Angeles | CA | 900245333 | 100.0 | 2007-07-10 | 20 | |
8 | 47 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 200.0 | 2007-08-14 | 20 | ||
9 | 101 | Aaronson | Rebecca | 2000 Village Green Dr Apt 12 | Mill Creek | WA | 980125787 | 100.0 | 2008-02-08 | 34 | ||
10 | 104 | Aarons | Elaine | 481 Buck Island Rd Apt 17A | APT 17A | West Yarmouth | MA | 26733300 | 100.0 | 2008-02-08 | 34 | |
11 | 106 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-29 | 34 | ||
12 | 107 | Aaronson | Rebecca | 2000 Village Green Dr Apt 12 | Mill Creek | WA | 980125787 | 100.0 | 2008-02-14 | 34 | ||
13 | 108 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-24 | 34 | ||
14 | 109 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-22 | 34 | ||
15 | 110 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-17 | 34 | ||
16 | 113 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-08 | 34 | ||
17 | 114 | Aaron | Shirley | 101 Cherry Ave | Havana | FL | 323331311 | 100.0 | 2008-02-03 | 34 | ||
18 | 116 | Aaron | Patricia | 418 NW 35th St | Oklahoma City | OK | 731188602 | 200.0 | 2008-02-26 | 34 | ||
19 | 117 | Aaron | Patricia | 418 NW 35th St | Oklahoma City | OK | 731188602 | 100.0 | 2008-02-12 | 34 | ||
20 | 119 | Aaron | Jim | 2178 Fairway Cir | Canton | MI | 481885097 | 200.0 | 2008-02-29 | 34 | ||
21 | 122 | Aaron | Carole | PO Box 1806 | Ogunquit | ME | 39071806 | 100.0 | 2008-02-03 | 34 | ||
22 | 125 | Aanonsen | Lin | 897 Raymond Ave | Saint Paul | MN | 551141508 | 100.0 | 2008-02-08 | 34 | ||
23 | 130 | ABTS | HENRY | P. O. BOX 7299 | INCLINE VILLAGE | NV | 894527299 | 100.0 | 2007-07-13 | 35 | ||
24 | 134 | ABREU | KEVIN | M. | 1305 GARDEN GLEN LANE | PEARLAND | TX | 775816547 | 150.0 | 2007-08-09 | 35 | |
25 | 147 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 100.0 | 2007-09-29 | 35 | |
26 | 149 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-09-15 | 35 | |
27 | 150 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-08-15 | 35 | |
28 | 154 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 200.0 | 2008-01-31 | 37 | |
29 | 155 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 100.0 | 2008-01-08 | 37 | |
30 | 158 | ABBOT | DAVID | M. | 56 SALEM STREET | ANDOVER | MA | 18102114 | 200.0 | 2008-01-21 | 37 | |
31 | 162 | ABACHERLI | SHIRLEY | M. | 29875 NEWPORT ROAD | MENIFEE | CA | 925849524 | 150.0 | 2008-01-28 | 37 | |
32 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 | |
33 | 167 | ABEL | MARLING | L. | 14 HANGING MOSS LANE | GREENVILLE | SC | 296155069 | 100.0 | 2008-01-22 | 37 | |
34 | 168 | ABEL | RUDOLPH | 4532 OCEAN BLVD. | # 108 | SARASOTA | FL | 342421337 | 100.0 | 2008-01-08 | 37 | |
35 | 171 | ABESHAUS | MERRILL | M. | 1801 N. HEREFORD DRIVE | FLAGSTAFF | AZ | 860011121 | 120.0 | 2008-01-16 | 37 |
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.
query = '''SELECT * FROM candidates ORDER BY id DESC'''
display(viz_tables(candidate_cols, query))
id | first_name | last_name | middle_init | party | |
---|---|---|---|---|---|
0 | 41 | Fred | Thompson | D. | R |
1 | 39 | Christopher | Dodd | J. | D |
2 | 38 | Tom | Tancredo | R | |
3 | 37 | John | McCain | R | |
4 | 36 | Samuel | Brownback | R | |
5 | 35 | Mitt | Romney | R | |
6 | 34 | Hillary | Clinton | R. | D |
7 | 33 | Joseph | Biden | D | |
8 | 32 | Ron | Paul | R | |
9 | 31 | Dennis | Kucinich | D | |
10 | 30 | Duncan | Hunter | R | |
11 | 29 | Bill | Richardson | D | |
12 | 26 | John | Edwards | D | |
13 | 24 | Mike | Gravel | D | |
14 | 22 | Rudolph | Giuliani | R | |
15 | 20 | Barack | Obama | D | |
16 | 16 | Mike | Huckabee | R |
Do the following sorts on the contributors
table:¶
- Sort the
candidates
table bylast_name
. - Sort the contributors table by the
amount
in decending order whereamount
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 byamount
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
- Hint: Multiple orderings can be accomplished by separating requests after
# Sort candidates by last name
query = '''SELECT * FROM candidates ORDER BY last_name'''
display(viz_tables(candidate_cols, query))
# Sort by amount in decending order
query = '''SELECT * FROM contributors WHERE amount BETWEEN 1000.00 and 5000.00 ORDER BY amount DESC'''
display(viz_tables(contributor_cols, query))
# Sort by amount in range and order by candidate_id and then by amount
query = '''SELECT * FROM contributors WHERE amount BETWEEN 1000.00 AND 5000.00 ORDER BY candidate_id, amount DESC'''
display(viz_tables(contributor_cols, query))
id | first_name | last_name | middle_init | party | |
---|---|---|---|---|---|
0 | 33 | Joseph | Biden | D | |
1 | 36 | Samuel | Brownback | R | |
2 | 34 | Hillary | Clinton | R. | D |
3 | 39 | Christopher | Dodd | J. | D |
4 | 26 | John | Edwards | D | |
5 | 22 | Rudolph | Giuliani | R | |
6 | 24 | Mike | Gravel | D | |
7 | 16 | Mike | Huckabee | R | |
8 | 30 | Duncan | Hunter | R | |
9 | 31 | Dennis | Kucinich | D | |
10 | 37 | John | McCain | R | |
11 | 20 | Barack | Obama | D | |
12 | 32 | Ron | Paul | R | |
13 | 29 | Bill | Richardson | D | |
14 | 35 | Mitt | Romney | R | |
15 | 38 | Tom | Tancredo | R | |
16 | 41 | Fred | Thompson | D. | R |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | ||
1 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | |
2 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | ||
3 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | ||
4 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | ||
5 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | ||
6 | 34 | Buck | Blaine | M | 45 Eaton Ave | Camden | ME | 48431752 | 2300.0 | 2007-09-30 | 20 | |
7 | 136 | ABRAMOWITZ | NIRA | 411 HARBOR ROAD | SOUTHPORT | CT | 68901376 | 2300.0 | 2007-09-14 | 35 | ||
8 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | ||
9 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | ||
10 | 46 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 1300.0 | 2007-08-09 | 20 | ||
11 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 | |
12 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | ||
13 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | ||
14 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | ||
15 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | ||
16 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | ||
17 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | ||
18 | 45 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 1000.0 | 2007-08-28 | 20 | ||
19 | 123 | Aaron | Barbara | 2298 Pacific Ave # 6 | San Francisco | CA | 941151435 | 1000.0 | 2008-02-11 | 34 | ||
20 | 139 | ABOUBAKARE | NASAR | 1400 SAN MIGUEL DRIVE | CORONA DEL MAR | CA | 926251300 | 1000.0 | 2007-07-09 | 35 | ||
21 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | |
22 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | ||
1 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | ||
2 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | ||
3 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | ||
4 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | ||
5 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | ||
6 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | ||
7 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | ||
8 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | ||
9 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | ||
10 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | ||
11 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | ||
12 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | ||
13 | 34 | Buck | Blaine | M | 45 Eaton Ave | Camden | ME | 48431752 | 2300.0 | 2007-09-30 | 20 | |
14 | 46 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 1300.0 | 2007-08-09 | 20 | ||
15 | 45 | Buchanan | John | 2025 NW 29th Rd | Boca Raton | FL | 334316303 | 1000.0 | 2007-08-28 | 20 | ||
16 | 123 | Aaron | Barbara | 2298 Pacific Ave # 6 | San Francisco | CA | 941151435 | 1000.0 | 2008-02-11 | 34 | ||
17 | 136 | ABRAMOWITZ | NIRA | 411 HARBOR ROAD | SOUTHPORT | CT | 68901376 | 2300.0 | 2007-09-14 | 35 | ||
18 | 139 | ABOUBAKARE | NASAR | 1400 SAN MIGUEL DRIVE | CORONA DEL MAR | CA | 926251300 | 1000.0 | 2007-07-09 | 35 | ||
19 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | |
20 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 | |
21 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | |
22 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 |
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
).
query = '''SELECT last_name, party FROM candidates'''
viz_tables(['last_name', 'party'], query)
last_name | party | |
---|---|---|
0 | Huckabee | R |
1 | Obama | D |
2 | Giuliani | R |
3 | Gravel | D |
4 | Edwards | D |
5 | Richardson | D |
6 | Hunter | R |
7 | Kucinich | D |
8 | Paul | R |
9 | Biden | D |
10 | Clinton | D |
11 | Romney | R |
12 | Brownback | R |
13 | McCain | R |
14 | Tancredo | R |
15 | Dodd | D |
16 | Thompson | R |
Using the DISTINCT
clause, you can remove duplicate rows.
query = '''SELECT DISTINCT party FROM candidates'''
viz_tables(['party'], query)
party | |
---|---|
0 | R |
1 | D |
Do the following:¶
- Get the first and last name of contributors. Make sure each row has distinct values.
query = '''SELECT DISTINCT last_name, first_name FROM contributors'''
display(viz_tables(['last_name', 'first_name'], query))
last_name | first_name | |
---|---|---|
0 | Agee | Steven |
1 | Ahrens | Don |
2 | Akin | Charles |
3 | Akin | Mike |
4 | Akin | Rebecca |
5 | Aldridge | Brittni |
6 | Allen | John D. |
7 | Allison | John W. |
8 | Allison | Rebecca |
9 | Altes | R.D. |
10 | Andres | Dale |
11 | Anthony | John |
12 | Arbogast | Robert |
13 | Ardle | William |
14 | Atiq | Omar |
15 | Baker | David |
16 | Bancroft | David |
17 | Banks | Charles |
18 | Barbee | John |
19 | Buckler | Steve |
20 | Buckheit | Bruce |
21 | Buckel | Linda |
22 | Buck | Thomas |
23 | Buck | Jay |
24 | Buck | Blaine |
25 | Buck | Barbara |
26 | Buchman | Mark M |
27 | Bucher | Ida |
28 | Buchanek | Elizabeth |
29 | Buchanan | John |
... | ... | ... |
96 | ABSHIER | DIANA |
97 | ABREU | KEVIN |
98 | ABRAMOWITZ | NIRA |
99 | ABRAMS | MICHAEL |
100 | ABRAMOWITZ | KEN |
101 | ABOUBAKARE | NASAR |
102 | ABEGG | PATRICIA |
103 | ABDELLA | THOMAS |
104 | ABBOTT | WELDON |
105 | ABBOTT | GERALD |
106 | ABEDIN | ZAINUL |
107 | ABBOTT | SYBIL |
108 | ABBOTT | RONALD |
109 | ABBOTT | ROBERT |
110 | ABBOTT | MIKE |
111 | ABBOT | DAVID |
112 | ABBO | PAULINE |
113 | ABATE | MARIA |
114 | ABAIR | PETER |
115 | ABACHERLI | SHIRLEY |
116 | AARONS | CHARLES |
117 | ABEL | JOHN |
118 | ABEL | MARLING |
119 | ABEL | RUDOLPH |
120 | ABELE | RODNEY |
121 | ABERCROMBIE | DENIS |
122 | ABESHAUS | MERRILL |
123 | ABRAHAM | GEORGE |
124 | ABRAHAMSON | PETER |
125 | ABRAHAM | SALEM |
126 rows × 2 columns
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
.
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''')
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 16 | Mike | Huckabee | R | None | |
1 | 20 | Barack | Obama | D | None | |
2 | 22 | Rudolph | Giuliani | R | None | |
3 | 24 | Mike | Gravel | D | None | |
4 | 26 | John | Edwards | D | None | |
5 | 29 | Bill | Richardson | D | None | |
6 | 30 | Duncan | Hunter | R | None | |
7 | 31 | Dennis | Kucinich | D | None | |
8 | 32 | Ron | Paul | R | None | |
9 | 33 | Joseph | Biden | D | None | |
10 | 34 | Hillary | Clinton | R. | D | None |
11 | 35 | Mitt | Romney | R | None | |
12 | 36 | Samuel | Brownback | R | None | |
13 | 37 | John | McCain | R | None | |
14 | 38 | Tom | Tancredo | R | None | |
15 | 39 | Christopher | Dodd | J. | D | None |
16 | 41 | Fred | Thompson | D. | R | None |
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.
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)
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 16 | Mike | Huckabee | R | Huckabee, Mike | |
1 | 20 | Barack | Obama | D | Obama, Barack | |
2 | 22 | Rudolph | Giuliani | R | Giuliani, Rudolph | |
3 | 24 | Mike | Gravel | D | Gravel, Mike | |
4 | 26 | John | Edwards | D | Edwards, John | |
5 | 29 | Bill | Richardson | D | Richardson, Bill | |
6 | 30 | Duncan | Hunter | R | Hunter, Duncan | |
7 | 31 | Dennis | Kucinich | D | Kucinich, Dennis | |
8 | 32 | Ron | Paul | R | Paul, Ron | |
9 | 33 | Joseph | Biden | D | Biden, Joseph | |
10 | 34 | Hillary | Clinton | R. | D | Clinton, Hillary |
11 | 35 | Mitt | Romney | R | Romney, Mitt | |
12 | 36 | Samuel | Brownback | R | Brownback, Samuel | |
13 | 37 | John | McCain | R | McCain, John | |
14 | 38 | Tom | Tancredo | R | Tancredo, Tom | |
15 | 39 | Christopher | Dodd | J. | D | Dodd, Christopher |
16 | 41 | Fred | Thompson | D. | R | Thompson, Fred |
Here's another update, this time on an existing column.
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)
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 16 | Mike | Huckabee | R | Huckabee, Mike | |
1 | 20 | Barack | Obama | D | Eventual Winner | |
2 | 22 | Rudolph | Giuliani | R | Giuliani, Rudolph | |
3 | 24 | Mike | Gravel | D | Gravel, Mike | |
4 | 26 | John | Edwards | D | Edwards, John | |
5 | 29 | Bill | Richardson | D | Richardson, Bill | |
6 | 30 | Duncan | Hunter | R | Hunter, Duncan | |
7 | 31 | Dennis | Kucinich | D | Kucinich, Dennis | |
8 | 32 | Ron | Paul | R | Paul, Ron | |
9 | 33 | Joseph | Biden | D | Biden, Joseph | |
10 | 34 | Hillary | Clinton | R. | D | Clinton, Hillary |
11 | 35 | Mitt | Romney | R | Eventual Loser | |
12 | 36 | Samuel | Brownback | R | Brownback, Samuel | |
13 | 37 | John | McCain | R | McCain, John | |
14 | 38 | Tom | Tancredo | R | Tancredo, Tom | |
15 | 39 | Christopher | Dodd | J. | D | Dodd, Christopher |
16 | 41 | Fred | Thompson | D. | R | Thompson, Fred |
Do the following:¶
- Add a new column to the contributors table called
full_name
. The value in that column should be in the formlast_name, first_name
. - Change the value in the
full_name
column to the string"Too Much"
if someone donated more than $\$1000.00$.
# Add full_name column
cursor.execute('''ALTER TABLE contributors ADD COLUMN full_name TEXT''')
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]
display(viz_tables(contributor_cols, '''SELECT * FROM contributors'''))
update = '''UPDATE contributors SET full_name = "Too Much" WHERE amount>1000.00'''
cursor.execute(update)
query = '''SELECT * FROM contributors'''
display(viz_tables(contributor_cols, query))
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | Agee | Steven | 549 Laurel Branch Road | Floyd | VA | 24091 | 500.0 | 2007-06-30 | 16 | None | ||
1 | 2 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 250.0 | 2007-05-16 | 16 | None | ||
2 | 3 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 50.0 | 2007-06-18 | 16 | None | ||
3 | 4 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 100.0 | 2007-06-21 | 16 | None | ||
4 | 5 | Akin | Charles | 10187 Sugar Creek Road | Bentonville | AR | 72712 | 100.0 | 2007-06-16 | 16 | None | ||
5 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | None | ||
6 | 7 | Akin | Rebecca | 181 Baywood Lane | Monticello | AR | 71655 | 500.0 | 2007-05-18 | 16 | None | ||
7 | 8 | Aldridge | Brittni | 808 Capitol Square Place, SW | Washington | DC | 20024 | 250.0 | 2007-06-06 | 16 | None | ||
8 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | None | ||
9 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | None | ||
10 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | None | ||
11 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | None | ||
12 | 13 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 200.0 | 2007-06-12 | 16 | None | ||
13 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | None | ||
14 | 15 | Andres | Dale | 1160 Glen Oaks Drive | West Des Moines | IA | 50266 | 250.0 | 2007-06-06 | 16 | None | ||
15 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | None | ||
16 | 17 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 500.0 | 2007-04-08 | 16 | None | ||
17 | 18 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 100.0 | 2007-06-22 | 16 | None | ||
18 | 19 | Ardle | William | 412 Dakota Avenue | Springfield | OH | 45504 | 50.0 | 2007-06-28 | 16 | None | ||
19 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | None | ||
20 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | None | ||
21 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | None | ||
22 | 23 | Bancroft | David | 2934 Broderick Street | San Francisco | CA | 94123 | 250.0 | 2007-04-24 | 16 | None | ||
23 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | None | ||
24 | 25 | Barbee | John | 516 Kellyridge Drive | Apex | NC | 27502 | 500.0 | 2007-05-23 | 16 | None | ||
25 | 26 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 50.0 | 2007-07-30 | 20 | None | ||
26 | 27 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 25.0 | 2007-08-16 | 20 | None | ||
27 | 28 | Buckheit | Bruce | 8904 KAREN DR | FAIRFAX | VA | 220312731 | 100.0 | 2007-09-19 | 20 | None | ||
28 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | None | ||
29 | 30 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | -2300.0 | 2007-08-14 | 20 | None | ||
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
145 | 146 | ABDELLA | THOMAS | M. | 4231 MONUMENT WALL WAY #340 | FAIRFAX | VA | 220308440 | 50.0 | 2007-09-30 | 35 | None | |
146 | 147 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 100.0 | 2007-09-29 | 35 | None | |
147 | 148 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 50.0 | 2007-08-09 | 35 | None | |
148 | 149 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-09-15 | 35 | None | |
149 | 150 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-08-15 | 35 | None | |
150 | 151 | ABEDIN | ZAINUL | 715 N. CENTRAL AVENUE | SUITE 212 | GLENDALE | CA | 912031164 | 500.0 | 2008-01-21 | 37 | None | |
151 | 152 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 75.0 | 2008-01-08 | 37 | None | |
152 | 153 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 50.0 | 2008-01-08 | 37 | None | |
153 | 154 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 200.0 | 2008-01-31 | 37 | None | |
154 | 155 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 100.0 | 2008-01-08 | 37 | None | |
155 | 156 | ABBOTT | ROBERT | A. | 3061 LOREE ROAD | DECKERVILLE | MI | 484279763 | 500.0 | 2008-01-21 | 37 | None | |
156 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | None | |
157 | 158 | ABBOT | DAVID | M. | 56 SALEM STREET | ANDOVER | MA | 18102114 | 200.0 | 2008-01-21 | 37 | None | |
158 | 159 | ABBO | PAULINE | MORENCY | 10720 JACOB LANE | WHITE LAKE | MI | 483862274 | 35.0 | 2008-01-07 | 37 | None | |
159 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | None | |
160 | 161 | ABAIR | PETER | 40 EVANS STREET | WATERTOWN | MA | 24722150 | 25.0 | 2008-01-09 | 37 | None | ||
161 | 162 | ABACHERLI | SHIRLEY | M. | 29875 NEWPORT ROAD | MENIFEE | CA | 925849524 | 150.0 | 2008-01-28 | 37 | None | |
162 | 163 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 300.0 | 2008-01-30 | 37 | None | ||
163 | 164 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 410.0 | 2008-01-15 | 37 | None | ||
164 | 165 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 500.0 | 2008-01-09 | 37 | None | ||
165 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 | None | |
166 | 167 | ABEL | MARLING | L. | 14 HANGING MOSS LANE | GREENVILLE | SC | 296155069 | 100.0 | 2008-01-22 | 37 | None | |
167 | 168 | ABEL | RUDOLPH | 4532 OCEAN BLVD. | # 108 | SARASOTA | FL | 342421337 | 100.0 | 2008-01-08 | 37 | None | |
168 | 169 | ABELE | RODNEY | 3620 METAIRIE HEIGHTS AVENUE | METAIRIE | LA | 700021823 | 500.0 | 2008-01-15 | 37 | None | ||
169 | 170 | ABERCROMBIE | DENIS | 11811 WATER OAK CT | MAGNOLIA | TX | 773546270 | 500.0 | 2008-01-30 | 37 | None | ||
170 | 171 | ABESHAUS | MERRILL | M. | 1801 N. HEREFORD DRIVE | FLAGSTAFF | AZ | 860011121 | 120.0 | 2008-01-16 | 37 | None | |
171 | 172 | ABRAHAM | GEORGE | P.O. BOX 1504 | LAKE CHARLES | LA | 706021504 | 800.0 | 2008-01-17 | 37 | None | ||
172 | 173 | ABRAHAMSON | PETER | J. | 1030 W. ROSCOE STREET | CHICAGO | IL | 606572207 | 50.0 | 2008-01-25 | 37 | None | |
173 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 | None | |
174 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 | None |
175 rows × 13 columns
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | Agee | Steven | 549 Laurel Branch Road | Floyd | VA | 24091 | 500.0 | 2007-06-30 | 16 | None | ||
1 | 2 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 250.0 | 2007-05-16 | 16 | None | ||
2 | 3 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 50.0 | 2007-06-18 | 16 | None | ||
3 | 4 | Ahrens | Don | 4034 Rennellwood Way | Pleasanton | CA | 94566 | 100.0 | 2007-06-21 | 16 | None | ||
4 | 5 | Akin | Charles | 10187 Sugar Creek Road | Bentonville | AR | 72712 | 100.0 | 2007-06-16 | 16 | None | ||
5 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | Too Much | ||
6 | 7 | Akin | Rebecca | 181 Baywood Lane | Monticello | AR | 71655 | 500.0 | 2007-05-18 | 16 | None | ||
7 | 8 | Aldridge | Brittni | 808 Capitol Square Place, SW | Washington | DC | 20024 | 250.0 | 2007-06-06 | 16 | None | ||
8 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | None | ||
9 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | Too Much | ||
10 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | None | ||
11 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | None | ||
12 | 13 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 200.0 | 2007-06-12 | 16 | None | ||
13 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | Too Much | ||
14 | 15 | Andres | Dale | 1160 Glen Oaks Drive | West Des Moines | IA | 50266 | 250.0 | 2007-06-06 | 16 | None | ||
15 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | Too Much | ||
16 | 17 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 500.0 | 2007-04-08 | 16 | None | ||
17 | 18 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 100.0 | 2007-06-22 | 16 | None | ||
18 | 19 | Ardle | William | 412 Dakota Avenue | Springfield | OH | 45504 | 50.0 | 2007-06-28 | 16 | None | ||
19 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | None | ||
20 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | None | ||
21 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | Too Much | ||
22 | 23 | Bancroft | David | 2934 Broderick Street | San Francisco | CA | 94123 | 250.0 | 2007-04-24 | 16 | None | ||
23 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | None | ||
24 | 25 | Barbee | John | 516 Kellyridge Drive | Apex | NC | 27502 | 500.0 | 2007-05-23 | 16 | None | ||
25 | 26 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 50.0 | 2007-07-30 | 20 | None | ||
26 | 27 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 25.0 | 2007-08-16 | 20 | None | ||
27 | 28 | Buckheit | Bruce | 8904 KAREN DR | FAIRFAX | VA | 220312731 | 100.0 | 2007-09-19 | 20 | None | ||
28 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | Too Much | ||
29 | 30 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | -2300.0 | 2007-08-14 | 20 | None | ||
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
145 | 146 | ABDELLA | THOMAS | M. | 4231 MONUMENT WALL WAY #340 | FAIRFAX | VA | 220308440 | 50.0 | 2007-09-30 | 35 | None | |
146 | 147 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 100.0 | 2007-09-29 | 35 | None | |
147 | 148 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 50.0 | 2007-08-09 | 35 | None | |
148 | 149 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-09-15 | 35 | None | |
149 | 150 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-08-15 | 35 | None | |
150 | 151 | ABEDIN | ZAINUL | 715 N. CENTRAL AVENUE | SUITE 212 | GLENDALE | CA | 912031164 | 500.0 | 2008-01-21 | 37 | None | |
151 | 152 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 75.0 | 2008-01-08 | 37 | None | |
152 | 153 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 50.0 | 2008-01-08 | 37 | None | |
153 | 154 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 200.0 | 2008-01-31 | 37 | None | |
154 | 155 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 100.0 | 2008-01-08 | 37 | None | |
155 | 156 | ABBOTT | ROBERT | A. | 3061 LOREE ROAD | DECKERVILLE | MI | 484279763 | 500.0 | 2008-01-21 | 37 | None | |
156 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | None | |
157 | 158 | ABBOT | DAVID | M. | 56 SALEM STREET | ANDOVER | MA | 18102114 | 200.0 | 2008-01-21 | 37 | None | |
158 | 159 | ABBO | PAULINE | MORENCY | 10720 JACOB LANE | WHITE LAKE | MI | 483862274 | 35.0 | 2008-01-07 | 37 | None | |
159 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | Too Much | |
160 | 161 | ABAIR | PETER | 40 EVANS STREET | WATERTOWN | MA | 24722150 | 25.0 | 2008-01-09 | 37 | None | ||
161 | 162 | ABACHERLI | SHIRLEY | M. | 29875 NEWPORT ROAD | MENIFEE | CA | 925849524 | 150.0 | 2008-01-28 | 37 | None | |
162 | 163 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 300.0 | 2008-01-30 | 37 | None | ||
163 | 164 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 410.0 | 2008-01-15 | 37 | None | ||
164 | 165 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 500.0 | 2008-01-09 | 37 | None | ||
165 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 | None | |
166 | 167 | ABEL | MARLING | L. | 14 HANGING MOSS LANE | GREENVILLE | SC | 296155069 | 100.0 | 2008-01-22 | 37 | None | |
167 | 168 | ABEL | RUDOLPH | 4532 OCEAN BLVD. | # 108 | SARASOTA | FL | 342421337 | 100.0 | 2008-01-08 | 37 | None | |
168 | 169 | ABELE | RODNEY | 3620 METAIRIE HEIGHTS AVENUE | METAIRIE | LA | 700021823 | 500.0 | 2008-01-15 | 37 | None | ||
169 | 170 | ABERCROMBIE | DENIS | 11811 WATER OAK CT | MAGNOLIA | TX | 773546270 | 500.0 | 2008-01-30 | 37 | None | ||
170 | 171 | ABESHAUS | MERRILL | M. | 1801 N. HEREFORD DRIVE | FLAGSTAFF | AZ | 860011121 | 120.0 | 2008-01-16 | 37 | None | |
171 | 172 | ABRAHAM | GEORGE | P.O. BOX 1504 | LAKE CHARLES | LA | 706021504 | 800.0 | 2008-01-17 | 37 | None | ||
172 | 173 | ABRAHAMSON | PETER | J. | 1030 W. ROSCOE STREET | CHICAGO | IL | 606572207 | 50.0 | 2008-01-25 | 37 | None | |
173 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 | None | |
174 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 | Too Much |
175 rows × 13 columns
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.
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)
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | Too Much |
Do the following:¶
- Count how many donations there were above $\$1000.00$.
- Calculate the average donation.
- Hint: Notice that some donations are negative. Probably best to exclude those.
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"
# How many donations about $1000.00
how_many = cursor.execute("SELECT COUNT(amount) AS num_above FROM contributors WHERE amount > 1000.00")
print(how_many.fetchall())
# Average donation
avg_don = cursor.execute("SELECT AVG(amount) FROM contributors WHERE amount > 0.0")
print(avg_don.fetchall())
# Average contribution from each state
function = '''SELECT state, AVG(amount) FROM contributors GROUP BY state'''
display(viz_tables(['state', 'sum'], function))
[(12,)] [(480.6913675213675,)]
state | sum | |
---|---|---|
0 | -500.000000 | |
1 | AK | 403.333333 |
2 | AR | 1183.333333 |
3 | AZ | 120.000000 |
4 | CA | -217.988261 |
5 | CO | -1455.750000 |
6 | CT | 2300.000000 |
7 | DC | -309.982000 |
8 | FL | -135.000000 |
9 | IA | 250.000000 |
10 | ID | -261.000000 |
11 | IL | -931.133333 |
12 | KS | -330.000000 |
13 | KY | -200.000000 |
14 | LA | 650.000000 |
15 | MA | -13.833333 |
16 | MD | 150.000000 |
17 | ME | 630.000000 |
18 | MI | -253.000000 |
19 | MN | 107.333333 |
20 | MO | 100.000000 |
21 | NC | 500.000000 |
22 | NH | -24.600000 |
23 | NJ | -408.725000 |
24 | NV | 181.250000 |
25 | NY | -809.312500 |
26 | OH | 112.500000 |
27 | OK | 266.666667 |
28 | PA | -429.200000 |
29 | RI | 100.000000 |
30 | SC | 800.000000 |
31 | TN | -25.000000 |
32 | TX | 220.582222 |
33 | UT | 459.090909 |
34 | VA | 103.184000 |
35 | WA | -166.666667 |
# Delete a row
deletion = '''DELETE FROM contributors WHERE last_name="Ahrens"'''
cursor.execute(deletion)
query = '''SELECT * FROM contributors'''
display(viz_tables(contributor_cols, query))
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | Agee | Steven | 549 Laurel Branch Road | Floyd | VA | 24091 | 500.0 | 2007-06-30 | 16 | None | ||
1 | 5 | Akin | Charles | 10187 Sugar Creek Road | Bentonville | AR | 72712 | 100.0 | 2007-06-16 | 16 | None | ||
2 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | Too Much | ||
3 | 7 | Akin | Rebecca | 181 Baywood Lane | Monticello | AR | 71655 | 500.0 | 2007-05-18 | 16 | None | ||
4 | 8 | Aldridge | Brittni | 808 Capitol Square Place, SW | Washington | DC | 20024 | 250.0 | 2007-06-06 | 16 | None | ||
5 | 9 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1000.0 | 2007-06-11 | 16 | None | ||
6 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | Too Much | ||
7 | 11 | Allison | John W. | P.O. Box 1089 | Conway | AR | 72033 | 1000.0 | 2007-05-18 | 16 | None | ||
8 | 12 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 1000.0 | 2007-04-25 | 16 | None | ||
9 | 13 | Allison | Rebecca | 3206 Summit Court | Little Rock | AR | 72227 | 200.0 | 2007-06-12 | 16 | None | ||
10 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | Too Much | ||
11 | 15 | Andres | Dale | 1160 Glen Oaks Drive | West Des Moines | IA | 50266 | 250.0 | 2007-06-06 | 16 | None | ||
12 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | Too Much | ||
13 | 17 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 500.0 | 2007-04-08 | 16 | None | ||
14 | 18 | Arbogast | Robert | 12900 State Route 56 SE | Mount Sterling | OH | 43143 | 100.0 | 2007-06-22 | 16 | None | ||
15 | 19 | Ardle | William | 412 Dakota Avenue | Springfield | OH | 45504 | 50.0 | 2007-06-28 | 16 | None | ||
16 | 20 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-05-18 | 16 | None | ||
17 | 21 | Atiq | Omar | 7200 S Hazel Street | Pine Bluff | AR | 71603 | 1000.0 | 2007-06-27 | 16 | None | ||
18 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | Too Much | ||
19 | 23 | Bancroft | David | 2934 Broderick Street | San Francisco | CA | 94123 | 250.0 | 2007-04-24 | 16 | None | ||
20 | 24 | Banks | Charles | P.O. Box 251310 | Little Rock | AR | 72225 | 1000.0 | 2007-05-14 | 16 | None | ||
21 | 25 | Barbee | John | 516 Kellyridge Drive | Apex | NC | 27502 | 500.0 | 2007-05-23 | 16 | None | ||
22 | 26 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 50.0 | 2007-07-30 | 20 | None | ||
23 | 27 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 25.0 | 2007-08-16 | 20 | None | ||
24 | 28 | Buckheit | Bruce | 8904 KAREN DR | FAIRFAX | VA | 220312731 | 100.0 | 2007-09-19 | 20 | None | ||
25 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | Too Much | ||
26 | 30 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | -2300.0 | 2007-08-14 | 20 | None | ||
27 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | Too Much | ||
28 | 32 | Buck | Thomas | 4206 Terrace Street | Kansas City | MO | 64111 | 100.0 | 2007-09-25 | 20 | None | ||
29 | 33 | Buck | Jay | K. | 1855 Old Willow Rd Unit 322 | Northfield | IL | 600932918 | 200.0 | 2007-09-12 | 20 | None | |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
142 | 146 | ABDELLA | THOMAS | M. | 4231 MONUMENT WALL WAY #340 | FAIRFAX | VA | 220308440 | 50.0 | 2007-09-30 | 35 | None | |
143 | 147 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 100.0 | 2007-09-29 | 35 | None | |
144 | 148 | ABBOTT | WELDON | S. | 777 EAST SOUTH TEMPLE 4E | SALT LAKE CITY | UT | 841021269 | 50.0 | 2007-08-09 | 35 | None | |
145 | 149 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-09-15 | 35 | None | |
146 | 150 | ABBOTT | GERALD | F. | 389 BENEFIT STREET | PROVIDENCE | RI | 29032946 | 100.0 | 2007-08-15 | 35 | None | |
147 | 151 | ABEDIN | ZAINUL | 715 N. CENTRAL AVENUE | SUITE 212 | GLENDALE | CA | 912031164 | 500.0 | 2008-01-21 | 37 | None | |
148 | 152 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 75.0 | 2008-01-08 | 37 | None | |
149 | 153 | ABBOTT | SYBIL | F. | 446 GAMES DRIVE | RENO | NV | 895093326 | 50.0 | 2008-01-08 | 37 | None | |
150 | 154 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 200.0 | 2008-01-31 | 37 | None | |
151 | 155 | ABBOTT | RONALD | LEANDER | 5453 HAWTHORNE STREET | MONTCLAIR | CA | 917632551 | 100.0 | 2008-01-08 | 37 | None | |
152 | 156 | ABBOTT | ROBERT | A. | 3061 LOREE ROAD | DECKERVILLE | MI | 484279763 | 500.0 | 2008-01-21 | 37 | None | |
153 | 157 | ABBOTT | MIKE | E. | 4516 OSPREY LNDG | NICEVILLE | FL | 325786810 | 1000.0 | 2008-01-15 | 37 | None | |
154 | 158 | ABBOT | DAVID | M. | 56 SALEM STREET | ANDOVER | MA | 18102114 | 200.0 | 2008-01-21 | 37 | None | |
155 | 159 | ABBO | PAULINE | MORENCY | 10720 JACOB LANE | WHITE LAKE | MI | 483862274 | 35.0 | 2008-01-07 | 37 | None | |
156 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | Too Much | |
157 | 161 | ABAIR | PETER | 40 EVANS STREET | WATERTOWN | MA | 24722150 | 25.0 | 2008-01-09 | 37 | None | ||
158 | 162 | ABACHERLI | SHIRLEY | M. | 29875 NEWPORT ROAD | MENIFEE | CA | 925849524 | 150.0 | 2008-01-28 | 37 | None | |
159 | 163 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 300.0 | 2008-01-30 | 37 | None | ||
160 | 164 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 410.0 | 2008-01-15 | 37 | None | ||
161 | 165 | AARONS | CHARLES | 1730 SHORE DRIVE | ANCHORAGE | AK | 995153207 | 500.0 | 2008-01-09 | 37 | None | ||
162 | 166 | ABEL | JOHN | H. | 422 THOMAS STREET | BETHLEHEM | PA | 180153316 | 200.0 | 2008-01-22 | 37 | None | |
163 | 167 | ABEL | MARLING | L. | 14 HANGING MOSS LANE | GREENVILLE | SC | 296155069 | 100.0 | 2008-01-22 | 37 | None | |
164 | 168 | ABEL | RUDOLPH | 4532 OCEAN BLVD. | # 108 | SARASOTA | FL | 342421337 | 100.0 | 2008-01-08 | 37 | None | |
165 | 169 | ABELE | RODNEY | 3620 METAIRIE HEIGHTS AVENUE | METAIRIE | LA | 700021823 | 500.0 | 2008-01-15 | 37 | None | ||
166 | 170 | ABERCROMBIE | DENIS | 11811 WATER OAK CT | MAGNOLIA | TX | 773546270 | 500.0 | 2008-01-30 | 37 | None | ||
167 | 171 | ABESHAUS | MERRILL | M. | 1801 N. HEREFORD DRIVE | FLAGSTAFF | AZ | 860011121 | 120.0 | 2008-01-16 | 37 | None | |
168 | 172 | ABRAHAM | GEORGE | P.O. BOX 1504 | LAKE CHARLES | LA | 706021504 | 800.0 | 2008-01-17 | 37 | None | ||
169 | 173 | ABRAHAMSON | PETER | J. | 1030 W. ROSCOE STREET | CHICAGO | IL | 606572207 | 50.0 | 2008-01-25 | 37 | None | |
170 | 174 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1000.0 | 2008-01-17 | 37 | None | |
171 | 175 | ABRAHAM | SALEM | A. | P.O. BOX 7 | CANADIAN | TX | 790140007 | 1300.0 | 2008-01-30 | 37 | Too Much |
172 rows × 13 columns
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.
query = '''SELECT * FROM candidates LIMIT 3'''
viz_tables(candidate_cols, query)
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 16 | Mike | Huckabee | R | Huckabee, Mike | |
1 | 20 | Barack | Obama | D | Eventual Winner | |
2 | 22 | Rudolph | Giuliani | R | Giuliani, Rudolph |
query = '''SELECT * FROM candidates LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 29 | Bill | Richardson | D | Richardson, Bill | |
1 | 30 | Duncan | Hunter | R | Hunter, Duncan | |
2 | 31 | Dennis | Kucinich | D | Kucinich, Dennis | |
3 | 32 | Ron | Paul | R | Paul, Ron |
query = '''SELECT * FROM candidates ORDER BY last_name LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)
id | first_name | last_name | middle_init | party | full_name | |
---|---|---|---|---|---|---|
0 | 22 | Rudolph | Giuliani | R | Giuliani, Rudolph | |
1 | 24 | Mike | Gravel | D | Gravel, Mike | |
2 | 16 | Mike | Huckabee | R | Huckabee, Mike | |
3 | 30 | Duncan | Hunter | R | Hunter, Duncan |
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...).
# 10 most generous donors
query = '''SELECT * FROM contributors ORDER BY amount DESC LIMIT 10'''
display(viz_tables(contributor_cols, query))
# 10 least generous donors
query = '''SELECT * FROM contributors WHERE amount > 0 ORDER BY amount LIMIT 10'''
display(viz_tables(contributor_cols, query))
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 31 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 4600.0 | 2007-08-14 | 20 | Too Much | ||
1 | 160 | ABATE | MARIA | ELENA | 1291 NIGHTINGALE AVENUE | MIAMI SPRINGS | FL | 331663832 | 2600.0 | 2008-01-25 | 37 | Too Much | |
2 | 14 | Altes | R.D. | 8600 Moody Road | Fort Smith | AR | 72903 | 2300.0 | 2007-06-21 | 16 | Too Much | ||
3 | 16 | Anthony | John | 211 Long Island Drive | Hot Springs | AR | 71913 | 2300.0 | 2007-06-12 | 16 | Too Much | ||
4 | 22 | Baker | David | 2550 Adamsbrooke Drive | Conway | AR | 72034 | 2300.0 | 2007-04-11 | 16 | Too Much | ||
5 | 29 | Buckel | Linda | PO Box 683130 | Park City | UT | 840683130 | 2300.0 | 2007-08-14 | 20 | Too Much | ||
6 | 34 | Buck | Blaine | M | 45 Eaton Ave | Camden | ME | 48431752 | 2300.0 | 2007-09-30 | 20 | Too Much | |
7 | 136 | ABRAMOWITZ | NIRA | 411 HARBOR ROAD | SOUTHPORT | CT | 68901376 | 2300.0 | 2007-09-14 | 35 | Too Much | ||
8 | 6 | Akin | Mike | 181 Baywood Lane | Monticello | AR | 71655 | 1500.0 | 2007-05-18 | 16 | Too Much | ||
9 | 10 | Allen | John D. | 1052 Cannon Mill Drive | North Augusta | SC | 29860 | 1300.0 | 2007-06-29 | 16 | Too Much |
id | last_name | first_name | middle_name | street_1 | street_2 | city | state | zip | amount | date | candidate_id | full_name | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 27 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 25.0 | 2007-08-16 | 20 | None | ||
1 | 50 | Harrison | Ryan | 2247 3rd St | La Verne | CA | 917504918 | 25.0 | 2007-07-26 | 20 | None | ||
2 | 102 | Aarons | Elaine | 481 Buck Island Rd Apt 17A | APT 17A | West Yarmouth | MA | 26733300 | 25.0 | 2008-02-26 | 34 | None | |
3 | 141 | ABEGG | PATRICIA | T. | 1862 E. 5150 S. | SALT LAKE CITY | UT | 841176911 | 25.0 | 2007-09-17 | 35 | None | |
4 | 144 | ABEGG | PATRICIA | T. | 1862 E. 5150 S. | SALT LAKE CITY | UT | 841176911 | 25.0 | 2007-08-06 | 35 | None | |
5 | 145 | ABEGG | PATRICIA | T. | 1862 E. 5150 S. | SALT LAKE CITY | UT | 841176911 | 25.0 | 2007-07-10 | 35 | None | |
6 | 161 | ABAIR | PETER | 40 EVANS STREET | WATERTOWN | MA | 24722150 | 25.0 | 2008-01-09 | 37 | None | ||
7 | 159 | ABBO | PAULINE | MORENCY | 10720 JACOB LANE | WHITE LAKE | MI | 483862274 | 35.0 | 2008-01-07 | 37 | None | |
8 | 19 | Ardle | William | 412 Dakota Avenue | Springfield | OH | 45504 | 50.0 | 2007-06-28 | 16 | None | ||
9 | 26 | Buckler | Steve | 24351 Armada Dr | Dana Point | CA | 926291306 | 50.0 | 2007-07-30 | 20 | None |
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()
.
db.commit()
db.close()