def storeteam(ID, creature1, creature2, creature3, creature4):
storequery = (
'''INSERT INTO userteams(userID, creature1, creature2, creature3, creature4)
VALUES(?, ?, ?, ?, ?)''') #creates the INSERT INTO query
cursor.execute(storequery, [(ID), (creature1[1]), (creature2[1]),
(creature3[1]), (creature4[1])])
db.commit() #commits the query to the database
def existingteamcheck(username):
query = ('''SELECT userID from userteams WHERE userID = ?
''') #Query created and discussed in write-up
cursor.execute(query, [(username)])
teams = cursor.fetchall() #puts teams fetched into a variable
if len(teams) > 1: #Checks how many teams fetched. Allows 2 teams max
return False
else: #function returns true if less than 2 teams
return True
def get_history(self):
sql = "SELECT * FROM logs WHERE ID = %s ORDER BY Num DESC LIMIT 5 " % self.id
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
except:
print "Error : Cant Fetch Data"
# Change tuple To
return results
def usernamecheck(username): #Function to check for an existing username
findusername = ("SELECT * FROM users WHERE username = ?")
cursor.execute(findusername, [(username)])
if cursor.fetchall():
return False #If found, function is false and parent function stops
elif len(username) >= 6: #If not found, checks length of username
if username.find(
' ') == -1: #attempts to locate any spaces in username
return True #if checks pass the function returns true
else:
return False
else:
return False
def insert_operation(self, totalwithdraw, totaldepposite):
sql = "INSERT INTO logs (ID, Date1 ,Day1, time1, operation, 1st, totalwithdraw, totaldepposite) VALUES ('%s','%s" \
"','%s','%s','%s', '%s', '%s', '%s')" % (self.id, self.date, self.day, self.time, self.operation, self.st1, totalwithdraw, totaldepposite)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
print " succ"
except:
# Rollback in case there is any error
print "error"
db.rollback()
def loginprocess(username, password):
find_user = ("SELECT userID FROM users WHERE username = ? AND password = ?"
) #Creates Query
try:
cursor.execute(find_user, [(username), (password)])
results = cursor.fetchone()[0]
#Puts result into a variable
print("Success") #Tells console the check was successful as testing
global globalID
globalID = str(results)
print(globalID)
return True
except:
return False #Booleans used to determine if the game should progress to the main menu
def updateelo(userID, battlestate):
fetchELO = '''SELECT ELO FROM users WHERE userID=?''' #query to get ELO
setELO = '''UPDATE users SET ELO = ? WHERE userID = ? ''' #Query to update ELO
cursor.execute(fetchELO, [(userID)]) #executes get query
currentELO = cursor.fetchone()[0] #places result in variable
print(f"Current ELO is {currentELO}")
if battlestate == True: #if battle is a win the ELO increases by 5%
newELO = currentELO + (currentELO * 0.05)
elif battlestate == False: #if battle is a loss ELO decreases by 10%
newELO = currentELO - (currentELO * 0.1)
print(f"New ELO is {newELO}")
cursor.execute(setELO, [(newELO), (userID)]) #sets the new ELO
db.commit()
db.close()
def get_any_totaldepposite(self):
sql = "SELECT totaldepposite FROM logs WHERE Day1 = %s and ID = %s ORDER BY Num DESC LIMIT 1" % (
self.day, self.id)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
except:
print "Error : Cant Fetch Data"
# Change tuple To int
check = int(results[0][0])
return check
def first_operation(self):
# Count
sql = "SELECT time1 FROM logs WHERE 1st = 1 and Day1 = %s and ID = %s" % (
self.day, self.id)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
except:
print "Error : Cant Fetch Data123"
# Change tuple To int
check = str(results[0][0])
# Check = 0 if ID is not Exist // Check = 1 if ID is Exist
return check
def registeringprocess(username, email, password, confirmpassword):
if usernamecheck(username): #Runs the data through each function
if emailcheck(email):
if passwordcheck(password, confirmpassword):
print(username, email, password) #test data
insertData = '''INSERT INTO users(username, emailaddress, password, ELO)
VALUES(?, ?, ?, 100)'''
cursor.execute(insertData, [(username), (email), (password)])
db.commit(
) #The data is only written to the database if all checks pass.
db.close()
print("Successful Register")
return True
else:
return "Password Check Issue"
else:
return "Email Check Issue"
else:
return "Username Already Exists!"
def creaturedatafetch(creature1, creature2, creature3, creature4):
creaturearray = []
query = 'SELECT creatureName, creatureType FROM creatures WHERE creatureID=?'
cursor.execute(query, [(creature1)]) #runs the query with each creature ID
creaturearray.append(
cursor.fetchall()) #puts the result of the query into creaturearray
cursor.execute(query, [(creature2)]) #repeats
creaturearray.append(cursor.fetchall())
cursor.execute(query, [(creature3)]) #repeats
creaturearray.append(cursor.fetchall())
cursor.execute(query, [(creature4)]) #repeats
creaturearray.append(cursor.fetchall())
return creaturearray
def getbattleteams(userID):
query = 'SELECT teamID, teamname FROM userteams WHERE userID = ?'
cursor.execute(query, [(userID)])
return cursor.fetchall()
def getuserteams(username):
query = 'SELECT creature1, creature2, creature3, creature4 FROM userteams WHERE userID = ?'
cursor.execute(query, [(username)])
teams = cursor.fetchall()
return teams
def fetchcreatures():
query = ('SELECT creatureID, creatureName FROM creatures')
cursor.execute(query)
return cursor.fetchall()
db.close()
def fetchplayers():
selectbyELO = ("SELECT username, ELO FROM users ORDER BY ELO DESC LIMIT 10")
cursor.execute(selectbyELO)
return cursor.fetchall()
db.close()
