#
# Sample update program
#
# The program changes the section id of a section
#
# It then changes the section id in all of the enroll
#  records with that section id
#
import mysql.connector
import sys
import getpass

# get command line arguments
if len(sys.argv) < 2:
    print "Usage:  java ChangeSectionID <oldid> <newid>"
    sys.exit()

oldid = int(sys.argv[1])
newid = int(sys.argv[2])

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

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

# first, make sure that the new id doesn't already exist
query = ("select * from section where sectid=%s")
cursor.execute(query, (newid,))
if cursor.rowcount > 0:
    print "section id "+str(newid)+" already exists.  update aborted."
    sys.exit()

# perform the update
update = ("update section set sectid=%s where sectid=%s")
cursor.execute(update, (newid,oldid) )
if cursor.rowcount == 0:
    print "no matching section tuples round"
    sys.exit()
print str(cursor.rowcount)+" section tuple updated"

# now, update the section id in all matching enroll tuples
update = ("update enroll set sectionid=%s where sectionid=%s")
cursor.execute(update, (newid,oldid))
print str(cursor.rowcount)+" enroll tuples updated"

# commit the transaction
cnx.commit()
cursor.close()
cnx.close()