def get_by_hostname(hostname): _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'select {column} from assets where status=0 and hostname = %s'.format(column = _column) _args=(hostname,) _cnt,_rt_list = execute_fetch_sql(_sql,_args) return None if _cnt == 0 else dict(zip(_columns,_rt_list[0]))
def validate_login(username, password): #_sql = 'select * from user where username="******" and password=md5("{password}")'.format(username=username, password=password) print username, password _sql = 'select * from user where username=%s and password=md5(%s)' _count, _rt_list = execute_fetch_sql(_sql, (username, password)) return _count != 0
def get_by_sn(sn): _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'SELECT {column} FROM assets WHERE status=0 and sn=%s'.format(column=_column) _args = (sn,) _cnt, _rt_list = execute_fetch_sql(_sql, _args) return None if _cnt == 0 else dict(zip(_columns, _rt_list[0]))
def get_list(): _column = 'id,sn,ip,hostname,os,purchase_date,warranty,vendor,model,idc_id,admin,business,cpu,mem,disk' _columns = _column.split(',') _sql = 'select {column} from assets where status=0'.format(column=_column) print _sql _cnt, _rt_list = execute_fetch_sql(_sql) return [dict(zip(_columns, _line)) for _line in _rt_list]
def validateSn(sn): _sql = "select * from assets where sn = %s" _args =(sn,) _cnt,_rt_list = execute_fetch_sql(_sql,_args) if _cnt: return True,_rt_list[0][0] return False,''
def validateHostname(hostname): _sql = "select * from assets where hostname = %s" _args =(hostname,) _cnt,_rt_list = execute_fetch_sql(_sql,_args) if _cnt: return True,_rt_list[0][0] return False,[]
def validate_login(username, password): _count, _rt = dbutils.execute_fetch_sql(SQL_VALIDATE_LOGIN, (username, password)) # rt = None # if record is not None: # rt = {'id': record[0], 'name': record[1]} return None if _rt is None else dict(zip(SQL_VALIDATE_LOGIN_COLUMNS, _rt))
def get_list(): _column = 'id,sn,ip,hostname,os,purchase_date,warranty,vendor,model,idc_id,admin,business,cpu,mem,disk' _columns = _column.split(',') _sql = 'select {column} from assets where status=0'.format(column=_column) print _sql _cnt, _rt_list = execute_fetch_sql(_sql) return [dict(zip(_columns, _line)) for _line in _rt_list]
def get_by_id(id): _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'select {column} from assets where status = 0 and id=%s'.format(column=_column) args =(id,) _cnt,_rt_list = execute_fetch_sql(_sql,args) return [dict(zip(_columns,_line)) for _line in _rt_list][0]
def get_users(): _columns = ('id', 'username', 'password', 'age') _sql = 'select * from user' _rt = [] _count, _rt_list = execute_fetch_sql(_sql) for _line in _rt_list: _rt.append(dict(zip(_columns, _line))) return _rt
def get_idcs_list(): _columns = ('id', 'name') _sql = 'select id,name from idcs where status=0' _cnt, _rt_list = execute_fetch_sql(_sql) a = [dict(zip(_columns, _line)) for _line in _rt_list] print a return [dict(zip(_columns, _line)) for _line in _rt_list]
def get_user_by_name(username): _columns = ('id', 'username', 'password', 'age') _sql = 'select * from user_auth where username = %s' _count, _rt_tuple = execute_fetch_sql(_sql, (username, )) #返回list _rt_list = [] for _line in _rt_tuple: _rt_list.append(dict(zip(_columns, _line))) return _rt_list[0] if len(_rt_list) > 0 else None
def get_user_by_uid(uid): _coulmns = ('id', 'username', 'password', 'age') _sql = "select * from user where id = %s" args = (uid, ) _count, _rt_list = execute_fetch_sql(_sql, args) _rt = [] for line in _rt_list: _rt.append(dict(zip(_coulmns, line))) return _rt[0] if len(_rt) > 0 else None
def validate_login(username, password): _sql = 'select * from user_auth where username=%s and password=md5(%s)' #无非就是查询count #_sql = 'select * from user_auth where username="******" and password=md5("{password}")'.format(username=username,password=password) _count, _rt_tuple = execute_fetch_sql(_sql, (username, password)) # return _count != 0 if _count == 0: return False else: return True
def get_by_id(key, value): _column = 'id,sn,ip,hostname,os,purchase_date,warranty,vendor,model,idc_id,admin,application,business,cpu,mem,disk' _columns = _column.split(',') _sql = 'select * from assets where {key}=%s'.format(key=key) _cnt, _rt_list = execute_fetch_sql(_sql, (value,)) if _cnt !=0: return _cnt, dict(zip(_columns,_rt_list[0])) else: return _cnt, _rt_list
def validate_login(username, password): _sql = "select * from user where username=%s and password=md5(%s)" args = (username, password) _count, _rt_list = execute_fetch_sql(_sql, args) #return _count != 0 if _count: return True else: return False
def accesslog(topn): _columns = ('count','url','ip','code') _sql = 'select * from accesslog limit %s' _args = (topn) _count, _rt_list = execute_fetch_sql(_sql,_args) _rt = [] for _line in _rt_list: _rt.append(dict(zip(_columns, _line))) return _rt
def get_user_id(id,fetch=True): _columns = ('id','username','password','age') _sql = 'select * from user where id=%s' _args = (id) _count, _rt_list = execute_fetch_sql(_sql,_args) _rt = [] for _line in _rt_list: _rt.append(dict(zip(_columns, _line))) return _rt
def get_users(): _counlms = ('id', 'username', 'password', 'age') #组成一个字典的元组 _sql = "select * from user" args = () _rt = [] _count, _rt_list = execute_fetch_sql(_sql, args) if _count: for line in _rt_list: _rt.append(dict(zip(_counlms, line))) #利用zip函数来组成字典 return _rt
def get_list(): _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'select {column} from assets where status = 0'.format(column=_column) _cnt,_rt_list = execute_fetch_sql(_sql) dic = [dict(zip(_columns,_line)) for _line in _rt_list] for line in dic: _idc_id = line.get('idc_id') _idc_name = get_idcs_name(line.get('idc_id')) line['idc_id'] = [_idc_id,_idc_name] return dic
def get_by_id(aid): _columns = ('id', 'sn', 'ip', 'hostname', 'os', 'cpu', 'ram', 'disk', 'idc_id', 'admin', 'business', 'purchase_date', 'warranty', 'vendor', 'model', 'status') _sql = 'select * from assets where status=0 and id=%s' #status=0,把正常的值拿出来,因为被删除的值的status=1.不写的话,都拿出来 _count, _rt_tuple = execute_fetch_sql(_sql, (aid, )) #返回list _rt_list = [] for _line in _rt_tuple: _rt_list.append(dict(zip(_columns, _line))) return None if _count == 0 else _rt_list[0]
def get_by_sn(sn): _columns = ('id', 'sn', 'ip', 'hostname', 'os', 'cpu', 'ram', 'disk', 'idc_id', 'admin', 'business', 'purchase_date', 'warranty', 'vendor', 'model', 'status') _sql = 'select * from assets where sn = %s' _count, _rt_tuple = execute_fetch_sql(_sql, (sn, )) #返回list _rt_list = [] for _line in _rt_tuple: _rt_list.append(dict(zip(_columns, _line))) return _rt_list[0] if len(_rt_list) > 0 else None
def get_asset_list(): _rt_list = [] _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'SELECT {column} FROM assets WHERE status=0'.format(column=_column) _count, _rt_tuple = execute_fetch_sql(_sql) #_rt_tuple => [(id,sn,hostname,ip,....)] for _line in _rt_tuple: print 'line:', _line _rt_list.append(dict(zip(_columns, _line))) print '_rt_list:', _rt_list return _rt_list
def get_users(wheres=[]): _columns = ('id', 'username', 'password', 'age') _sql = "select * from user where 1=1" _args = [] for _key, _value in wheres: _sql += ' AND {key} = %s'.format(key=_key) _args.append(_value) _count, _rt_list = execute_fetch_sql(_sql, _args) _rt = [] for _line in _rt_list: _rt.append(dict(zip(_columns, _line))) return _rt
def get_users(): _columns = ('id', 'username', 'password', 'age') _sql = 'select * from user_auth' print _sql #初始化参数 _rt_list = [] _count, _rt_tuple = execute_fetch_sql(_sql) for _line in _rt_tuple: #(6L,u'dick',u'wdwqedwqewqdsadwqdwqdwqw',29L) _rt_list.append(dict(zip(_columns, _line))) print '_rt_list:', _rt_list return _rt_list
def get_users(wheres=[]): _columns = ('id', 'name', 'password', 'age') _sql = 'select * from users where 1=1' _args = [] for _key, _value in wheres: _sql += ' AND {key} = %s'.format(key=_key) _args.append(_value) _count, _rt_list = execute_fetch_sql(_sql, _args) _rt = [] for _line in _rt_list: #(6L, u'kk4', u'e10adc3949ba59abbe56e057f20f883e', 29L) _rt.append(dict(zip(_columns, _line))) return _rt
def get_users(wheres=[]): _columns = ('id', 'username', 'password', 'age') _sql = 'select * from user where 1=1' _args = [] for _key, _value in wheres: _sql += ' AND {key} = %s'.format(key=_key) _args.append(_value) _count, _rt_list = execute_fetch_sql(_sql, _args) _rt = [] for _line in _rt_list: # (6L, u'kk4', u'e10adc3949ba59abbe56e057f20f883e', 29L) _rt.append(dict(zip(_columns, _line))) return _rt
def validate_user_modify(username, age, uid): username = username.strip() if not get_user_by_id(uid): return False, 'user is not found' if username == '': return False, 'username is empty' if len(username) > 25: return False, 'username is lt 25' if not str(age).isdigit() or int(age) < 1 or int(age) > 120: return False, 'age is 1 and 120 integer' _count, _rt = dbutils.execute_fetch_sql(SQL_VALIDATE_USER_MODIFY, (uid, username)) if _rt: return False, 'username is used by others' return True, ''
def validate_user_save(username, password, age): username = username.strip() password = password.strip() if username == '': return False, 'username is empty' if len(username) > 25: return False, 'username is lt 25' if password == '': return False, 'password is empty' if len(password) > 25 or len(password) < 6: return False, 'password is between 6 and 25' if not str(age).isdigit() or int(age) < 1 or int(age) > 120: return False, 'age is 1 and 120 integer' _count, _rt = dbutils.execute_fetch_sql(SQL_VALIDATE_ROOM_SAVE, (name, )) if _rt: return False, 'username is used by others' return True, ''
def validate_room_save(roomname, addr, ip_ranges): roomname = roomname.strip() addr = addr.strip() ip_ranges = ip_ranges.strip() print ip_ranges if roomname == '': return False, 'roomname is empty' if len(roomname) > 64: return False, 'roomname is gt 64' if addr == '': return False, 'addr is empty' if len(addr) > 128: return False, 'addr is gt 128' if not IPChecker_re(ip_ranges): return False, 'ip_ranges is not ip_address type' _count, _rt = dbutils.execute_fetch_sql(SQL_VALIDATE_ROOM_SAVE, (roomname, )) if _rt: return False, 'roomname is used by others' return True, ''
def validate_room_modify(rid, roomname, addr, ip_ranges): roomname = roomname.strip() addr = addr.strip() ip_ranges = ip_ranges.strip() if not get_room_by_id(rid): return False, 'room is not found' if roomname == '': return False, 'roomname is empty' if len(roomname) > 64: return False, 'roomname is gt 64' if addr == '': return False, 'addr is empty' if len(addr) > 128: return False, 'addr is gt 128' if not IPChecker_re(ip_ranges): return False, 'ip_ranges is not ip_address type' _count, _rt = dbutils.execute_fetch_sql(SQL_VALIDATE_ROOM_MODIFY, (rid, roomname)) if _rt: return False, 'roomname is used by others' return True, ''
def get_idc_list(): _columns = 'id,name' _sql = 'select {column} from idcs where status=0'.format(column=_columns) _cnt,_rt_list = execute_fetch_sql(_sql) return [ _line for _line in _rt_list]
def get_list(): _column = 'id,sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model' _columns = _column.split(',') _sql = 'SELECT {column} FROM assets WHERE status=0'.format(column=_column) _cnt, _rt_list = execute_fetch_sql(_sql) return [dict(zip(_columns, _line)) for _line in _rt_list] if _cnt != 0 else None
def validate_login(name, password): #_sql = 'select * from user where username="******" and password=md5("{password}")'.format(username=username, password=password) _sql = 'select * from users where name=%s and password=(%s)' _count, _rt_list = execute_fetch_sql(_sql, (name, password)) return _count != 0
def get_topn(topn=10): _sql = 'select ip, url, code, cnt from accesslog order by cnt desc limit %s' _cnt, _rt_list = execute_fetch_sql(_sql, (topn, )) return _rt_list
def get_by_id(aid): _column = 'id,sn,ip,hostname,os,purchase_date,warranty,vendor,model,idc_id,admin,business,cpu,mem,disk' _columns = _column.split(',') _sql = 'select * from assets where sn=%s' _cnt, _rt_list = execute_fetch_sql(_sql, (aid,)) return _cnt, dict(zip(_columns,_rt_list[0]))