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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
import pymysql
import subprocess
import subreddit
import time
import datetime
import logging
import re
class Database:
def __enter__(self):
self.__connection = pymysql.connect(
**subreddit.CONFIG["mysql"],
charset = "utf8mb4",
# cursorclass = pymysql.cursors.DictCursor
)
return self
def __exit__(self, type, value, traceback):
self.__connection.close()
def append_log(self, line, permalink = None, commit = True):
"""Function for adding a log file line to the database. Switched to
use the database for logging at the same time as switched to MySQL.
Args:
line (str): a line of a log
permalink (str, optional): a url about which the log line converns. Defaults to None.
commit (bool, optional): autocommit. Defaults to True.
"""
def get_date(stri):
# strip microseconds
stri = stri.split(",")[0]
try:
return datetime.datetime.strptime(stri, "%Y-%m-%d %H:%M:%S")
except ValueError:
return datetime.datetime.strptime(stri, "%b %d %Y %H:%M:%S")
addFlag = False
s = line.split("\t")
if len(s) == 3:
pid = int(s[0])
date = get_date(s[1][1:-1])
misc = s[2].rstrip()
addFlag = True
elif len(s) == 1:
s = s[0].rstrip()
result = re.search("\[(.*)\]", s)
if result is not None:
pid = None
date = get_date(result.group(1))
misc = s.replace("[%s]" % result.group(1), "")
addFlag = True
if addFlag:
if re.search(r"{ERROR", misc) is not None:
category = "ERROR"
else:
category = "INFO"
with self.__connection.cursor() as cursor:
cursor.execute("""
INSERT INTO log (pid, datetime_, category, data_, reddit_id) VALUES (
%s, %s, %s, %s, %s
);""", (pid, date, category, misc, permalink))
if commit:
self.__connection.commit()
def set_lambda(self, user, toset):
with self.__connection.cursor() as cursor:
cursor.execute("UPDATE users SET lambda = %s WHERE user_name = %s;", (toset, user))
self.__connection.commit()
def change_lambda(self, user, changeby):
with self.__connection.cursor() as cursor:
#this will make it go negative. You must check this operation is allowed.
cursor.execute("""
UPDATE users SET lambda = (
(SELECT lambda FROM users WHERE user_name = %s) + %s
) WHERE user_name = %s;
""", (user, changeby, user))
self.__connection.commit()
def give_lambda(self, user, link, timestamp = int(time.time()), op = None):
def give(user, link = None):
with self.__connection.cursor() as cursor:
#check if the user has an entry in the database
cursor.execute("SELECT userID FROM users WHERE user_name = %s;", (user, ))
try:
id_ = cursor.fetchone()[0]
except TypeError:
#the user isn't in the database
cursor.execute("""
INSERT INTO users (user_name, lambda) VALUES (%s, 1);
""", (user, ))
if link is not None:
cursor.execute("""
INSERT INTO lambdas (userID, permalink, created) VALUES ((
SELECT userID FROM users WHERE user_name = %s
), %s, %s);
""", (user, link, timestamp))
else:
#update their lambda and add to lambdas
self.change_lambda(user, 1)
if link is not None:
cursor.execute("""
INSERT INTO lambdas (userID, permalink, created) VALUES (%s, %s, %s);
""", (id_, link, timestamp))
self.__connection.commit()
#give one lambda to both the user and the OP
give(user, link)
if op is not None:
give(op)
def get_lambda(self, user):
with self.__connection.cursor() as cursor:
cursor.execute("SELECT lambda FROM users WHERE user_name = %s", (user, ))
try:
lambda_ = cursor.fetchone()[0]
except TypeError:
#the user isn't in the database, and therefore has no lambda
return 0, []
else:
cursor.execute("SELECT permalink FROM lambdas WHERE userID = (SELECT userID FROM users WHERE user_name = %s);", (user, ))
links = [i[0] for i in cursor.fetchall()]
return lambda_, links
def link_in_db(self, link):
with self.__connection.cursor() as cursor:
cursor.execute("SELECT permalink FROM lambdas WHERE permalink = %s;", (link, ))
try:
links = [i[0] for i in cursor.fetchall()]
except TypeError:
links = []
return link in links
def add_to_blacklist(self, id):
with self.__connection.cursor() as cursor:
cursor.execute("INSERT INTO blacklist (prawID) VALUES (%s);", (id, ))
self.__connection.commit()
def id_in_blacklist(self, id):
with self.__connection.cursor() as cursor:
cursor.execute("SELECT prawID FROM blacklist WHERE prawID = %s;", (id, ))
try:
ids = [i[0] for i in cursor.fetchall()]
except TypeError:
ids = []
return id in ids
def get_scores(self):
with self.__connection.cursor() as cursor:
cursor.execute("""
SELECT users.user_name, users.lambda, COUNT(users.user_name)
FROM lambdas INNER JOIN users ON users.userID = lambdas.userID
GROUP BY users.user_name;
""")
return cursor.fetchall()
def update_stats(self):
with self.__connection.cursor() as cursor:
cursor.execute("""
INSERT INTO stats (lambdaCount, helpGiven, uniqueUsers, date) VALUES (
(SELECT SUM(lambda) FROM users),
(SELECT COUNT(lambdaID) FROM lambdas),
(SELECT COUNT(user_name) FROM users),
(SELECT DATE_FORMAT(NOW(), "%Y-%m-%d")));
""")
self.__connection.commit()
def get_stats(self):
with self.__connection.cursor() as cursor:
cursor.execute("SELECT * FROM stats;")
return cursor.fetchall()
def user_given_lambda(self, user, permalink):
links = self.get_lambda(user)[1]
return permalink in links or permalink.replace("https://www.reddit.com", "") in links
def get_all_lambdas(self):
with self.__connection.cursor() as cursor:
cursor.execute("""
SELECT lambdas.lambdaID, lambdas.permalink, users.user_name, lambdas.created
FROM lambdas INNER JOIN users ON lambdas.userID = users.userID;
""")
return cursor.fetchall()
def add_date_to_permalink(self, permalink, date):
with self.__connection.cursor() as cursor:
cursor.execute("UPDATE lambdas SET created = %s WHERE permalink = %s;", (date, permalink))
self.__connection.commit()
def get_lambda_leaderboard(self):
with self.__connection.cursor() as cursor:
cursor.execute("""
SELECT users.user_name, COUNT(lambdas.userID) AS times_helped, users.lambda
FROM lambdas INNER JOIN users ON users.userID = lambdas.userID
WHERE created > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 30))
GROUP BY lambdas.userID ORDER BY times_helped DESC LIMIT 10;
""")
return cursor.fetchall()
if __name__ == "__main__":
print(subreddit.format_monthly_leaderboard())
|
