def log2db(logfile):
reader = geoip2.database.Reader('GeoLite2-City.mmdb')
fhandler = open(logfile,'r')
rt_list = []
#统计
while True:
line = fhandler.readline()
if line=='':
break
nodes = line.split()
ip , logtime , url, status = nodes[0], nodes[3], nodes[6] , nodes[8]
logtime = time.strftime('%Y-%m-%d %H:%M:%S', time.strptime(logtime,'%d/%b/%Y:%H:%M:%S'))
try:
reponse = reader.city(ip)
if 'China' != reponse.country.name:
print 'ip not in china %s' % ip
continue
city = reponse.city.names.get('zh-CN','')
if city == '':
print "ip city is empty:%s" % ip
continue
lat = reponse.location.latitude
lng = reponse.location.longitude
rt_list.append((ip, logtime, url, status, lat, lng, city))
except BaseException as e:
print 'geo ip not found ip %s' % ip
fhandler.close()
reader.close()
_sql = 'insert into accesslog2(logtime, ip , url, status, lat, lng, city) values (%s,%s,%s,%s,%s,%s,%s)'
MySQLConnection.bulker_commit_sql(_sql,rt_list)
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 validate_login(cls, username, password):
_column = 'username'
_columns = _column.split(',')
_sql = "select {column} from user where username=%s and password=md5(%s)".format(
column=_column)
_count, _rt_list = MySQLConnection.execute_sql(_sql,
(username, password))
return dict(zip(_columns, _rt_list[0])) if _count != 0 else None
def get_status_distribution(cls):
_sql = "select status , count(*) from accesslog2 group by status"
_rt_cnt, _rt_list = MySQLConnection.execute_sql(_sql)
_legend = []
_data = []
# 使用列表推导式代替for循环
#for _status, _cnt in _rt_list:
# _legend.append(status)
# _data.append({"name": _status , "value": _cnt})
_legend = [_node[0] for _node in _rt_list]
_data = [dict(zip(("name", "value"), _node)) for _node in _rt_list]
return _legends, _data
def get_list(cls, ip):
_sql = 'SELECT cpu, ram, 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)))
print _args
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
datetime_list = []
cpu_list = []
ram_list = []
for _cpu, _ram, _time in _rt_list:
cpu_list.append(_cpu)
ram_list.append(_ram)
datetime_list.append(_time.strftime('%H:%M:%S'))
return datetime_list, cpu_list, ram_list
def has_alarm(ip):
_sql = 'select cpu, ram 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)
print _rt_cnt, _rt_list
if _rt_cnt < CNT:
return False, False
_cpu_alarm = True
_ram_alarm = True
for _cpu, _ram in _rt_list:
if _cpu < CPU_PERCENT:
_cpu_alarm = False
if _ram < RAM_PERCENT:
_ram_alarm = False
return _cpu_alarm, _ram_alarm
def get_time_status_stack(cls):
_sql = "select DATE_FORMAT(logtime,'%%Y-%%m-%%d %%H:00:00') ltime, status , count(*) from accesslog2 where logtime > %s group by ltime,status"
_lasttime = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()) - 12 * 60 * 60)
_rt_cnt, _rt_list = MySQLConnection.execute_sql(_sql, (_lasttime, ))
_legends = []
_xaxis = []
_data = []
_tmp_dict = {}
#[('1:00',200,2),('1:00' , 404, 7),('3:00', 404, 3)]
for _ltime, _status, _cnt in _rt_list:
#相同的只加入一次
if _status not in _legends:
_legends.append(_status)
#相同的只加入一次
if _ltime not in _xaxis:
_xaxis.append(_ltime)
# 转换成每个状态，每个时间的次数
# {200:{'1:00': 2}, 404:{'1:00': 7, '3:00':3}}
_tmp_dict.setdefault(_status, {})
_tmp_dict[status][_ltime] = _cnt
# 将数据转换成如下的形式
for _status, _stat in _tmp_dict.items():
_node = {
"name": _status,
"type": 'bar',
"stack": 'time_status_stack',
"data": []
}
# 按照时间将data
for _ltime in _xaxis:
_cnt = _stat.get(_ltime, 0)
_node['data'].append(_cnt)
_data.append(_node)
return _legends, _xaxis, _data
def get_list(cls, wheres=[]):
_column = 'username,password,age'
_columns = _column.split(',')
_sql = "select {column} from user where 1=1".format(column=_column)
_args = []
for _key, _value in wheres:
_sql += ' AND {key} = %s'.format(key=_key)
_args.append(_value)
print _sql
_count, _rt_list = MySQLConnection.execute_sql(_sql, _args)
#返回一个User对象
#_rt = []
#for _line in _rt_list:
# _user_dict = dict(zip(_columns, _line))
# _user = User(_user_dict['id'], _user_dict['username'],_user_dict['password'],_user_dict['age'])
# _rt.append(_user)
#return _rt
# 简单的写法
# User(**dict) = 将每个字典中的value传入
return [User(**dict(zip(_columns, _line))) for _line in _rt_list]
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 change_password(cls, username, upassword):
_sql = 'update user set password=md5(%s) where username =%s'
_args = (upassword, username)
MySQLConnection.execute_sql(_sql, _args, False)
def delete(cls, username):
_sql = 'delete from user where username=%s'
MySQLConnection.execute_sql(_sql, (username, ), False)
def update(cls, user):
username = user.get('username', '')
age = user.get('age', '')
_sql = 'update user set age=%s where username=%s'
MySQLConnection.execute_sql(_sql, (age, username), 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)