import sys
# Import pymysql connector to MySQL: allow the uses of the object pymysql
import pymysql
# import everything from dbconfig: get_mysql_param()
from dbconfig import *

# [1] Making connection to the MySQL server
#       localhost: accessing local MySQL.
#       if accessing dcm server: host='dcm.uhcl.edu'
#       database='toyu': make the default database to by 'toyu'.
#       May still use supply.part.
#       If default port of 3306 is used, then port=3308 not needed.
#   see: https://pymysql.readthedocs.io/en/latest/ for the documentation
#    of the connection object: cnx.
db = get_mysql_param()
cnx = pymysql.connect(user=db['user'], password=db['password'],
                      host=db['host'],
                      database=db['database'],
                      port=int(db['port']))

# Create a cursor using the connection.
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)
WHERE d.schoolCode = %s;
'''

# [2b] Get input values
school_code = sys.argv[1]

# Execute the query
cursor.execute(query, (school_code,))

# [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()