def rssd_dict(id): result = {} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,title,author,link,content from rss_detail where id=:id" cursor.execute(sql, {"id": id}) adata = cursor.fetchone() if adata: result["id"] = adata[0] result["title"] = adata[1] if (adata[2] != None): result["author"] = adata[2] else: result["author"] = "" result["link"] = adata[3] if (adata[4] != None): result["content"] = del_script(content(adata[4])) else: result["content"] = "" cursor.close() dbconn.close() return result
def rssd_taglist(): """ TAG List """ result = {"result": "nook", "taglist": None} dbname = common.getdbname() try: dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() taglists = [] sql = "select id,tname from tags order by tname" cursor.execute(sql) adata = cursor.fetchone() while adata: taglists.append({"id": adata[0], "name": adata[1]}) adata = cursor.fetchone() cursor.close() dbconn.close() result["result"] = "ok" result["taglist"] = taglists except: pass return result
def rssd_taglist() : """ TAG List """ result = {"result": "nook", "taglist": None} dbname = common.getdbname() try : dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() taglists = [] sql = "select id,tname from tags order by tname" cursor.execute(sql) adata = cursor.fetchone() while adata : taglists.append({"id":adata[0], "name":adata[1]}) adata = cursor.fetchone() cursor.close() dbconn.close() result["result"] = "ok" result["taglist"] = taglists except : pass return result
def rssd_cancel(hashid): # 刪除 rss_main 及 rss_detail 中關於 hashid 的資料 result = {"result": "ok"} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql0 = "select id from rss_main where hashid=:hashid" cursor.execute(sql0, {"hashid": hashid}) adata = cursor.fetchone() if adata: mainid = adata[0] else: mainid = 0 if (mainid != 0): sql1 = "delete from rss_main where id=:mainid" cursor.execute(sql1, {"mainid": mainid}) sql2 = "delete from rss_detail where mainid=:mainid" cursor.execute(sql2, {"mainid": mainid}) sql3 = "delete from tag_detail where rid=:mainid" cursor.execute(sql3, {"mainid": mainid}) dbconn.commit() cursor.close() dbconn.close() return result
def rssd_dict(id) : result = {} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,title,author,link,content from rss_detail where id=:id" cursor.execute(sql, {"id": id}) adata = cursor.fetchone() if adata : result["id"] = adata[0] result["title"] = adata[1] if (adata[2] != None) : result["author"] = adata[2] else : result["author"] = "" result["link"] = adata[3] if (adata[4] != None) : result["content"] = del_script(content(adata[4])) else : result["content"] = "" cursor.close() dbconn.close() return result
def rssd_cancel(hashid) : # 刪除 rss_main 及 rss_detail 中關於 hashid 的資料 result = {"result": "ok"} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql0 = "select id from rss_main where hashid=:hashid" cursor.execute(sql0, {"hashid": hashid}) adata = cursor.fetchone() if adata : mainid = adata[0] else : mainid = 0 if (mainid != 0) : sql1 = "delete from rss_main where id=:mainid" cursor.execute(sql1, {"mainid": mainid}) sql2 = "delete from rss_detail where mainid=:mainid" cursor.execute(sql2, {"mainid": mainid}) sql3 = "delete from tag_detail where rid=:mainid" cursor.execute(sql3, {"mainid": mainid}) dbconn.commit() cursor.close() dbconn.close() return result
def rssd_tagchoice(hashid,tagid,sel) : """ 將收集的 rss網站 加上 TAG 當 tagid 或 sel 其中一個為 -1 時, 只查詢目前 TAG 情形 """ result = {"result":"nook", "tagselected":None} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid":hashid}) adata = cursor.fetchone() if adata : mainid = adata[0] else : mainid = "" if (mainid) : if ((tagid != -1) and (sel != -1)) : sql = "select count(rid) from tag_detail where rid=:rid and tid=:tid" cursor.execute(sql, {"rid":mainid, "tid":tagid}) adata = cursor.fetchone() acnt = adata[0] if (sel == 1) : # 增加 TAG if (acnt == 0) : sql = "insert into tag_detail(rid,tid) values(:rid, :tid)" cursor.execute(sql, {"rid":mainid, "tid":tagid}) dbconn.commit() else : # 刪除 TAG if (acnt > 0) : sql = "delete from tag_detail where rid=:rid and tid=:tid" cursor.execute(sql, {"rid":mainid, "tid":tagid}) dbconn.commit() tagselected = [] sql = "select tid from tag_detail where rid=:rid" cursor.execute(sql, {"rid":mainid}) adata = cursor.fetchone() while adata : tagselected.append(adata[0]) adata = cursor.fetchone() result["result"] = "ok" result["tagselected"] = tagselected cursor.close() dbconn.close() return result
def patch001() : """ rss_detail 增加欄位 star ; 2013-05-22 """ if (not common.checkfield("star", "rss_detail")) : dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "alter table rss_detail add column star boolean default 0" cursor.execute(sql) cursor.close() dbconn.close() print "patch 001 OK"
def patch001(): """ rss_detail 增加欄位 star ; 2013-05-22 """ if (not common.checkfield("star", "rss_detail")): dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "alter table rss_detail add column star boolean default 0" cursor.execute(sql) cursor.close() dbconn.close() print "patch 001 OK"
def tag_markasread(tagid, lastdate): result = {"result": "nook"} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() try: # 將某 tag 的所有 子item 改為已閱讀 sql = "update rss_detail set readed=1 where mainid in (select rid from tag_detail where tid=:tagid) and pubdate<=:lastdate and readed<>1" cursor.execute(sql, {"tagid": tagid, "lastdate": lastdate}) dbconn.commit() sql = "select rid from tag_detail where tid=:tagid" cursor.execute(sql, {"tagid": tagid}) adata = cursor.fetchone() while adata: aid = adata[0] cursor2 = dbconn.cursor() # 重新計算 未讀 count cursor2.execute( "select count(*) from rss_detail where mainid=:mainid and readed<>1", {"mainid": aid}) bdata = cursor2.fetchone() if bdata: unreadcnt = bdata[0] else: unreadcnt = 0 # 更新 未讀 count sql2 = "update rss_main set unreadcnt=:unreadcnt where id=:aid" cursor2.execute(sql2, {"aid": aid, "unreadcnt": unreadcnt}) dbconn.commit() cursor2.close() adata = cursor.fetchone() result["result"] = "ok" except: pass cursor.close() dbconn.close() return result
def markasread(hashid, lastdate): result = {"result": "nook"} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,hashid from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid": hashid}) adata = cursor.fetchone() if adata: aid = adata[0] # 將某 hashid 的所有 子item 改為已閱讀 cursor2 = dbconn.cursor() sql2 = "update rss_detail set readed=1 where mainid=:mainid and pubdate<=:lastdate and readed<>1" cursor2.execute(sql2, {"mainid": aid, "lastdate": lastdate}) dbconn.commit() # 重新計算 未讀 count cursor2.execute( "select count(*) from rss_detail where mainid=:mainid and readed<>1", {"mainid": aid}) bdata = cursor2.fetchone() if bdata: unreadcnt = bdata[0] else: unreadcnt = 0 # 更新 未讀 count sql3 = "update rss_main set unreadcnt=:unreadcnt where hashid=:hashid" cursor2.execute(sql3, {"hashid": hashid, "unreadcnt": unreadcnt}) dbconn.commit() cursor2.close() result["result"] = "ok" result["id"] = aid cursor.close() dbconn.close() return result
def rssdetail_readed(aid) : result = {"result":"nook" ,"hashid":None, "unreadcnt":0} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "update rss_detail set readed=1 where id=:aid" cursor.execute(sql, {"aid": aid}) dbconn.commit() # 找出 mainid sql = "select mainid from rss_detail where id=:aid" cursor.execute(sql, {"aid": aid}) adata = cursor.fetchone() if adata: mainid = adata[0] else : mainid = -1 # 根據 mainid, 將 rss_main.unreadcnt - 1 if (mainid != -1) : sql = "select count(*) from rss_detail where mainid=:mainid and readed<>1" cursor.execute(sql, {"mainid":mainid}) adata = cursor.fetchone() unread = adata[0] sql = "update rss_main set unreadcnt=:unread where id=:id" cursor.execute(sql, {"id":mainid, "unread":unread}) dbconn.commit() sql = "select id,hashid,unreadcnt from rss_main where id=:id" cursor.execute(sql, {"id":mainid}) adata = cursor.fetchone() if adata : result["result"] = "ok" result["hashid"] = adata[1] result["unreadcnt"] = adata[2] cursor.close() dbconn.close() return result
def markasread(hashid, lastdate) : result = {"result": "nook"} dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,hashid from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid":hashid}) adata = cursor.fetchone() if adata : aid = adata[0] # 將某 hashid 的所有 子item 改為已閱讀 cursor2 = dbconn.cursor() sql2 = "update rss_detail set readed=1 where mainid=:mainid and pubdate<=:lastdate and readed<>1" cursor2.execute(sql2, {"mainid":aid, "lastdate":lastdate}) dbconn.commit() # 重新計算 未讀 count cursor2.execute("select count(*) from rss_detail where mainid=:mainid and readed<>1", {"mainid":aid}) bdata = cursor2.fetchone() if bdata : unreadcnt = bdata[0] else : unreadcnt = 0 # 更新 未讀 count sql3 = "update rss_main set unreadcnt=:unreadcnt where hashid=:hashid" cursor2.execute(sql3, {"hashid":hashid, "unreadcnt":unreadcnt}) dbconn.commit() cursor2.close() result["result"] = "ok" result["id"] = aid cursor.close() dbconn.close() return result
def patch002() : """ 增加 table tags """ dbname = common.getdbname() if (not common.checktable("tags")) : dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = """ create table if not exists tags ( id integer primary key autoincrement, tname varcar ); """ cursor.execute(sql) cursor.close() dbconn.close() print "patch 002 OK"
def patch002(): """ 增加 table tags """ dbname = common.getdbname() if (not common.checktable("tags")): dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = """ create table if not exists tags ( id integer primary key autoincrement, tname varcar ); """ cursor.execute(sql) cursor.close() dbconn.close() print "patch 002 OK"
def tag_rename(tagid, newname) : result = {"result": "nook"} dbname = common.getdbname() try : dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "update tags set tname=:newname where id=:tagid" cursor.execute(sql, {"tagid":tagid, "newname":urllib.unquote(newname.decode("utf-8"))}) dbconn.commit() cursor.close() dbconn.close() result["result"] = "ok" except : pass return result
def rssd_star(aid, star=0): """ 將 rss_detail 的 id 這筆記錄 加上星號 或 不加星號 """ result = {} try: dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "update rss_detail set star=:star where id=:id" cursor.execute(sql, {"star": star, "id": aid}) dbconn.commit() cursor.close() dbconn.close() result["result"] = "ok" except e: result["result"] = e return result
def rssd_star(aid, star=0) : """ 將 rss_detail 的 id 這筆記錄 加上星號 或 不加星號 """ result = {} try : dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "update rss_detail set star=:star where id=:id" cursor.execute(sql, {"star":star, "id":aid}) dbconn.commit() cursor.close() dbconn.close() result["result"] = "ok" except e : result["result"] = e return result
def rsslist() : rsslist_array = [] dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,title,hashid,unreadcnt from rss_main order by id" cursor.execute(sql) adata = cursor.fetchone() while adata : aid = adata[0] atitle = adata[1] alink = adata[2] unread = adata[3] rsslist_array.append({"title":atitle, "link":alink, "unread":unread}) adata = cursor.fetchone() cursor.close() dbconn.close() return rsslist_array
def tag_rename(tagid, newname): result = {"result": "nook"} dbname = common.getdbname() try: dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "update tags set tname=:newname where id=:tagid" cursor.execute(sql, { "tagid": tagid, "newname": urllib.unquote(newname.decode("utf-8")) }) dbconn.commit() cursor.close() dbconn.close() result["result"] = "ok" except: pass return result
def rssd_newtag(tagname) : """ 新增 TAG """ result = {"result": "nook", "taglist": None} tagname = urllib.unquote(tagname.decode("utf-8")) dbname = common.getdbname() try : dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select count(*) from tags where tname=:tname" cursor.execute(sql, {"tname": tagname}) adata = cursor.fetchone() if (adata[0] == 0) : sql = "insert into tags(tname) values(:tagname)" cursor.execute(sql, {"tagname": tagname}) dbconn.commit() taglists = [] sql = "select id,tname from tags order by tname" cursor.execute(sql) adata = cursor.fetchone() while adata : taglists.append({"id":adata[0], "name":adata[1]}) adata = cursor.fetchone() cursor.close() dbconn.close() result["result"] = "ok" result["taglist"] = taglists except : pass return result
def rsstags() : rsstags_array = [] dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,tname from tags order by tname" cursor.execute(sql) adata = cursor.fetchone() while adata : aid = "tag_%03d" % adata[0] atname = adata[1] rsslist_array = [] cursor2 = dbconn.cursor() sql2 = "select id,title,hashid,unreadcnt from rss_main where id in (select rid from tag_detail where tid=:tid) order by id" cursor2.execute(sql2, {"tid": adata[0]}) bdata = cursor2.fetchone() while bdata : bid = bdata[0] btitle = bdata[1] blink = bdata[2] unread = bdata[3] rsslist_array.append({"title":btitle, "link":blink, "unread":unread}) bdata = cursor2.fetchone() cursor2.close() rsstags_array.append({"title":atname, "link":aid, "unread":0, "sub":rsslist_array}) adata = cursor.fetchone() cursor.close() dbconn.close() return rsstags_array
def rssd_dict(atype, hashid="", keyword="", showmode=1, lastid="", tagid=-1, cnt=30) : result = {"title":None, "link":None, "hashid":"", "unreadcnt":0, "detail":[]} if (atype == 0) : # 一般情形 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,title,link,hashid,unreadcnt from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid": hashid}) adata = cursor.fetchone() if adata : mainid = adata[0] # print mainid result["title"] = adata[1] result["link"] = adata[2] result["hashid"] = adata[3] result["unreadcnt"] = adata[4] # 取得 lastid 的 pubdate lastpubdate = "" if (lastid) : cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,rssid,title,pubdate,readed,star from rss_detail where mainid=:mainid and title<>''" if (lastpubdate) : sql2 += " and pubdate<:lastpubdate" if (showmode == 2) : sql2 += " and readed<>1" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, {"mainid":mainid, "lastpubdate":lastpubdate, "cnt":cnt}) bdata = cursor2.fetchone() while bdata : rsshash = {} rsshash["id"] = bdata[0] rsshash["rssid"] = bdata[1] rsshash["title"] = bdata[2] rsshash["pubdate"] = bdata[3] rsshash["readed"] = bdata[4] rsshash["star"] = bdata[5] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 1) : # 檢視 星號List result["title"] = "星號列表" result["hashid"] = "" result["unreadcnt"] = 0 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata : main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid) : cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where star=1 and title<>''" if (lastpubdate) : sql2 += " and pubdate<:lastpubdate" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, {"lastpubdate":lastpubdate, "cnt":cnt}) bdata = cursor2.fetchone() while bdata : rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 2) : # 搜尋 result["title"] = "搜尋" result["hashid"] = "" result["unreadcnt"] = 0 keyword = urllib.unquote(keyword.decode("utf-8")) dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata : main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid) : cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where 1=1 and title<>''" sql2 += " and (title like :keyword or content like :keyword or author like :keyword)" if (lastpubdate) : sql2 += " and pubdate<:lastpubdate" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" # print sql2 cursor2.execute(sql2, {"lastpubdate":lastpubdate, "keyword":'%'+keyword+'%', "cnt":cnt}) bdata = cursor2.fetchone() while bdata : rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 3) : # TAG result["title"] = "TAG" result["hashid"] = "" result["unreadcnt"] = 0 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # TAG 名稱 sql = "select tname from tags where id=:tagid" cursor.execute(sql, {"tagid": tagid}) adata = cursor.fetchone() if adata : result["title"] = adata[0] # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata : main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid) : cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where mainid in (select rid from tag_detail where tid=:tid) and title<>''" if (lastpubdate) : sql2 += " and pubdate<:lastpubdate" if (showmode == 2) : sql2 += " and readed<>1" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, {"tid":tagid, "lastpubdate":lastpubdate, "cnt":cnt}) bdata = cursor2.fetchone() while bdata : rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() return result
cursor.close() def updatesingle(self, hashid) : """ 更新 db.rss_main 中某個 xmlurl 的 RSS 資料 """ cursor = self.dbconn.cursor() sql = "select id,title,xmlurl from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid": hashid}) alldata = cursor.fetchall() for adata in alldata: aid = adata[0] atitle = adata[1].encode("utf-8") axmlurl = adata[2] if (self.showmsg) : print "===== %03d [%s] =====" % (aid, atitle) self.rss2db(axmlurl, aid) cursor.close() if __name__ == "__main__" : dbname = common.getdbname() if (dbname and os.path.exists(dbname)) : rss2db = Rss2DB(dbname) if (len(sys.argv) > 1) : rss2db.updatesingle(sys.argv[1]) else : rss2db.updateall() else : print "Can't get db !!"
def rssd_dict(atype, hashid="", keyword="", showmode=1, lastid="", tagid=-1, cnt=30): result = { "title": None, "link": None, "hashid": "", "unreadcnt": 0, "detail": [] } if (atype == 0): # 一般情形 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() sql = "select id,title,link,hashid,unreadcnt from rss_main where hashid=:hashid" cursor.execute(sql, {"hashid": hashid}) adata = cursor.fetchone() if adata: mainid = adata[0] # print mainid result["title"] = adata[1] result["link"] = adata[2] result["hashid"] = adata[3] result["unreadcnt"] = adata[4] # 取得 lastid 的 pubdate lastpubdate = "" if (lastid): cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,rssid,title,pubdate,readed,star from rss_detail where mainid=:mainid and title<>''" if (lastpubdate): sql2 += " and pubdate<:lastpubdate" if (showmode == 2): sql2 += " and readed<>1" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, { "mainid": mainid, "lastpubdate": lastpubdate, "cnt": cnt }) bdata = cursor2.fetchone() while bdata: rsshash = {} rsshash["id"] = bdata[0] rsshash["rssid"] = bdata[1] rsshash["title"] = bdata[2] rsshash["pubdate"] = bdata[3] rsshash["readed"] = bdata[4] rsshash["star"] = bdata[5] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 1): # 檢視 星號List result["title"] = "星號列表" result["hashid"] = "" result["unreadcnt"] = 0 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata: main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid): cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where star=1 and title<>''" if (lastpubdate): sql2 += " and pubdate<:lastpubdate" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, {"lastpubdate": lastpubdate, "cnt": cnt}) bdata = cursor2.fetchone() while bdata: rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 2): # 搜尋 result["title"] = "搜尋" result["hashid"] = "" result["unreadcnt"] = 0 keyword = urllib.unquote(keyword.decode("utf-8")) dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata: main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid): cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where 1=1 and title<>''" sql2 += " and (title like :keyword or content like :keyword or author like :keyword)" if (lastpubdate): sql2 += " and pubdate<:lastpubdate" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" # print sql2 cursor2.execute(sql2, { "lastpubdate": lastpubdate, "keyword": '%' + keyword + '%', "cnt": cnt }) bdata = cursor2.fetchone() while bdata: rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() elif (atype == 3): # TAG result["title"] = "TAG" result["hashid"] = "" result["unreadcnt"] = 0 dbname = common.getdbname() dbconn = sqlite3.connect(dbname) cursor = dbconn.cursor() # TAG 名稱 sql = "select tname from tags where id=:tagid" cursor.execute(sql, {"tagid": tagid}) adata = cursor.fetchone() if adata: result["title"] = adata[0] # 建立 rss_main.id 對 title 的 dict main_titles = {} sql = "select id,title from rss_main" cursor.execute(sql) adata = cursor.fetchone() while adata: main_titles[adata[0]] = adata[1] adata = cursor.fetchone() # 取得 lastid 的 pubdate lastpubdate = "" if (lastid): cursor1 = dbconn.cursor() sql1 = "select pubdate from rss_detail where id=:id" cursor1.execute(sql1, {"id": lastid}) lastpubdate = cursor1.fetchone()[0] cursor1.close() rssd_list = [] cursor2 = dbconn.cursor() sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where mainid in (select rid from tag_detail where tid=:tid) and title<>''" if (lastpubdate): sql2 += " and pubdate<:lastpubdate" if (showmode == 2): sql2 += " and readed<>1" sql2 += " order by pubdate desc" sql2 += " limit 0,:cnt" cursor2.execute(sql2, { "tid": tagid, "lastpubdate": lastpubdate, "cnt": cnt }) bdata = cursor2.fetchone() while bdata: rsshash = {} rsshash["id"] = bdata[0] rsshash["main_title"] = main_titles[bdata[1]] rsshash["rssid"] = bdata[2] rsshash["title"] = bdata[3] rsshash["pubdate"] = bdata[4] rsshash["readed"] = bdata[5] rsshash["star"] = bdata[6] rssd_list.append(rsshash) bdata = cursor2.fetchone() cursor2.close() result["detail"] = rssd_list cursor.close() dbconn.close() return result
"utf-8") return rssdict if __name__ == "__main__": result = {"result": None, "result2": None, "message": None, "hashid": None} params = cgiFieldStorageToDict(cgi.FieldStorage()) url = params["weburl"] if (url != ""): rssdict = webrss(url) if (rssdict and rssdict["title"]): dbname = common.getdbname() if (dbname and os.path.exists(dbname)): conn = sqlite3.connect(dbname) cursor0 = conn.cursor() sql0 = "select xmlurl from rss_main where xmlurl=:xmlurl" cursor0.execute(sql0, {'xmlurl': rssdict["xmlurl"]}) adata = cursor0.fetchone() if adata: result["result2"] = "update" result["message"] = "[update]:" + "(" + rssdict[ "htmlurl"] + ")" + rssdict["title"].encode("utf-8") result["hashid"] = rssdict["hashid"] cursor = conn.cursor() sql = "update rss_main set title=:title,link=:link,description=:description where xmlurl=:xmlurl"