import pymysql
from pymysql.converters import escape_string
import os
# import csv
import xlrd
import re


# -------------------- read excel ---------------------------

print(os.path.abspath(__file__))
filename = "/Users/billxzy1215/Desktop/COMP 2043 SE/QuizApp/data/course/course_china_2020.xlsx"

Module_Id = []
Name = []
Des = []

data = xlrd.open_workbook(filename)
sh = data.sheet_by_index(0)
# with open (filename,encoding='utf-8') as myFile:
#     lines = csv.reader(myFile)
#     for line in lines:
#         print(line)

for rx in range(sh.nrows):
    Module_Id.append(sh.row(rx)[1].value)
    Name.append(escape_string(sh.row(rx)[2].value))
    html = sh.row(rx)[5].value
    pre = re.compile('>(.*?)<') #html去标签化
    text= ''.join(pre.findall(html))
    Des.append(escape_string(text))

# ------------------- mysql connection ---------------------------
db = pymysql.connect(host="172.16.17.32",port=3306,user="root",passwd="<PASSWORD>",db="QuizApp")

cursor = db.cursor()

# SELECT EXAMPLE
# sql = "SELECT * FROM Student"
# try:
#     cursor.execute(sql)
#     results = cursor.fetchall()
#     for row in results:
#         id = row[0]
#         Student_Id = row[1]
#         Email = row[2]
#         WeChatId = row[3]
#         Avatar = row[4]
#         print("id:",id, "Student_Id: ", Student_Id, "Email: ", Email, "WeChatId: ", WeChatId, "Avatar: ", Avatar)
# except:
#     print("Error: unable to fetch data")
# sql = "INSERT INTO `Teacher` (`id`,`Email`, `Name`, `WeChatId`, `Avatar`) VALUES (NULL,'{}','{}', NULL, NULL)"
# print(sql.format(email[0], name[0]))
# sql = sql.format(email[0], name[0])
# cursor.execute(sql)
# results = cursor.fetchall()
# print(results)
for rx in range(sh.nrows):
    # print("Module_Id:", Module_Id[rx])
    # print("Name:", Name[rx])
    # print("Des:", Des[rx])
    sql = "INSERT INTO `Module` (`Module_Id`,`Year`, `Name`, `Description`) VALUES ('{}','2020','{}', '{}')"
    sql = sql.format(Module_Id[rx], Name[rx], Des[rx])
    cursor.execute(sql)
    results = cursor.fetchall()

db.commit() # 不commit会导致phpmyadmin搜不到，但实际上primary key已经auto increment了
cursor.close()
db.close()