def channel_activity(channel=None): connection = utils.connect() cursor = connection.cursor() # All channels if channel is None: cursor.execute("SELECT * FROM channel_activity") return cursor.fetchall() return getOrAddChannel(channel.id)
def add(userid, link): user = get_user(userid) connection = utils.connect() cursor = connection.cursor() if len(user) == 0: cursor.execute("INSERT INTO twitch(userid, link) VALUES (%s, %s)", (int(userid), str(link),)) else: cursor.execute("""UPDATE twitch SET link = %s WHERE userid = %s""", (link, int(userid),)) connection.commit()
def blacklist(user, bl=True): connection = utils.connect() cursor = connection.cursor() cursor.execute("UPDATE info " "SET poke_blacklist = %s WHERE userid = %s", ( bl, int(user), )) connection.commit()
def getAllPlatDinks(): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT userid, amount FROM inventory WHERE itemid = 1") result = cursor.fetchall() dic = {} for user in result: dic[str(user[0])] = user[1] return dic
def insert(id, name, fields): if getMeme(name) is not None: return "Deze meme staat al in de database." connection = utils.connect() cursor = connection.cursor() cursor.execute("INSERT INTO memes(id, name, fields) VALUES (%s, %s, %s)", [int(id), name.lower(), int(fields)]) connection.commit() return "{} is toegevoegd aan de database.".format(name[0].upper() + name[1:].lower())
def add(quote, date, location): connection = utils.connect() cursor = connection.cursor() cursor.execute( "INSERT INTO trump(quote, date, location) VALUES (%s, %s, %s)", ( str(quote), str(date), str(location), )) connection.commit()
def _find_by_name(message): """ Find a command by its name """ connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM custom_commands WHERE name = %s", (message,)) return cursor.fetchone()
def add_user(userid, day, month, year): connection = utils.connect() cursor = connection.cursor() if get_user(userid): cursor.execute("UPDATE birthdays SET day = %s, month = %s, year = %s WHERE userid = %s", (int(day), int(month), int(year), int(userid),)) else: cursor.execute("INSERT INTO birthdays(userid, day, month, year) VALUES (%s, %s, %s, %s)", (int(userid), int(day), int(month), int(year),)) connection.commit()
def update(userid, column, value): _ = getOrAddUser(userid) connection = utils.connect() cursor = connection.cursor() query = "UPDATE personalstats " \ "SET {} = %s " \ "WHERE userid = %s".format(column) cursor.execute(query, (value, userid,)) connection.commit() statsTracker(column)
def getOrAddUser(userid): connection = utils.connect() cursor = connection.cursor() query = "SELECT * FROM currencytable WHERE userid = %s" cursor.execute(query, (int(userid), )) result = cursor.fetchall() # User didn't exist yet, so create a new default file if len(result) == 0: createNewUser(userid, connection) return getOrAddUser(userid) return result[0]
def sell(userid, itemid, sold, amount): connection = utils.connect() cursor = connection.cursor() # Don't store amount: 0 if sold == amount: cursor.execute("DELETE FROM inventory WHERE userid = %s AND itemid = %s", (userid, itemid,)) return connection.commit() cursor.execute("UPDATE inventory SET amount = %s WHERE userid = %s AND itemid = %s", (amount - sold, userid, itemid)) return connection.commit()
def getMeme(name): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM memes WHERE name like %s", ["%" + name.lower() + "%"]) result = cursor.fetchall() if len(result) == 0: return None meme = Meme(result[0][0], result[0][1], result[0][2]) return meme
def imprison(userid, bailsum, days, daily): connection = utils.connect() cursor = connection.cursor() cursor.execute( "INSERT INTO prison(userid, bail, days, daily) VALUES (%s, %s, %s, %s)", ( int(userid), float(bailsum), int(days), float(daily), )) connection.commit()
def add_command(name: str, response: str): """ Add a new custom command """ name = _clean(name.lower()) if not is_name_free(name): return "Er is al een commando met deze naam." connection = utils.connect() cursor = connection.cursor() cursor.execute("INSERT INTO custom_commands(name, response) VALUES (%s, E%s)", (name, response,)) connection.commit()
def addItem(userid, itemid, amount, inv): connection = utils.connect() cursor = connection.cursor() # It's already in there, add more to the counter if inv: amount += inv[0][0] cursor.execute("UPDATE inventory SET amount = %s WHERE userid = %s AND itemid = %s", (amount, userid, itemid)) connection.commit() return # Doesn't own this item yet, add a new row cursor.execute("INSERT INTO inventory(userid, itemid, amount) VALUES (%s, %s, %s)", (userid, itemid, amount)) connection.commit()
def removeMuttn(message): if str(message.author.id) == constants.didierId: return connection = utils.connect() cursor = connection.cursor() user = getOrAddUser(message.author.id) cursor.execute("UPDATE muttn SET count = %s WHERE userid = %s", ( int(user[2]) - 1, int(message.author.id), )) connection.commit()
def getOrAddUser(userid): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM remind WHERE userid = %s", (int(userid), )) res = cursor.fetchall() if not res: cursor.execute("INSERT INTO remind(userid) VALUES(%s)", (int(userid), )) connection.commit() return getOrAddUser(userid) return res[0]
def getOrAddChannel(channelid: int): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM channel_activity WHERE channel_id = %s", (channelid,)) res = cursor.fetchall() if not res: cursor.execute("INSERT INTO channel_activity(channel_id, message_count) VALUES (%s, 0)", (channelid,)) connection.commit() return getOrAddChannel(channelid) return res
def dinksAll(userid): platinumDinks = 0 connection = utils.connect() cursor = connection.cursor() cursor.execute( "SELECT amount FROM inventory WHERE userid = %s AND itemid = %s", ( int(userid), 1, )) result = cursor.fetchall() if result: platinumDinks = result[0][0] return {"dinks": dinks(userid), "platinum": platinumDinks}
def is_name_free(name) -> bool: """ Check if a name is already in use by a command Includes aliases """ connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT id from custom_commands WHERE name = %s", (name,)) if cursor.fetchone(): return False cursor.execute("SELECT id FROM custom_command_aliases WHERE alias = %s", (name,)) return cursor.fetchone() is None
def add(userid, link): user = get_user(userid) connection = utils.connect() cursor = connection.cursor() if len(user) == 0: cursor.execute( "INSERT INTO githubs(userid, githublink) VALUES (%s, %s)", ( int(userid), str(link), )) else: cursor.execute( """UPDATE githubs SET githublink = %s WHERE userid = %s""", ( user[0][0] + "\n" + link, int(userid), )) connection.commit()
def _find_by_alias(message): """ Find a command by one of its aliases """ connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT command FROM custom_command_aliases WHERE alias = %s", (message,)) res = cursor.fetchone() # No command matched this alias if not res: return () cursor.execute("SELECT * FROM custom_commands WHERE id = %s", (res,)) return cursor.fetchone()
def switchReminder(userid, column): connection = utils.connect() cursor = connection.cursor() columns = ["id", "nightly", "les"] res = getOrAddUser(userid) # Switch the column value to = not (res[columns.index(column)]) cursor.execute( "UPDATE remind SET {} = %s WHERE userid = %s".format(column), ( to, int(userid), )) connection.commit() return to
def dailyLowers(): connection = utils.connect() cursor = connection.cursor() # Release people from prison on their last day cursor.execute("DELETE FROM prison WHERE days = 1") connection.commit() # Get all remaining users cursor.execute("SELECT * FROM prison") prisoners = cursor.fetchall() for prisoner in prisoners: cursor.execute( "UPDATE prison " "SET bail = %s, days = %s " "WHERE userid = %s", ( float(prisoner[1]) - float(prisoner[3]), int(prisoner[2]) - 1, int(prisoner[0]), )) connection.commit()
def add_alias(command: str, alias: str): """ Add an alias for a command Assumes the command exists """ command = _clean(command.lower()) alias = _clean(alias.lower()) # Base command doesn't exist if is_name_free(command): return "Er is geen commando met deze naam." # Alias already exists if not is_name_free(alias): return "Er is al een commando met deze naam." # Find the id of the base command command_id = CustomCommand(*_find_by_name(command)).id connection = utils.connect() cursor = connection.cursor() cursor.execute("INSERT INTO custom_command_aliases(command, alias) VALUES(%s, %s)", (command_id, alias,)) connection.commit()
def getAllRows(): connection = utils.connect() cursor = connection.cursor() cursor.execute("""SELECT * FROM currencytable""") return cursor.fetchall()
def getAllRows(): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM muttn") return cursor.fetchall()
def getUser(userId): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT * FROM prison WHERE userid = %s", (int(userId), )) return cursor.fetchall()
def remove(userId): connection = utils.connect() cursor = connection.cursor() cursor.execute("DELETE FROM prison WHERE userid = %s", (int(userId), )) connection.commit()
def get_user(userid): connection = utils.connect() cursor = connection.cursor() cursor.execute("SELECT githublink FROM githubs WHERE userid = %s", (int(userid), )) return cursor.fetchall()