· 10 years ago · Mar 21, 2016, 06:15 PM
1"""
2-------------------------------------------------------
3notown.py
4-------------------------------------------------------
5Author: Andrew Georgescu
6ID: 130280590
7Email: geor0590@mylaurier.ca
8-------------------------------------------------------
9"""
10from _mysql_connector import MySQL
11
12
13"""
14-------------------------------------------------------
15Main Program
16-------------------------------------------------------
17"""
18
19
20import mysql.connector
21db = mysql.connector.connect(user = 'geor0590', host = 'hopper.wlu.ca', password = 'bigtop4',database = 'geor0590')
22cur = db.cursor()
23
24UID = -1
25
26Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
27 ssn varCHAR(10),
28 name varCHAR(30),
29 PRIMARY KEY (ssn))"""
30
31
32Perform = """CREATE TABLE IF NOT EXISTS Perform(
33 songId INTEGER ,
34 ssn varCHAR(10),
35 title varCHAR(30),
36 PRIMARY KEY (ssn,songId),
37 FOREIGN KEY (songId) References Songs(songId),
38 FOREIGN KEY (ssn) References Musicians (ssn))"""
39
40
41Album_Producer = """CREATE TABLE IF NOT EXISTS Album(
42 albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
43 ssn varCHAR(10),
44 producerName varchar(30),
45 copyrightDate DATE,
46 title CHAR(30),
47 PRIMARY KEY (albumIdentifier),
48 FOREIGN KEY (ssn) References Musicians(ssn))"""
49
50Songs_Appears = """CREATE TABLE IF NOT EXISTS Songs(
51 songId INTEGER NOT NULL AUTO_INCREMENT,
52 author varCHAR(30),
53 title varCHAR(30),
54 albumIdentifier INTEGER NOT NULL,
55 PRIMARY KEY (songId),
56 FOREIGN KEY (albumIdentifier) References Album (albumIdentifier) )"""
57
58Users = """CREATE TABLE IF NOT EXISTS Users(
59 uid INTEGER NOT NULL AUTO_INCREMENT,
60 username varCHAR(20),
61 password varCHAR(20),
62 address varCHAR(30),
63 PRIMARY KEY(uid))"""
64
65Credit_Cards = """CREATE TABLE IF NOT EXISTS CreditCards(
66 uid INTEGER,
67 creditCard INTEGER,
68 PRIMARY KEY (creditCard),
69 FOREIGN KEY (uid) REFERENCES Users(uid))"""
70
71
72Likes = """CREATE TABLE IF NOT EXISTS Likes(
73 lid INTEGER NOT NULL AUTO_INCREMENT,
74 uid INTEGER NOT NULL,
75 ssn varCHAR(10),
76 PRIMARY KEY(lid),
77 FOREIGN KEY (uid) REFERENCES Users(uid),
78 FOREIGN KEY (ssn) REFERENCES Musicians(ssn))"""
79
80Cart = """CREATE TABLE IF NOT EXISTS Cart(
81 cid INTEGER NOT NULL AUTO_INCREMENT,
82 uid INTEGER,
83 albumIdentifier INTEGER,
84 PRIMARY KEY(cid),
85 FOREIGN KEY (uid) REFERENCES Users(uid),
86 FOREIGN KEY (albumIdentifier) REFERENCES Album(albumIdentifier))"""
87
88def choices():
89 print("Menu:\n1. Login\n2. Search\n3. Register new user\n4. Add items to cart\n5. Check Out\n6. See purchase total (rewards)\n");
90 choice = input("Enter Selection: ");
91 return choice;
92
93def one():
94 login(input("Please enter username: "), input("Please enter password: "))
95 return;
96
97def two():
98 val = input("Search Text: ");
99 search(val)
100 return;
101
102def three():
103 register(input("Please enter username: "), input("Please enter password: "), input("Please enter address: "), input("Please enter phone number: "))
104 return;
105
106def four():
107 item_add(input("Please enter exact album name: "));
108 return;
109
110def five():
111 checkout()
112 return;
113
114def six():
115 purchase_total()
116 return;
117
118def search(val):
119 val = '"%'+val+'%"'
120 cur.execute('select * from Songs as S, Album as A, Musicians M where S.title like {0} or A.title like {0} or M.name like {0}'.format(val))
121 searched = cur.fetchall()
122 print(searched)
123
124
125def register(username, password, address, phone,):
126 cur.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
127 print("Created new user: " + username)
128 db.commit()
129 login (username, password)
130 i = ""
131 print ("Add credit cards. Use text STOP when finished")
132 while i != "STOP":
133 i = input("Add new credit card: ")
134 if i == "STOP":
135 break
136 cc_add(i)
137
138def login(username, password):
139 cur.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
140 data = cur.fetchone()
141 if data != "()":
142 print (data)
143 global UID
144 UID = data[0]
145 print("User: {0} has logged on".format(UID))
146 else:
147 print("Login unsuccessful")
148
149
150def item_add(albumName):
151 if (UID != -1):
152 cur.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
153 albumData = cur.fetchone()
154 cur.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(UID, albumData[0]))
155 print("Added {0} to Cart".format(albumData[3]))
156 db.commit()
157
158def cc_add(creditCard):
159 if (UID != -1):
160 cur.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(UID,creditCard))
161 db.commit()
162
163
164def checkout():
165 if (UID != -1):
166 cur.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(UID))
167 cartData = cur.fetchall()
168 for i in cartData:
169 cur.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(UID, i[4]))
170 db.commit()
171 print (i)
172 cur.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(UID, i[2]))
173 db.commit()
174 print("Cart has been cleared.")
175 purchase_total()
176
177
178def purchase_total():
179 count = 0
180 if (UID != -1):
181 cur.execute('SELECT * from Likes where uid = "{0}"'.format(UID))
182 dataLikes = cur.fetchall()
183 for x in dataLikes:
184 count += 1
185 print("Your total purchases are now: {0}".format(count))
186
187cur.execute(Musicians)
188cur.execute(Album_Producer)
189cur.execute(Songs_Appears)
190cur.execute(Perform)
191cur.execute(Users)
192cur.execute(Credit_Cards)
193cur.execute(Likes)
194cur.execute(Cart)
195
196
197
198i=0;
199while i<1:
200 selection = choices();
201 if selection == '1':
202 one();
203 elif selection == '2':
204 two()
205 elif selection == '3':
206 three();
207 elif selection == '4':
208 four();
209 elif selection == '5':
210 five();
211 elif selection == '6':
212 six();
213 else:
214 print("Invalid.\n");