Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import MySQLdb
- userID = 0
- db = MySQLdb.connect('hopper.wlu.ca','haba9050', 'bigtop4','haba9050')
- cursor = db.cursor()
- sql = "show tables;"
- cursor.execute(sql)
- data = cursor.fetchall()
- print(data)
- Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
- ssn varCHAR(10),
- name varCHAR(30),
- PRIMARY KEY (ssn))"""
- Songs = """CREATE TABLE IF NOT EXISTS Songs(
- songId INTEGER NOT NULL AUTO_INCREMENT,
- author varCHAR(30),
- title varCHAR(30),
- albumIdentifier INTEGER NOT NULL,
- PRIMARY KEY (songId),
- FOREIGN KEY (albumIdentifier) References Album (albumIdentifier) )"""
- Perform = """CREATE TABLE IF NOT EXISTS Perform(
- songId INTEGER ,
- ssn varCHAR(10),
- title varCHAR(30),
- PRIMARY KEY (ssn,songId),
- FOREIGN KEY (songId) References Songs(songId),
- FOREIGN KEY (ssn) References Musicians (ssn))"""
- Album = """CREATE TABLE IF NOT EXISTS Album(
- albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
- ssn varCHAR(10),
- producerName varchar(30),
- copyrightDate DATE,
- title CHAR(30),
- PRIMARY KEY (albumIdentifier),
- FOREIGN KEY (ssn) References Musicians(ssn))"""
- Users = """CREATE TABLE IF NOT EXISTS Users(
- uid INTEGER NOT NULL AUTO_INCREMENT,
- username varCHAR(20),
- password varCHAR(20),
- address varCHAR(30),
- PRIMARY KEY(uid)
- )"""
- CreditCards = """CREATE TABLE IF NOT EXISTS CreditCards(
- uid INTEGER,
- creditCard INTEGER,
- PRIMARY KEY (creditCard),
- FOREIGN KEY (uid) REFERENCES Users(uid)
- )"""
- Likes = """CREATE TABLE IF NOT EXISTS Likes(
- lid INTEGER NOT NULL AUTO_INCREMENT,
- uid INTEGER NOT NULL,
- ssn varCHAR(10),
- PRIMARY KEY(lid),
- FOREIGN KEY (uid) REFERENCES Users(uid),
- FOREIGN KEY (ssn) REFERENCES Musicians(ssn)
- )"""
- Cart = """CREATE TABLE IF NOT EXISTS Cart(
- cid INTEGER NOT NULL AUTO_INCREMENT,
- uid INTEGER,
- albumIdentifier INTEGER,
- PRIMARY KEY(cid),
- FOREIGN KEY (uid) REFERENCES Users(uid),
- FOREIGN KEY (albumIdentifier) REFERENCES Album(albumIdentifier))"""
- cursor.execute(Musicians)
- cursor.execute(Album)
- cursor.execute(Songs)
- cursor.execute(Perform)
- cursor.execute(Users)
- cursor.execute(CreditCards)
- cursor.execute(Likes)
- cursor.execute(Cart)
- def printMenu():
- print("Menu:\n1. Search\n2. Register\n3. Add Items\n4. Check Out\n5. Claim Rewards\n");
- option = input("Enter Selection: ");
- return option;
- def selectOne():
- print("Search: ");
- return;
- def selectTwo():
- print("Selection 2 was chosen.");
- return;
- def selectThree():
- print("Selection 3 was chosen.");
- return;
- def selectFour():
- print("Selection 4 was chosen.");
- return;
- def search( value):
- value = '"%'+value+'%"'
- #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))
- 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))
- searched = cursor.fetchall()
- print(searched)
- def register(username, password, address, phone,):
- cursor.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
- # cursor.execute('insert into Users values({0},{1},{2},{3},{4})'.format(username, password, address, phone))
- print("Created new user: " + username)
- db.commit()
- addCreditCard()
- def login(username, password):
- cursor.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
- data = cursor.fetchone()
- if data != "()":
- print (data)
- global userID
- userID = data[0]
- print("You have successfully logged in with userid {0}".format(userID))
- else:
- print("Login failed")
- def addItem(albumName):
- if (userID != 0):
- cursor.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
- albumData = cursor.fetchone()
- cursor.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(userID, albumData[0]))
- print("Added {0} to Cart".format(albumData[3]))
- db.commit()
- def addCreditCard(creditCard):
- if (UserID != 0):
- cursor.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(userID,creditCard))
- db.commit()
- def checkOut():
- if (userID != 0):
- cursor.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(userID))
- cartData = cursor.fetchall()
- for i in cartData:
- cursor.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(userID, i[4]))
- db.commit()
- print (i)
- cursor.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(userID, i[2]))
- db.commit()
- print("Cart has been cleared.")
- purchaseTotal()
- def purchaseTotal():
- count = 0
- #print("You like these albums!")
- if (userID != 0):
- cursor.execute('SELECT * from Likes where uid = "{0}"'.format(userID))
- dataLikes = cursor.fetchall()
- for x in dataLikes:
- count += 1
- #print x
- print("Your total purchases are now: {0}".format(count))
- #search(cursor, 'swag')
- #register('Bobbie Dyl', '123456789', '123 fake st','12396358980')
- login("Bobbie Dyl", '123456789')
- #addItem('Album of Swag')
- checkOut()
- i=0;
- while i<1:
- selection = printMenu();
- if selection == '1':
- selectOne();
- elif selection == '2': #search
- value = input('Enter search Value: ')
- search(value)
- elif selection == '3':
- selectThree();
- elif selection == '4':
- selectFour();
- else:
- print("No option selected.\n");
- print(userID)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement