· 10 years ago · Mar 21, 2016, 05:15 PM
1
2import MySQLdb
3
4userID = 0
5
6db = MySQLdb.connect('hopper.wlu.ca','haba9050', 'bigtop4','haba9050')
7cursor = db.cursor()
8
9sql = "show tables;"
10
11
12cursor.execute(sql)
13data = cursor.fetchall()
14
15print(data)
16
17
18
19Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
20 ssn varCHAR(10),
21 name varCHAR(30),
22 PRIMARY KEY (ssn))"""
23
24Songs = """CREATE TABLE IF NOT EXISTS Songs(
25 songId INTEGER NOT NULL AUTO_INCREMENT,
26 author varCHAR(30),
27 title varCHAR(30),
28 albumIdentifier INTEGER NOT NULL,
29 PRIMARY KEY (songId),
30 FOREIGN KEY (albumIdentifier) References Album (albumIdentifier) )"""
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
40Album = """CREATE TABLE IF NOT EXISTS Album(
41 albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
42 ssn varCHAR(10),
43 producerName varchar(30),
44 copyrightDate DATE,
45 title CHAR(30),
46 PRIMARY KEY (albumIdentifier),
47 FOREIGN KEY (ssn) References Musicians(ssn))"""
48
49Users = """CREATE TABLE IF NOT EXISTS Users(
50 uid INTEGER NOT NULL AUTO_INCREMENT,
51 username varCHAR(20),
52 password varCHAR(20),
53 address varCHAR(30),
54 PRIMARY KEY(uid)
55 )"""
56
57CreditCards = """CREATE TABLE IF NOT EXISTS CreditCards(
58 uid INTEGER,
59 creditCard INTEGER,
60 PRIMARY KEY (creditCard),
61 FOREIGN KEY (uid) REFERENCES Users(uid)
62 )"""
63Likes = """CREATE TABLE IF NOT EXISTS Likes(
64 lid INTEGER NOT NULL AUTO_INCREMENT,
65 uid INTEGER NOT NULL,
66 ssn varCHAR(10),
67 PRIMARY KEY(lid),
68 FOREIGN KEY (uid) REFERENCES Users(uid),
69 FOREIGN KEY (ssn) REFERENCES Musicians(ssn)
70 )"""
71
72Cart = """CREATE TABLE IF NOT EXISTS Cart(
73 cid INTEGER NOT NULL AUTO_INCREMENT,
74 uid INTEGER,
75 albumIdentifier INTEGER,
76 PRIMARY KEY(cid),
77 FOREIGN KEY (uid) REFERENCES Users(uid),
78 FOREIGN KEY (albumIdentifier) REFERENCES Album(albumIdentifier))"""
79
80cursor.execute(Musicians)
81cursor.execute(Album)
82cursor.execute(Songs)
83cursor.execute(Perform)
84cursor.execute(Users)
85cursor.execute(CreditCards)
86cursor.execute(Likes)
87cursor.execute(Cart)
88
89def printMenu():
90 print("Menu:\n1. Search\n2. Register\n3. Add Items\n4. Check Out\n5. Claim Rewards\n");
91 option = input("Enter Selection: ");
92 return option;
93
94def selectOne():
95 print("Search: ");
96
97 return;
98
99def selectTwo():
100 print("Selection 2 was chosen.");
101
102 return;
103
104def selectThree():
105 print("Selection 3 was chosen.");
106
107 return;
108
109def selectFour():
110 print("Selection 4 was chosen.");
111
112 return;
113
114
115
116
117
118def search( value):
119 value = '"%'+value+'%"'
120 #cursor.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} or A.producerName like {0}'.format(value))
121 cursor.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(value))
122
123 searched = cursor.fetchall()
124 print(searched)
125
126
127def register(username, password, address, phone,):
128 cursor.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
129 # cursor.execute('insert into Users values({0},{1},{2},{3},{4})'.format(username, password, address, phone))
130 print("Created new user: " + username)
131 db.commit()
132 addCreditCard()
133
134def login(username, password):
135 cursor.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
136 data = cursor.fetchone()
137 if data != "()":
138 print (data)
139 global userID
140 userID = data[0]
141 print("You have successfully logged in with userid {0}".format(userID))
142 else:
143 print("Login failed")
144
145
146def addItem(albumName):
147 if (userID != 0):
148 cursor.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
149 albumData = cursor.fetchone()
150 cursor.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(userID, albumData[0]))
151 print("Added {0} to Cart".format(albumData[3]))
152 db.commit()
153
154def addCreditCard(creditCard):
155 if (UserID != 0):
156 cursor.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(userID,creditCard))
157 db.commit()
158
159
160def checkOut():
161 if (userID != 0):
162 cursor.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(userID))
163 cartData = cursor.fetchall()
164 for i in cartData:
165 cursor.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(userID, i[4]))
166 db.commit()
167 print (i)
168 cursor.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(userID, i[2]))
169 db.commit()
170 print("Cart has been cleared.")
171 purchaseTotal()
172
173
174def purchaseTotal():
175 count = 0
176 #print("You like these albums!")
177 if (userID != 0):
178 cursor.execute('SELECT * from Likes where uid = "{0}"'.format(userID))
179 dataLikes = cursor.fetchall()
180 for x in dataLikes:
181 count += 1
182 #print x
183 print("Your total purchases are now: {0}".format(count))
184#search(cursor, 'swag')
185#register('Bobbie Dyl', '123456789', '123 fake st','12396358980')
186login("Bobbie Dyl", '123456789')
187#addItem('Album of Swag')
188checkOut()
189
190
191 i=0;
192
193while i<1:
194 selection = printMenu();
195 if selection == '1':
196 selectOne();
197 elif selection == '2': #search
198 value = input('Enter search Value: ')
199 search(value)
200 elif selection == '3':
201 selectThree();
202 elif selection == '4':
203 selectFour();
204 else:
205 print("No option selected.\n");
206
207print(userID)