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