import csv    # for reading and manipulating the CSV file
import MySQLdb   #Mysql Connector

#Establish connection to MysqlDB
mysql_db = MySQLdb.connect(host="", user="root", password="", db="edxapp", port=3306)

#create a cursor for sql statments
mysql_cursor = mysql_db.cursor()

# container for users emails, their countries and their foriegn key id
users_countries_ids = []

# loop though CSV file and get the id of each email address from auth_user
input_file = open('country.csv')
csv_f = csv.reader(input_file)
cursor = 0

for row in csv_f:
    # add all emails and countries code to containes
    users_countries_ids.append(row)
    # query the id of the email address
    mysql_cursor.execute('''select id from auth_user where email="%s"''' % (users_countries_ids[cursor][0]))
    user_id = mysql_cursor.fetchone()
    user_id = int(user_id[0])
    #user_id = int(user_id)
    # append id of each email to the container
    users_countries_ids[cursor].append(user_id)
    # update the country field in auth_userprofile
    mysql_cursor.execute('''update auth_userprofile set country=%s where user_id=%s''',(users_countries_ids[cursor][1],users_countries_ids[cursor][2]))
    mysql_db.commit()
    cursor += 1