async def update_time(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: channel_time_table = Table('timeChannels', metadata, autoload=True, autoload_with=conn) select_st = select([channel_time_table]) res = conn.execute(select_st) for _row in res: former_name = _row[0] now_time = datetime.datetime.today().now(pytz.timezone( _row[3])).strftime('%H:%M') if _row[0] != f"⌚ {_row[1]}: {now_time}": update_statement = channel_time_table.update().values( channelName=f"⌚ {_row[1]}: {now_time}").where( and_( channel_time_table.c.channelName != f"⌚ {_row[1]}: {now_time}", channel_time_table.c.channelID == _row[2])) res = conn.execute(update_statement) await client.wait_until_ready() guild = client.get_guild(_row[5]) if guild: channel = get(guild.voice_channels, id=int(_row[2])) if channel: await channel.edit(name=f"⌚ {_row[1]}: {now_time}") except Exception as err: print(err) if conn: conn.close()
def clear_daily_trivia_leaderboard(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: delete_query = "DELETE FROM pwm.\"dailyLeaderboard\"" res = conn.execute(delete_query) return True except Exception as err: print(err) if conn: conn.close()
def set_current_question(question_id): metadata = MetaData(schema="pwm") try: with db.connect() as conn: current_question_table = Table('currentQuestion', metadata, autoload=True, autoload_with=conn) insert_statement = current_question_table.insert().values( question_id=question_id) conn.execute(insert_statement) except Exception as err: print(err) if conn: conn.close()
def remove_current_trivia(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: curr_question_table = Table('currentQuestion', metadata, autoload=True, autoload_with=conn) now = datetime.datetime.now() delete_query = f"DELETE FROM pwm.\"currentQuestion\"" res = conn.execute(delete_query) return True except Exception as err: print(err) if conn: conn.close()
def get_current_trivia_question_id(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: curr_question_table = Table('currentQuestion', metadata, autoload=True, autoload_with=conn) select_st = select([curr_question_table]) res = conn.execute(select_st) for _row in res: return _row[1] return None except Exception as err: print(err) if conn: conn.close()
def get_question_by_id(question_id): metadata = MetaData(schema="pwm") try: with db.connect() as conn: questions_table = Table('triviaQuestions', metadata, autoload=True, autoload_with=conn) select_st = select([questions_table ]).where(questions_table.c.id == question_id) res = conn.execute(select_st) for row in res: return {"question": row[1], "time_asked": row[2]} return None except Exception as err: print(err) if conn: conn.close()
def get_questions(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: questions = [] questions_table = Table('triviaQuestions', metadata, autoload=True, autoload_with=conn) select_st = select([questions_table]) res = conn.execute(select_st) for row in res: question = row[1] questions.append({ "id": row[0], "question": question, }) return questions except Exception as err: print(err) if conn: conn.close()
def get_trivia_leader_board(): metadata = MetaData(schema="pwm") try: with db.connect() as conn: participants = [] leaderboard_table = Table('triviaLeaderboard', metadata, autoload=True, autoload_with=conn) select_st = select([leaderboard_table ]).order_by(leaderboard_table.c.score.desc(), leaderboard_table.c.lastUpdated) res = conn.execute(select_st) for _row in res: participants.append({ 'id': _row[0], 'name': _row[1], 'score': _row[2] }) return participants[0:3] except Exception as err: print(err) if conn: conn.close()