XrbxEFjP

1import MySQLdb
2
3userID = 0
4
5db = MySQLdb.connect('hopper.wlu.ca','haba9050', 'bigtop4','haba9050')
6cursor = db.cursor()
7
8sql = "show tables;"
9
10
11cursor.execute(sql)
12data = cursor.fetchall()
13
14print(data)
15
16
17
18Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
19            ssn varCHAR(10),
20            name varCHAR(30),
21            PRIMARY KEY (ssn))"""
22
23Songs = """CREATE TABLE IF NOT EXISTS Songs(
24                songId INTEGER NOT NULL AUTO_INCREMENT,
25                author varCHAR(30),
26                title varCHAR(30),
27                albumIdentifier INTEGER NOT NULL,
28                PRIMARY KEY (songId),
29                FOREIGN KEY (albumIdentifier) References Album (albumIdentifier) )"""
30
31Perform = """CREATE TABLE IF NOT EXISTS Perform(
32            songId INTEGER ,
33            ssn varCHAR(10),
34            title varCHAR(30),
35            PRIMARY KEY (ssn,songId),
36            FOREIGN KEY (songId) References  Songs(songId),
37            FOREIGN KEY (ssn) References Musicians (ssn))"""
38
39Album = """CREATE TABLE IF NOT EXISTS Album(
40                albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
41                ssn varCHAR(10),
42                producerName varchar(30),
43                copyrightDate DATE,
44                title CHAR(30),
45    PRIMARY KEY (albumIdentifier),
46    FOREIGN KEY (ssn) References  Musicians(ssn))"""
47
48Users = """CREATE TABLE IF NOT EXISTS Users(
49        uid INTEGER NOT NULL AUTO_INCREMENT,
50        username varCHAR(20),
51        password varCHAR(20),
52        address varCHAR(30),
53        PRIMARY KEY(uid)
54        )"""
55
56CreditCards = """CREATE TABLE IF NOT EXISTS CreditCards(
57                uid INTEGER,
58                creditCard INTEGER,
59                PRIMARY KEY (creditCard),
60                FOREIGN KEY (uid) REFERENCES  Users(uid)
61                )"""
62Likes = """CREATE TABLE IF NOT EXISTS Likes(
63        lid INTEGER NOT NULL AUTO_INCREMENT,
64        uid INTEGER NOT NULL,
65        ssn varCHAR(10),
66        PRIMARY KEY(lid),
67        FOREIGN KEY (uid) REFERENCES  Users(uid),
68        FOREIGN KEY (ssn) REFERENCES  Musicians(ssn)
69        )"""
70
71Cart = """CREATE TABLE IF NOT EXISTS Cart(
72        cid INTEGER NOT NULL AUTO_INCREMENT,
73        uid INTEGER,
74        albumIdentifier INTEGER,
75        PRIMARY KEY(cid),
76        FOREIGN KEY (uid) REFERENCES  Users(uid),
77        FOREIGN KEY (albumIdentifier) REFERENCES  Album(albumIdentifier))"""
78
79cursor.execute(Musicians)
80cursor.execute(Album)
81cursor.execute(Songs)
82cursor.execute(Perform)
83cursor.execute(Users)
84cursor.execute(CreditCards)
85cursor.execute(Likes)
86cursor.execute(Cart)
87
88
89
90def search(cursor, value):
91    value = '"%'+value+'%"'
92    #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))
93    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))
94
95    searched = cursor.fetchall()
96    print(searched)
97
98
99def register(username, password, address, phone,):
100    cursor.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
101    # cursor.execute('insert into Users values({0},{1},{2},{3},{4})'.format(username, password, address, phone))
102    print("Created new user: " + username)
103    db.commit()
104    addCreditCard()
105
106def login(username, password):
107    cursor.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
108    data = cursor.fetchone()
109    if data != "()":
110        print (data)
111        global userID
112        userID = data[0]
113        print("You have successfully logged in with userid {0}".format(userID))
114    else:
115        print("Login failed")
116
117
118def addItem(albumName):
119    if (userID != 0):
120        cursor.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
121        albumData = cursor.fetchone()
122        cursor.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(userID, albumData[0]))
123        print("Added {0} to Cart".format(albumData[3]))
124        db.commit()
125
126def addCreditCard(creditCard):
127    if (UserID != 0):
128        cursor.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(userID,creditCard))
129        db.commit()
130
131
132def checkOut():
133    if (userID != 0):
134        cursor.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(userID))
135        cartData = cursor.fetchall()
136        for i in cartData:
137            cursor.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(userID, i[4]))
138            db.commit()
139            print (i)
140            cursor.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(userID, i[2]))
141            db.commit()
142            print("Cart has been cleared.")
143    purchaseTotal()
144
145
146def purchaseTotal():
147    count = 0
148    #print("You like these albums!")
149    if (userID != 0):
150        cursor.execute('SELECT * from Likes where uid = "{0}"'.format(userID))
151        dataLikes = cursor.fetchall()
152        for x in dataLikes:
153            count += 1
154            #print x
155    print("Your total purchases are now: {0}".format(count))
156#search(cursor, 'swag')
157#register('Bobbie Dyl', '123456789', '123 fake st','12396358980')
158login("Bobbie Dyl", '123456789')
159#addItem('Album of Swag')
160checkOut()
161
162
163print(userID)