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 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,pub_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 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)