Пример #1
0
def get_theme_anno_basic_info(theme_name):
    """
    get some theme's anno basic information from db with it's name
    @Param str: theme's name
    @Return dict: key: version_id, value: list [...]
    """
    get_theme_anno_basic_info_sql = "select ThemeVersionID as theme_version_id, count(*) as theme_anno_count, count(distinct NewsID) as theme_news_anno_count, sum(if(AnnoCorr='Strong', 1, 0)) as corr_strong, sum(if(AnnoCorr='Weak', 1, 0)) as corr_weak, sum(if(AnnoCorr='Not', 1, 0)) as corr_not from theme_news_anno where ThemeName='%s' group by ThemeVersionID"
    
    cursor, conn = db_api.conn()
    db_api.query(cursor, get_theme_anno_basic_info_sql)
    theme_anno_count_dict = {}
    row = db_api.fetch_one_row(cursor)
    while row != None:
        theme_version_id = row['theme_version_id']
        theme_anno_count = row['theme_anno_count']
        theme_news_anno_count = row['theme_news_anno_count']
        theme_news_anno_strong_count = row['corr_strong']
        theme_news_anno_weak_count = row['corr_weak']
        theme_news_anno_not_count = row['corr_not']

        theme_anno_count_dict[theme_version_id] = [theme_anno_count, theme_news_anno_count, theme_news_anno_strong_count, theme_news_anno_weak_count, theme_news_anno_not_count]
        row = db_api.fetch_one_row(cursor)

    db_api.close(cursor)

    return theme_anno_count_dict
Пример #2
0
def get_theme_user_article_info(theme_name, user_name, version_id):
    """
    @Param str: theme name
    @Param str: user name
    @Param str: version id
    """
    print("get_theme_user_article_info")
    get_theme_user_article_anno_count_info_sql = "select count(distinct NewsID) as theme_user_article_anno_count from theme_news_anno where ThemeName = '%s' and ThemeVersionID = '%s' and UserName = '******'"
    get_theme_user_article_anno_last_page_id_sql = "select NewsPageID as lastest_anno_page_id from theme_news_anno where ThemeName = '%s' and ThemeVersionID = '%s' and UserName = '******' order by InsertTime desc limit 1"

    cursor, conn = db_api.conn()
    db_api.query(cursor, get_theme_user_article_anno_count_info_sql % (theme_name, version_id, user_name))
    row = db_api.fetch_one_row(cursor)
    theme_user_anno_article_count = 0
    if row != None:
        theme_user_anno_article_count = row['theme_user_article_anno_count']

    db_api.query(cursor, get_theme_user_article_anno_last_page_id_sql % (theme_name, version_id, user_name))
    row = db_api.fetch_one_row(cursor)
    lastest_anno_page_id = 1
    if row != None:
        lastest_anno_page_id = row['lastest_anno_page_id']
    
    
    db_api.close(cursor)
    return theme_user_anno_article_count, lastest_anno_page_id
Пример #3
0
def get_user_anno_basic_info(user_name):
    """
    get some user's annotation information, like how much article have he or she remarked;
    @Param str: user's name
    @Return int: count of annotations of the user;
    """
    get_user_anno_basic_info_sql = "select count(*) as user_anno_count from theme_news_anno where UserName='******'"
    cursor, conn = db_api.conn()
    db_api.query(cursor, get_user_anno_basic_info_sql % user_name)
    row = db_api.fetch_one_row(cursor)
    user_anno_count = row['user_anno_count']

    db_api.close(cursor)
    return user_anno_count
Пример #4
0
def get_if_news_remarked(theme_name, user_name, news_id):
    """
    get if a news have been annotated by the user;
    @Param str: theme name
    @Param str: user name
    @Param str: NewsID
    """
    print("get_if_news_remarked")
    get_if_news_remarked_sql = "select * from theme_news_anno where ThemeName = '%s' and UserName = '******' and NewsID = '%s'"
    cursor, conn = db_api.conn()
    db_api.query(cursor, get_if_news_remarked_sql % (theme_name, user_name, news_id))
    row = db_api.fetch_one_row(cursor)

    db_api.close(cursor)
    if row != None:
        return True
    else:
        return False
Пример #5
0
def get_all_themes_anno_basic_info():
    get_theme_anno_basic_info_group_by_theme_sql = "select ThemeName as theme_name, ThemeVersionID as theme_version_id, count(*) as theme_anno_count, count(distinct NewsID) as theme_news_anno_count, sum(if(AnnoCorr='Strong', 1, 0)) as corr_strong, sum(if(AnnoCorr='Weak', 1, 0)) as corr_weak, sum(if(AnnoCorr='Not', 1, 0)) as corr_not from theme_news_anno group by ThemeName, ThemeVersionID"
    cursor, conn = db_api.conn()
    db_api.query(cursor, get_theme_anno_basic_info_group_by_theme_sql)
    theme_anno_count_dict = {}
    row = db_api.fetch_one_row(cursor)
    while row != None:
        theme_name = row['theme_name']
        theme_version_id = row['theme_version_id']
        theme_anno_count = row['theme_anno_count']
        theme_news_anno_count = row['theme_news_anno_count']
        theme_news_anno_strong_count = row['corr_strong']
        theme_news_anno_weak_count = row['corr_weak']
        theme_news_anno_not_count = row['corr_not']

        theme_anno_count_dict[(theme_name, theme_version_id)] = [theme_anno_count, theme_news_anno_count, theme_news_anno_strong_count, theme_news_anno_weak_count, theme_news_anno_not_count]
        row = db_api.fetch_one_row(cursor)

    db_api.close(cursor)
        
    return theme_anno_count_dict
Пример #6
0
def insert_article_anno_into_db(theme_name, version_id, news_id, news_page_id, user_name, anno_correlation, anno_add_words, anno_del_words, anno_comment):
    """
    insert article annotation data into database
    @Param str theme_name
    @Param str version_id
    @Param str news_id (eg.IP_3125434)
    @Param int news_page_id
    @Param str anno_corr('Strong', 'Weak', 'Not')
    @Param str anno_added_key_words
    @Param str anno_delete_key_words
    @Param str comment
    @Return str status code
    """
    insert_article_anno_sql = "insert into theme_news_anno (ThemeName, ThemeVersionID, NewsID, NewsPageID, UserName, AnnoCorr, AnnoAdd, AnnoDel, AnnoComment) values ('%s', '%s', '%s', %d, '%s', '%s', '%s', '%s', '%s')"
    try:
        cursor, conn = db_api.conn()
        db_api.insert(cursor, conn, insert_article_anno_sql % (theme_name, version_id, news_id, news_page_id, user_name, anno_correlation, anno_add_words, anno_del_words, anno_comment))
        db_api.close(cursor)
        return "Success"
    except Exception as e:
        print(e)
        return "False"
Пример #7
0
def get_remarked_news_id_list(theme_name, version_id, user_name):
    """
    @Param str: theme's name;
    @Param str: version id
    @Param str: user name
    @Return list[str]
    """
    print("get_remarked_news_id_list")
    get_article_remarked_sql = "select distinct NewsID as news_id from theme_news_anno where ThemeName = '%s' and ThemeVersionID = '%s' and UserName = '******'"
    cursor, conn = db_api.conn()
    db_api.query(cursor, get_article_remarked_sql % (theme_name, version_id, user_name))
    row = db_api.fetch_one_row(cursor)
    remarked_article_list = []
    while row != None:
        news_id = row['news_id']
        remarked_article_list.append(news_id)

        row = db_api.fetch_one_row(cursor)

    db_api.close(cursor)
    print("after get_remarked_news_id_list")
    return remarked_article_list