Type code in PYTHON please. Also type it please TYPE it to avoid messy handwriti
ID: 3833306 • Letter: T
Question
Type code in PYTHON please. Also type it please TYPE it to avoid messy handwriting.
1. Practice with SQLite (or Python plus Database)
Create in Python – use the class discussed methods of Try, Except and Finally – a SQLite database. The database should be name “my_first.db”. It should have the following two tables.
English
a) First Function. Create Python code to create the above tables and populate them with data as indicated above. Use Parametrized queries. You can add more data to the tables than the minimum described above.
b) Second Function. In a separate Python function read the data in the two tables and compute and print the average age of the students in each course Major. Again use Try, Except, Finally Loops.
Majors id_course Name 4 Mathematics 5 Chemistry 6 Physics 7 Computer Science 8English
Explanation / Answer
from __future__ import print_function
import sqlite3
#Python 3.6
def create_db():
try:
conn = sqlite3.connect('my_first.db')
test = conn.cursor()
# Create table Majors and store data in it
test.execute('''CREATE TABLE Majors(id_course, Name)''')
test.execute("INSERT INTO Majors VALUES (4, 'Mathematics')")
test.execute("INSERT INTO Majors VALUES (5, 'Chemistry')")
test.execute("INSERT INTO Majors VALUES (6, 'Physics')")
test.execute("INSERT INTO Majors VALUES (7, 'Computer Science')")
test.execute("INSERT INTO Majors VALUES (8, 'English')")
# Create table Students and store data in it
test.execute('''CREATE TABLE Students (Id INTEGER PRIMARY KEY, First, Last, Age, id_course)''')
test.execute("INSERT INTO Students VALUES (NULL, 'Doe', 'John', 45, 5)")
test.execute("INSERT INTO Students VALUES (NULL, 'Bob', 'Smith', 23, 7)")
test.execute("INSERT INTO Students VALUES (NULL, 'Sally', 'Jones', 22, 7)")
test.execute("INSERT INTO Students VALUES (NULL, 'Janet', 'Cooke', 35, 7)")
test.execute("INSERT INTO Students VALUES (NULL, 'Arthur', 'Peck', 22, 5)")
conn.commit()
except:
print("Error while connect to DB my_first.db")
finally:
print("Connection closed")
conn.close()
def print_avg():
try:
conn = sqlite3.connect('my_first.db') #Try to connect to my_first.db
test = conn.cursor()
for row in test.execute('SELECT * FROM Majors'): #Fetch all rows from Majors Table
count = 0 #Count of students
Total = 0 #Total age
for student in test.execute('SELECT Age FROM Students WHERE id_course = row[0]'): #Query to fetch age of student on the basis of course id
count += 1
Total += student
print(row[1],Total/count)
except:
print("Error while connect to DB my_first.db")
finally:
print("Connection closed")
conn.close()
def main():
create_db()
print_avg()
if __name__ == '__main__':
main()
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.