def addUser(username, password): q = "SELECT * FROM user_tbl WHERE username = '******';" % username data = exec(q).fetchone() if (data is None): q = "SELECT user_id FROM user_tbl;" data = exec(q).fetchall() addUserHelper(username, password, data) return True return False #if username already exists
def addUserHelper(username, password, data): rand = random.randrange(limit) while (rand in data): #if the id is already in use rand = random.randrange(limit) #print(rand) command = "INSERT INTO user_tbl VALUES(" + str( rand) + ",\"" + username + "\",\"" + password + "\")" #print(command) exec(command)
def addBlog(user_id, title): title = title.strip() user_id = int(user_id) q = "SELECT * FROM blog_tbl WHERE user_id = %d AND title = '%s'" % ( user_id, title) data = exec(q).fetchone() if (data is not None): return None q = "SELECT blog_id FROM blog_tbl WHERE user_id = %d;" % user_id data = exec(q).fetchall() return addBlogHelper(user_id, title, data)
def addEntry(blog_id, title, content): title = title.strip() blog_id = int(blog_id) q = "SELECT * FROM entry_tbl WHERE blog_id = %d AND title='%s'" % (blog_id, title) data = exec(q).fetchone() print(data) if (data is not None): return None q = "SELECT blog_id FROM blog_tbl" data = exec(q).fetchall() return addEntryHelper(blog_id, title, content, data)
def addBlogHelper(user_id, title, data): rand = random.randrange(limit) while (rand in data): #if the id is already in use rand = random.randrange(limit) #print(rand) #print(command) command = "INSERT INTO blog_tbl VALUES(%s, %s, '%s')" % (rand, user_id, title) #print(command) exec(command) return rand
def editEntry(entry_id, blog_id, title, content): title = title.strip() entry_id = int(entry_id) blog_id = int(blog_id) q = "SELECT * FROM entry_tbl WHERE entry_id != %d AND title='%s' AND blog_id = %d" % ( entry_id, title, blog_id) data = exec(q).fetchall() print(data) #print(len(data)) if (len(data) > 0): return False q = "UPDATE entry_tbl SET title = '%s', content = '%s' WHERE entry_id = %d" % ( title, content, entry_id) exec(q) return True
def addMulti(username, type): '''def addMulti(username): generating a new game_id for a new multipliayer player game (team or pvp)''' #generate random game id game_id = type + str(random.randrange(10000000000)) command = "SELECT game_id FROM game_tbl" data = exec(command).fetchall() list = [] for entry in data: list.append(entry[0]) if len(list) >= 10000000000: return False while game_id in list: game_id = type + str(random.randrange(10000000000)) #add game to game table command = "INSERT INTO game_tbl VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" inputs = (str(game_id), '0,0,' + username, '', username, '', 0, 0, 0, '', '') execmany(command, inputs) #add game to user table command = 'SELECT game_id FROM user_tbl WHERE username=?' inputs = (username, ) games = execmany(command, inputs).fetchone()[0] command = 'UPDATE user_tbl SET game_id=? WHERE username=?' games += "," + game_id inputs = (games, username) execmany(command, inputs) return True
def updateQuestion(username, game_id): #determine team number '''def updateQuestion(username, game_id): generate the next question for a team and updates data tables accordingly''' team = getTeamNum(username, game_id) q = "SELECT team%s FROM game_tbl WHERE game_id=?" % team r = "UPDATE game_tbl SET currentq%s=? WHERE game_id=?" % team s = "UPDATE game_tbl SET team%s=? WHERE game_id=?" % team #update question number that team is up to inputs = (game_id, ) data = execmany(q, inputs).fetchone()[0].split(",") number = int(data[1]) + 1 if number > 10: completeGame(username, game_id) return data[1] = str(number) data = ",".join(data) inputs = (data, game_id) execmany(s, inputs) #update current question for that team command = "SELECT * FROM question_tbl ORDER BY random() LIMIT 1" question = exec(command).fetchone()[1] inputs = (question, game_id) execmany(r, inputs)
def findUser(user_id, query): '''def findUser(query): search for a user''' query = query.lower().strip() list = [] q = "SELECT username FROM user_tbl;" data = exec(q).fetchall() for name in data: info = [] if (query in name[0].lower()): friend_id = getUserID(name[0]) if (user_id != friend_id): info.append(name[0]) #name info.append(friend_id) #user_id friend = isFriend(user_id, friend_id) requested = isRequested(user_id, friend_id) reverse_requested = isRequested(friend_id, user_id) if friend: #color info.append("btn-secondary") info.append("disabled") info.append("Added") else: info.append("btn-primary") if requested: info.append("disabled") info.append("Requested") elif reverse_requested: info.append("") info.append(None) else: info.append("") info.append("Send Request") list.append(tuple(info)) return list
def addEntryHelper(blog_id, title, content, data): rand = random.randrange(limit) while (rand in data): #if id is already in use rand = random.randrange(limit) command = "INSERT INTO entry_tbl VALUES(%s, %s, '%s', '%s')" % ( rand, blog_id, title, content) #print(command) return exec(command)
def findUser(query): '''def findUser(query): search for a user''' query = query.lower().strip() list = [] q = "SELECT username FROM user_tbl;" data = exec(q).fetchall() for name in data: if (query in name[0].lower()): list.append(name[0]) return list
def packM(username): '''def packM(username): gives random three cards of Mystery pack to user in session''' q = "SELECT pic FROM pic_tbl WHERE category LIKE 'M%' ORDER BY random() LIMIT 3 " data = exec(q).fetchall() for pic in data: pic = pic[0] coll = getColl(username) coll.append(pic) coll = ",".join(coll) q = "UPDATE user_tbl SET coll=? WHERE username=?" inputs = (coll, username) execmany(q, inputs)
def userValid(username, password): '''def userValid(username,password): determines if username is in database and password corresponds to username''' q = "SELECT username FROM user_tbl;" data = exec(q) for uName in data: if uName[0] == username: q = "SELECT password from user_tbl WHERE username=?" inputs = (username, ) data = execmany(q, inputs) for passW in data: if (passW[0] == password): return True return False
def joinTeam(username, type): '''def joinTeam(username, type: add user to an already existing team game if the game is not full''' q = "SELECT game_id FROM game_tbl WHERE game_id LIKE 'T%'" data = exec(q).fetchall() list = [] for game in data: game_id = game[0] owner = ownGame(username, game_id) full = gameFull(game_id) if not full and not owner: list.append(game_id) if len(list) == 0: return addMulti(username, type) else: joinGame(username, list[0]) return True
def getLists(user_id): '''def getLists(user_id): returns array of list_ids that user can edit''' q = "SELECT list_id FROM future_tbl" data = exec(q) list = [] for id in data: list_id = id[0] if canEdit(list_id, user_id): info = [] info.append(list_id) info.append(getTitle(list_id)) info.append(getItemsFromList(list_id)) info.append(getOwner(list_id)) info.append(getCollaborators(list_id)) info.append(getType(list_id)) list.append(info) return list
def addUser(username, password, flag): '''def addUser(username, password, flag): adding user from sign up, taking in form inputs and writing to data table''' q = "SELECT * FROM user_tbl WHERE username=?" inputs = (username, ) data = execmany(q, inputs).fetchone() if (data is None): q = "INSERT INTO user_tbl VALUES(?, ?, ?, ?, '', 200, ?, ?, 0)" command = "SELECT flag from flags_tbl where country=?" inputs = (flag, ) pic = execmany(command, inputs).fetchone()[0] command = "SELECT stat FROM user_tbl WHERE username='******'" stats = exec(command).fetchone()[0] inputs = (username, password, pic, pic, flag, stats) execmany(q, inputs) return True return False #if username already exists
def findGame(username, query): '''def findGame(username, query): search for a game using game_id''' query = query.lower().strip() output = [] q = "SELECT game_id FROM game_tbl;" data = exec(q).fetchall() for game in data: game_id = game[0] if query in game_id.lower( ) and "S" not in game_id: #meets these requirements entry = [] #first item in tuple if "P" in game_id: entry.append("PVP") else: entry.append("Team") #second item in tuple entry.append(game_id) #third, fourth, fifth item in tuple if gameCompleted(game_id): entry.append("View") entry.append("") entry.append("btn-secondary") else: if ownGame(username, game_id): entry.append("Play") entry.append("") entry.append("btn-primary") else: if gameFull(game_id): entry.append("Full") entry.append("disabled") entry.append("btn-danger") else: entry.append("Join") entry.append("") entry.append("btn-success") output.append(tuple(entry)) return output
def findBlog(search): search = search.lower().strip() data = getAllBlogs() blogsList = [] #creates a list of blogs with acceptable titles for blogT, blogID in data: title = str(blogT) #print(title) titleSmall = title.lower().strip() if search in titleSmall: blogsList.append(blogID) #print(blogsList) blogIDList = [] #creates a list of tuples containing blog_id, title, user_id, and username for id in blogsList: q = "SELECT blog_id, title, user_id FROM blog_tbl WHERE blog_id=%d" % id data = exec(q).fetchone() data = data + getUserInfo(getUserfromBlog(str(data[0]))) blogIDList.append(data) return blogIDList #list of tuples
def getUserInfo(user_id): q = "SELECT username FROM user_tbl WHERE user_id = '%s';" % user_id data = exec(q).fetchone() return data
def getEntryID(blog_id, title): q = "SELECT entry_id FROM entry_tbl WHERE blog_id ='%s' AND title='%s';" % ( blog_id, title) data = exec(q).fetchone() return data
def allCountries(): '''def allCountries(): getting all the countries from country api''' q = "SELECT country FROM flags_tbl;" data = exec(q).fetchall() return formatFetch(data)
def getEntryContent(entry_id): q = "SELECT content FROM entry_tbl WHERE entry_id ='%s';" % entry_id data = exec(q).fetchone() if (data is None): return "" return str(data[0])
def getAllEntries(blog_id): q = "SELECT title, content, entry_id FROM entry_tbl WHERE blog_id= '%s'" % blog_id data = exec(q).fetchall() return data
def getBlogfromEntry(entry_id): q = "SELECT blog_id FROM entry_tbl WHERE entry_id = '%s'" % entry_id data = exec(q).fetchone() return str(data[0])
def getAllBlogs(): q = "SELECT title, blog_id FROM blog_tbl" data = exec(q).fetchall() return data
def verifyUser(username, password): q = "SELECT username,password FROM user_tbl;" data = exec(q).fetchall() return verifyUserHelper(username, password, data)
def nationLeaderboard(): '''def nationLeaderboard(): Place all countries in order based on sum of scores of users in those countries''' q = "SELECT country, SUM(score) FROM user_tbl, flags_tbl WHERE flags_tbl.country = user_tbl.flag GROUP BY flags_tbl.country" data = exec(q).fetchall() data = orderDict(data) return data
def userLeaderboard(): '''def userLeaderboard(): Place all users in order based on scores''' q = "SELECT username, score FROM user_tbl" data = exec(q).fetchall() return orderDict(data)
def getUserID(username): q = "SELECT user_id FROM user_tbl WHERE username = '******';" % username data = exec(q).fetchone() return data
def getAllUsers(): q = "SELECT username, user_id FROM user_tbl" data = exec(q).fetchall() return data