import mysql.connector
cnx = mysql.connector.connect(host="localhost",user="root", password="newpass?21" , database = "DB")
#print(cnx)
mycursor = cnx.cursor()
#prwta ftiaxnw ti vasi
#mycursor.execute("CREATE DATABASE DB")
#meta elegxw an uparxei
##mycursor.execute("SHOW DATABASES")
##for db in mycursor:
##    print(db)
###ftiaxnw table
###mycursor.execute("CREATE TABLE students (name VARCHAR(255), subject VARCHAR(255), grade INTEGER(100))")
####elegxw an uparxei
####mycursor.execute("SHOW TABLES")
####for tb in mycursor:
####    print(tb)
def main():
    cnx = mysql.connector.connect(host="localhost",user="root", password="newpass?21")
    # h metavliti cnx.cursor() den yparxei pou8ena
    # einai cnx.cursor()
    # mycursor = cnx.cursor()
    mycursor = cnx.cursor()
    # Auto thn deuterh fora pou trexei kwdika den xreiazetai
    # Giati ka8e fora pou trexei to programma 8a ftiaxnei thn vash
    #mycursor.execute("CREATE TABLE students (name VARCHAR(255), subject VARCHAR(255), grade INTEGER(100))")

    message = ("The valid actions are:" )
    message2 = ("insert // read // delete // update // view // exit ")

    print(message)
    print(message2)
    userInput = ""
    while userInput != "exit":
        userInput = raw_input("enter what you want to do:")
        if userInput == "insert":
            createEntry()
        elif userInput == "read":
            retrieveEntry()
        elif userInput == "delete":
            deleteEntry()
        elif userInput == "update":
            updateEntry()
        elif userInput == 'view':
            viewEntries()

def viewEntries():
    mycursor = cnx.cursor()
    mycursor.execute("SELECT * FROM students")
    myresult = mycursor.fetchall()
    for row in myresult:
        print(row)
    main()
    pass



def createEntry():
    mycursor = cnx.cursor()
    print("Give a name:")
    namei = raw_input()
    print("Give a subject:")
    subjecti = raw_input()
    print("Give a grade:")
    gradei = raw_input()
    sqlFormula = "INSERT INTO students (name, subject, grade) VALUES (%s, %s, %s)"
    val = (namei, subjecti, gradei)
    mycursor.execute(sqlFormula, val)

    cnx.commit()

    print(mycursor.rowcount, "record inserted.")
    main()
    "We will use this function to create DB Entries"
    pass

def retrieveEntry():
    print("Retrieve entries by name, by subject, by grade. y/n?")
    answer = raw_input()
    if answer == "y":
        print("By name = n, by subject = s, by grade = g:")
        entriesby = raw_input()
        if entriesby == "n":
            print("Give a name:")
            namer = raw_input()
            mycursor = cnx.cursor()
            mycursor.execute("SELECT * FROM students WHERE name = 'namer'")
            myresultbyname = mycursor.fetchall()
            for result in myresultbyname:
                print(result)
            main()
        elif entriesby == "s":
            print("Give a subject:")
            subjectr = raw_input()
            mycursor = cnx.cursor()
            mycursor.execute("SELECT * FROM students WHERE subject = 'subjectr'")
            myresultbysubject = mycursor.fetchall()
            for result in myresultbysubject:
                print(result)
            main()
        elif entriesby == "g":
            print("Give a grade:")
            grader = raw_input()
            mycursor = cnx.cursor()
            mycursor.execute("SELECT * FROM students WHERE grade = 'grader'")
            myresultbygrade = mycursor.fetchall()
            for result in myresultbygrade:
                print(result)
            main()
    elif answer =="n":
        print("Retrieve entries by starting character/s? y/n")
        answer2 = raw_input()
        if answer2 == "y":
            print("Starting character/s of name = n, subject = s, grade = g")
            startchar = raw_input()
            if starchar == 'n':
                print("Give the starting character/s:")
                starcharn = raw_input()
                mycursor = cnx.cursor()
                mycursor.execute("SELECT * FROM students WHERE name LIKE 'starcharn%'")
                myresultbynamec = mycursor.fetchall()
                for result in myresultbynamec:
                    print(result)
                main()
            elif starchar == 's':
                print("Give the starting character/s:")
                starchars = raw_input()
                mycursor = cnx.cursor()
                mycursor.execute("SELECT * FROM students WHERE subject LIKE 'starchars%'")
                myresultbysubjectc = mycursor.fetchall()
                for result in myresultbysubjectc:
                    print(result)
                main()
            elif starchar == 'g':
                print("Give the starting character/s:")
                starcharg = raw_input()
                mycursor = cnx.cursor()
                mycursor.execute("SELECT * FROM students WHERE grade LIKE 'starcharg%'")
                myresultbygradec = mycursor.fetchall()
                for result in myresultbygradec:
                    print(result)
                main()
        elif answer2 == "n":
            print ("Retrieve entries by last character/s? y/n")
            answer3 = raw_input()
            if answer3 == 'y':
                print ("By name = n or subject = s?")
                answer4 = raw_input()
                if answer4 == 'n':
                    print ("Give the last character/s:")
                    lastcharn = raw_input()
                    mycursor = cnx.cursor()
                    mycursor.execute("SELECT * FROM students WHERE name LIKE '%lastcharn'")
                    myresultbynamecl = mycursor.fetchall()
                    for result in myresultbynamecl:
                        print(result)
                    main()
                elif answer4 == 's':
                    print ("Give the last character/s:")
                    lastchars = raw_input()
                    mycursor = cnx.cursor()
                    mycursor.execute("SELECT * FROM students WHERE name LIKE '%lastchars'")
                    myresultbysubjectcl = mycursor.fetchall()
                    for result in myresultbysubjectcl:
                        print(result)
                    main()
            elif answer3 == 'n':
                main()


