def add_unique_words(msg):
"""
Checks if message contains at least one unique word, adds unique words if true
:param msg:
:return:
"""
con = get_con('uniquewords', msg.server.id)
content = msg.content
translator = str.maketrans('', '', punctuation)
content = content.translate(translator)
content = content.lower().split(' ')
valid, bools = contains_unique_word(con, content)
if not valid:
return False
with con:
cur = con.cursor()
for c in range(len(content)):
if bools[c]: # want to add
cur.execute(
"INSERT INTO " + 'UniqueWords' +
" VALUES(?, ?, ?, ?, ?, ?, ?)",
(content[c], msg.id, msg.author.id, msg.author.name,
msg.channel.id, str(msg.timestamp), msg.server.id))
return True
def add_message(db_name, msg):
"""
Add discord message to database
:param db_name: database to augment
:param msg: message to add
"""
#metadata = get_meta(msg.server.id)
#count = metadata['count'] + 1
con = get_con(db_name, msg.server.id)
msg_content = msg.content
if len(msg.attachments) > 0:
temp1 = temp2 = msg_content
for atch in msg.attachments:
temp2 += "\n" + atch['url']
if len(temp2) > 2000:
break
temp1 = temp2
msg_content = temp1
with con:
cur = con.cursor()
cur.execute(
"INSERT INTO " + db_name + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
(None, msg_content, msg.id, msg.author.id, msg.author.name,
msg.channel.id, str(msg.timestamp), msg.server.id))
async def rand_query(server_id, target_usr, num_msgs, img_flag=False):
try:
con = get_con('messages', server_id)
cur = con.cursor()
getCountQuery = "SELECT count(*) FROM " + 'messages'
cur.execute(getCountQuery)
cnt = int(cur.fetchall()[0][0])
msg_results = []
for i in range(num_msgs):
rand_id = random.randrange(cnt) + 1
randMsgQuery = "SELECT Content, UserId, Username, Time FROM messages WHERE DatabaseId = " + str(
rand_id)
if target_usr:
randMsgQuery += " AND UserId = " + str(
target_usr
) # LOL no, this goes in the getCountQuery part (sorta)
cur.execute(randMsgQuery)
x = list(cur.fetchall()[0])
#print('msg =', x)
msg_results.append(x)
print(msg_results)
return True, msg_results
except:
print('THERE WAS A FAILURE!')
return False, None
async def id_query(server_id, start_idx, num_msgs, img_flag=False):
try:
con = get_con('messages', server_id)
cur = con.cursor()
getCountQuery = "SELECT count(*) FROM " + 'messages'
cur.execute(getCountQuery)
cnt = int(cur.fetchall()[0][0])
print('cnt =', cnt)
print("type(start_idx) =", type(start_idx))
if start_idx > cnt:
return False, "That ID too high!!!!!!!!"
#start_idx = (cnt + start_idx)%cnt + 1
if start_idx < 0:
start_idx = cnt + start_idx + 1
print('start_idx =', start_idx)
lftovr = 0
right_idx = start_idx + num_msgs // 2
if cnt < right_idx:
lftovr += right_idx - cnt
right_idx = cnt
left_idx = start_idx - math.ceil(num_msgs / 2)
if 1 > left_idx:
left_idx = 1
right_idx = min(right_idx + (1 - left_idx))
left_idx = max(left_idx - lftovr, 1)
idMsgQuery = "SELECT Content, UserId, Username, Time FROM messages WHERE DatabaseId = "
msg_results = []
print(left_idx, "|", right_idx)
for i in range(left_idx + 1, right_idx + 1):
cur.execute(idMsgQuery + str(i))
#content, author, date = cur.fetchall()[0]
x = list(cur.fetchall()[0])
print('x =', x)
msg_results.append(x)
print('msg_results:')
print(msg_results)
return True, msg_results
except Exception as e:
print('error querying messages:')
print(e)
return False, "Error getting logs... :*("
def contains_unique_word_by_channel(db_name, channel, msg):
con = get_con(db_name, msg.server.id)
with con:
cur = con.cursor()
cur.execute(
"SELECT Content FROM " + db_name + " WHERE Channel LIKE ? ",
(channel, ))
rows = cur.fetchall()
content = msg.content.split(' ')
if not rows:
return True
for row in rows:
unique_to_row = False
for w in content:
if w not in row[0]:
unique_to_row = True
if not unique_to_row:
return False
return True
async def get_top_words(
user_id,
server_id,
num_words,
min_lth,
punct_flag=False
): # sort arguments? that way the @mentions will all come after db_name
con = get_con('messages', server_id)
cur = con.cursor()
words = dict()
SQLQuery = "SELECT Content FROM " + 'messages'
if user_id:
SQLQuery += ' WHERE UserId = ' + user_id
# print('SQLQuery =',SQLQuery)
cur.execute(SQLQuery)
#messages = cur.fetchall()[0]
filter_words = open('data/prepositions.txt', 'r').read().split('\n')
translator = str.maketrans('', '', string.punctuation)
for sentence in cur.fetchall():
for w in sentence[0].split(' '):
if not punct_flag:
w = w.translate(translator).lower()
if len(w) > min_lth and w not in filter_words:
if w in words:
words[w] += 1
else:
words[w] = 1
top_words = [[key, value]
for key, value in sorted(words.items(), key=lambda t: t[1])]
#print(top_words[:num_words])
#print('top_words[-num_words:][::-1 =',top_words[-num_words:][::-1])
rankings = top_words[-num_words:][::-1]
for i in range(len(rankings)):
rankings[i] = str(i + 1) + ". " + rankings[i][0] + " (" + str(
rankings[i][1]) + ")"
return rankings
def plot_activity(db_name, server_id, author_id=None, key_word=None):
con = get_con(db_name, server_id)
sql_query = "SELECT UserId, Time FROM messages"
msg_list = []
with con:
cur = con.cursor()
cur.execute(sql_query)
msg_list = cur.fetchall()
#print('msg_list =',msg_list)
print('length is',len(msg_list))
print(msg_list[0],'\n',msg_list[-1])
print()
fmt1 = '%Y-%m-%d %H:%M:%S.%f' # or w/e you have
fmt2 = '%Y-%m-%d %H:%M:%S' # or w/e you have
start_date = datetime.strptime('2018-03-19 22:00:06.006000', fmt1) # make dynamic (obviously)
end_date = datetime.strptime('2018-04-07 04:02:31.681000', fmt1)
time_span = end_date - start_date
time_span_days = time_span.days
# want 1600x800 image
img_width = 1600
img_height = 800
img = Image.new('RGB', (img_width, img_height), color=(255,255,255))
pixels = img.load()
col_dict = dict()
diff = None
print('time_span_days =',time_span_days)
print()
for author_id, send_date in msg_list:
#print(author_id, '|', send_date)
if '.' not in send_date:
diff = datetime.strptime(send_date, fmt2) - start_date
else:
diff = datetime.strptime(send_date, fmt1) - start_date
diff_days = diff.days
#print('diff_days =',diff_days)
col = int((img_width-1) * (diff_days / time_span_days)) # img_width-1 ?
if col in col_dict:
col_dict[col] += 1
else:
col_dict[col] = 1
max_col_val = max(col_dict.values())
min_col_val = min(col_dict.values())
print(col_dict)
line_color = (0, 0, 255)
def draw_line(c1, r1, c2, r2):
line = get_line(c1, r1, c2, r2)
for x, y in line:
pixels[x, y] = line_color
prev_col, prev_row = 0,0
for col, val in col_dict.items():
#print('val / max_col_val =',((val - min_col_val) / max_col_val)) # (val / max_col_val)? not relative to min?
row = int((img_height-1) * (val / max_col_val))
draw_line(prev_col, prev_row, col, row)
prev_col, prev_row = col, row
img.save('message_graph.png')
async def db_count(
self, msg, *args
): # sort arguments? that way the @mentions will all come after db_name
con = get_con('messages', msg.server.id)
cur = con.cursor()
num_msgs = 0
print('args =', *args)
SQLQuery = "SELECT count(*) FROM " + 'messages'
if len(*args) == 1:
cur.execute(SQLQuery)
num_msgs = cur.fetchall()[0][0]
await self.ntsk.send_message(msg.channel,
str(num_msgs) + " messages logged~")
print(str(num_msgs) + " messages logged~")
else:
params = list(args[0])[1:]
like = False
usr_mention = False
target_usr = '******'
chn_mention = False
target_chn_name = '%'
target_chn_id = '%'
msgQuery = ''
if len(msg.raw_mentions) != 0:
usr_mention = True
target_usr = msg.raw_mentions[0]
if len(msg.channel_mentions) != 0:
chn_mention = True
target_chn_name = msg.channel_mentions[0].name
target_chn_id = msg.channel_mentions[0].id
for p in params:
if not like and p == '.like':
like = True
continue
if usr_mention and p == '<@' + target_usr + '>' or p == '<@!' + target_usr + '>':
continue
if chn_mention and p == '<#' + target_chn_id + '>' or p == '<#!' + target_chn_id + '>':
continue
msgQuery += p + ' '
msgQuery = msgQuery[:-1]
print('params =', params)
print('raw_mentions =', msg.raw_mentions)
print('channel_mentions =', msg.channel_mentions)
equals = 'LIKE' if like else '='
op = '%' if like else ''
MQ = False
if msgQuery != '':
MQ = True
SQLQuery += " WHERE Content " + equals + ' ' + "'" + op + msgQuery + op + "'"
if usr_mention:
if MQ:
SQLQuery += " AND "
else:
SQLQuery += " WHERE "
SQLQuery += "UserId = " + target_usr
if chn_mention:
if MQ or usr_mention:
SQLQuery += " AND "
else:
SQLQuery += " WHERE "
SQLQuery += "Channel = " + "'" + target_chn_name + "'"
print('SQLQuery =', SQLQuery)
cur.execute(SQLQuery)
num_msgs = cur.fetchall()[0][0]
await self.ntsk.send_message(
msg.channel,
str(num_msgs) + " messages matched your query!")