Museum-Database-Manager/AdminMenu.py at main · cotill/Museum-Database-Manager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
import mysql.connector
import AlterMenu
import BrowseMenu

def AdminMenu(dbuser,dbpass):
    cnx = mysql.connector.connect(user=dbuser, password=dbpass,
                              host='127.0.0.1',
                              database='ARTMUSEUM')

    cursor = cnx.cursor()


    while True:
        print ("\nWhat would you like to do today?")

        print ("\n\n(0) Add Users")
        print ("(1) Delete Users")
        print ("(2) Show Users")
        print ("(3) Alter data")
        print ("(4) Browse data")
        print ("(5) run sql command")
        print ("(6) run sql file")
        print ("(7) Log out")

        ans = str(input("  ->  "))

        if ans == ("0"):
            userid = str(input("enter new UserID  ->  "))
            passid = str(input("enter new PassID  ->  "))
            usertype = str(input("enter new Utype  ->  "))

            cursor.execute("INSERT INTO USERS (UserID, PassID, Utype) VALUES (%s,%s,%s);",(userid,passid,usertype,))

            cnx.commit()
            print("\n\n Done!\n")

        elif ans == "1":
            userid = str(input("enter UserID to delete ->  "))

            cursor.execute("DELETE FROM USERS WHERE UserID = %s ;",(userid,))
            cnx.commit()
            print("\n  ",userid," deleted!")

        elif ans == "2":

            cursor.execute("SELECT * FROM USERS;")
            rows=cursor.fetchall()

            # print(rows)
            for row in rows:
                for col in row:
                    print(col,end=' ')
                print()

        elif ans == "3":

            AlterMenu.alter(dbuser,dbpass)

        elif ans == "4":

            BrowseMenu.browse(dbuser,dbpass)

        elif ans == "5":
            query = str(input(" -> "))

            cursor.execute(query)
            cnx.commit()

        elif ans == "6":
            fd = open("QUERIES.sql", 'r')
            sqlFile = fd.read()
            fd.close()


            sqlCommands = sqlFile.split(';')
            newsqlcommands = []

            newnewsql = []

            for subsd in sqlCommands:
                newsqlcommands.append(subsd.replace("\n",""))

            for i in newsqlcommands:
                i = i+";"
                newnewsql.append(i)

            print(newnewsql)

            for comand in newnewsql:
                try:
                    cursor.execute(comand)

                    data = cursor.fetchall()
                    if(data):
                        for j in range(0,len(data)):
                            print(data[j])

                        print("\n\n\n")
                    else:
                        ("invalid")
                except:
                    print("invalid")


        elif ans == "7":
            cnx.close()
            break

        else:
            print("not valid :(")