Key Word(s): SQL

Lecture 21¶

Tuesday, November 17th, 2020¶

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. 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().
Save and close your database. Be sure to upload your database with the lecture exercises. You must name your database L21DB.sqlite.

Table of Contents¶

Setting the Stage

Step 1

Interlude: Not required but highly recommended.

Setting the Stage¶

You should import sqlite3 again like last time.

In [1]:

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 [2]:

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 [3]:

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

Step 1¶

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

In [4]:

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 [5]:

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 [6]:

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.

In [7]:

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.

In [8]:

contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]
query = '''SELECT * FROM contributors'''
viz_tables(contributor_cols, query)

Out[8]:

	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.

In [9]:

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:

In [10]:

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

In [11]:

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

In [12]:

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

In [13]:

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

In [14]:

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

Out[14]:

	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.

In [15]:

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

Out[15]:

	party
0	R
1	D

Do the following:¶

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

In [16]:

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.

In [17]:

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

Out[17]:

	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.

In [18]:

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)

Out[18]:

	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.

In [19]:

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)

Out[19]:

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

In [20]:

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

In [21]:

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)

Out[21]:

	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"
```

In [22]:

# 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

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

In [23]:

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

In [24]:

query = '''SELECT * FROM candidates LIMIT 3'''
viz_tables(candidate_cols, query)

Out[24]:

	id	first_name	last_name	party	full_name
0	16	Mike	Huckabee	R	Huckabee, Mike
1	20	Barack	Obama	D	Eventual Winner
2	22	Rudolph	Giuliani	R	Giuliani, Rudolph

In [25]:

query = '''SELECT * FROM candidates LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)

Out[25]:

	id	first_name	last_name	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

In [26]:

query = '''SELECT * FROM candidates ORDER BY last_name LIMIT 4 OFFSET 5'''
viz_tables(candidate_cols, query)

Out[26]:

	id	first_name	last_name	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...).

In [27]:

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

In [28]:

db.commit()
db.close()

Lecture 21¶

Tuesday, November 17th, 2020¶

Databases II¶

SQLite Exercises¶

Exercise Deliverables¶

Table of Contents¶

Setting the Stage¶

Step 1¶

Interlude¶

Do the following:¶

Step 2: Various Queries¶

Do the following queries:¶

Step 3: Sorting¶

Do the following sorts on the contributors table:¶

Step 4: Selecting Columns¶

Do the following:¶

Step 5: Altering Tables¶

Do the following:¶

Step 6: Aggregation¶

Do the following:¶

Step 7: DELETE¶

Do the following:¶

Step 8: LIMIT¶

Do the following:¶

Save¶

`SQLite` Exercises¶

Do the following sorts on the `contributors` table:¶