def is_user_exists(screen_name): sql = "SELECT * FROM sw_users WHERE screen_name='%s'" % screen_name db = SQLite(config.DB_FILE) rows = db.fetch_sql(sql) if rows: return True else: return False
def is_status_exists(status): sql = "SELECT * FROM sw_messages WHERE message='%s';" % status db = SQLite(sql) rows = db.fetch_sql(sql) if rows: return True else: return False
def is_message_exists(message): sql = "SELECT * FROM sw_messages WHERE src='%s' AND dst='%s' AND message='%s' AND time='%s'" % \ (message["src"], message["dst"], message["message"], message["time"]) db = SQLite(config.DB_FILE) rows = db.fetch_sql(sql) if rows: return True else: return False
def add_user(user): if not is_user_exists(user["name"]): sql = "INSERT INTO sw_users(user_id, screen_name, sex, school) VALUES('%s', '%s', '%s', '%s')" \ % (user["id"],user["name"],user["sex"],user["school"]) db = SQLite(config.DB_FILE) db.do_sql(sql) log("add user %s!" % user["name"]) return True else: return False
def get_status(last_post_time): sql = "SELECT sex,school,message,pub_time FROM sw_messages,sw_users WHERE src!='%s' AND src=screen_name AND pub_time>'%s' ORDER BY pub_time" % ('我'.decode('utf-8'),last_post_time) db = SQLite(config.DB_FILE) rows = db.fetch_sql(sql) # write the time back #now = datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S") #sql = "UPDATE sw_app SET cfg_value='%s' WHERE cfg_name='post_time'" % now if len(rows): sql = "UPDATE sw_app SET cfg_value='%s' WHERE cfg_name='post_time'" % rows[-1][3] db.do_sql(sql) return rows
def save_2_sqlite(messages): db = SQLite(config.DB_FILE) count = 0 for message in messages: if is_message_exists(message): count += 1 continue sql = "INSERT INTO sw_messages(src,dst,message,time) VALUES('%s', '%s', '%s', '%s')" % \ (message["src"], message["dst"], message["message"], message["time"]) db.do_sql(sql) log("%d messages existed!" % count)
def refresh_at_time(): sql = "UPDATE sw_app SET cfg_value=(SELECT time FROM sw_log ORDER BY time DESC LIMIT 1) WHERE cfg_name='at_time';" db = SQLite(config.DB_FILE) db.do_sql(sql)
def add_command_log(screen_name, command, text, time): sql = "INSERT INTO sw_log(screen_name,command,text, time) VALUES('%s', '%s', '%s', '%s');" % (screen_name, command, text, time) db = SQLite(config.DB_FILE) db.do_sql(sql)
def get_app_value(cfg_name): """get cfg_name's value.""" sql = "SELECT cfg_value FROM sw_app WHERE cfg_name='%s'" % cfg_name db = SQLite(config.DB_FILE) rows = db.fetch_sql(sql) return rows[0][0]
def set_app_value(cfg_name, cfg_value): """set cfg_name's value.""" sql = "UPDATE sw_app SET cfg_value='%s' WHERE cfg_name='%s'" % (cfg_value, cfg_name) db = SQLite(config.DB_FILE) db.do_sql(sql)
def drop_table(table_name='sw_messages'): sql = "DELETE FROM %s" % table_name db = SQLite(config.DB_FILE) db.do_sql(sql)
def getDBA( self , path , query = None ) : conn = SQLite( creator = self , filename = query ) conn.prepare( path ) return conn
#-*- coding: utf-8 -*- import sys sys.path.insert(0, "..") sys.path.insert(1, "weibo_backup") import HTMLParser import Helper import config from lib.SQLite import SQLite from weibo_backup.run import WeiboBackup parser = HTMLParser.HTMLParser() sql = "SELECT message FROM sw_messages WHERE src!='我'" db = SQLite("../"+config.DB_FILE) rows = db.fetch_sql(sql) total_status = [parser.unescape(r[0]) for r in rows] Helper.log("Total %d Status in DataBase!" % len(total_status)) fetcher = WeiboBackup() post_status = [w.decode("utf-8") for w in fetcher.backup('大学秘密')] Helper.log("Total %d Status Posted!" % len(post_status)) diff = [w for w in total_status if w not in post_status] Helper.log("%d Status Swallowed!" % len(diff)) """ from Poster import Poster poster = Poster() for w in diff: