# Import pymysql connector to MySQL: allow the uses of the object pymysql
import pymysql

# [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.
cnx = pymysql.connect(user='temp1', 
    password='abcdef',
    host='localhost',
    database='toyu',
    port=3308)

# Create a cursor using the connection.
#      cursor(cursor=None)
#             Create a new cursor to execute queries with.

#   Parameters:	cursor – The type of cursor to create; 
#   one of Cursor, SSCursor, DictCursor, or SSDictCursor. 
#   None means use Cursor.
cursor = cnx.cursor()

# [2] Prepare a SQL query for the problem
#     For example: Tony Hawk: major=Computer Science; advisor=Paul Smith
#         student: FName, LName; department: deptName; faculty: fname, lname
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
#   https://pymysql.readthedocs.io/en/latest/modules/cursors.html
#   execute(query, args=None)
#   Execute a query

#   Parameters:	
#   query (str) – Query to execute.
#   args (tuple, list or dict) – parameters used with query. (optional)
#   Returns:	
#       Number of affected rows
#       Return type: int

#  If args is a list or tuple, %s can be used as a placeholder in the query. 
#   If args is a dict, %(name)s can be used as a placeholder in the query.
cursor.execute(query)

# [3] Use the result in the query
#     Iterate through the result and put the three result columns into
#     the variables student, major and advsior.
for (student, major, advisor) in cursor:
    #   "{}: major={}; advisor={}" is a string object.
    #   {}: a placeholder for an argument in the format method.
    print("{}: major={}; advisor={}".format(student, major, advisor))

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