def incrementStatistics(field: str, userID: int):
addUser(userID)
with conn.cursor() as cursor:
cursor.execute(
f"UPDATE \"stats\" SET \"{field}\" = \"{field}\" + 1 WHERE \"userID\" = %s",
(userID, ))
def updateUserSettings(userID: int, **settings):
item, option = list(settings.items())[0]
with conn.cursor() as cursor:
cursor.execute(
f"UPDATE \"settings\" SET \"{item}\" = %s WHERE \"userID\" = %s",
(option, userID))
def change_nickname(user_id: int, new_nickname: str):
with conn.cursor() as cursor:
cursor.execute(
f"UPDATE \"user\" SET \"nickname\" = %s WHERE \"userID\" = %s",
(new_nickname, user_id))
for i, entry in enumerate(usersSet.nicknames):
if entry['id'] == user_id:
usersSet.nicknames[i]['nickname'] = new_nickname
break
def getPhotoReceivedUserSettings(userID: int) -> str:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM \"settings\" WHERE \"userID\" = %s;",
(userID, ))
result = [
i for i in cursor
][0] # tuple из всех настроек, где tuple[0] - userID, tuple[1] - photoReceived итд (как в бд)
photoReceived = result[1]
return photoReceived
def getUserStats(userID: int):
with conn.cursor() as cursor:
cursor.execute(f"SELECT * FROM \"stats\" WHERE \"userID\" = %s;",
(userID, ))
result = [i for i in cursor][0]
possibleStats = ["demoCreated", "inlineAnswered"]
stats = {
possibleStats[i]: result[i + 1]
for i in range(len(possibleStats))
}
return stats
def addUser(userID) -> bool:
if usersSet.inUsers(userID):
return False
with conn.cursor() as cursor:
cursor.execute(
"INSERT INTO \"user\" (\"userID\", \"signUpDate\") VALUES (%(userID)s, %(date)s);"
"INSERT INTO \"stats\" (\"userID\") VALUES (%(userID)s);"
"INSERT INTO \"settings\" (\"userID\") VALUES (%(userID)s);", {
'userID': userID,
'date': Date.today()
})
loop = get_event_loop()
loop.create_task(bot.send_message(adminUserID, f"User {userID} added"))
usersSet.update()
return True
def getWholeDb() -> str:
with conn.cursor() as cursor:
cursor.execute(f"SELECT * FROM \"user\";")
users = cursor.fetchall()
cursor.execute(f"SELECT * FROM \"stats\";")
stats = cursor.fetchall()
cursor.execute(f"SELECT * FROM \"settings\";")
settings = cursor.fetchall()
users = {user[0]: user[1:] for user in users}
stats = {stat[0]: stat[1:] for stat in stats}
settings = {setting[0]: setting[1:] for setting in settings}
userStats = [
f"\n\nUser -- {userID} ({users.get(userID)[1]}):\n"
f"> demoCreated = {stats.get(userID)[0]}, inlineAnswered = {stats.get(userID)[1]}\n"
f"> photoReceived = {settings.get(userID)[0]}" for userID in users
]
answer = "".join(userStats)
return answer
def get(self) -> List[Dict[str, str]]:
with conn.cursor() as cursor:
cursor.execute("SELECT \"userID\", \"nickname\" FROM \"user\";")
return [{'id': i[0], 'nickname': i[1]} for i in cursor]