· 6 years ago · Nov 25, 2019, 08:56 PM
1import sqlite3
2from sqlite3 import Error
3import random
4
5sql_create_college_table = """ CREATE TABLE IF NOT EXISTS College (
6 CollegeID integer PRIMARY KEY,
7 CollegeName text NOT NULL
8 ); """
9
10sql_create_score_table = """CREATE TABLE IF NOT EXISTS Score (
11 StudentID integer PRIMARY KEY,
12 CollegeID integer NOT NULL,
13 HW1 integer NULL,
14 HW2 integer NULL,
15 HW3 integer NULL,
16 Quiz1 integer NULL,
17 Quiz2 integer NULL,
18 Midterm integer NULL,
19 Final real NULL,
20 FOREIGN KEY (CollegeID) REFERENCES College (CollegeID)
21 );"""
22sql_insert_colleges = """INSERT INTO College
23 ('CollegeID', 'CollegeName')
24 VALUES
25 (1,'College of Engineering'),
26 (2,'College of Business'),
27 (3,'College of Computing and Informatics')"""
28
29sql_insert_scores = """INSERT INTO Score
30 ('StudentID', 'CollegeID', 'HW1', 'HW2', 'HW3', 'Quiz1', 'Quiz2', 'Midterm', 'Final')
31 VALUES
32 (18, 1, {}, {}, {}, {}, {}, {}, {}),
33 (20, 2, {}, {}, {}, {}, {}, {}, {}),
34 (15, 2, {}, {}, {}, {}, {}, {}, {}),
35 (14, 1, {}, {}, {}, {}, {}, {}, {}),
36 (25, 1, {}, {}, {}, {}, {}, {}, {}),
37 (30, 1, {}, {}, {}, {}, {}, {}, {}),
38 (45, 3, {}, {}, {}, {}, {}, {}, {}),
39 (50, 3, {}, {}, {}, {}, {}, {}, {})""".format(random.randrange(40, 101),
40 random.randrange(40, 101),
41 random.randrange(40, 101),
42 random.randrange(40, 101),
43 random.randrange(40, 101),
44 random.randrange(40, 101),
45 random.randrange(40, 101),
46 random.randrange(40, 101),
47 random.randrange(40, 101),
48 random.randrange(40, 101),
49 random.randrange(40, 101),
50 random.randrange(40, 101),
51 random.randrange(40, 101),
52 random.randrange(40, 101),
53 random.randrange(40, 101),
54 random.randrange(40, 101),
55 random.randrange(40, 101),
56 random.randrange(40, 101),
57 random.randrange(40, 101),
58 random.randrange(40, 101),
59 random.randrange(40, 101),
60 random.randrange(40, 101),
61 random.randrange(40, 101),
62 random.randrange(40, 101),
63 random.randrange(40, 101),
64 random.randrange(40, 101),
65 random.randrange(40, 101),
66 random.randrange(40, 101),
67 random.randrange(40, 101),
68 random.randrange(40, 101),
69 random.randrange(40, 101),
70 random.randrange(40, 101),
71 random.randrange(40, 101),
72 random.randrange(40, 101),
73 random.randrange(40, 101),
74 random.randrange(40, 101),
75 random.randrange(40, 101),
76 random.randrange(40, 101),
77 random.randrange(40, 101),
78 random.randrange(40, 101),
79 random.randrange(40, 101),
80 random.randrange(40, 101),
81 random.randrange(40, 101),
82 random.randrange(40, 101),
83 random.randrange(40, 101),
84 random.randrange(40, 101),
85 random.randrange(40, 101),
86 random.randrange(40, 101),
87 random.randrange(40, 101),
88 random.randrange(40, 101),
89 random.randrange(40, 101),
90 random.randrange(40, 101),
91 random.randrange(40, 101),
92 random.randrange(40, 101),
93 random.randrange(40, 101),
94 random.randrange(40, 101))
95
96
97def create_connection(db_file):
98 """ create a database connection to the SQLite database
99 specified by db_file
100 :param db_file: database file
101 :return: Connection object or None
102 """
103 conn = None
104 try:
105 conn = sqlite3.connect(db_file)
106 return conn
107 except Error as e:
108 print(e)
109
110 return conn
111
112
113def create_table(conn, create_table_sql):
114 """ create a table from the create_table_sql statement
115 :param conn: Connection object
116 :param create_table_sql: a CREATE TABLE statement
117 :return:
118 """
119 try:
120 c = conn.cursor()
121 c.execute(create_table_sql)
122 except Error as e:
123 print(e)
124
125
126def print_college_table(conn):
127 dash = '-' * 100
128 sql_select_Query = "select * from College"
129 cursor = conn.cursor()
130 cursor.execute(sql_select_Query)
131 records = cursor.fetchall()
132
133 print("\nCollege Table")
134 print(dash)
135 print('{:<10s}{:<25s}'.format("CollegeID", "CollegeName"))
136 print(dash)
137 for row in records:
138 print('{:<10s}{:<25s}'.format(str(row[0]), str(row[1])))
139
140
141def print_score_table(conn, more):
142 dash = '-' * 100
143 sql_select_Query = "select * from Score"
144 cursor = conn.cursor()
145 cursor.execute(sql_select_Query)
146 records = cursor.fetchall()
147
148 print("\nScore Table")
149 print(dash)
150 if more:
151 print('{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<12s}{:<25s}'.format("StudentID",
152 "CollegeID", "HW1",
153 "HW2", "HW3",
154 "Quiz1", "Quiz2",
155 "Midterm", "Final",
156 "TotalScore",
157 "Grade"))
158 else:
159 print('{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}'.format("StudentID", "CollegeID", "HW1",
160 "HW2", "HW3", "Quiz1", "Quiz2",
161 "Midterm", "Final"))
162
163 print(dash)
164 for row in records:
165 if more:
166 print('{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<12s}{:<25s}'.format(str(row[0]),
167 str(row[1]),
168 str(row[2]),
169 str(
170 row[3]),
171 str(row[4]),
172 str(row[5]),
173 str(row[6]),
174 str(row[7]),
175 str(round(
176 row[8])),
177 str(round(
178 row[9],
179 2)),
180 str(row[10])))
181 else:
182 print('{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}{:<10s}'.format(str(row[0]), str(row[1]),
183 str(row[2]), str(
184 row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]), str(round(row[8]))))
185
186
187def populate_college_table(conn):
188 try:
189 c = conn.cursor()
190 c.execute(sql_insert_colleges)
191 except Error as e:
192 print(e)
193
194
195def populate_score_table(conn):
196 try:
197 c = conn.cursor()
198 c.execute(sql_insert_scores)
199 except Error as e:
200 print(e)
201
202
203def add_new_columns(conn):
204 try:
205 c = conn.cursor()
206 addColumn1 = "ALTER TABLE Score ADD COLUMN TotalScore real"
207 addColumn2 = "ALTER TABLE Score ADD COLUMN Grade varchar(2)"
208 c.execute(addColumn1)
209 c.execute(addColumn2)
210 updateTotalScoreColumns = "UPDATE Score SET TotalScore = HW1*0.1+HW2*0.1+HW3*0.1+Quiz1*0.15+Quiz2*0.15+Midterm*0.2+Final*0.2"
211 updateGradeColumns = "UPDATE Score SET Grade = CASE WHEN TotalScore >= 90 THEN 'A' WHEN TotalScore >= 80 THEN 'B' WHEN TotalScore >= 70 THEN 'C' WHEN TotalScore >= 60 THEN 'D' ELSE 'F' END"
212
213 c.execute(updateTotalScoreColumns)
214 c.execute(updateGradeColumns)
215 except Error as e:
216 print(e)
217
218
219def average_score(conn):
220 sql_avg_Query = "select avg(TotalScore) from Score"
221 cursor = conn.cursor()
222 cursor.execute(sql_avg_Query)
223 records = cursor.fetchall()
224 print("\nAverage score for class: " + str(records[0][0]))
225
226
227def number_of_pass(conn):
228 sql_count_Query = "select count(*) from Score where Grade <> 'F'"
229 cursor = conn.cursor()
230 cursor.execute(sql_count_Query)
231 records = cursor.fetchall()
232 print("\nNumber of students pass the course: " + str(records[0][0]))
233
234
235def better_then_C(conn):
236 dash = '-' * 30
237 sql_count_Query = "select StudentID, Final from Score where Grade = 'B' or Grade = 'A' order by Final asc"
238 cursor = conn.cursor()
239 cursor.execute(sql_count_Query)
240 records = cursor.fetchall()
241 print("\nStudents with grade better then C table")
242 print(dash)
243 print('{:<10s}{:<10s}'.format("StudentID", "Final"))
244 print(dash)
245 for row in records:
246 print('{:<10s}{:<10s}'.format(str(row[0]), str(row[1])))
247
248
249def average_scores_for_college(conn):
250 dash = '-' * 60
251 sql_avg_college_Query = "select CollegeName, avg(TotalScore) from Score s, College c where s.CollegeID = c.CollegeID group by s.CollegeID"
252 cursor = conn.cursor()
253 cursor.execute(sql_avg_college_Query)
254 records = cursor.fetchall()
255 print("\nAverage score for each college table")
256 print(dash)
257 print('{:<40s}{:<30s}'.format("CollegeName", "AverageScore"))
258 print(dash)
259 for row in records:
260 print('{:<40s}{:<30s}'.format(str(row[0]), str(row[1])))
261
262
263def update_records(conn):
264 try:
265 c = conn.cursor()
266 updateFinal = "UPDATE Score SET Final = Final*1.05"
267 updateTotalScoreColumns = "UPDATE Score SET TotalScore = HW1*0.1+HW2*0.1+HW3*0.1+Quiz1*0.15+Quiz2*0.15+Midterm*0.2+Final*0.2"
268 updateGradeColumns = "UPDATE Score SET Grade = CASE WHEN TotalScore >= 90 THEN 'A' WHEN TotalScore >= 80 THEN 'B' WHEN TotalScore >= 70 THEN 'C' WHEN TotalScore >= 60 THEN 'D' ELSE 'F' END"
269
270 c.execute(updateFinal)
271 c.execute(updateTotalScoreColumns)
272 c.execute(updateGradeColumns)
273 except Error as e:
274 print(e)
275
276
277def main():
278 database = "Rodrigo.db"
279
280 conn = create_connection(database)
281
282 if conn is not None:
283 mycursor = conn.cursor()
284 sql1 = "DROP TABLE IF EXISTS Score"
285 sql2 = "DROP TABLE IF EXISTS College"
286 mycursor.execute(sql1)
287 mycursor.execute(sql2)
288
289 create_table(conn, sql_create_college_table)
290
291 create_table(conn, sql_create_score_table)
292
293 populate_college_table(conn)
294 populate_score_table(conn)
295
296 print_college_table(conn)
297 print_score_table(conn, False)
298
299 add_new_columns(conn)
300 print("\nScore table after adding two more columns")
301 print_score_table(conn, True)
302 average_score(conn)
303 number_of_pass(conn)
304 better_then_C(conn)
305 average_scores_for_college(conn)
306 update_records(conn)
307 print("\nScore table after multiply final with 105%")
308 print_score_table(conn, True)
309 else:
310 print("Error! cannot create the database connection.")
311
312main()