-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql_functions.py
83 lines (62 loc) · 2.46 KB
/
sql_functions.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
import sqlite3
# TODO: try except
def get_user_list():
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('SELECT * FROM Utenti WHERE Status=""')
return cur.fetchall()
def get_old_grades(telegramID):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('SELECT * FROM Voti WHERE user=? ORDER BY subject', (telegramID,))
return cur.fetchall()
def check_user(telegramID):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('SELECT * FROM Utenti WHERE TelegramID=?', (telegramID,))
return cur.fetchall()
def change_status(new_status, telegramID):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('UPDATE Utenti SET Status=? WHERE TelegramID=?', (new_status, telegramID))
def register_user(telegramID, status):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('INSERT INTO Utenti (TelegramID, Status) VALUES (?, ?)', (telegramID, status))
def register_username(telegramID, username):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('UPDATE Utenti SET Username=? WHERE TelegramID=?', (username, telegramID))
def register_password(telegramID, password):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('UPDATE Utenti SET Password=? WHERE TelegramID=?', (password, telegramID))
def register_vote(telegramID, gradesID, value, subject, date):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('INSERT INTO Voti (user, gradesID, value, subject, date) VALUES (?, ?, ?, ?, ?)', (telegramID, gradesID, value, subject, date))
def delete_user_grades(telegramID):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('DELETE FROM Voti WHERE user=?', (telegramID,))
def delete_user(telegramID):
delete_user_grades(telegramID)
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('DELETE FROM Utenti WHERE TelegramID=?', (telegramID,))
def get_averages(telegramID):
conn = sqlite3.connect('voti.db')
with conn:
cur = conn.cursor()
cur.execute('SELECT subject, avg(value) FROM Voti WHERE user=? GROUP BY subject', (telegramID,))
return cur.fetchall()