def checkForWrong(text): wrong = [] text = toAscii(text) (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT correct, listOfIncorrect FROM corrections;") rows = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) for row in rows: for incorrect in [x.split(" -")[0] for x in row[1].split(",")]: if searchWord(incorrect, text) and not row[0] in wrong and not searchWord(row[0], text): wrong.append([row[0], majuscules(incorrect)]) (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "UPDATE corrections SET overallCount = overallCount+1, monthlyCount = monthlyCount+1 WHERE correct = '"+row[0]+"';") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return wrong
def getOnePokemonToWorkOn(incorrect = ""): if incorrect: (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT correct,listOfIncorrect,failcount FROM corrections WHERE listOfIncorrect LIKE '%"+incorrect+"%';") fetched = cur.fetchall() if fetched: line = fetched[0] except Exception: raise finally: bdd.fermerConnexion(cur, conn) if not incorrect or not resultLength: (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT correct,listOfIncorrect,failcount FROM corrections WHERE failcount < "+str(maxFails)+";") fetched = cur.fetchall() if fetched: line = fetched[random.randint(0,len(list(cur))-1)] else: resetFailcount() getOnePokemonToWorkOn() except Exception: raise finally: bdd.fermerConnexion(cur, conn) return line
def cleanAnswered(): tooOld = [] answered = [] (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT id, date FROM alreadyAnswered;") rows = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) for row in rows: twtDate = datetime.datetime.strptime(row[1], "%d-%m-%y").date() if (datetime.date.today() - twtDate).days < howOldAreTweets + 2: answered.append(row[0]) else: tooOld.append(row[0]) (cur, conn) = bdd.ouvrirConnexion() try: for _id in tooOld: bdd.executerReq(cur, "DELETE FROM alreadyAnswered WHERE id = '%s';" % (_id)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return answered
def checkForWrong(text): wrong = [] text = toAscii(text) (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT correct, listOfIncorrect FROM corrections;") rows = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) for row in rows: for incorrect in [x.split(" -")[0] for x in row[1].split(",")]: if searchWord(incorrect, text) and not row[0] in wrong and not searchWord( row[0], text): wrong.append([row[0], majuscules(incorrect)]) (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "UPDATE corrections SET overallCount = overallCount+1, monthlyCount = monthlyCount+1 WHERE correct = '" + row[0] + "';") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return wrong
def getOnePokemonToWorkOn(incorrect=""): if incorrect: (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "SELECT correct,listOfIncorrect,failcount FROM corrections WHERE listOfIncorrect LIKE '%" + incorrect + "%';") fetched = cur.fetchall() if fetched: line = fetched[0] except Exception: raise finally: bdd.fermerConnexion(cur, conn) if not incorrect or not resultLength: (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "SELECT correct,listOfIncorrect,failcount FROM corrections WHERE failcount < " + str(maxFails) + ";") fetched = cur.fetchall() if fetched: line = fetched[random.randint(0, len(list(cur)) - 1)] else: resetFailcount() getOnePokemonToWorkOn() except Exception: raise finally: bdd.fermerConnexion(cur, conn) return line
def cleanAnswered(): tooOld = [] answered = [] (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT id, date FROM alreadyAnswered;") rows = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) for row in rows: twtDate = datetime.datetime.strptime(row[1], "%d-%m-%y").date() if (datetime.date.today() - twtDate).days < howOldAreTweets + 2: answered.append(row[0]) else: tooOld.append(row[0]) (cur, conn) = bdd.ouvrirConnexion() try: for _id in tooOld: bdd.executerReq( cur, "DELETE FROM alreadyAnswered WHERE id = '%s';" % (_id)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return answered
def populateAlreadyAnswered(): f = open(os.path.join(here, "alreadyAnswered.txt"), "r") answered = [] for line in f: answered.append(line) f.close() f = open(os.path.join(here, "lastReply.txt"), "r") lastId = f.readline().replace("\n", "") f.close() (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "DROP TABLE alreadyAnswered; CREATE TABLE alreadyAnswered (id TEXT, date VARCHAR(8), isLast BOOL);" ) for line in answered: bdd.executerReq( cur, "INSERT INTO alreadyAnswered VALUES ('%s', '%s', 0);" % (line.split(", ")[0], line.split(", ")[1])) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def addNewSpelling(correct, listOfIncorrect): (cur,conn) = bdd.ouvrirConnexion() try: rowNumber = bdd.executerReq(cur, "SELECT listOfIncorrect FROM corrections WHERE correct = '"+correct+"';") if rowNumber: oldListOfIncorrect = cur.fetchone()[0].split(",") else: plop = addOneMon(correct, listOfIncorrect) return plop except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) for incorrect in oldListOfIncorrect for newSpelling in listOfIncorrect: oldSplit = incorrect.split(" -") newSplit = newSpelling.split(" -") if oldSplit[0] == newSplit[0]: negativeKeywords = [] if len(oldSplit>1): negativeKeywords += oldSplit[1:] if len(newSplit>1): negativeKeywords += newSplit[1:] if negativeKeywords: listOfIncorrect[listOfIncorrect.index(newSpelling)] = newSplit[0] + " -" + " -".join(list(set(negativeKeywords))) break else: listOfIncorrent.append(incorrect) (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq("UPDATE corrections SET listOfIncorrects=%s WHERE correct = '%s';", (",".join(list(set(newSpellings))), correct)) bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) return 0
def resetMonthlycount(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "UPDATE corrections SET monthlyCount = 0;") bdd.validerModifs(conn) except Exception: raise bdd.fermerConnexion(cur, conn) return 0
def incrementFailcount(correctName, increment=1): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "UPDATE corrections SET failcount = failcount+"+str(increment)+" WHERE correct =\""+correctName+"\";") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def getLastDM(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT id FROM lastDM;") lastDM = cur.fetchone()[0] except Exception: raise finally: bdd.fermerConnexion(cur, conn) return lastDM
def lastAnswer(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT id FROM isLast;") lastId = int(cur.fetchone()[0]) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return lastId
def setLastDM(value): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "UPDATE lastDM SET id="+value+";") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def resetFailcount(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "UPDATE corrections SET failcount = 0 WHERE failcount < "+str(failDecrement)+";") bdd.executerReq(cur, "UPDATE corrections SET failcount = failcount-"+str(failDecrement)+" WHERE failcount >= "+str(failDecrement)+";") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def incrementFailcount(correctName, increment=1): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "UPDATE corrections SET failcount = failcount+" + str(increment) + " WHERE correct =\"" + correctName + "\";") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def getAlreadyAnswered(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT id FROM alreadyAnswered;") answered = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) answered = [a[0] for a in answered] return answered
def getBlockedUsers(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "SELECT name FROM blockedUsers;") blocked = cur.fetchall() except Exception: raise finally: bdd.fermerConnexion(cur, conn) blocked = [b[0] for b in blocked] return blocked
def addToAnswered(s): twtId = str(s.id) twtDate = s.created_at.date().strftime("%d-%m-%y") (cur,conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "INSERT INTO alreadyAnswered (id, date) VALUES ('%s', '%s');" % (twtId, twtDate)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def blockUser(s, swearword): screenName = s.user.screen_name content = s.text (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "INSERT INTO blockedUsers VALUES (%s, %s, %s);", (screenName, content, swearword)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def addIdToAnswered(id_str): twtDate = datetime.datetime.today().strftime("%d-%m-%y") (cur,conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "INSERT INTO answered (id, date) VALUES ('%s', '%s');" % (id_str, twtDate)) bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) return 0
def manualUnblock(screenName): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "DELETE FROM blockedUsers WHERE name = '%s';" % (screenName)) deletedRows = cur.rowcount bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) if cur.rowcount: return 0 return 1
def manualBlock(screenName): if screenName == masterName: return 1 (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "INSERT INTO blockedUsers (name, tweet, swearword) VALUES (%s, %s, %s);", (screenName, "Manual block", "Manual block") ) bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) return 0
def resetFailcount(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "UPDATE corrections SET failcount = 0 WHERE failcount < " + str(failDecrement) + ";") bdd.executerReq( cur, "UPDATE corrections SET failcount = failcount-" + str(failDecrement) + " WHERE failcount >= " + str(failDecrement) + ";") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def manualUnblock(screenName): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "DELETE FROM blockedUsers WHERE name = '%s';" % (screenName)) deletedRows = cur.rowcount bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) if cur.rowcount: return 0 return 1
def createPokemonTable(): (cur,conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "CREATE TABLE corrections (correct VARCHAR(12), listOfIncorrect TEXT, emoji TEXT, overallCount MEDIUMINT UNSIGNED, monthlyCount SMALLINT UNSIGNED, PRIMARY KEY (correct));") for row in corrections: emoji = "" if len(row) > 2: emoji = row[2] bdd.executerReq(cur, "INSERT INTO corrections (correct, listOfIncorrect, emoji, overallCount, monthlyCount) VALUES (%s, %s, %s, 0, 0);", (row[0], list2str(row[1]), emoji)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def insertPokemon(): (cur, conn) = bdd.ouvrirConnexion() try: #bdd.executerReq(cur, "insert into corrections values ('Sarmuraï', 'sarmourai', '🎎', 0, 0);") #bdd.executerReq(cur, "delete from corrections where correct = 'Silvallié';") #bdd.executerReq(cur, "delete from corrections where correct = 'Ékaiser';") #bdd.executerReq(cur, "insert into corrections values ('Zéroïd', 'zeroide', '', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Magearna', 'magerna', '🤖', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Rapasdepic', 'rapacedepic', '', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Psystigri', 'psistigri', '', 0, 0);") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def insertPokemon(): (cur,conn) = bdd.ouvrirConnexion() try: #bdd.executerReq(cur, "insert into corrections values ('Sarmuraï', 'sarmourai', '🎎', 0, 0);") #bdd.executerReq(cur, "delete from corrections where correct = 'Silvallié';") #bdd.executerReq(cur, "delete from corrections where correct = 'Ékaiser';") #bdd.executerReq(cur, "insert into corrections values ('Zéroïd', 'zeroide', '', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Magearna', 'magerna', '🤖', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Rapasdepic', 'rapacedepic', '', 0, 0);") #bdd.executerReq(cur, "insert into corrections values ('Psystigri', 'psistigri', '', 0, 0);") bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def addToAnswered(s): twtId = str(s.id) twtDate = s.created_at.date().strftime("%d-%m-%y") (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "INSERT INTO alreadyAnswered (id, date) VALUES ('%s', '%s');" % (twtId, twtDate)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def addEmojisToPkmn(): (cur,conn) = bdd.ouvrirConnexion() try: for row in corrections: emoji = "" if len(row) > 2: emoji = row[2] bdd.executerReq(cur, "UPDATE corrections SET emoji = '%s' WHERE correct = '%s';" % (emoji, row[0])) #if not cur.rowcount: #bdd.executerReq(cur, "INSERT INTO corrections (correct, listOfIncorrect, emoji, overallCount, monthlyCount) VALUES (%s, %s, %s, 0, 0);", (row[0], list2str(row[1]), emoji)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def manualBlock(screenName): if screenName == masterName: return 1 (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "INSERT INTO blockedUsers (name, tweet, swearword) VALUES (%s, %s, %s);", (screenName, "Manual block", "Manual block")) bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) return 0
def addOneMon(correct, listOfIncorrect): if listOfIncorrect[-1].beginswith('emoji='): emoji = listOfIncorrect[-1] listOfIncorrect.pop() else: emoji = "" print(stuff) (cur,conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "INSERT INTO corrections VALUES (%s, %s, %s, 0, 0);", (correct, listOfIncorrect, emoji)) bdd.validerModifs(conn) except Exception: raise return 1 finally: bdd.fermerConnexion(cur, conn) return 0
def addEmojisToPkmn(): (cur, conn) = bdd.ouvrirConnexion() try: for row in corrections: emoji = "" if len(row) > 2: emoji = row[2] bdd.executerReq( cur, "UPDATE corrections SET emoji = '%s' WHERE correct = '%s';" % (emoji, row[0])) #if not cur.rowcount: #bdd.executerReq(cur, "INSERT INTO corrections (correct, listOfIncorrect, emoji, overallCount, monthlyCount) VALUES (%s, %s, %s, 0, 0);", (row[0], list2str(row[1]), emoji)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def createPokemonTable(): (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "CREATE TABLE corrections (correct VARCHAR(12), listOfIncorrect TEXT, emoji TEXT, overallCount MEDIUMINT UNSIGNED, monthlyCount SMALLINT UNSIGNED, PRIMARY KEY (correct));" ) for row in corrections: emoji = "" if len(row) > 2: emoji = row[2] bdd.executerReq( cur, "INSERT INTO corrections (correct, listOfIncorrect, emoji, overallCount, monthlyCount) VALUES (%s, %s, %s, 0, 0);", (row[0], list2str(row[1]), emoji)) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
def populateAlreadyAnswered(): f = open(os.path.join(here,"alreadyAnswered.txt"),"r") answered = [] for line in f: answered.append(line) f.close() f = open(os.path.join(here,"lastReply.txt"),"r") lastId = f.readline().replace("\n","") f.close() (cur,conn) = bdd.ouvrirConnexion() try: bdd.executerReq(cur, "DROP TABLE alreadyAnswered; CREATE TABLE alreadyAnswered (id TEXT, date VARCHAR(8), isLast BOOL);") for line in answered: bdd.executerReq(cur, "INSERT INTO alreadyAnswered VALUES ('%s', '%s', 0);" % (line.split(", ")[0], line.split(", ")[1])) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn) return 0
["Mimiqui", ["mimiki", "mimmiki", "mimmiqui"], "👻"], ["Chelours", ["chelourse"], "🐻"], ["Plumeline", ["plumelline", "plumelinne"], "🐦"], ["Météno", ["metenno"], "🌟"], ["Argouste", ["hargouste"], "👱"], ["Mimantis", ["mimantiss", "mimantisse"]], ["Floramantis", ["floramantiss", "floramantisse"]], ["Tiboudet", ["tibaudet"], "🐎"], ["Ossatueur", ["osatueur", "ossatuer", "ossateur"], "💀"], ["Cocombaffe", ["coconbaffe", "coconbafe", "cocombafe", "cocombaff"], "🍈"], ["Froussardine", ["frousardine"], "🐟"], ["Nounourson", ["nounoursson"], "🐻"], ["Trépassable", ["trepasable"], "🌅"], ["Bacabouh", ["bacabout", "bacabou", "bacaboue"], "🌅"], ["Crabagarre", ["crabbagarre", "crabagare", "crabbagare"], "🦀"], ["Boumata", ["boumatta"]] ] (cur,conn) = bdd.ouvrirConnexion() try: for row in corrections: emoji = "" if len(row) > 2: emoji = row[2] bdd.executerReq(cur, "UPDATE corrections SET emoji = '%s' WHERE correct = '%s';" % (emoji, row[0])) bdd.validerModifs(conn) except Exception: raise finally: bdd.fermerConnexion(cur, conn)
# -*- coding: UTF-8 -*- from toolbox import * import bddAccess as bdd (cur, conn) = bdd.ouvrirConnexion() try: bdd.executerReq( cur, "SELECT correct, monthlyCount FROM corrections ORDER BY monthlyCount DESC;" ) corrected = cur.fetchall() except Exception: raise bdd.fermerConnexion(cur, conn) resetMonthlycount() resetFailcount() m = "Pokémon les plus corrigés du mois :\n" i = 0 line = "" while len(m + line) <= 140: m += line print(m) print(len(m)) line = "" pokemons = [] counter = corrected[i][1] while corrected[i][1] == counter: pokemons.append(corrected[i][0])