from dbconfig import * import pymysql import sys # Get command line argument of product line id. if len(sys.argv) > 1: dept = sys.argv[1] else: dept = 'ITEC' # Connect to big_pvfc of the MySQL server and # set up SQL statement to obtain product information # in the product line db = get_mysql_param() cnx = pymysql.connect(user=db['user'], password=db['password'], host=db['host'], database=db['database']) cursor = cnx.cursor() query = ''' select concat(s.fname, ' ', s.lname) as student, count(e.classId) as num_classes from department d join student s on (d.deptCode = s.major) left join enroll e on (s.stuId = e.stuId) where d.deptCode = %s group by student order by student ''' cursor.execute(query,(dept,)) # Print product report. print('Classes enrolled by students majoring in dept ' + dept + ':') print('----------------------------------------------------') for (student, num_classes) in cursor: print(student + ': ' + str(num_classes) + '.') cursor.close() cnx.close()