def audit_logs(my_filename, my_path, username):
sql = 'insert into audit_logs(filename,path,username) values(%s,%s,%s)'
args = (my_filename, my_path, username)
count, rt_list = execute_sql(sql, args=args, fetch=False)
return count != 0
def validate_login(username, password):
sql = 'select * from user where username=%s and password=md5(%s)'
_count, _rt_list = dbutils.execute_sql(sql, (username, password),
fetch=True)
return _count != 0
def change_user_passwd(userid, user_passwd):
sql = 'update user set password=md5(%s) where id=%s'
args = (user_passwd, userid)
count, rt_list = execute_sql(sql, args=args, fetch=False)
return count != 0
def get_list():
_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 = dbutils.execute_sql(_sql, fetch=True)
return [dict(zip(_columns, _list)) for _list in _rt_list]
def update_users(username, password, age, uid):
sql = 'update user set username = %s ,password=md5(%s),age=%s where id=%s'
args = (username, password, age, uid)
_count, _rt_list = dbutils.execute_sql(sql, args)
return _count != 0
def user_update(uid, age, job):
sql = 'update user set job=%s,age=%s where id=%s;'
args = (job, age, uid)
count, rt_list = execute_sql(sql, args=args, fetch=False)
return count != 0
def get_by_id(uid):
sql = 'select * from idcs where id=%s and status=0'
_count, _rt_list = dbutils.execute_sql(sql, uid, fetch=True)
return _rt_list
def GetTopN(log_file, topN=10):
# 第一步：打开文件，统计IP,URL,status获取访问次数
rt_dict = {}
log_files = open(log_file, 'r')
while True:
line = log_files.readline()
if not line:
break
(ip, url, status) = line.split()[0], line.split()[6], line.split()[8]
rt_dict[(ip, url, status)] = rt_dict.get((ip, url, status), 0) + 1
log_files.close()
# 字典数据转换成list，方便排序
rt_list = rt_dict.items()
result = sorted(rt_list, key=lambda x: x[1], reverse=True)[:topN]
return result
if __name__ == '__main__':
log_file = 'access.txt'
my_list = GetTopN(log_file, topN=3000)
for i in my_list:
IP = i[0][0]
URL = i[0][1]
my_status = i[0][2]
my_count = i[1]
# print IP,URL,my_status,my_count
sql = 'insert into accesslog(ip,URL,status,count) values(%s,%s,%s,%s)'
args = (IP, URL, my_status, my_count)
execute_sql(sql, args=args, fetch=False)
def user_delete(uid):
sql = 'delete from user where id=%s'
args = (uid,)
count, rt_list = execute_sql(sql,args=args,fetch=False)
return count != 0
def update_password(_id, _password):
_sql = 'update user set password=md5(%s) where id=%s;'
_args = (_password, int(_id))
execute_sql(_sql, _args, fetch=False)
def validate_login(username, password):
_sql = 'select * from user where username=%s and password=md5(%s)'
_cnt, _rt_list = execute_sql(_sql, (username, password), fetch=True)
if _cnt:
return True
def delete_user(id):
_sql = 'delete from user where id=%s;'
_args = (id, )
execute_sql(_sql, _args, fetch=False)
def add_user(username, password, age):
_sql = 'insert into user(username, password, age) values(%s, md5(%s), %s)'
_args = (username, password, age)
execute_sql(_sql, _args, fetch=False)
def show_audit_logs(limit_number=10):
sql = 'select * from audit_logs limit %s'
args = (limit_number, )
count, rt_list = execute_sql(sql, args=args, fetch=True)
return rt_list
def idcs_list():
sql = 'select id,name from idcs where status=0'
count, rt_list = execute_sql(sql)
return rt_list
def user_delete(uid):
sql = 'delete from user where id=%s' % uid
count, rt_list = execute_sql(sql, fetch=False)
return count != 0
def drop(asset_id):
sql = 'update assets set status=1 where id=%s'
args = (asset_id, )
fetch = False
count, rt_list = execute_sql(sql, args, fetch)
return count != 0
def validate_user(username):
sql = 'select * from user where username=%s'
args = username
count, rt_list = execute_sql(sql, args)
return count == 0
def delete(uid):
sql = 'update assets set status=1 where id=%s'
_count, _rt_list = dbutils.execute_sql(sql, uid)
return _count != 0
def user_del(username):
sql = 'delete from user where id = (select id from (select id from user where username = %s) temp)'
args = username
fetch = False
count, rt_list = execute_sql(sql, args, fetch)
return count != 0
def get_idc():
_sql = 'select id,name from idcs where status=0'
_count, _rt_list = dbutils.execute_sql(_sql, fetch=True)
return _rt_list
def user_edit(username, password, age):
sql = 'update user set password=md5(%s), age=%s where id = (select id from (select id from user where username = %s) temp)'
args = (password, age, username)
fetch = False
count, rt_list = execute_sql(sql, args, fetch)
return count != 0
def add_users(username, password, age):
sql = 'insert into user(username, password, age) values(%s,md5(%s),%s)'
args = (username, password, age)
_count, _rt_list = dbutils.execute_sql(sql, args)
return _count != 0
def get_topn(topn=10):
_sql = 'select url,ip,code,count(1) from logs group by url,ip,code order by count(1) desc LIMIT %s'
args = (topn, )
# print _sql,args
_rt_list, _cnt = execute_sql(_sql, args, fetch=True)
return _rt_list
def del_users(uid):
sql = 'delete from user where id=%s'
_count, _rt_list = dbutils.execute_sql(sql, uid)
return _count != 0
def update(_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))
execute_sql(_sql, _args, fetch=False)
def vilidate_login(username, password):
sql = 'select * from user where username=%s and password=md5(%s)'
args = (username, password)
count, rt_list = execute_sql(sql, args, fetch=True)
return count != 0
def delete(id):
_sql = 'update assets set status=1 where id=%s;'
_args = (id, )
execute_sql(_sql, _args, fetch=False)
def add_user(username, passwd, age, job):
sql = 'insert into user(username,password,job,age) values(%s,md5(%s),%s,%s)'
args = (username, passwd, job, age)
count, rt_list = execute_sql(sql, args=args, fetch=False)
return count != 0
#encoding: utf-8
if __name__ == '__main__':
import gconf
import dbutils
with open('mysql.sql', 'rb') as h:
sql = h.read()
dbutils.execute_sql(sql, (), False)
rt = dbtuils.execute_sql('select count(*) from user where name=%s', ('kk',), True)
if rt and rt[0] == 0:
dbutils.execute_sql('insert into user(`name`, `password`, `age`) values(%s, md5(%s), %s)', ('kk', '123456', 29), False)