Repository navigation
Expand file tree
/
Copy pathMA5.py
More file actions
236 lines (191 loc) · 7.69 KB
/
Copy pathMA5.py
File metadata and controls
236 lines (191 loc) · 7.69 KB
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
# muzammil lab 12 MA5.py
# I declare that I did not collaborate with anyone in this micro-assignment.
# Besides the lab and class notes, I used the following resources:
import sqlite3
import matplotlib.pyplot as plt
connection = None
cursor = None
def connect(path):
# using global variables already defined in main method, not new variables
global connection, cursor
# create a connection to the sqlite3 database
connection = sqlite3.connect(path)
# create a cursor object which will be used to execute sql statements
cursor = connection.cursor()
# execute a sql statement to enforce foreign key constraint
cursor.execute(' PRAGMA foreign_keys=ON; ')
# commit the changes we have made so they are visible by any other connections
connection.commit()
return
def query1():
# using globally define dconnection and cursor
global connection, cursor
# define the sql query string with variables
query = '''SELECT C.NEIGHBOURHOOD, SUM(C.FEMALE)/CAST(SUM(C.MALE) AS FLOAT)
FROM Census2014 C
GROUP BY C.NEIGHBOURHOOD
HAVING SUM(C.MALE)+SUM(C.FEMALE)>:num1 AND SUM(c.MALE)>0'''
number=int(input("what is the threshold value?"))
# execute query with provided flags
cursor.execute(query, {'num1':number})
# get list of tuples
rows = cursor.fetchall()
# need list of years and revenues for plotting pie chart
NEIGHBOURHOOD = []
ratio = []
# iterate through results to build lists
for row in rows:
NEIGHBOURHOOD.append(row[0])
ratio.append(row[1])
# pass lists to plotting functions to generate and save charts
pie_chart(NEIGHBOURHOOD, ratio, 'Query 1 Pie Chart')
bar_chart(NEIGHBOURHOOD, ratio, 'Query 1 Bar Chart', 'NEIGHBOURHOOD', 'ratio')
connection.commit()
return
def query2():
# using globally define dconnection and cursor
global connection, cursor
# define the sql query string with variables
query = '''SELECT '2009' AS YEAR, SUM(C.FEMALE),SUM(C.MALE)
FROM CENSUS2009 AS C
WHERE C.AGE>=:min_age AND C.AGE<=max_age
UNION
SELECT '2012' as YEAR,SUM(C.FEMALE),SUM(C.MALE)
FROM CENSUS2012 AS C
WHERE C.AGE>=:min_age AND C.AGE<=max_age
UNION
SELECT '2014' AS YEAR,SUM(C.FEMALE),SUM(C.MALE)
FROM CENSUS2014 AS C
WHERE C.AGE>=:min_age AND C.AGE<=max_age
UNION
SELECT '2016' AS YEAR,SUM(C.FEMALE),SUM(C.MALE)
FROM CENSUS2016 AS C
WHERE C.AGE>=:min_age AND C.AGE<=max_age
'''
# need to get two inputs from user for year range
max_age=int(input("What is the max AGE?"))
min_age=int(input("what is the min AGE?"))
# execute query with provided years
cursor.execute(query, {'min_age': min_age , 'max_age': max_age})
# get list of tuples
rows = cursor.fetchall()
# need list of years and revenues for plotting pie chart
years = []
sum_female = []
sum_male=[]
# iterate through results to build lists
for row in rows:
years.append(row[0])
sum_female.append(row[1])
sum_male.append(row[2])
# pass lists to plotting functions to generate and save charts
bar_chart2(sum_male,sum_female, years, 'Query 2 Bar Chart', 'Years', 'SUMS')
connection.commit()
return
# define pie chart plotting function
# takes list of values and labels and title string
# saves pie chart to file
def pie_chart(values, labels, title):
# see matplotlib website for more details
# https://matplotlib.org/stable/gallery/pie_and_polar_charts/pie_features.html
# if lists empty, print warning
if len(values) < 1:
print('Warning: empty input so generated plot will be empty')
# create a pie chart
plt.pie(values, # these are the values that will make up the pie slices
labels=labels, # these are names/labels for each slice
autopct='%1.1f%%') # putting fomatted percent values on slices
# give plot a title
plt.title(title)
# save plot to file
# we'll use passed title to give file name
path = './{}_piechart.png'.format(title)
plt.savefig(path)
print('Chart saved to file {}'.format(path))
# close figure so it doesn't display
plt.close()
return
# define bar chart plotting function
# takes list of values and labels and title string
# saves plot to file
def bar_chart(values, labels, title, x_label, y_label):
# see matplotlib website for more details
# https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.bar.html
# if lists empty print warning
if len(values) < 1:
print('Warning: empty input so generated plot will be empty')
# create bar chart
plt.bar(range(len(values)), # x coordinates of bars will be 0, 1,... len(values)-1
values, # height of bars will be values
tick_label=labels) # label bars with years
# label x and y axis
plt.xlabel(x_label)
plt.ylabel(y_label)
# give plot a title
plt.title(title)
# save plot to file
# we'll use passed title to give file name
path = './{}_barchart.png'.format(title)
plt.savefig(path)
print('Chart saved to file {}'.format(path))
# close figure so it doesn't display
plt.close()
return
def bar_chart2(male_values, female_values, years, title, x_label, y_label):
# see matplotlib website for more details
# https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.bar.html
# if lists empty print warning
if len(years) < 1:
print('Warning: empty input so generated plot will be empty')
xs = list(range(len(years)))
width = 0.35
x_male = [x - width/2 for x in xs]
x_female = [x + width/2 for x in xs]
fig, ax = plt.subplots()
ax.bar(x_male, male_values, width, label='Male')
ax.bar(x_female, female_values, width, label='Female')
ax.set_ylabel(y_label)
ax.set_title(title)
ax.set_xticks(xs)
ax.set_xticklabels(years)
ax.legend()
# save plot to file
# we'll use passed title to give file name
path = './{}_barchart2.png'.format(title)
plt.savefig(path)
print('Chart saved to file {}'.format(path))
# close figure so it doesn't display
plt.close()
return
# define the main method that will run when our python program runs
def main():
global connection
# we will hard code the database name, could also get from user
db_path = './ma5.db'
# create connection using function defined above
connect(db_path)
# loop program until user chooses to exit
while(True):
# prompt user to selet query
print('\nPlease select query to execute')
print('1. ratio of female over male residents for each neighbourhood')
print('2. change of male and female residents in a given age range over 2009-2016')
print('3. Exit program')
query_selection = input('Selection: ')
# input function will return string so compare to strings
if query_selection == '1':
query1()
elif query_selection == '2':
query2()
# if user selects 3 break program while loop and exit program
elif query_selection == '3':
print('Goodbye :)')
break
# if user enters anything but 1, 2, or 3 prompt for valid input
else:
print("\nInvalid input!\nSelection must be 1, 2, or 3")
# close connection before exiting
connection.close()
# run main method when program starts
if __name__ == "__main__":
main()