YfXh3a0i

1from __future__ import print_function
2import mysql.connector as mariadb
3from mysql.connector import errorcode
4import csv
5
6file_name = 'grade.csv'
7
8def main():
9    #DB detail
10    DB_NAME = 'grade_record'
11    TABLES = {}
12    TABLES['grade'] = (
13        "CREATE TABLE `grade` ("
14        "  `year` varchar(4) NOT NULL,"
15        "  `semester` varchar(1) NOT NULL,"
16        "  `subject_code` varchar(10) NOT NULL,"
17        "  `subject` varchar(50) NOT NULL,"
18        "  `credit` varchar(1) NOT NULL,"
19        "  `grade` varchar(2) NOT NULL,"
20        "  `score` varchar(3) NOT NULL)")
21    
22    #connect to mariadb
23    mariadb_connection = mariadb.connect(user='root', password='054620649')
24    cursor = mariadb_connection.cursor()
25    
26    #try to use or create db
27    try:
28        cursor.execute("USE {}".format(DB_NAME))
29    except:
30        cursor.execute("CREATE DATABASE {}".format(DB_NAME))
31        cursor.execute("USE {}".format(DB_NAME))
32        
33    #try to create table        
34    for name, ddl in TABLES.iteritems():
35        try:
36            print("Creating table {}: ".format(name), end='')
37            cursor.execute(ddl)
38        except mariadb.Error as err:
39            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
40                print("already exists.")
41            else:
42                print(err.msg)
43        else:
44            print("OK")
45 
46    #read csv
47    with open(file_name) as csvfile:
48        next(csvfile)
49        reader = csv.reader(csvfile, delimiter=',')
50        store = []
51        for row in reader:
52            store.append(row)
53                
54    #insert data
55    for data in store:
56        try:
57            #add_grade = (
58                #"INSERT INTO grade "
59                #"(year,semester,subject_code,subject,credit,grade,score) "
60                #"VALUES (%s,%s,%s,%s,%s,%s,%s);", (data[0], data[1], data[2], data[3], data[4], data[5], data[6]))
61            cursor.execute(
62                "INSERT INTO grade "
63                "(year,semester,subject_code,subject,credit,grade,score) "
64                "VALUES (%s,%s,%s,%s,%s,%s,%s);", (data[0], data[1], data[2], data[3], data[4], data[5], data[6]))
65            print ("Insert Data : Done")
66        except mariadb.Error as error:
67            print("Error: {}".format(error))
68
69    mariadb_connection.commit()
70
71    mariadb_connection.close()
72                
73main()