from dbconfig import *
import pymysql

db = get_mysql_param()

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

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 AS s LEFT JOIN department AS d
        ON (s.major = d.deptCode)
     LEFT JOIN faculty AS f
        ON (s.advisor = f.facId);
'''

# Execute the query
cursor.execute(query)

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

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