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]))
