rss统计 基于ttrss的PostgresQL数据库

2022/05/08

前言 统计ttrss的使用数据。

TODO：

按月保存mark的内容

账号 密码 和你docker文件里面的一致。

# 按月来保存
import html2text
# psycopg2 head
import psycopg2
# pip3 install psycopg2-binary
# ref:https://blog.csdn.net/Haiqiang1995/article/details/89069791

def get_from_dataset(cmd_sql):
    conn = None
    try:
        # read database configuration
        params = {
        'host': "?????",
        'user': "????",
        'password': "????",
        'dbname': "ttrss",
        'port': ??00
        }
        # connect to the PostgresQL database
        conn = psycopg2.connect(**params)
        # create a new cursor object
        cur = conn.cursor()
        # execute the SELECT statement


        print(cmd_sql)
        cur.execute(cmd_sql)

        blob = cur.fetchall()
        # blob = cur.fetchone()
        #open(path_to_dir + str(blob[0]) + '.jpg', 'wb').write(blob[1])
        # close the communication with the PostgresQL database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
    print("get_from_dataset success")
    return blob

_s="ref_id,feed_id,last_read,last_marked"
cmd_sql="SELECT %s  FROM public.ttrss_user_entries Where marked=TRUE AND last_marked BETWEEN '2022-04-01' And '2022-5-01'" % _s
# 可以自己改日期

g_content=""
for _e in sorted(get_from_dataset(cmd_sql),key=lambda x:x[3]):
    [ref_id,feed_id,last_read,last_marked] = _e
    _s="id,title,link,updated,content"
    cmd_sql="SELECT %s FROM public.ttrss_entries WHERE id = %s" % (_s,ref_id)
    [id,title,link,updated,content] =get_from_dataset(cmd_sql)[0]
    
    g_content += "# [%s](%s)\n"%(title,link)
    g_content +="pubdata:%s\n"%str(updated)
    g_content +="markdate:%s\n"%str(last_marked)
    g_content+=html2text.html2text(content)
    
    g_content+="\n\n"
    
    
D="C:\\Users\\talen\\Desktop\\"
with open(D+"cc.md",'w',encoding='utf-8') as f:
        f.write(g_content)