def ana_high(self):
url, port, username, password, dbname = ("10.200.6.239", 3306,
'python', '(Uploo00king)',
'dba')
mysql = mysqlhelper(url, port, username, password, dbname)
dba = self.dba_user
# 1. 指定时间范围内的事件数统计
start_time = raw_input('统计的起始时间【2017-01-01】: ')
end_time = raw_input('统计的结束时间【2017-09-01】: ')
re_date = r"^\d{4}-\d{2}-\d{2}$"
while True:
if re.match(re_date, start_time) and re.match(re_date, end_time):
break
else:
print "Error:时间格式输入错误，请重新输入"
start_time = raw_input('统计的起始时间【2017-01-01】: ')
end_time = raw_input('统计的结束时间【2017-09-01】: ')
sql = """select count(*) from technical_archiving where dba = '{0}' and
event_start_time BETWEEN '{1}' and '{2}' ;""".format(
dba, start_time, end_time)
count_case = mysql.queryRow(sql)[0]
print("1. 从 {0} 至 {1} 处理的事件总数 : {2}".format(start_time, end_time,
count_case))
def insert_mysql(self):
"""
连接数据库，插入某个dba的事件记录，以覆盖的形式，即每次同步都是全量
:return:
"""
url, port, username, password, dbname = ("10.200.6.239", 3306,
'python', '(Uploo00king)',
'dba')
mysql = mysqlhelper(url, port, username, password, dbname)
dba = self.dba_user
sql0 = """show tables;"""
table_tuple = mysql.queryRow(sql0)
# 如果表不存在则新建
if table_tuple is None or 'technical_archiving' not in table_tuple:
sql1 = """create table technical_archiving(
id int primary key auto_increment,
company varchar(50) not null comment '客户名称',
company_type varchar(50) not null comment '客户类型',
event_start_time datetime not null comment '事件发起时间',
case_database_type varchar(50) not null comment '数据库类型',
case_database_carrier varchar(50) not null comment '数据库载体',
case_event_type varchar(50) not null comment '事件类别',
case_event_info varchar(50) not null comment '事件简述',
dba varchar(50) not null comment 'DBA',
update_time timestamp not null default CURRENT_TIMESTAMP comment '更新时间')
charset utf8
comment '事件归档表';"""
mysql.query(sql1)
# 覆盖该用户的所有事件记录
## 先删除
sql2 = """
delete from technical_archiving where dba = '{0}';
""".format(dba)
mysql.query(sql2)
mysql.commit()
## 后添加
self.count_customer()
self.count_case()
for client_case in self.client_case_list:
#print client_case
client_case_each_list = client_case.split('_')
company = client_case_each_list[1]
company_type = client_case_each_list[0]
event_start_time = client_case_each_list[2]
case_database_type = client_case_each_list[3]
case_database_carrier = client_case_each_list[4]
case_event_type = client_case_each_list[5]
case_event_info = client_case_each_list[6]
sql3 = """
insert into technical_archiving values (null,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',now());
""".format(company, company_type, event_start_time,
case_database_type, case_database_carrier,
case_event_type, case_event_info, dba)
mysql.query(sql3)
mysql.commit()
def __init__(self, url, port, username, password, dbname, sql):
"""
:param url:
:param port:
:param username:
:param password:
:param dbname:
:param sql:
连接数据库并执行查表，返回tuple
"""
self.mysql = mysqlhelper(url, port, username, password, dbname)
self.table_tuple = self.mysql.queryAll_tuple(sql)