from dbconfig import *
import pymysql

db = get_mysql_param()
cnx = pymysql.connect(user=db['user'], password=db['password'],
                      host=db['host'],
                      database=db['database'])
cursor = cnx.cursor()


#   [2] Prepare a SQL query for the problem
query = '''
select concat(s.fname, ' ', s.lname) as student, 
   d.deptName, 
   concat(f.fname, ' ', f.lname) as advisor
from student s, department d, faculty f
where s.major = d.deptCode
and s.advisor = f.facId;
'''

#   Execute the query
cursor.execute(query)

#   [3] Use the result in the query
for (student, major, advisor) in cursor:
   print("{}: major={}; advisor={}".format(student, major, advisor))

#   [4] Housekeeping
cursor.close()
cnx.close()