Mr8vFg9P

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
89
90
91def search(cursor, value):
92    value = '"%'+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} or A.producerName like {0}'.format(value))
94    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))
95
96    searched = cursor.fetchall()
97    print(searched)
98
99
100def register(username, password, address, phone,):
101    cursor.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
102    # cursor.execute('insert into Users values({0},{1},{2},{3},{4})'.format(username, password, address, phone))
103    print("Created new user: " + username)
104    db.commit()
105    addCreditCard()
106
107def login(username, password):
108    cursor.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
109    data = cursor.fetchone()
110    if data != "()":
111        print (data)
112        global userID
113        userID = data[0]
114        print("You have successfully logged in with userid {0}".format(userID))
115    else:
116        print("Login failed")
117
118
119def addItem(albumName):
120    if (userID != 0):
121        cursor.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
122        albumData = cursor.fetchone()
123        cursor.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(userID, albumData[0]))
124        print("Added {0} to Cart".format(albumData[3]))
125        db.commit()
126
127def addCreditCard(creditCard):
128    if (UserID != 0):
129        cursor.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(userID,creditCard))
130        db.commit()
131
132
133def checkOut():
134    if (userID != 0):
135        cursor.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(userID))
136        cartData = cursor.fetchall()
137        for i in cartData:
138            cursor.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(userID, i[4]))
139            db.commit()
140            print (i)
141            cursor.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(userID, i[2]))
142            db.commit()
143            print("Cart has been cleared.")
144    purchaseTotal()
145
146
147def purchaseTotal():
148    count = 0
149    #print("You like these albums!")
150    if (userID != 0):
151        cursor.execute('SELECT * from Likes where uid = "{0}"'.format(userID))
152        dataLikes = cursor.fetchall()
153        for x in dataLikes:
154            count += 1
155            #print x
156    print("Your total purchases are now: {0}".format(count))
157#search(cursor, 'swag')
158#register('Bobbie Dyl', '123456789', '123 fake st','12396358980')
159login("Bobbie Dyl", '123456789')
160#addItem('Album of Swag')
161checkOut()
162
163
164print(userID)