def get_quote(sv): # gets a random quote from current server
cursor = tokenfile.get_cursor(connection)
query = 'SELECT quote_id, quoted_tag, quote, tstamp FROM Quotes WHERE server_id_e=%s'
cursor.execute(query, (sv, ))
quotes = cursor.fetchall(
) # returns list of tuples, use double index to get actual values
return quotes
def check_mode(sv): # checks and returns mode
cursor = tokenfile.get_cursor(connection)
query = "SELECT mode FROM Servers WHERE server_id=%s"
cursor.execute(query, (sv, ))
mode = cursor.fetchall(
) # returns list of tuples, use double index to get actual values
return mode[0][0]
def get_prefix(sv): # gets prefix for current server
cursor = tokenfile.get_cursor(connection)
query = "SELECT prefix FROM Servers WHERE server_id=%s"
cursor.execute(query, (sv, ))
prefix = cursor.fetchall(
) # returns list of tuples, use double index to get actual values
return prefix[0][0]
def get_last_quote(): # gets a random quote from current server
cursor = tokenfile.get_cursor(connection)
query = 'SELECT id FROM Quote ORDER BY id DESC LIMIT 1'
cursor.execute(query, )
quotes = cursor.fetchone(
) # query result is just one record, this should work
return quotes
def select_handicap(user): # selects handicap
cursor = tokenfile.get_cursor(connection)
query = "SELECT handicap FROM Handicap WHERE user_id=%s"
cursor.execute(query, (user, ))
handicap = cursor.fetchall(
) # returns list of tuples, use double index to get actual values
if not handicap:
return None
return handicap[0][0]
def new_server(sv, md): # adds new server to db, defaults to sfw
cursor = tokenfile.get_cursor(connection)
query = "INSERT INTO Servers (server_id, mode) VALUES (%s, %s)"
cursor.execute(query, (sv, md))
db.commit()
def send_query(q): # executes query q
cursor = tokenfile.get_cursor(connection)
cursor.execute(q)
db.commit()
def change_prefix(sv, pf): # changes prefix for current server
cursor = tokenfile.get_cursor(connection)
query = "UPDATE Servers SET prefix=%s WHERE server_id=%s"
cursor.execute(query, (pf, sv))
db.commit()
def get_time(): # fetch last time escape was used
cursor = tokenfile.get_cursor(connection)
query = 'SELECT cooldown FROM Escape'
cursor.execute(query,)
cooldown = cursor.fetchone()
return cooldown
def remove_quote(quote_nr): # removes quote from db
cursor = tokenfile.get_cursor(connection)
query = 'DELETE FROM Quote WHERE id = %s'
cursor.execute(query, (quote_nr, ))
db.commit()
def get_bancount(u_id): # gets nr of offenses
cursor = tokenfile.get_cursor(connection)
query = 'SELECT offense_count FROM Offense WHERE user_id = %s'
cursor.execute(query, (str(u_id), ))
bancount = cursor.fetchone()
return bancount
def remove_ignored(c_id): # removes channel from db
cursor = tokenfile.get_cursor(connection)
query = 'DELETE FROM Ignored_channel WHERE channel_id = %s'
cursor.execute(query, (c_id,))
db.commit()
def add_ignored(c_id, c_name): # adds channel to ignored list
cursor = tokenfile.get_cursor(connection)
query = 'INSERT INTO Ignored_channel (channel_id, channel_name) VALUES (%s, %s)'
cursor.execute(query, (c_id, c_name))
db.commit()
def get_quote_count():
cursor = tokenfile.get_cursor(connection)
query = 'SELECT quoted_tag, count(quoted_tag) FROM Quote GROUP BY quoted_tag'
cursor.execute(query, )
quote_count = cursor.fetchall()
return quote_count
def update_quote_no_user(
quote_nr, quote): # updates quote to db when no username is provided
cursor = tokenfile.get_cursor(connection)
query = 'UPDATE Quote SET quote = %s, quoted_tag = NULL WHERE id = %s'
cursor.execute(query, (quote, quote_nr))
db.commit()
def update_quote(quote_nr, quote, quoted_tag): # alters quote
cursor = tokenfile.get_cursor(connection)
query = 'UPDATE Quote SET quote = %s, quoted_tag = %s WHERE id = %s'
cursor.execute(query, (quote, quoted_tag, quote_nr))
db.commit()
def add_ban(u_id, offense_count): # adds new user to ban count
cursor = tokenfile.get_cursor(connection)
query = 'INSERT INTO Offense (user_id, offense_count) VALUES (%s, %s)'
cursor.execute(query, (u_id, offense_count))
db.commit()
def alter_ban(u_id, offense_count): # modifies offense count for user
cursor = tokenfile.get_cursor(connection)
query = 'UPDATE Offense SET offense_count = %s WHERE user_id = %s'
cursor.execute(query, (offense_count, u_id))
db.commit()
def add_quote(tag, quote, sv): # adds quote to db
cursor = tokenfile.get_cursor(connection)
query = 'INSERT INTO Quotes (quoted_tag, quote, server_id_e) VALUES (%s, %s, %s)'
cursor.execute(query, (tag, quote, sv))
db.commit()
def get_ignored(): # gets list of ignored channels
cursor = tokenfile.get_cursor(connection)
query = 'SELECT channel_id FROM Ignored_channel'
cursor.execute(query,)
ignored = cursor.fetchall() # returns list of tuples, use double index to get actual values
return ignored
def add_quote_no_user(quote,
sv): # adds quote to db when no username is provided
cursor = tokenfile.get_cursor(connection)
query = 'INSERT INTO Quote (quote, server_id) VALUES (%s, %s)'
cursor.execute(query, (quote, sv))
db.commit()
def change_handicap(user, handicap): # inserts handicap
cursor = tokenfile.get_cursor(connection)
query = "UPDATE Handicap SET handicap=%s WHERE user_id=%s"
cursor.execute(query, (handicap, user))
db.commit()
def change_mode(sv, md): # changes sfw mode
cursor = tokenfile.get_cursor(connection)
query = "UPDATE Servers SET mode=%s WHERE server_id=%s"
cursor.execute(query, (md, sv))
db.commit()
def insert_handicap(user, handicap): # inserts handicap
cursor = tokenfile.get_cursor(connection)
query = 'INSERT INTO Handicap (user_id, handicap) VALUES (%s, %s)'
cursor.execute(query, (user, handicap))
db.commit()
def update_time(new_cooldown): # updates when escape was used
cursor = tokenfile.get_cursor(connection)
query = 'UPDATE Escape SET cooldown = %s WHERE id = 1'
cursor.execute(query, (new_cooldown,))
db.commit()
