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()