def updateEntry():
    mycursor = cnx.cursor()
    print  ('Update by name = n, subject = s, grade = g:')
    optionu = raw_input()
    if optionu == "n":
        nameu = raw_input("Give a name:")
        print ("Update a subject = s or a grade = g")
        optionn = raw_input()
        if optionn == "s":
            print  ("Give a subject:")
            subjectn = raw_input()
            sql = "UPDATE students SET subject = 'subjectn' WHERE name = 'nameu' "
            mycursor.execute(sql)
            cnx.commit()
            main()
        elif optionn == "g":
            print  ("Give a grade:")
            graden = raw_input()
            sql = "UPDATE students SET grade = 'graden' WHERE name = 'nameu'"
            mycursor.execute(sql)
            cnx.commit()
            main()
    elif optionu == "s":
        subjectu = raw_input("Give a subject:")
        print  ("Update a name = n or a grade = g")
        options = raw_input()
        if options == "n":
            print ("Give a name:")
            names = raw_input()
            sql = "UPDATE students SET name = 'names' WHERE subject = 'subjectu'"
            mycursor.execute(sql)
            cnx.commit()
            main()
        elif options == "g":
            print ("Give a grade:")
            grades = raw_input()
            sql = "UPDATE students SET grade = 'grades' WHERE subject = 'subjectu'"
            mycursor.execute(sql)
            cnx.commit()
            main()
    elif optionu == "g":
        gradeu = raw_input("Give a grade:")
        print("Update a name = n or a subject = s")
        optiong = input()
        if optiong == "n":
            print("Give a name:")
            nameg = raw_input()
            sql = "UPDATE students SET name = 'nameg' WHERE grade = 'gradeu'"
            mycursor.execute(sql)
            cnx.commit()
            main()
        elif optiong == "s":
            print("Give a subject:")
            subjectg = raw_input()
            sql = "UPDATE students SET subject = 'subjectg' WHERE grade = 'gradeu' "
            mycursor.execute(sql)
            cnx.commit()
            main()

def deleteEntry():
    mycursor = cnx.cursor()
    print('Choose to delete by name = n,subject = s,grade = g:, drop = d')
    optiond = raw_input()
    if optiond == "n":
        print("Give a name:")
        nameD = raw_input()
        sql = "DELETE FROM students WHERE name = 'nameD'"
        mycursor.execute(sql)
        cnx.commit()
        main()

    elif optiond == 's':
        print("Give a subject:")
        subjectD = raw_input()
        sql = "DELETE FROM students WHERE subject = 'subjectD'"
        mycursor.execute(sql)
        cnx.commit()
        main()

    elif optiond == 'g':
        print("Give a grade:")
        gradeD = raw_input()
        sql = "DELETE FROM students WHERE grade = 'gradeD'"
        mycursor.execute(sql)
        cnx.commit()
        main()
    elif optiond == 'd':
        sql = "DROP TABLE IF EXIST students"
        mycursor.execute(sql)
        main()


# The reason why we use
# if __name__== "__main__":
# is
# if we want to reuse this code
# and import it somewhere as library
# we don't want the code after
# if __name__== "__main__":
# to be executed
# Now that we don't use this code as a library
# we want to run the main() function
# as soon as the programm start
if __name__== "__main__":
    main()