/
db.py
106 lines (78 loc) · 2.64 KB
/
db.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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
import psycopg2
import os
import re
# DATABASE_URL = os.environ['DATABASE_URL']
def db_create() -> None:
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS
globalData(
currentconnection INT
);
""")
cur.execute("""CREATE TABLE IF NOT EXISTS
msg(
chat TEXT,
sender TEXT,
userName TEXT,
puzzNum INT
);
""")
cur.execute("""CREATE TABLE IF NOT EXISTS
userStats(
userName TEXT,
userMention TEXT,
allClutches INT,
allPerfect INT,
allFails INT,
allWins INT,
UNIQUE(userMention)
);
""")
conn.commit()
cur.close()
conn.close()
def dbinsertConnMsg(message,author) -> int:
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
pattern = r'\b\d+\b'
match = re.search(pattern, message)
puzzNum = 0
if match : puzzNum = int(match.group(0))
sql = "INSERT INTO msg (chat, sender, userName, puzzNum) VALUES (%s, %s, %s, %s)"
val = (message, author.mention, author.name, puzzNum)
cur.execute(sql,val)
conn.commit()
cur.close()
conn.close()
return puzzNum
def dbUpdateCurrent(puzzNum):
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
cur.execute("UPDATE globalData SET currentconnection = %s", (puzzNum,))
conn.commit()
cur.close()
conn.close()
def dbUpdateStats(author, isPerfect, isFail, isClutch, isWin):
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
cur.execute("""
INSERT INTO userStats (username, userMention, allClutches, allPerfect, allFails, allWins)
VALUES (%s, %s, %s, %s, %s, %s)
ON CONFLICT (userMention) DO UPDATE
SET allClutches = userStats.allClutches + EXCLUDED.allClutches, allPerfect= userStats.allPerfect + EXCLUDED.allPerfect, allFails = userStats.allFails + EXCLUDED.allFails , allWins = userStats.allWins + EXCLUDED.allWins
""", (author.name, author.mention, isClutch , isPerfect, isFail, isWin))
conn.commit()
cur.close()
conn.close()
def viewStats(author):
conn = psycopg2.connect(DATABASE_URL, sslmode='require')
cur = conn.cursor()
cur.execute("SELECT * FROM userStats WHERE userMention = %s", (author.mention,))
user_stats = cur.fetchone()
conn.commit()
cur.close()
conn.close()
if user_stats:
return user_stats[2] , user_stats[3], user_stats[4], user_stats[5]
return None