def del_local_record(self): ids = self.local_list.selectedIndexes() for id in ids: sql = "delete from msg where username = '******' and id = %d" % ( self.user_info['username'], self.local_records[id.row()]['id']) sqlite_db.transaction(sql) self.local_list.takeItem(id.row())
def check(self): ''' 用于检测用户的登录状态 :return: ''' self.user_info = mine.get_value('user_info') logging.warning(self.user_info) ret = get_notes(self.user_info['username']) if not ret['status']: logging.error(ret['msg']) records = ret['records'] if 'records' in ret.keys() else list() if not records: table = 'Msg' dict = { 'message': 'Welcome', 'detail': 'Thanks\ you\ support', 'username': self.user_info['username'] } dict['message'] = cryptograph_text( dict['message'], 'message', user_name=self.user_info['username']) dict['detail'] = cryptograph_text( dict['detail'], 'detail', user_name=self.user_info['username']) sql = be_sql().ins_sql(table, dict) sqlite_db.transaction(sql) return self.user_info['username']
def set_is_default_email_conf(id, username): sql = """update email_settings set is_default = 0 where username = ? """ param = [username] if not sqlite_db.transaction(sql, param)['status']: return sql = """update email_settings set is_default = 1 where id= ? ;""" param = [id] return sqlite_db.transaction(sql, param)
def do_logout(self): response = logout(self.username) if response['status']: sql = "update user set token=NULL where username = :username" sqlite_db.transaction(sql,{"username",self.username}) self.close() self.logout_signal.emit('visitor') else: Toast(self).show_toast("{}".format('请检查数据库文件和网络状态'))
def clear_notes(filename, sever_date): ''' 根据系统返回天数物理删除记录 :param filename: :param sever_date: :return: ''' try: sqlite_db.transaction( ("delete from Msg where del_time<strftime('yyyy-mm-dd',%s);" % sever_date)) logging.info('clear done') except Exception as e: print("clear error") logging.warning(e)
def add_email_conf(username, addr, password, sender_name, ssl_port, user_ssl, *args, **kwargs): param = (username, addr, password, sender_name, ssl_port, user_ssl) sql = """insert into email_settings(username,addr, password, sender_name, ssl_port, user_ssl) values(?,?,?,?,?,?) """ return sqlite_db.transaction(sql, param)
def update_notes(data, ele, **kwargs): # 加密 data[ele] = cryptograph_text(data[ele], ele, username=kwargs['username']) sql = "update Msg set {}=:{},update_time=datetime(CURRENT_TIMESTAMP, 'localtime') where id != -1 and id = ?".format( ele, ele) ret = sqlite_db.transaction(sql, [data['id']]) if not ret['status']: logging.error("message:{}\nsql:{}".format(ret['msg'], ret['errorsql'])) return ret
def add_notes(data): for k, v in data.items(): if 'username' != k: ele = k data[k] = cryptograph_text(v, k, username=data['username']) sql = "insert into Msg({},username) values(?,?)".format(ele) ret = sqlite_db.transaction(sql, [data[ele], data['usename']]) if not ret['status']: logging.error("message:{}\nsql:{}".format(ret['msg'], ret['errorsql'])) sql = "select * from Msg where username = ? and is_del = 0 order by id desc limit 0 ,1" ret = sqlite_db.select(sql, [data['username']], just_first=True) return ret
def change_del_flag(filter_list, is_del=1): ''' 软删除数据 :param filename: :param filer_list: :return: ''' table = 'Msg' sql = be_sql().update_sql(table=table, value_dict={'is_del': str(is_del)}, filter_list=filter_list) # print(sql) return sqlite_db.transaction(sql)
def update_email_conf(id, username, addr, password, sender_name, ssl_port, user_ssl, *args, **kwargs): param = (username, addr, password, sender_name, ssl_port, user_ssl, id) sql = """update email_settings set username = ?, addr = ?, password = ?, sender_name = ?, ssl_port = ?, user_ssl = ? where id = ? """ return sqlite_db.transaction(sql, param)
def add_msg(data: dict): ''' 添加数据 更改会影响 增加逻辑 延后重构 wrb :param filename: sqlit3文件名 :param data: dict {'id':-1,'{ele}':'','username':''} :return: ''' if data['id'] == -1: data.pop('id') # 明文加密 for k, v in data.items(): if 'username' != k: data[k] = cryptograph_text(v, k, user_name=data['username']) sql = "insert into msg(username,msg,detail) values(?,?,?)" ret = sqlite_db.transaction( sql, [data['username'], data['msg'], data['detail']]) if not ret['status']: logging.error("msg:{}\nsql:{}".format(ret['msg'], ret['sql'])) return ret
def do_login(self): if self.username_le.text() and self.password_le.text(): result = login( username=self.username_le.text(), password=self.password_le.text()) # assert result['token'], '本地无记录' logging.debug(result) try: if result['result']['token']: table = 'user' # 写入缓存 mine.update_item('username', self.username_le.text()) mine.update_item('token', result['result']['token']) if not utils.get_user_info(table, self.username_le.text()): # 没有用户直接插入 dict = {'username': self.username_le.text(), 'token': result['token']} sql = "insert into user(username,token) values ('%s','%s');" sqlite_db.transaction(sql) times = ['00:15:00', '00:30:00', '01:00:00'] for time in times: sqlite_db.transaction( "insert into Reminder(username,time) values ('%s','%s');" % (dict['username'], time)) # 已有用户更新数据 else: value_dict = {'token': result['result']['token']} filter_list = [ ['username', '=', self.username_le.text()]] sql = be_sql().update_sql(table=table, value_dict=value_dict, filter_list=filter_list) sqlite_db.transaction(sql) try: # 发射信号 self.login_signal.emit(self.username_le.text()) except Exception as e: logging.error(e) self.close() else: Toast(self).show_toast("{}".format(result['msg'])) except Exception as e: logging.error(e) elif not self.username_le.text(): Toast(self).show_toast("请输入账号") elif not self.password_le.text(): Toast(self).show_toast("请输入密码")
def change_del_flag(id, is_del=1): ''' ''' sql = "update Msg set is_del = ? where id=?" return sqlite_db.transaction(sql, [is_del, id])
def del_email_conf(id): sql = """delete from email_settings where id=? ; """ param = [id] return sqlite_db.transaction(sql, param)