def add(cls, req):
_ip = req.get('ip')
_cpu = req.get('cpu')
_mem = req.get('mem')
_time =req.get('time')
_sql = 'insert into performs(ip, cpu, mem, time)values(%s,%s,%s,%s)'
MySQLConnection.execute_sql(_sql, (_ip, _cpu, _mem, _time), False)
def log2db(cls, logfile):
MySQLConnection.execute_sql('DELETE FROM accesslog;', (), False)
fhandler = open(logfile, 'r')
rt_dict = {}
while True:
line = fhandler.readline()
if line == '':
break
nodes = line.split()
ip, url, code = nodes[0], nodes[6], nodes[8]
key = (ip, url, code)
if key not in rt_dict:
rt_dict[key] = 1
else:
rt_dict[key] = rt_dict[key] + 1
fhandler.close()
rt_list = []
for _key, _cnt in rt_dict.items():
rt_list.append(_key + (_cnt, ))
_sql = 'insert into accesslog(ip, url, code, cnt) values (%s, %s, %s, %s)'
MySQLConnection.bulker_execute_sql(_sql, rt_list)
def log2db(cls, logfile):
MySQLConnection.execute_sql('DELETE FROM accesslog;', (), False)
fhandler = open(logfile, 'r')
rt_dict = {}
while True:
line = fhandler.readline()
if line == '':
break
nodes = line.split()
ip, url, code = nodes[0], nodes[6], nodes[8]
key = (ip, url, code)
if key not in rt_dict:
rt_dict[key] = 1
else:
rt_dict[key] = rt_dict[key] + 1
fhandler.close()
rt_list = []
for _key, _cnt in rt_dict.items():
rt_list.append(_key + (_cnt, ))
_sql = 'insert into accesslog(ip, url, code, cnt) values (%s, %s, %s, %s)'
MySQLConnection.bulker_execute_sql(_sql, rt_list)
def log_execute(self, log_files):
sql = 'DELETE FROM accesslog;'
MySQLConnection.execute_sql(sql, fetch=False)
sql = 'insert into accesslog(ip,url,status,count) values(%s, %s, %s, %s)'
rt_dict = {}
try:
log_files = open(log_files, 'r')
while True:
line = log_files.readline()
if not line:
break
logs = line.split()
(ip, url, status) = logs[0], logs[6], logs[8]
if (ip, url, status) not in rt_dict:
rt_dict[(ip, url, status)] = 1
else:
rt_dict[(ip, url, status)] += 1
log_files.close()
# args_list = sorted(rt_dict.items(), key=lambda x:x[1], reverse=True)
_count, _rt_list = MySQLConnection.bulker_commit_sql(sql, args=rt_dict.items())
return _count != 0
except BaseException as e:
import traceback
print traceback.format_exc()
print e
return False
def log2db(log_files='', topn=10, fetch=True):
sql = 'insert into accesslog(ip,url,status,count) values(%s,%s,%s,%s)'
rt_dict = {}
_rt = []
if fetch: # 查询数据
_columns = ('id', 'ip', 'url', 'status', 'count')
sql = 'select * from accesslog order by count desc limit %s'
_count, _rt_list = MySQLConnection.execute_log_sql(sql,
args=(topn, ),
fetch=True)
for _list in _rt_list:
_rt.append(dict(zip(_columns, _list)))
return _rt
else: # 写入数据
MySQLConnection.execute_sql('delete from accesslog', fetch=False)
try:
log_files = open(log_files, 'r')
while True:
line = log_files.readline()
if not line:
break
logs = line.split()
(ip, url, status) = logs[0], logs[6], logs[8]
if (ip, url, status) not in rt_dict:
rt_dict[(ip, url, status)] = 1
else:
rt_dict[(ip, url, status)] += 1
log_files.close()
_count, _rt_list = dbutils.execute_log_sql(
sql, args_list=rt_dict.items(), fetch=False)
return _count != 0
except:
return False
def add(cls, req):
_ip = req.get('ip')
_cpu = req.get('cpu')
_ram = req.get('ram')
_time = req.get('time')
_sql = 'insert into performs(ip,cpu,ram,time) values(%s,%s,%s,%s)'
MySQLConnection.execute_sql(_sql, (_ip, _cpu, _ram, _time), False)
def update(cls, _sn, _ip, _hostname, _os, _cpu, _ram, _disk, _idc_id,
_admin, _business, _purchase_date, _warranty, _vendor, _model,
_id):
_sql = 'update assets set sn=%s,ip=%s,hostname=%s,os=%s,cpu=%s,ram=%s,disk=%s,idc_id=%s,admin=%s,business=%s,purchase_date=%s,warranty=%s,vendor=%s,model=%s where id=%s;'
_args = (_sn, _ip, _hostname, _os, _cpu, _ram, _disk, _idc_id,
_admin, _business, _purchase_date, _warranty, _vendor, _model,
int(_id))
MySQLConnection.execute_sql(_sql, _args, fetch=False)
def idc_create(cls, name):
_column = 'name'
name = str(name)
_sql = 'insert into idcs(name) values(%s);'
_args = (name)
#print _sql
#print _args
MySQLConnection.execute_sql(_sql, _args, fetch=False)
def create(self):
_columns = self._columns[1::]
_column_str = ','.join(_columns)
_args = []
for _column in _columns:
_args.append(self.ob.get(_column, ''))
_sql = 'insert into assets({columns}) values({values})'.format(
columns=_column_str, values=','.join(['%s'] * len(_columns)))
MySQLConnection.execute_sql(_sql, _args, False)
def change_user(self, userid, updateage):
user_list = self.get_users()
for users in user_list:
if users['age'] != updateage:
_sql = 'update user set age=%s where id =%s'
_args = (updateage, userid)
MySQLConnection.execute_sql(_sql, _args, False)
def add(cls, req):
_column_str = 'sn,ip,hostname,os,admin,business,vendor,model,idc_id,cpu,ram,disk,warranty,purchase_date'
_columns = _column_str.split(',')
_args = []
for _column in _columns:
_args.append(req.get(_column, ''))
_sql = 'INSERT INTO assets({columns}) VALUES({values})'.format(columns=_column_str, values=','.join(['%s'] * len(_columns)))
MySQLConnection.execute_sql(_sql, _args, False)
def update(self):
_columns = self._columns[1::]
_values = []
_args = []
for _column in _columns:
_values.append('{column}=%s'.format(column=_column))
_args.append(self.ob.get(_column, ''))
_args.append(self.ob.get('id'))
_sql = 'update assets SET {values} where id=%s'.format(
values=','.join(_values))
MySQLConnection.execute_sql(_sql, _args)
def update(cls, req):
_column_str = 'sn,ip,hostname,os,admin,business,vendor,model,idc_id,cpu,ram,disk,warranty,purchase_date'
_columns = _column_str.split(',')
_values = []
_args = []
for _column in _columns:
_values.append('{column}=%s'.format(column=_column))
_args.append(req.get(_column, ''))
_args.append(req.get('id'))
_sql = 'UPDATE assets SET {values} WHERE id=%s'.format(values=','.join(_values))
MySQLConnection.execute_sql(_sql, _args, False)
def validate_login(cls, username, password):
_column = ("id", "username")
_sql = 'select id,username from user_auth where username=%s and password=md5(%s)' #无非就是查询count
_count, _rt_tuple = MySQLConnection.execute_sql(
_sql, (username, password))
print "_rt_tuple:", _rt_tuple
return dict(zip(_column, _rt_tuple[0])) if _count != 0 else None
def get_by_key(cls, value, key='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 {key}=%s'.format(column=_column, key=key)
_args = (value,)
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
return None if _count == 0 else dict(zip(_columns, _rt_list[0]))
def get_list2(cls):
_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'.format(column=_column)
_cnt, _rt_list = MySQLConnection.execute_sql(_sql)
return [dict(zip(_columns, _line)) for _line in _rt_list]
def get_list(cls):
column = 'id,sn,ip,hostname,idc_id,purchase_date,warranty,vendor,model,admin,business,cpu,ram,disk,os,status'
columns = column.split(',')
sql = 'select {column} from assets where status=0'.format(
column=column)
count, rt_list = MySQLConnection.execute_sql(sql, fetch=True)
return [dict(zip(columns, line)) for line in rt_list]
def update(self):
sql = 'update user set job=%s, age=%s where id=%s'
args = (self.job, self.age, self.id)
count, rt_list = MySQLConnection.execute_sql(sql,
args=args,
fetch=False)
return count != 0
def validate(cls, asset_info, repeat=True):
errors = {}
for value in asset_info:
if asset_info[value] == "":
errors[value] = "%s为空" % value
if errors != {}:
return False, errors
if not asset_info["cpu"].isdigit():
errors["cpu"] = "cpu核数只能写整数,请重新填写"
if not asset_info["disk"].isdigit():
errors["disk"] = "硬盘大小只能写整数,请重新填写"
if not asset_info["ram"].isdigit():
errors["ram"] = "内存大小只能写整数,请重新填写"
if not asset_info["warranty"].isdigit():
errors["warranty"] = "保修时间只能写整数,请重新填写"
if errors != {}:
return False, errors
if repeat:
sql = 'select sn from assets where sn=%s'
args = (asset_info['sn'], )
count, rt_list = MySQLConnection.execute_sql(sql, args)
if count != 0:
errors['sn'] = "sn编码重复,请重新输入"
if errors != {}:
return False, errors
else:
return True, {}
def validate_login(cls, username, password):
_column = ("id", "username")
password_md5 = cls.md5_str(password)
_sql = 'select id,username from user_auth where username=%s and password=%s' #无非就是查询count
_count, _rt_tuple = MySQLConnection.execute_sql(
_sql, (username, password_md5))
return dict(zip(_column, _rt_tuple[0])) if _count != 0 else None
def get_info(cls,
_sql=None,
_count=None,
_user=None,
_id=None,
username=None,
_args=()):
_rt = []
if _sql:
_columns = ('id', 'ip', 'url', 'code', 'cnt')
for i in _sql.split():
if i in ['drop', 'delete']:
return False, u'sql语句中有危险操作！'
elif _count:
_sql = 'select * from logs limit %s;'
_columns = ('id', 'ip', 'url', 'code', 'cnt')
_args = (int(_count), )
_count = _count
elif _id:
_sql = 'select * from user where id=%s;'
_args = (_id, )
_columns = ('id', 'username', 'password', 'age')
elif username:
_sql = "select * from user where username=%s;"
_args = (username, )
_columns = ('id', 'username', 'password', 'age')
else:
_columns = ('id', 'username', 'password', 'age')
_sql = 'select * from user;'
_cnt, _rt_list = MySQLConnection.execute_sql(_sql, _args, fetch=True)
for _line in _rt_list:
_rt.append(dict(zip(_columns, _line)))
return _rt, ''
def validate_login(cls, username, password):
_columns = ('id', 'username', 'password')
_sql = 'select * from user where username=%s and password=md5(%s)'
_cnt, _rt_list = MySQLConnection.execute_sql(_sql,
(username, password),
fetch=True)
return dict(zip(_columns, _rt_list[0])) if _cnt != 0 else None
def save(self):
sql = 'insert into user(username,password,job,age) values(%s,md5(%s),%s,%s)'
args = (self.username, self.password, self.job, self.age)
count, rt_list = MySQLConnection.execute_sql(sql,
args=args,
fetch=False)
return count != 0
def create(cls, _sn, _ip, _hostname, _os, _cpu, _ram, _disk, _idc_id,
_admin, _business, _purchase_date, _warranty, _vendor, _model):
_column = 'sn,ip,hostname,os,cpu,ram,disk,idc_id,admin,business,purchase_date,warranty,vendor,model'
_rt, _error = cls.validate_create(_sn, _ip, _hostname, _os, _cpu, _ram,
_disk, _idc_id, _admin, _business,
_purchase_date, _warranty, _vendor,
_model)
if _rt:
_sql = 'insert into assets({column}) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'.format(
column=_column)
_args = (_sn, _ip, _hostname, _os, _cpu, _ram, _disk, _idc_id,
_admin, _business, _purchase_date, _warranty, _vendor,
_model)
#print _sql
#print _args
MySQLConnection.execute_sql(_sql, _args, fetch=False)
def get_list(cls):
sql = 'select * from user'
rt = []
columns = ("id", "username", "password", "age", "gender", "email")
count, rt_list = MySQLConnection.execute_sql(sql)
for line in rt_list:
rt.append(dict(zip(columns, line)))
return rt
def create(cls, asset_dict):
sql = 'insert into assets(sn,ip,hostname,idc_id,purchase_date,warranty,vendor,model,admin,business,cpu,ram,disk,os) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
args_list = []
lists = ['sn','ip','hostname','idc_id','purchase_date','warranty','vendor','model','admin','business','cpu','ram','disk','os']
for i in lists:
args_list.append(asset_dict.get('_'+i))
_count, _rt_list = MySQLConnection.execute_sql(sql, args_list, fetch=False)
return _count != 0
def log_fetch(self, topn):
_rt = []
_columns = ('id', 'ip', 'url', 'status', 'count')
sql = 'select * from accesslog order by count desc limit %s'
_count, _rt_list = MySQLConnection.execute_sql(sql, args=topn, fetch=True)
for _list in _rt_list:
_rt.append(dict(zip(_columns, _list)))
return _rt
def get_list(cls):
_columns = ('id', 'domain_name', 'username', 'password', 'function')
_sql = 'select * from service_manage'
_count, _rt_list = MySQLConnection.execute_sql(_sql, fetch=True)
_rt = []
for _list in _rt_list:
_rt.append(dict(zip(_columns, _list)))
return _rt
def get_list(cls):
columns = ('id', 'username', 'password', 'job', 'age')
sql = 'select * from user'
rt = []
count, rt_list = MySQLConnection.execute_sql(sql)
for line in rt_list:
rt.append(dict(zip(columns, line)))
return rt
def statis_get_list(cls):
_sql = 'select code,sum(cnt) from accesslog group by code'
_count, _rt_list = MySQLConnection.execute_sql(_sql)
_dict = dict(_rt_list)
_rt = [str(key) for key in _dict]
_sum = sum([_dict.get(key) for key in _dict])
_float = map(lambda x: round(float(x) / float(_sum) * 100, 4),
[float(_dict.get(key)) for key in _dict])
return zip(_rt, _float)
def get_user_by_name(cls, username):
_columns = ('id', 'username', 'password', 'age')
_sql = 'select * from user_auth where username = %s'
_count, _rt_tuple = MySQLConnection.execute_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 validate_user(self, username=None, user_id=None):
if username:
sql = 'select * from user where username=%s'
args = (username, )
elif user_id:
sql = 'select * from user where id=%s'
args = (user_id, )
count, rt_list = MySQLConnection.execute_sql(sql, args)
return count == 0
def get_by_id(cls,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 = MySQLConnection.execute_sql(_sql, (value,))
if _cnt !=0:
return _cnt, dict(zip(_columns,_rt_list[0]))
else:
return _cnt, _rt_list
def get_list(cls, 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 = MySQLConnection.execute_sql(_sql, _args)
return [dict(zip(_columns, _line)) for _line in _rt_list]
def get_list(cls, ip):
_sql = 'select cpu, mem, time from performs where ip=%s and time >=%s order by time asc'
_args = (ip, time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()-60 * 60)))
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
datetime_list = []
cpu_list = []
mem_list = []
for _cpu, _mem, _time in _rt_list:
cpu_list.append(_cpu)
mem_list.append(_mem)
datetime_list.append(_time.strftime('%H:%M'))
return datetime_list, cpu_list, mem_list
def get_users(cls, wheres=[]):
_columns = ('id', 'username', 'password', 'age', 'phone', 'email')
_sql = 'select * from user where 1=1'
_args = []
print wheres
for _key, _value in wheres:
_sql += ' AND {key} = %s'.format(key=_key)
_args.append(_value)
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
_rt = []
for _line in _rt_list:
# (6L, u'kk4', u'e10adc3949ba59abbe56e057f20f883e', 29L)
_rt.append(dict(zip(_columns, _line)))
return _rt
def has_alarm(ip):
_sql = 'select cpu, mem from performs where ip = %s and time >=%s order by time desc limit %s'
_time = datetime.datetime.now() - datetime.timedelta(minutes=5)
_args = (ip, _time.strftime('%Y-%m-%d %H:%M:%S'), CNT)
_rt_cnt, _rt_list = MySQLConnection.execute_sql(_sql, _args)
if _rt_cnt < CNT:
return False, False
_cpu_alarm = True
_mem_alarm = True
for cpu, mem in _rt_list:
if cpu < CPU_PERCENT:
_cpu_alarm = False
if mem < MEM_PERCENT:
_mem_alarm = False
return _cpu_alarm, _mem_alarm
def get_list(cls, topn=10):
_sql = 'select ip, url, code, cnt from accesslog order by cnt desc limit %s'
_args = (topn, )
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
return _rt_list
def delete(cls, id):
_sql = 'UPDATE assets SET status=1 WHERE id=%s'
_args = (id, )
MySQLConnection.execute_sql(_sql, _args, False)
def execute_sql(cls, sql, args=(), fetch=True):
return MySQLConnection.execute_sql(sql, args, fetch)
def add(cls,username, password, age, phone, email):
_sql = 'insert into user(username, password, age, phone, email) values(%s, %s, %s, %s, %s)'
_args = (username, utils.md5_str(password), age, phone, email)
MySQLConnection.execute_sql(_sql, _args, False)
def get_list(cls):
_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_list = MySQLConnection.execute_sql(_sql)
return [dict(zip(_columns, _line)) for _line in _rt_list]
def delete(cls, uid):
_sql = 'delete from user where id=%s'
_args = (uid, )
MySQLConnection.execute_sql(_sql, _args, False)
def change_user_password(cls,uid, upassword):
_sql = 'update user set password=%s where id=%s'
MySQLConnection.execute_sql(_sql, (utils.md5_str(upassword), uid), False)
def update_user(cls,uid, password, age, phone, email):
_sql = 'update user set password=%s, age=%s, phone=%s, email=%s where id=%s'
MySQLConnection.execute_sql(_sql, (utils.md5_str(password), age, phone, email, uid),False)
def save(self):
_sql = 'insert into user(username, password, age, phone, email) values(%s, %s, %s, %s, %s)'
_args = (self.username, utils.md5_str(self.password), self.age, self.phone, self.email)
MySQLConnection.execute_sql(_sql, _args, False)
def save(self):
_sql = 'insert into user(username, password, age) values(%s, md5(%s), %s)'
_args = (self.username, self.password, self.age)
MySQLConnection.execute_sql(_sql, _args, False)
def add(cls, username, password, age):
_sql = 'insert into user(username, password, age) values(%s, md5(%s), %s)'
_args = (username, password, age)
MySQLConnection.execute_sql(_sql, _args, False)
def validate_login(cls, username, password):
_columns = ('id', 'username')
_sql = 'select id,username from user where username=%s and password=md5(%s)'
_count, _rt_list = MySQLConnection.execute_sql(_sql, (username, password))
return dict(zip(_columns, _rt_list[0])) if _count != 0 else None
def Assets_create(cls,sn, ip, hostname, os, purchase_date, warranty, vendor, model, idcs, admin, application,business, cpu, mem, disk):
_sql = 'insert into assets(sn, ip, hostname, os, purchase_date, warranty, vendor, model, idc_id, admin, application,business, cpu, mem, disk) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)'
args = (sn, ip, hostname, os, purchase_date, warranty, vendor, model, idcs, admin, application,business, cpu, mem, disk)
MySQLConnection.execute_sql(_sql, args, False)
return True
def update(cls, uid, username, password, age):
_sql = 'update user set age=%s where id=%s'
_args = (age, uid)
MySQLConnection.execute_sql(_sql, _args, False)
def charge_password(cls, uid, upassword):
_sql = 'update user set password=md5(%s) where id=%s'
_args = (upassword, uid)
MySQLConnection.execute_sql(_sql, _args, False)