def count_tags(url_id): """ Получаем список из тегов и количество их уникальных вхождений """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT tag.name, count(*) " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "INNER JOIN texts_text AS text " "ON text.id = tagtext.text_id " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE url.id = {} " "GROUP BY tag.name;".format(url_id) ) tag_count = cursor.fetchall() connect.close() return tag_count
def texts_of_tag(tag): """ Получаем список текстов, в которых встречался tag """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT text.id " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "INNER JOIN texts_text AS text " "ON text.id = tagtext.text_id " "WHERE tag.name = '{}' " "ORDER BY text.id".format(tag) ) texts = cursor.fetchall() connect.close() return texts
def get_url_id(url): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT url.id " "FROM texts_url AS url " "WHERE url.url = '{}';".format(url)) return cursor.fetchall()[0][0] #id of URL
def get_url_with_id(url_id): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT url.url " "FROM texts_url AS url " "WHERE url.id = {}".format(url_id)) url = cursor.fetchall() connect.close() return url
def get_cluster_summary(cluster_id): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT cluster.summary " "FROM clusters_cluster AS cluster " "WHERE cluster.id = {}".format(cluster_id)) summary = cursor.fetchall() connect.close() return summary
def get_all_url(): """ Iterator of all URLs from DB :return: """ connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT url.id, url.url " "FROM texts_url AS url;") urls = iter(cursor.fetchall()) connect.close() for url in urls: yield url
def get_text(text_id): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT text.plain_text " "FROM texts_text AS text " "WHERE text.id = {}".format(text_id)) text = cursor.fetchall() connect.close() return text
def get_texts(list_id): """ Итератор текстов из поступающего на вход списка идетификаторов """ connect = get_connect() cursor = connect.cursor() for item in list_id: cursor.execute("SELECT text.plain_text, text.id " "FROM texts_text AS text " "WHERE text.id = {}".format(item[0])) yield cursor.fetchall() connect.close()
def get_url(text_id): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT url.url " "FROM texts_text AS text " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE text.id = {}".format(text_id)) url = cursor.fetchall() connect.close() return url
def spam_status_to_non_active(id_text): """ Убираем пометку о том, что текст является спамом """ connect = get_connect() cursor = connect.cursor() cursor.execute("UPDATE texts_text " "SET status = 0 " "WHERE texts_text.id = {};".format(id_text)) connect.commit() connect.close()
def spam_status_to_active(id_text): """ Пометка о том, что текст является спамом """ connect = get_connect() cursor = connect.cursor() cursor.execute("UPDATE texts_text " "SET status = 2 " "WHERE texts_text.id = {};".format(id_text)) print("Yes") connect.commit() connect.close()
def spam_status_to_active(id_text): """ Пометка о том, что текст является спамом """ connect = get_connect() cursor = connect.cursor() cursor.execute( "UPDATE texts_text " "SET status = 2 " "WHERE texts_text.id = {};".format(id_text) ) print("Yes") connect.commit() connect.close()
def spam_status_to_non_active(id_text): """ Убираем пометку о том, что текст является спамом """ connect = get_connect() cursor = connect.cursor() cursor.execute( "UPDATE texts_text " "SET status = 0 " "WHERE texts_text.id = {};".format(id_text) ) connect.commit() connect.close()
def get_text(text_id): connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT text.plain_text " "FROM texts_text AS text " "WHERE text.id = {}".format(text_id) ) text = cursor.fetchall() connect.close() return text
def cluster_of_text(text_id): """ Получаем кластер по идентификатору текста """ connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT cluster.id, cluster.summary " "FROM clusters_cluster AS cluster " "INNER JOIN texts_text AS text " "ON text.cluster_id = cluster.id " "WHERE text.id = {}".format(text_id)) cluster = cursor.fetchall() connect.close() return cluster
def get_image_url_by_id(url_id): """ Подсчет количества текстов в каждом кластере Возвращает описание кластера и количество текство в нем """ connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT url.image " "FROM texts_url AS url " "WHERE url.id = {} ".format(url_id)) clusters = cursor.fetchall() connect.close() return clusters
def get_texts(list_id): """ Итератор текстов из поступающего на вход списка идетификаторов """ connect = get_connect() cursor = connect.cursor() for item in list_id: cursor.execute( "SELECT text.plain_text, text.id " "FROM texts_text AS text " "WHERE text.id = {}".format(item[0]) ) yield cursor.fetchall() connect.close()
def get_url(text_id): connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT url.url " "FROM texts_text AS text " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE text.id = {}".format(text_id) ) url = cursor.fetchall() connect.close() return url
def texts_of_cluster(cluster_id, url_id): connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT text.id " "FROM clusters_cluster AS cluster " "INNER JOIN texts_text AS text " "ON cluster.id = text.cluster_id " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE cluster.id = {} AND url.id = {} " "ORDER BY text.id".format(cluster_id, url_id)) texts = cursor.fetchall() connect.close() return texts
def cluster_of_text(text_id): """ Получаем кластер по идентификатору текста """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT cluster.id, cluster.summary " "FROM clusters_cluster AS cluster " "INNER JOIN texts_text AS text " "ON text.cluster_id = cluster.id " "WHERE text.id = {}".format(text_id) ) cluster = cursor.fetchall() connect.close() return cluster
def texts_of_cluster(cluster_id): connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT text.id " "FROM clusters_cluster AS cluster " "INNER JOIN texts_text AS text " "ON cluster.id = text.cluster_id " "WHERE cluster.id = '{}' " "ORDER BY text.id".format(cluster_id) ) texts = cursor.fetchall() connect.close() return texts
def tags_of_text(text_id): """ Получаем теги по идентификатору текста """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT tag.name " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "INNER JOIN texts_text AS text " "ON text.id = tagtext.text_id " "WHERE text.id = {}".format(text_id) ) tags = cursor.fetchall() connect.close() return tags
def tags_of_text(text_id): """ Получаем теги по идентификатору текста """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT tag.name " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "INNER JOIN texts_text AS text " "ON text.id = tagtext.text_id " "WHERE text.id = {}".format(text_id) ) tags = cursor.fetchall() connect.close() return tags
def count_clusters(): """ Подсчет количества текстов в каждом кластере Возвращает описание кластера и количество текство в нем """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT cluster.id, cluster.summary, count(*) " "FROM clusters_cluster AS cluster " "LEFT JOIN texts_text AS text " "ON cluster.id = text.cluster_id " "GROUP BY cluster.summary, cluster.id;" ) clusters = cursor.fetchall() connect.close() return clusters
def count_tags(): """ Получаем список из тегов и количество их уникальных вхождений """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT tag.name, count(*) " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "GROUP BY tag.name;" ) tag_count = cursor.fetchall() connect.close() return tag_count
def count_clusters(url_id): """ Подсчет количества текстов в каждом кластере Возвращает описание кластера и количество текство в нем """ connect = get_connect() cursor = connect.cursor() cursor.execute( "SELECT cluster.id, cluster.summary, cluster.image, count(*) " "FROM clusters_cluster AS cluster " "INNER JOIN texts_text AS text " "ON cluster.id = text.cluster_id " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE url.id = {} " "GROUP BY cluster.summary, cluster.id;".format(url_id)) clusters = cursor.fetchall() connect.close() return clusters
def texts_of_tag(tag, url_id): """ Получаем список текстов, в которых встречался tag """ connect = get_connect() cursor = connect.cursor() cursor.execute("SELECT text.id " "FROM tags_tag AS tag " "INNER JOIN texts_tagtext AS tagtext " "ON tag.id = tagtext.tag_id " "INNER JOIN texts_text AS text " "ON text.id = tagtext.text_id " "INNER JOIN texts_url AS url " "ON text.url_id = url.id " "WHERE tag.name = '{}' AND url.id = {} " "ORDER BY text.id;".format(tag, url_id)) texts = cursor.fetchall() connect.close() return texts