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!")