-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
93 lines (76 loc) · 2.79 KB
/
database.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
__author__ = 'workhorse'
import sqlite3 as lite
import pandas as pd
con = lite.connect('cities.db')
with con:
cur = con.cursor()
cur.execute("drop table if exists cities")
cur.execute("drop table if exists weather")
cur.execute("drop TABLE if EXISTS cities_copy")
cur.execute("CREATE TABLE cities(name TEXT, state TEXT)")
cur.execute("CREATE TABLE weather(city TEXT, year INTEGER , warm_month TEXT, cold_month TEXT, average_high INTEGER)")
cities = (
('New York City', 'NY'),
('Boston', 'MA'),
('Chicago', 'IL'),
('Miami', 'FL'),
('Dallas', 'TX'),
('Seattle', 'WA'),
('Portland', 'OR'),
('San Francisco', 'CA'),
('Los Angeles', 'CA'),
('Washington', 'DC'),
('Houston', 'TX'),
('Las Vegas', 'NV'),
('Atlanta', 'GA')
)
weather = (
('New York City', 2013, 'July', 'January', 62),
('Boston', 2013, 'July', 'January', 59),
('Chicago', 2013, 'July', 'January',59),
('Miami',2013,'August','January',84),
('Dallas',2013,'July','January',77),
('Seattle',2013,'July','January', 61),
('Portland',2013,'July','December',63),
('San Francisco',2013,'September','December',64),
('Los Angeles',2013,'September','December',75),
('Washington', 2013, 'July', 'January', 55),
('Houston', 2013, 'July', 'January',56),
('Las Vegas',2013,'July','December', 77),
('Atlanta', 2013, 'July', 'January', 96)
)
con = lite.connect('cities.db')
with con:
cur = con.cursor()
cur.executemany("INSERT INTO cities VALUES(?,?)", cities)
cur.executemany("INSERT INTO weather VALUES(?,?,?,?,?)", weather)
with con:
#compared iterating across rows vs a dataframe
cur = con.cursor()
cur.execute("SELECT name, state, year, warm_month, cold_month, average_high \
FROM cities INNER JOIN weather WHERE name = city;")
rows = cur.fetchall()
#printed cur.descriptions so I could see the headers
print cur.description
#named the columns from descriptions to avoid having to use index numbers
cols = [desc[0] for desc in cur.description]
df = pd.DataFrame(rows, columns=cols)
print df
cur.execute("SELECT city, state, average_high, warm_month \
FROM weather \
Inner join cities\
WHERE city = name AND warm_month ='July';")
rows = cur.fetchall()
print"Iterating via rows - The cities that are warmest in July are:"
for row in rows:
print row[0]
print"Iterating across dataframe - The cities that are warmest in July are:"
cols = [desc[0] for desc in cur.description]
df = pd.DataFrame(rows, columns=cols)
for city in (df['city']):
print city
#testing formating strings
print "{} is my favorite city". format(city)
"""
Question - how do do I iterate across dataframe properly
"""