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
|
import sqlite3
class Database:
def __init__(self):
self.connection = sqlite3.connect("SmallYTChannelDatabase.db")
self.cursor = self.connection.cursor()
def change_lambda(self, user, changeby):
#this will make it go negative. You must check this operation is allowed.
self.cursor.execute("UPDATE users SET lambda = ((SELECT lambda FROM users WHERE user_name = ?) + ?) WHERE user_name = ?;", (user, changeby, user))
self.connection.commit()
def give_lambda(self, user, link, op = None):
def give(user, link = None):
#check if the user has an entry in the database
self.cursor.execute("SELECT userID FROM users WHERE user_name = ?;", (user, ))
try:
id_ = self.cursor.fetchone()[0]
except TypeError:
#the user isn't in the database
self.cursor.execute("INSERT INTO users (user_name, lambda) VALUES (?, 1);", (user, ))
self.connection.commit()
if link is not None:
self.cursor.execute("INSERT INTO lambdas (userID, permalink) VALUES ((SELECT userID FROM users WHERE user_name = ?), ?);", (user, link))
else:
#update their lambda and add to lambdas
self.change_lambda(user, 1)
if link is not None:
self.cursor.execute("INSERT INTO lambdas (userID, permalink) VALUES (?, ?);", (id_, link))
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):
self.cursor.execute("SELECT lambda FROM users WHERE user_name = ?", (user, ))
try:
lambda_ = self.cursor.fetchone()[0]
except TypeError:
#the user isn't in the database, and therefore has no lambda
return 0, []
else:
self.cursor.execute("SELECT permalink FROM lambdas WHERE userID = (SELECT userID FROM users WHERE user_name = ?);", (user, ))
links = [i[0] for i in self.cursor.fetchall()]
return lambda_, links
def link_in_db(self, link):
self.cursor.execute("SELECT permalink FROM lambdas;")
try:
links = [i[0] for i in self.cursor.fetchall()]
except TypeError:
links = []
return link in links
def add_to_blacklist(self, id):
self.cursor.execute("INSERT INTO blacklist (prawID) VALUES (?);", (id, ))
self.connection.commit()
def id_in_blacklist(self, id):
self.cursor.execute("SELECT prawID FROM blacklist;")
try:
ids = [i[0] for i in self.cursor.fetchall()]
except TypeError:
ids = []
return id in ids
def get_scores(self):
self.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 self.cursor.fetchall()
def update_stats(self):
query = """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 strftime('%Y-%m-%d')));"""
self.cursor.execute(query)
self.connection.commit()
def get_stats(self):
self.cursor.execute("SELECT * FROM stats;")
return self.cursor.fetchall()
|
