def petsitter_signup_data(user, data): try: with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT id FROM customer WHERE email = %s""" cursor.execute( SQL, (user, ), ) return_data = cursor.fetchall() customerid = return_data[0][0] #customerid = ''.join(str(return_data[0])) if data.getlist('service[]'): petservice = format(data.getlist('service[]')) else: petservice = "[]" if data.getlist('type[]'): pettype = format(data.getlist('type[]')) pettype = pettype.replace(", ''", "") else: pettype = "[]" if data.getlist('size[]'): petsize = format(data.getlist('size[]')) else: petsize = "[]" with DBcm.UseDatabase(connection) as cursor: SQL = """ INSERT INTO petsitter ( customerid, petsittername, email, phone, country, city, town, address, description, price, petservice, petnums, pettype, petsize, housetype, outdoor, transport, deleteflag ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0) """ cursor.execute( SQL, (customerid, data['petsittername'], data["email"], data["phone"], data['country'], data['city'], data['town'], data['street'], data['description'], data['price'], petservice, data['numofpet'], pettype, petsize, data['house'], data['outdoor'], data['transport']), ) except: print("Error")
def api_connect_irc(): try: parser = reqparse.RequestParser() parser.add_argument('host', help="IRC Server host") parser.add_argument('port', help='User login name for Authentication') parser.add_argument('username', help="IRC User Name") args = parser.parse_args() _userName = args['username'] _host = args['host'] _port = args['port'] data = None with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor: cursor.execute( """ SELECT idIrcServers FROM IrcServers WHERE host = %s AND port = %s""", ( _host, _port, )) # if server exist data = cursor.fetchone() if data is not None: session["server_id"] = data[0] # if server does not exist, then create it else: with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor: cursor.execute( """ INSERT INTO IrcServers (host, port) VALUES (%s, %s) """, ( _host, _port, )) session["server_id"] = cursor.lastrowid # update username for user with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor: cursor.execute( """ UPDATE Users SET userName = %s WHERE login = %s """, (_userName, session["user"])) msg = { 'Id': session["server_id"], 'Host': _host, 'Port': _port, 'Username': _userName } return jsonify(session=msg), 200 except Exception as e: return jsonify(error=str(e)), 500
def add_scores(name: str, score: float, sourceword: str) -> None: _SQL = """Insert into leaderboard (name, score, sourceword) values (%s, %s, %s)""" with DBcm.UseDatabase(config) as cursor: cursor.execute(_SQL, (name, score, sourceword))
def get_all_modes(): """ simply reads all json files in the current directory, any with the type param of "gameMode" will be read in and stored in a list, this is needed for the user to choose which "gameMode" later """ game_modes = [] with DBcm.UseDatabase(config) as cursor: _SQL = """select * from game_modes""" cursor.execute(_SQL) data = cursor.fetchall() game_modes = [] for row in data: game_mode = {} game_mode["id"] = row[0] game_mode["name"] = row[2] game_mode["max_players"] = row[3] game_mode["number_questions"] = row[4] game_mode["time"] = row[1] game_modes.append(game_mode) return game_modes
def check_password(username: str): """Check the passwords.""" with DBcm.UseDatabase(DBconfig) as cursor: _SQL = "select * from user where (username = '******')" % username cursor.execute(_SQL) return cursor.fetchone()
def gameDetails(): session["gameID"] = request.form["gameID"] with DBcm.UseDatabase(config) as cursor: SQL = "select * from review where review.game_id = " + session["gameID"] cursor.execute(SQL) data = cursor.fetchall() return render_template("gameDetails.html", the_data=data)
def customer_signup_data(data): try: newstring = "'" + data['email'] + "','" + data['password'] + "'" SQL = """ INSERT INTO customer (email,password""" if data['fname'] != '': SQL += ",fname" newstring += ",'" + data['fname'] + "'" if data['sname'] != '': SQL += ",sname" newstring += ",'" + data['sname'] + "'" if data['title'] != '': SQL += ",title" newstring += ",'" + data['title'] + "'" if data['dob'] != '': SQL += ",dateOfBirth" newstring += ",'" + data['dob'] + "'" if data['address'] != '': SQL += ",address" newstring += ",'" + data['address'] + "'" if data['phone'] != '': SQL += ",contactNumber" newstring += ",'" + data['phone'] + "'" SQL += ") VALUES (" + newstring + ")" print(SQL) with DBcm.UseDatabase(connection) as cursor: cursor.execute(SQL) except: print("Error")
def list_pettype_data(): try: with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT DISTINCT pettype FROM petsitter""" cursor.execute(SQL) data = cursor.fetchall() pettype = ['dog', 'cat'] lastEle = ['other'] newlist = [] while len(data) > 0: temp = data.pop() templist = str_to_list(temp[0]) while len(templist) > 0: tempElement = templist.pop() if tempElement.lower( ) not in newlist and tempElement.lower( ) not in pettype and tempElement.lower( ) not in lastEle and tempElement != "": newlist.append(tempElement.lower()) newlist.sort() pettype.extend(newlist) pettype.extend(lastEle) return pettype except: print("Error")
def update_account_data(data, email): try: title = data['title'] fname = data['fname'] sname = data['sname'] dateOfBirth = str(data['dob']) contactNumber = data['phone'] address = data['address'] with DBcm.UseDatabase(connection) as cursor: SQL = """UPDATE customer SET title = %s, fname = %s, sname = %s, dateOfBirth = %s, contactNumber = %s, address = %s WHERE email = %s""" cursor.execute( SQL, ( title, fname, sname, dateOfBirth, contactNumber, address, email, ), ) except: print("Error")
def retrieve_sorted_leaderboard() -> list: _SQL = """Select score, name, sourceword From leaderboard order By score""" with DBcm.UseDatabase(config) as cursor: cursor.execute(_SQL) data = cursor.fetchall() return [(float(row[0]), row[1], row[2]) for row in data]
def api_login(): try: # Parse the arguments parser = reqparse.RequestParser() parser.add_argument('login', help='User login name for Authentication') parser.add_argument('password', help='Password for Authentication') args = parser.parse_args() _userLogin = args['login'] _userPassword = args['password'] with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor: cursor.execute( """SELECT idUsers FROM Users WHERE login=%s AND password=%s """, (_userLogin, _userPassword)) data = cursor.fetchone() # data is a tuple if data is not None: session["logged_in"] = True session["user"] = _userLogin session["id"] = data[0] return jsonify(logged_in=session["logged_in"], user=session["user"], id=session["id"]), 200 else: return jsonify(info="Bad credentials"), 400 except Exception as e: return jsonify(error=str(e)), 500
def get_towns(self): with DBcm.UseDatabase(config) as cursor: cursor.execute( """SELECT storeTown FROM store WHERE storeCounty = countyValue""" ) rows = cursor.fetchall() self.label_lang.values = map(str, rows)
def view_booking_data(user): try: with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT id FROM customer WHERE email = %s""" cursor.execute( SQL, (user, ), ) return_data = cursor.fetchall() customerid = int(return_data[0][0]) with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT booking.bookingRef, booking.petsitterID, booking.status, booking.title, booking.fname, booking.sname, booking.phone, booking.email, booking.startDate, booking.endDate, booking.totalPrice, booking.petQunatity, booking.typeOfPet, booking.breed, booking.note, booking.createdBy, petsitter.petsittername, petsitter.country, petsitter.city, petsitter.town, petsitter.address FROM booking INNER JOIN petsitter ON (booking.petsitterID = petsitter.id) WHERE booking.customerID = %s ORDER BY booking.createdBy DESC""" cursor.execute( SQL, (customerid, ), ) data = cursor.fetchall() return data except: print("Error")
def getHomepageNews(): sql = """SELECT headline, paragraph FROM news ORDER BY newsID DESC LIMIT 3""" stories = [] with DBcm.UseDatabase(config) as database: database.execute(sql) stories = database.fetchall() return stories
def getHeadline(id): sql = """SELECT headline, FROM news WHERE newsID = %s""" with DBcm.UseDatabase(config) as database: database.execute(sql, (id)) rows = database.fetchall() return rows[0][0]
def addAdmin(admin): for i in admin: admin[i] = encrypt.encrypt(admin[i]) _SQL = """INSERT INTO admin (adminUsername, adminPassword) values (%s, %s)""" with DBcm.UseDatabase(config) as database: database.execute(_SQL, (admin['username'], admin['password']))
def get_sorted_leaderboard() -> list: """Return a sorted list of tuples - this is the leaderboard.""" with DBcm.UseDatabase(config) as cursor: _SQL = """ select timetaken, name, sourceword from leaderboard order by timetaken asc """ cursor.execute(_SQL) return cursor.fetchall()
def check_email_exists_data(): try: with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT email FROM customer""" cursor.execute(SQL) data = cursor.fetchall() return data except: print("Error")
def api_join_channel(channel: int): with DBcm.UseDatabase(app.config["DB_CONFIG"]) as cursor: cursor.execute( """ UPDATE Users SET channel = %s, idIrcServers = %s WHERE login = %s """, (channel, session["server_id"], session["user"])) session["channel"] = channel return jsonify(info="Channel joined"), 200
def insertNewGame(): createGame(request.form["GameName"], request.form["ReleaseYear"], request.form["GameStudio"], request.form["Genre"], request.form["Platform"]) with DBcm.UseDatabase(config) as cursor: SQL = "select titles.id, titles.name, titles.game_studio, titles.release_year, genre.name, platform.name,titles.likes, titles.dislikes from titles, genre, platform where titles.genre = genre.id and titles.platform = platform.id " cursor.execute(SQL) data = cursor.fetchall() return render_template("viewTitles.html", the_data=data)
def add_to_scores(name, score, word) -> None: """Add the name and its associated score to the pickle.""" with DBcm.UseDatabase(config) as cursor: _SQL = """ insert into leaderboard (name, sourceword, timetaken) values (%s, %s, %s) """ cursor.execute(_SQL, (name, word, score))
def getPrice(name): decPrice = [] name = encrypt.encrypt(name) _SQL = """SELECT price FROM cardtypes WHERE name = %s;""" with DBcm.UseDatabase(config) as database: database.execute(_SQL, (name, )) price = database.fetchall() decPrice.append(encrypt.decrypt(price[0][0])) return decPrice
def checkUsername(username): username = encrypt.encrypt(username) _SQL = """SELECT username FROM users WHERE username = %s;""" with DBcm.UseDatabase(config) as database: database.execute(_SQL, (username, )) user = database.fetchall() if len(user): return True return False
def checkAdmin(user): user = encrypt.encrypt(user) _SQL = """SELECT adminUsername FROM admin WHERE adminUsername = %s""" with DBcm.UseDatabase(config) as database: database.execute(_SQL, (user, )) row = database.fetchone() if row: return True return False
def getBalance(username ): #used to display the users balance at the top of each screen username = encrypt.encrypt(username) _SQL = """SELECT balance FROM users WHERE username = %s;""" with DBcm.UseDatabase(config) as database: database.execute(_SQL, (username, )) balance = database.fetchall() decBalance = encrypt.decrypt(balance[0][0]) numBal = '{:.2f}'.format(float(decBalance)) return numBal
def getNewsID(): sql = """SELECT id WHERE news ORDER BY newsID DESC LIMIT 1""" stories = [] with DBcm.UseDatabase(config) as database: database.execute(sql) stories = database.fetchall() id = stories[0][0] return id
def put_alert(): _id = request.form['id'] alert = request.form['alert'] with DBcm.UseDatabase(config) as cursor: _SQL = """insert into alerts (alert_id, alert_text) values (%s, %s)""" cursor.execute(_SQL, (_id, alert)) return "Thanks for the alert!"
def view_petsitter_data(petsittername): with DBcm.UseDatabase(connection) as cursor: SQL = """SELECT * FROM petsitter WHERE petsittername = %s """ cursor.execute( SQL, (petsittername, ), ) data = cursor.fetchall() return data
def addPlayer(player): sql = """INSERT INTO players (firstname,lastname,email,phonenumber,club,username,password,isboardmember,approved) values (%s,%s,%s,%s,%s,%s,%s,0,0)""" with DBcm.UseDatabase(config) as database: database.execute( sql, (player['firstname'], player['lastname'], player['email'], player['phonenumber'], player['club'], player['username'], player['password']))
def submitReview(): createReview(session["gameID"], 0, request.form["Like/Dislike"], request.form["played"], request.form["ownership"], request.form["Rating"], request.form["comment"]) #updates titles likes with DBcm.UseDatabase(config) as cursor: SQL = "update titles set likes = (select count(*) from review where review.game_id = 1 && review.liked = 1) where id =" + session[ "gameID"] cursor.execute(SQL) #updates titles dislikes with DBcm.UseDatabase(config) as cursor: SQL = "update titles set dislikes = (select count(*) from review where review.game_id = 1 && review.liked = 2 ) where id =" + session[ "gameID"] cursor.execute(SQL) with DBcm.UseDatabase(config) as cursor: SQL = "select titles.id, titles.name, titles.game_studio, titles.release_year, genre.name, platform.name,titles.likes, titles.dislikes from titles, genre, platform where titles.genre = genre.id and titles.platform = platform.id " cursor.execute(SQL) data = cursor.fetchall() return render_template("viewTitles.html", the_data=data)