import mysql.connector
import sys
import getpass

# get command line arguments
if len(sys.argv) < 2:
    print ("Usage: python addstudent.py <student-name> <major> <graduation-year>")
    sys.exit()

sname = sys.argv[1]
smajor = sys.argv[2]
gradyr = int(sys.argv[3])

# prompt user for username and password
uname = input("Enter username: ");
pwd = getpass.getpass("Enter password: ")

cnx = mysql.connector.connect(user=uname, password=pwd, host='sql.cs.oberlin.edu', database='studentdb', auth_plugin='mysql_native_password')
cursor = cnx.cursor()

# first, determine the maximum value of sid in the database
query = ("select max(sid) from student")
cursor.execute(query)
maxsid = cursor.fetchone()

# the new student id is 1 higher that the current max
sid = maxsid[0]+1

# now, look up the dept name to get the dept did
query = ("select did from dept where dname='"+smajor+"'")
cursor.execute(query)
did = cursor.fetchone()
majorid = str(did[0])

# next, perform the insertion
insertStmt = ("insert into student values("+str(sid)+",'"+sname+"',"+majorid+","+str(gradyr)+")")
print (insertStmt)
cursor.execute(insertStmt)

# the transaction must be committed
cnx.commit()
cursor.close()
cnx.close()