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
