Advertisement
Guest User

DB4

a guest
Mar 21st, 2016
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 6.27 KB | None | 0 0
  1.  
  2. import MySQLdb
  3.  
  4. userID = 0
  5.  
  6. db = MySQLdb.connect('hopper.wlu.ca','haba9050', 'bigtop4','haba9050')
  7. cursor = db.cursor()
  8.  
  9. sql = "show tables;"
  10.  
  11.  
  12. cursor.execute(sql)
  13. data = cursor.fetchall()
  14.  
  15. print(data)
  16.  
  17.  
  18.  
  19. Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
  20.            ssn varCHAR(10),
  21.            name varCHAR(30),
  22.            PRIMARY KEY (ssn))"""
  23.  
  24. Songs = """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.  
  32. Perform = """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. Album = """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.  
  49. Users = """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.  
  57. CreditCards = """CREATE TABLE IF NOT EXISTS CreditCards(
  58.                uid INTEGER,
  59.                creditCard INTEGER,
  60.                PRIMARY KEY (creditCard),
  61.                FOREIGN KEY (uid) REFERENCES  Users(uid)
  62.                )"""
  63. Likes = """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.  
  72. Cart = """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.  
  80. cursor.execute(Musicians)
  81. cursor.execute(Album)
  82. cursor.execute(Songs)
  83. cursor.execute(Perform)
  84. cursor.execute(Users)
  85. cursor.execute(CreditCards)
  86. cursor.execute(Likes)
  87. cursor.execute(Cart)
  88.  
  89. def 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.  
  94. def selectOne():
  95.     print("Search: ");
  96.  
  97.     return;
  98.  
  99. def selectTwo():
  100.     print("Selection 2 was chosen.");
  101.  
  102.     return;
  103.  
  104. def selectThree():
  105.     print("Selection 3 was chosen.");
  106.  
  107.     return;
  108.  
  109. def selectFour():
  110.     print("Selection 4 was chosen.");
  111.  
  112.     return;
  113.  
  114.  
  115.  
  116.  
  117.  
  118. def 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.  
  127. def 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.  
  134. def 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.  
  146. def 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.  
  154. def 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.  
  160. def 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.  
  174. def 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')
  186. login("Bobbie Dyl", '123456789')
  187. #addItem('Album of Swag')
  188. checkOut()
  189.  
  190.  
  191.  i=0;
  192.  
  193. while 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.  
  207. print(userID)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement