In [43]:
import sqlite3
import numpy as np
import pandas as pd
import time
pd.set_option('display.width', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.notebook_repr_html', True)

db = sqlite3.connect('L19DB_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)

candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]

def viz_tables(cols, query):
    q = cursor.execute(query).fetchall()
    framelist = []
    for i, col_name in enumerate(cols):
        framelist.append((col_name, [col[i] for col in q]))
    return pd.DataFrame.from_items(framelist)

In [44]:
query='''SELECT contributors.last_name, contributors.first_name, candidates.last_name FROM contributors INNER JOIN candidates ON contributors.candidate_id = candidates.id'''
q=cursor.execute(query).fetchall()
print(q)


[('Agee', 'Steven', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Akin', 'Charles', 'Huckabee'), ('Akin', 'Mike', 'Huckabee'), ('Akin', 'Rebecca', 'Huckabee'), ('Aldridge', 'Brittni', 'Huckabee'), ('Allen', 'John D.', 'Huckabee'), ('Allen', 'John D.', 'Huckabee'), ('Allison', 'John W.', 'Huckabee'), ('Allison', 'Rebecca', 'Huckabee'), ('Allison', 'Rebecca', 'Huckabee'), ('Altes', 'R.D.', 'Huckabee'), ('Andres', 'Dale', 'Huckabee'), ('Anthony', 'John', 'Huckabee'), ('Arbogast', 'Robert', 'Huckabee'), ('Arbogast', 'Robert', 'Huckabee'), ('Ardle', 'William', 'Huckabee'), ('Atiq', 'Omar', 'Huckabee'), ('Atiq', 'Omar', 'Huckabee'), ('Baker', 'David', 'Huckabee'), ('Bancroft', 'David', 'Huckabee'), ('Banks', 'Charles', 'Huckabee'), ('Barbee', 'John', 'Huckabee'), ('Buckler', 'Steve', 'Obama'), ('Buckler', 'Steve', 'Obama'), ('Buckheit', 'Bruce', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buck', 'Thomas', 'Obama'), ('Buck', 'Jay', 'Obama'), ('Buck', 'Blaine', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buchman', 'Mark M', 'Obama'), ('Bucher', 'Ida', 'Obama'), ('Buchanek', 'Elizabeth', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Harrison', 'Ryan', 'Obama'), ('BYNUM', 'HERBERT', 'Giuliani'), ('BYINGTON', 'MARGARET', 'Giuliani'), ('BYERS', 'BOB', 'Giuliani'), ('BYERS', 'AUDREY', 'Giuliani'), ('BUSH', 'KRYSTIE', 'Giuliani'), ('BUSH', 'ERIC', 'Giuliani'), ('BURTON', 'SUSAN', 'Giuliani'), ('BURTON', 'STEVEN', 'Giuliani'), ('BURTON', 'GLENN', 'Giuliani'), ('BURKHARDT', 'CRAIG', 'Giuliani'), ('BURKHARDT', 'CRAIG', 'Giuliani'), ('BURKHARDT', 'BARBARA', 'Giuliani'), ('BURKE', 'SUZANNE', 'Giuliani'), ('BURKE', 'GAIL', 'Giuliani'), ('BURKE', 'DONALD', 'Giuliani'), ('BURGERT', 'RONALD', 'Giuliani'), ('BULL', 'BARTLE', 'Giuliani'), ('BULL', 'BARTLE', 'Giuliani'), ('BUKOWSKI', 'DANIEL', 'Giuliani'), ('BUISSON', 'MARGARET', 'Giuliani'), ('BUCKLEY', 'WALTER', 'Giuliani'), ('BUCKLEY', 'MARJORIE', 'Giuliani'), ('BRUNO', 'JOHN', 'Giuliani'), ('BRUNO', 'IRENE', 'Giuliani'), ('BROWN', 'TIMOTHY', 'Giuliani'), ('Schuff', 'Bryan', 'Paul'), ('Hobbs', 'James', 'Paul'), ('Ranganath', 'Anoop', 'Paul'), ('Nystrom', 'Michael', 'Paul'), ('Muse', 'Nina', 'Paul'), ('Waddell', 'James', 'Paul'), ('Brucks', 'William', 'Paul'), ('Kuehn', 'David', 'Paul'), ('Verster', 'Jeanette', 'Paul'), ('Uihlein', 'Richard', 'Paul'), ('Eskenberry', 'Robert', 'Paul'), ('Froehling', 'Alan', 'Paul'), ('Duryea', 'Marcia', 'Paul'), ('Perreault', 'Louise', 'Paul'), ('Rozenfeld', 'Timur', 'Paul'), ('Kazor', 'Christopher', 'Paul'), ('Lehner', 'Thomas', 'Paul'), ('Plummer', 'Joseph', 'Paul'), ('Raught', 'Philip', 'Paul'), ('Ferrara', 'Judith', 'Paul'), ('Johnson', 'Cathleen', 'Paul'), ('Sanford', 'Bradley', 'Paul'), ('Gaarder', 'Bruce', 'Paul'), ('Choe', 'Hyeokchan', 'Paul'), ('Jacobs', 'Richard', 'Paul'), ('Aaronson', 'Rebecca', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaronson', 'Rebecca', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Reid', 'Elizabeth', 'Clinton'), ('Reich', 'Thomas', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Sharron', 'Clinton'), ('Aaron', 'Patricia', 'Clinton'), ('Aaron', 'Patricia', 'Clinton'), ('Aaron', 'Jim', 'Clinton'), ('Aaron', 'Jim', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Barbara', 'Clinton'), ('Aanonsen', 'Lin', 'Clinton'), ('Aanonsen', 'Lin', 'Clinton'), ('BOURNE', 'TRAVIS', 'Romney'), ('SECRIST', 'BRIAN', 'Romney'), ('TOLLESTRUP', 'TRAVIS', 'Romney'), ('ACCORD', 'DEAN', 'Romney'), ('ABTS', 'HENRY', 'Romney'), ('ABSHIER', 'LANNY', 'Romney'), ('ABSHIER', 'DIANA', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABRAMOWITZ', 'NIRA', 'Romney'), ('ABRAMS', 'MICHAEL', 'Romney'), ('ABRAMOWITZ', 'KEN', 'Romney'), ('ABOUBAKARE', 'NASAR', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABDELLA', 'THOMAS', 'Romney'), ('ABBOTT', 'WELDON', 'Romney'), ('ABBOTT', 'WELDON', 'Romney'), ('ABBOTT', 'GERALD', 'Romney'), ('ABBOTT', 'GERALD', 'Romney'), ('ABEDIN', 'ZAINUL', 'McCain'), ('ABBOTT', 'SYBIL', 'McCain'), ('ABBOTT', 'SYBIL', 'McCain'), ('ABBOTT', 'RONALD', 'McCain'), ('ABBOTT', 'RONALD', 'McCain'), ('ABBOTT', 'ROBERT', 'McCain'), ('ABBOTT', 'MIKE', 'McCain'), ('ABBOT', 'DAVID', 'McCain'), ('ABBO', 'PAULINE', 'McCain'), ('ABATE', 'MARIA', 'McCain'), ('ABAIR', 'PETER', 'McCain'), ('ABACHERLI', 'SHIRLEY', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('ABEL', 'JOHN', 'McCain'), ('ABEL', 'MARLING', 'McCain'), ('ABEL', 'RUDOLPH', 'McCain'), ('ABELE', 'RODNEY', 'McCain'), ('ABERCROMBIE', 'DENIS', 'McCain'), ('ABESHAUS', 'MERRILL', 'McCain'), ('ABRAHAM', 'GEORGE', 'McCain'), ('ABRAHAMSON', 'PETER', 'McCain'), ('ABRAHAM', 'SALEM', 'McCain'), ('ABRAHAM', 'SALEM', 'McCain')]

In [45]:
query='''SELECT contributors.last_name, contributors.first_name, candidates.last_name FROM contributors INNER JOIN candidates ON contributors.candidate_id = candidates.id WHERE candidates.last_name="Obama"'''
q=cursor.execute(query).fetchall()
print(q)


[('Buckler', 'Steve', 'Obama'), ('Buckler', 'Steve', 'Obama'), ('Buckheit', 'Bruce', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buck', 'Thomas', 'Obama'), ('Buck', 'Jay', 'Obama'), ('Buck', 'Blaine', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buchman', 'Mark M', 'Obama'), ('Bucher', 'Ida', 'Obama'), ('Buchanek', 'Elizabeth', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Harrison', 'Ryan', 'Obama')]
nA attr idA
s1 23 0
s2 7 2
s3 15 2
s4 31 0
nB attr idB
t1 60 0
t2 14 7
t3 22 2
nA A.attr nB B.attr
s1 23 t1 60
s2 7 t3 22
s3 15 t3 22
s4 31 t1 60
nA A.attr nB B.attr
s1 23 t1 60
s4 31 t1 60
none NaN t2 14
s2 7 t3 22
s3 15 t3 22

In [35]:
query='''SELECT AVG(amount),count(last_name) from contributors GROUP BY candidate_id'''
q=cursor.execute(query).fetchall()
pd.DataFrame(q)


Out[35]:
0 1
0 772.0000 25
1 472.4356 25
2 -1360.0000 25
3 -519.7088 25
4 50.6000 25
5 156.0000 25
6 452.6000 25

In [38]:
query = '''SELECT AVG(contributors.amount) AS average_contribution, 
COUNT(*) AS number_of_contributrs,
candidates.last_name AS candidate_last_name FROM contributors 
LEFT JOIN candidates ON candidates.id = contributors.candidate_id 
GROUP BY candidate_last_name'''
viz_tables(["average_contribution","number_of_contributrs","candidate_last_name"], query)


Out[38]:
average_contribution number_of_contributrs candidate_last_name
0 50.6000 25 Clinton
1 -1360.0000 25 Giuliani
2 772.0000 25 Huckabee
3 452.6000 25 McCain
4 472.4356 25 Obama
5 -519.7088 25 Paul
6 156.0000 25 Romney

In [39]:
q=cursor.execute(query).fetchall()
print(q)


[(50.6, 25, 'Clinton'), (-1360.0, 25, 'Giuliani'), (772.0, 25, 'Huckabee'), (452.6, 25, 'McCain'), (472.43559999999997, 25, 'Obama'), (-519.7088, 25, 'Paul'), (156.0, 25, 'Romney')]

In [40]:
dfcand = pd.read_csv("candidates.txt", sep="|")
dfcand


Out[40]:
id first_name last_name middle_name party
0 33 Joseph Biden NaN D
1 36 Samuel Brownback NaN R
2 34 Hillary Clinton R. D
3 39 Christopher Dodd J. D
4 26 John Edwards NaN D
5 22 Rudolph Giuliani NaN R
6 24 Mike Gravel NaN D
7 16 Mike Huckabee NaN R
8 30 Duncan Hunter NaN R
9 31 Dennis Kucinich NaN D
10 37 John McCain NaN R
11 20 Barack Obama NaN D
12 32 Ron Paul NaN R
13 29 Bill Richardson NaN D
14 35 Mitt Romney NaN R
15 38 Tom Tancredo NaN R
16 41 Fred Thompson D. R

In [41]:
dfcontr = pd.read_csv("contributors.txt", sep="|")
dfcontr


Out[41]:
id last_name first_name middle_name street_1 street_2 city state zip amount date candidate_id
0 NaN Agee Steven NaN 549 Laurel Branch Road NaN Floyd VA 24091 500.0 2007-06-30 16
1 NaN Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 250.0 2007-05-16 16
2 NaN Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 50.0 2007-06-18 16
3 NaN Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 100.0 2007-06-21 16
4 NaN Akin Charles NaN 10187 Sugar Creek Road NaN Bentonville AR 72712 100.0 2007-06-16 16
5 NaN Akin Mike NaN 181 Baywood Lane NaN Monticello AR 71655 1500.0 2007-05-18 16
6 NaN Akin Rebecca NaN 181 Baywood Lane NaN Monticello AR 71655 500.0 2007-05-18 16
7 NaN Aldridge Brittni NaN 808 Capitol Square Place, SW NaN Washington DC 20024 250.0 2007-06-06 16
8 NaN Allen John D. NaN 1052 Cannon Mill Drive NaN North Augusta SC 29860 1000.0 2007-06-11 16
9 NaN Allen John D. NaN 1052 Cannon Mill Drive NaN North Augusta SC 29860 1300.0 2007-06-29 16
10 NaN Allison John W. NaN P.O. Box 1089 NaN Conway AR 72033 1000.0 2007-05-18 16
11 NaN Allison Rebecca NaN 3206 Summit Court NaN Little Rock AR 72227 1000.0 2007-04-25 16
12 NaN Allison Rebecca NaN 3206 Summit Court NaN Little Rock AR 72227 200.0 2007-06-12 16
13 NaN Altes R.D. NaN 8600 Moody Road NaN Fort Smith AR 72903 2300.0 2007-06-21 16
14 NaN Andres Dale NaN 1160 Glen Oaks Drive NaN West Des Moines IA 50266 250.0 2007-06-06 16
15 NaN Anthony John NaN 211 Long Island Drive NaN Hot Springs AR 71913 2300.0 2007-06-12 16
16 NaN Arbogast Robert NaN 12900 State Route 56 SE NaN Mount Sterling OH 43143 500.0 2007-04-08 16
17 NaN Arbogast Robert NaN 12900 State Route 56 SE NaN Mount Sterling OH 43143 100.0 2007-06-22 16
18 NaN Ardle William NaN 412 Dakota Avenue NaN Springfield OH 45504 50.0 2007-06-28 16
19 NaN Atiq Omar NaN 7200 S Hazel Street NaN Pine Bluff AR 71603 1000.0 2007-05-18 16
20 NaN Atiq Omar NaN 7200 S Hazel Street NaN Pine Bluff AR 71603 1000.0 2007-06-27 16
21 NaN Baker David NaN 2550 Adamsbrooke Drive NaN Conway AR 72034 2300.0 2007-04-11 16
22 NaN Bancroft David NaN 2934 Broderick Street NaN San Francisco CA 94123 250.0 2007-04-24 16
23 NaN Banks Charles NaN P.O. Box 251310 NaN Little Rock AR 72225 1000.0 2007-05-14 16
24 NaN Barbee John NaN 516 Kellyridge Drive NaN Apex NC 27502 500.0 2007-05-23 16
25 NaN Buckler Steve NaN 24351 Armada Dr NaN Dana Point CA 926291306 50.0 2007-07-30 20
26 NaN Buckler Steve NaN 24351 Armada Dr NaN Dana Point CA 926291306 25.0 2007-08-16 20
27 NaN Buckheit Bruce NaN 8904 KAREN DR NaN FAIRFAX VA 220312731 100.0 2007-09-19 20
28 NaN Buckel Linda NaN PO Box 683130 NaN Park City UT 840683130 2300.0 2007-08-14 20
29 NaN Buckel Linda NaN PO Box 683130 NaN Park City UT 840683130 -2300.0 2007-08-14 20
... ... ... ... ... ... ... ... ... ... ... ... ...
145 NaN ABDELLA THOMAS M. 4231 MONUMENT WALL WAY #340 NaN FAIRFAX VA 220308440 50.0 2007-09-30 35
146 NaN ABBOTT WELDON S. 777 EAST SOUTH TEMPLE 4E NaN SALT LAKE CITY UT 841021269 100.0 2007-09-29 35
147 NaN ABBOTT WELDON S. 777 EAST SOUTH TEMPLE 4E NaN SALT LAKE CITY UT 841021269 50.0 2007-08-09 35
148 NaN ABBOTT GERALD F. 389 BENEFIT STREET NaN PROVIDENCE RI 29032946 100.0 2007-09-15 35
149 NaN ABBOTT GERALD F. 389 BENEFIT STREET NaN PROVIDENCE RI 29032946 100.0 2007-08-15 35
150 NaN ABEDIN ZAINUL NaN 715 N. CENTRAL AVENUE SUITE 212 GLENDALE CA 912031164 500.0 2008-01-21 37
151 NaN ABBOTT SYBIL F. 446 GAMES DRIVE NaN RENO NV 895093326 75.0 2008-01-08 37
152 NaN ABBOTT SYBIL F. 446 GAMES DRIVE NaN RENO NV 895093326 50.0 2008-01-08 37
153 NaN ABBOTT RONALD LEANDER 5453 HAWTHORNE STREET NaN MONTCLAIR CA 917632551 200.0 2008-01-31 37
154 NaN ABBOTT RONALD LEANDER 5453 HAWTHORNE STREET NaN MONTCLAIR CA 917632551 100.0 2008-01-08 37
155 NaN ABBOTT ROBERT A. 3061 LOREE ROAD NaN DECKERVILLE MI 484279763 500.0 2008-01-21 37
156 NaN ABBOTT MIKE E. 4516 OSPREY LNDG NaN NICEVILLE FL 325786810 1000.0 2008-01-15 37
157 NaN ABBOT DAVID M. 56 SALEM STREET NaN ANDOVER MA 18102114 200.0 2008-01-21 37
158 NaN ABBO PAULINE MORENCY 10720 JACOB LANE NaN WHITE LAKE MI 483862274 35.0 2008-01-07 37
159 NaN ABATE MARIA ELENA 1291 NIGHTINGALE AVENUE NaN MIAMI SPRINGS FL 331663832 2600.0 2008-01-25 37
160 NaN ABAIR PETER NaN 40 EVANS STREET NaN WATERTOWN MA 24722150 25.0 2008-01-09 37
161 NaN ABACHERLI SHIRLEY M. 29875 NEWPORT ROAD NaN MENIFEE CA 925849524 150.0 2008-01-28 37
162 NaN AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 300.0 2008-01-30 37
163 NaN AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 410.0 2008-01-15 37
164 NaN AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 500.0 2008-01-09 37
165 NaN ABEL JOHN H. 422 THOMAS STREET NaN BETHLEHEM PA 180153316 200.0 2008-01-22 37
166 NaN ABEL MARLING L. 14 HANGING MOSS LANE NaN GREENVILLE SC 296155069 100.0 2008-01-22 37
167 NaN ABEL RUDOLPH NaN 4532 OCEAN BLVD. # 108 SARASOTA FL 342421337 100.0 2008-01-08 37
168 NaN ABELE RODNEY NaN 3620 METAIRIE HEIGHTS AVENUE NaN METAIRIE LA 700021823 500.0 2008-01-15 37
169 NaN ABERCROMBIE DENIS NaN 11811 WATER OAK CT NaN MAGNOLIA TX 773546270 500.0 2008-01-30 37
170 NaN ABESHAUS MERRILL M. 1801 N. HEREFORD DRIVE NaN FLAGSTAFF AZ 860011121 120.0 2008-01-16 37
171 NaN ABRAHAM GEORGE NaN P.O. BOX 1504 NaN LAKE CHARLES LA 706021504 800.0 2008-01-17 37
172 NaN ABRAHAMSON PETER J. 1030 W. ROSCOE STREET NaN CHICAGO IL 606572207 50.0 2008-01-25 37
173 NaN ABRAHAM SALEM A. P.O. BOX 7 NaN CANADIAN TX 790140007 1000.0 2008-01-17 37
174 NaN ABRAHAM SALEM A. P.O. BOX 7 NaN CANADIAN TX 790140007 1300.0 2008-01-30 37

175 rows × 12 columns


In [42]:
del dfcontr['id']
dfcontr.head()


Out[42]:
last_name first_name middle_name street_1 street_2 city state zip amount date candidate_id
0 Agee Steven NaN 549 Laurel Branch Road NaN Floyd VA 24091 500.0 2007-06-30 16
1 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 250.0 2007-05-16 16
2 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 50.0 2007-06-18 16
3 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 100.0 2007-06-21 16
4 Akin Charles NaN 10187 Sugar Creek Road NaN Bentonville AR 72712 100.0 2007-06-16 16

In [46]:
dbp = sqlite3.connect('L19_pandas_DB.sqlite')
csr = dbp.cursor()
csr.execute("DROP TABLE IF EXISTS candidates")
csr.execute("DROP TABLE IF EXISTS contributors")
csr.execute("PRAGMA foreign_keys=1")

csr.execute('''CREATE TABLE candidates (
               id INTEGER PRIMARY KEY NOT NULL, 
               first_name TEXT, 
               last_name TEXT, 
               middle_name TEXT, 
               party TEXT NOT NULL)''')

dbp.commit() # Commit changes to the database

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

dbp.commit()

In [47]:
dfcand.query("first_name=='Mike' & party=='D'")


Out[47]:
id first_name last_name middle_name party
6 24 Mike Gravel NaN D

In [48]:
dfcand[dfcand.middle_name.notnull()]


Out[48]:
id first_name last_name middle_name party
2 34 Hillary Clinton R. D
3 39 Christopher Dodd J. D
16 41 Fred Thompson D. R

In [49]:
dfcand[(dfcand.first_name=="Mike") & (dfcand.party=="D")]


Out[49]:
id first_name last_name middle_name party
6 24 Mike Gravel NaN D

In [50]:
dfcand[dfcand.first_name.isin(['Mike', 'Hillary'])]


Out[50]:
id first_name last_name middle_name party
2 34 Hillary Clinton R. D
6 24 Mike Gravel NaN D
7 16 Mike Huckabee NaN R

In [52]:
dfcontr


Out[52]:
last_name first_name middle_name street_1 street_2 city state zip amount date candidate_id
0 Agee Steven NaN 549 Laurel Branch Road NaN Floyd VA 24091 500.0 2007-06-30 16
1 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 250.0 2007-05-16 16
2 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 50.0 2007-06-18 16
3 Ahrens Don NaN 4034 Rennellwood Way NaN Pleasanton CA 94566 100.0 2007-06-21 16
4 Akin Charles NaN 10187 Sugar Creek Road NaN Bentonville AR 72712 100.0 2007-06-16 16
5 Akin Mike NaN 181 Baywood Lane NaN Monticello AR 71655 1500.0 2007-05-18 16
6 Akin Rebecca NaN 181 Baywood Lane NaN Monticello AR 71655 500.0 2007-05-18 16
7 Aldridge Brittni NaN 808 Capitol Square Place, SW NaN Washington DC 20024 250.0 2007-06-06 16
8 Allen John D. NaN 1052 Cannon Mill Drive NaN North Augusta SC 29860 1000.0 2007-06-11 16
9 Allen John D. NaN 1052 Cannon Mill Drive NaN North Augusta SC 29860 1300.0 2007-06-29 16
10 Allison John W. NaN P.O. Box 1089 NaN Conway AR 72033 1000.0 2007-05-18 16
11 Allison Rebecca NaN 3206 Summit Court NaN Little Rock AR 72227 1000.0 2007-04-25 16
12 Allison Rebecca NaN 3206 Summit Court NaN Little Rock AR 72227 200.0 2007-06-12 16
13 Altes R.D. NaN 8600 Moody Road NaN Fort Smith AR 72903 2300.0 2007-06-21 16
14 Andres Dale NaN 1160 Glen Oaks Drive NaN West Des Moines IA 50266 250.0 2007-06-06 16
15 Anthony John NaN 211 Long Island Drive NaN Hot Springs AR 71913 2300.0 2007-06-12 16
16 Arbogast Robert NaN 12900 State Route 56 SE NaN Mount Sterling OH 43143 500.0 2007-04-08 16
17 Arbogast Robert NaN 12900 State Route 56 SE NaN Mount Sterling OH 43143 100.0 2007-06-22 16
18 Ardle William NaN 412 Dakota Avenue NaN Springfield OH 45504 50.0 2007-06-28 16
19 Atiq Omar NaN 7200 S Hazel Street NaN Pine Bluff AR 71603 1000.0 2007-05-18 16
20 Atiq Omar NaN 7200 S Hazel Street NaN Pine Bluff AR 71603 1000.0 2007-06-27 16
21 Baker David NaN 2550 Adamsbrooke Drive NaN Conway AR 72034 2300.0 2007-04-11 16
22 Bancroft David NaN 2934 Broderick Street NaN San Francisco CA 94123 250.0 2007-04-24 16
23 Banks Charles NaN P.O. Box 251310 NaN Little Rock AR 72225 1000.0 2007-05-14 16
24 Barbee John NaN 516 Kellyridge Drive NaN Apex NC 27502 500.0 2007-05-23 16
25 Buckler Steve NaN 24351 Armada Dr NaN Dana Point CA 926291306 50.0 2007-07-30 20
26 Buckler Steve NaN 24351 Armada Dr NaN Dana Point CA 926291306 25.0 2007-08-16 20
27 Buckheit Bruce NaN 8904 KAREN DR NaN FAIRFAX VA 220312731 100.0 2007-09-19 20
28 Buckel Linda NaN PO Box 683130 NaN Park City UT 840683130 2300.0 2007-08-14 20
29 Buckel Linda NaN PO Box 683130 NaN Park City UT 840683130 -2300.0 2007-08-14 20
... ... ... ... ... ... ... ... ... ... ... ...
145 ABDELLA THOMAS M. 4231 MONUMENT WALL WAY #340 NaN FAIRFAX VA 220308440 50.0 2007-09-30 35
146 ABBOTT WELDON S. 777 EAST SOUTH TEMPLE 4E NaN SALT LAKE CITY UT 841021269 100.0 2007-09-29 35
147 ABBOTT WELDON S. 777 EAST SOUTH TEMPLE 4E NaN SALT LAKE CITY UT 841021269 50.0 2007-08-09 35
148 ABBOTT GERALD F. 389 BENEFIT STREET NaN PROVIDENCE RI 29032946 100.0 2007-09-15 35
149 ABBOTT GERALD F. 389 BENEFIT STREET NaN PROVIDENCE RI 29032946 100.0 2007-08-15 35
150 ABEDIN ZAINUL NaN 715 N. CENTRAL AVENUE SUITE 212 GLENDALE CA 912031164 500.0 2008-01-21 37
151 ABBOTT SYBIL F. 446 GAMES DRIVE NaN RENO NV 895093326 75.0 2008-01-08 37
152 ABBOTT SYBIL F. 446 GAMES DRIVE NaN RENO NV 895093326 50.0 2008-01-08 37
153 ABBOTT RONALD LEANDER 5453 HAWTHORNE STREET NaN MONTCLAIR CA 917632551 200.0 2008-01-31 37
154 ABBOTT RONALD LEANDER 5453 HAWTHORNE STREET NaN MONTCLAIR CA 917632551 100.0 2008-01-08 37
155 ABBOTT ROBERT A. 3061 LOREE ROAD NaN DECKERVILLE MI 484279763 500.0 2008-01-21 37
156 ABBOTT MIKE E. 4516 OSPREY LNDG NaN NICEVILLE FL 325786810 1000.0 2008-01-15 37
157 ABBOT DAVID M. 56 SALEM STREET NaN ANDOVER MA 18102114 200.0 2008-01-21 37
158 ABBO PAULINE MORENCY 10720 JACOB LANE NaN WHITE LAKE MI 483862274 35.0 2008-01-07 37
159 ABATE MARIA ELENA 1291 NIGHTINGALE AVENUE NaN MIAMI SPRINGS FL 331663832 2600.0 2008-01-25 37
160 ABAIR PETER NaN 40 EVANS STREET NaN WATERTOWN MA 24722150 25.0 2008-01-09 37
161 ABACHERLI SHIRLEY M. 29875 NEWPORT ROAD NaN MENIFEE CA 925849524 150.0 2008-01-28 37
162 AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 300.0 2008-01-30 37
163 AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 410.0 2008-01-15 37
164 AARONS CHARLES NaN 1730 SHORE DRIVE NaN ANCHORAGE AK 995153207 500.0 2008-01-09 37
165 ABEL JOHN H. 422 THOMAS STREET NaN BETHLEHEM PA 180153316 200.0 2008-01-22 37
166 ABEL MARLING L. 14 HANGING MOSS LANE NaN GREENVILLE SC 296155069 100.0 2008-01-22 37
167 ABEL RUDOLPH NaN 4532 OCEAN BLVD. # 108 SARASOTA FL 342421337 100.0 2008-01-08 37
168 ABELE RODNEY NaN 3620 METAIRIE HEIGHTS AVENUE NaN METAIRIE LA 700021823 500.0 2008-01-15 37
169 ABERCROMBIE DENIS NaN 11811 WATER OAK CT NaN MAGNOLIA TX 773546270 500.0 2008-01-30 37
170 ABESHAUS MERRILL M. 1801 N. HEREFORD DRIVE NaN FLAGSTAFF AZ 860011121 120.0 2008-01-16 37
171 ABRAHAM GEORGE NaN P.O. BOX 1504 NaN LAKE CHARLES LA 706021504 800.0 2008-01-17 37
172 ABRAHAMSON PETER J. 1030 W. ROSCOE STREET NaN CHICAGO IL 606572207 50.0 2008-01-25 37
173 ABRAHAM SALEM A. P.O. BOX 7 NaN CANADIAN TX 790140007 1000.0 2008-01-17 37
174 ABRAHAM SALEM A. P.O. BOX 7 NaN CANADIAN TX 790140007 1300.0 2008-01-30 37

175 rows × 11 columns


In [51]:
dfcontr.to_sql("contributors", dbp, if_exists="append", index=False)


---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-51-5d6987a27146> in <module>()
----> 1 dfcontr.to_sql("contributors", dbp, if_exists="append", index=False)

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
   1343         sql.to_sql(self, name, con, flavor=flavor, schema=schema,
   1344                    if_exists=if_exists, index=index, index_label=index_label,
-> 1345                    chunksize=chunksize, dtype=dtype)
   1346 
   1347     def to_pickle(self, path, compression='infer'):

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
    469     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    470                       index_label=index_label, schema=schema,
--> 471                       chunksize=chunksize, dtype=dtype)
    472 
    473 

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1504                             dtype=dtype)
   1505         table.create()
-> 1506         table.insert(chunksize)
   1507 
   1508     def has_table(self, name, schema=None):

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in insert(self, chunksize)
    664 
    665                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 666                 self._execute_insert(conn, keys, chunk_iter)
    667 
    668     def _query_iterator(self, result, chunksize, columns, coerce_float=True,

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in _execute_insert(self, conn, keys, data_iter)
   1292     def _execute_insert(self, conn, keys, data_iter):
   1293         data_list = list(data_iter)
-> 1294         conn.executemany(self.insert_statement(), data_list)
   1295 
   1296     def _create_table_setup(self):

IntegrityError: FOREIGN KEY constraint failed

In [55]:
0.5*(1+2+0.9*0.315)


Out[55]:
1.64175

In [ ]: