def get_file(self, src_path, dest_path):
'''
:param src_path: 远端目录
:param dest_path: 本地目录
:return:
'''
if src_path[-1] == '/':
src_path = src_path[:-1]
if dest_path[-1] == '/':
dest_path = dest_path[:-1]
src_path = src_path.replace('\\', '/')
dest_path = dest_path.replace('\\', '/')
self.__makedirs_if_not_exist(dest_path)
try:
transport = paramiko.Transport((self.server_ip_address, self.server_port))
transport.connect(username=self.server_username, password=self.server_password)
sftp = paramiko.SFTPClient.from_transport(transport)
all_files = self.__get_all_files_in_remote_dir(sftp, src_path)
for full_file_path in all_files:
# filename = os.path.split(full_file_path)[1]
# filepath = os.path.split(full_file_path)[0]
prefix_dir_path = os.path.dirname(src_path)
relative_path = self.__trunc_prefix_path(prefix_dir_path, full_file_path)
dest_full_path = dest_path + '/' + relative_path
dest_dir_path = os.path.dirname(dest_full_path)
self.__makedirs_if_not_exist(dest_dir_path)
# dest_full_path = dest_full_path.replace('\\', '/')
logger.info('[{}] get: {} to [Local]: {}'.format(self.server_ip_address, src_path, dest_full_path))
# print(
# '[{}] get: {} to [Local]: {}'.format(self.server_ip_address,src_path,dest_full_path)
# )
sftp.get(full_file_path, dest_full_path)
except Exception as error:
logger.error("Get File ERR: [{}] [{}]".format(self.server_ip_address, error))
def open_xlsx(self, xlsx_name):
try:
xh = xlrd.open_workbook(xlsx_name)
return xh
except Exception as err:
logger.error('open file: [{}] error, error msg: [{}]'.format(
xlsx_name, err))
return False
def ddl_db(self, sql):
try:
cursor = self.db.cursor()
cursor.execute(sql)
cursor.close()
except cx_Oracle.DatabaseError as exce:
error, = exce.args
logger.error("sql statment: [{}], Oracle-Error-Code:{}".format(sql, error.code))
logger.error("Oracle-Error-Message:{}", error.message)
cursor.close()
def get_single_db_info(self, db_map):
try:
host = db_map['db_url'].split(':')[0]
listen_port = db_map['db_url'].split(':')[1]
service_name = db_map['db_url'].split(':')[2]
db_username = db_map['db_username']
db_password = db_map['db_password']
return host, listen_port, service_name, db_username, db_password
except Exception as err:
logger.error('error: {}'.format(err))
def __init__(self):
cfg_path = os.path.join(os.path.dirname(os.path.dirname(__file__)),
'config', 'ora_setting_awr.yml')
cfg_data = ConfigFactory(cfg_path).get_settings()
if cfg_data['database_type'] != 'ORACLE':
logger.error('database type: [{}] not support'.format(
cfg_data['database_type']))
exit()
self.db_config = cfg_data['database_setting']
self.awr_sql = cfg_data['database_awr_sql']
self.awr_interval_time = cfg_data['awr_interval_time']
logger.debug('self.db_config={}'.format(self.db_config))
def main_process(self, collect_date):
rescode = self.isVaildDate(collect_date)
if not rescode:
logger.error('invaild date: {}'.format(collect_date))
exit()
for onekey in self.db_config:
onerow = self.db_config[onekey]
logger.info(onerow)
(host, listen_port, service_name, db_username,
db_password) = self.get_single_db_info(onerow)
ora = OracleExecFactory(host, listen_port, service_name,
db_username, db_password)
col_date = self.format_date(collect_date)
start_date = col_date
end_date = col_date
flag = True
while start_date.strftime("%Y-%m-%d") == end_date.strftime(
"%Y-%m-%d") and flag:
cur_date = datetime.datetime.now() + datetime.timedelta(
hours=-1)
start_date = end_date
end_date = end_date + datetime.timedelta(
hours=self.awr_interval_time)
start_str = start_date.strftime("%Y%m%d%H")
end_str = end_date.strftime("%Y%m%d%H")
if end_date < cur_date:
(db_id, inst_num, bid,
eid) = self.get_awr_element(ora, start_str, end_str)
filename = 'awrrpt_{}_{}_{}_{}-{}.html'.format(
host, service_name, start_date.strftime("%Y%m%d"),
start_str, end_str)
logger.info(
'db_id: {}, inst_num: {}, bid: {}, eid: {}'.format(
db_id, inst_num, bid, eid))
self.make_single_awr(ora, db_id, inst_num, bid, eid,
filename)
else:
end_str = cur_date.strftime("%Y%m%d%H")
flag = False
if start_str != end_str:
(db_id, inst_num, bid,
eid) = self.get_awr_element(ora, start_str, end_str)
filename = 'awrrpt_{}_{}_{}_{}-{}.html'.format(
host, service_name, start_date.strftime("%Y%m%d"),
start_str, end_str)
logger.info(
'db_id: {}, inst_num: {}, bid: {}, eid: {}'.format(
db_id, inst_num, bid, eid))
self.make_single_awr(ora, db_id, inst_num, bid, eid,
filename)
else:
break
def __makedirs_if_not_exist(self, full_path, sftp_instacne=None):
if sftp_instacne is None:
try:
if not os.path.exists(full_path):
os.makedirs(full_path)
except Exception as error:
logger.error("create directory [{}] error! {}".format(full_path, error))
# print("create directory [{}] error! {}".format(full_path, error))
else:
try:
file_stat = sftp_instacne.lstat(full_path)
except Exception as err:
sftp_instacne.makedirs(full_path)
def make_ora_connect(self, host, port, sid, username, password, mode=None):
try:
ora_dsn = cx_Oracle.makedsn(host, port, sid)
if mode is not None:
ora_db = cx_Oracle.connect(username, password, dsn=ora_dsn, mode=mode)
else:
ora_db = cx_Oracle.connect(username, password, dsn=ora_dsn)
return ora_db
except cx_Oracle.DatabaseError as exce:
error, = exce.args
logger.error(
"username: [{}], password: [{}], dsn: [{}], Oracle-Error-Code:{}".format(username, password, ora_dsn,
error.code))
logger.error("Oracle-Error-Message:{}", error.message)
def select_db(self, sql, numRows=None):
try:
cursor = self.db.cursor()
cursor.execute(sql)
if numRows is not None:
res_msg = cursor.fetchmany(numRows=numRows)
else:
res_msg = cursor.fetchall()
cursor.close()
return res_msg
except cx_Oracle.DatabaseError as exce:
error, = exce.args
logger.error("sql statment: [{}], Oracle-Error-Code:{}".format(sql, error.code))
logger.error("Oracle-Error-Message:{}", error.message)
cursor.close()
exit()
def connect_server(self):
while True:
try:
self.ssh = paramiko.SSHClient()
self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
self.ssh.connect(hostname=self.server_ip_address, port=self.server_port, username=self.server_username,
password=self.server_password)
logger.info('Connect server :{} Success'.format(self.server_ip_address))
# print('Connect server :{} Success'.format(self.server_ip_address))
return
except Exception as err:
if self.try_times != 0:
logger.warn('Connect failed, Begin to retry [{}]...'.format(4 - self.try_times))
# print('Connect failed, Begin to retry ...')
self.try_times -= 1
else:
logger.error(
'Retry {0} times, Catch an error {1}, Skip connect to {2} !'.format(self.try_times, err,
self.server_ip_address))
# print('Retry {0} times, Catch an error {1}, Skip connect to {2} !'.format(self.try_times, err,
# self.server_ip_address))
break
def dml_db(self, sql):
try:
cursor = self.db.cursor()
if isinstance(sql, str):
cursor.execute(sql)
elif isinstance(sql, list):
rownum = 1
for onesql in sql:
onesql = onesql.strip()
cursor.execute(onesql)
rownum += 1
if rownum >= 500:
self.db.commit()
cursor.close()
self.db.commit()
except cx_Oracle.DatabaseError as exce:
error, = exce.args
logger.error("sql statment: [{}], Oracle-Error-Code:".format(sql, error.code))
logger.error("Oracle-Error-Message:{}", error.message)
self.db.rollback()
cursor.close()
def exec_commands(self, cmd_str):
'''
:param exec_cmd: 需要执行的命令(单个命令)
:return:
'''
if not isinstance(cmd_str, str):
logger.error("Only suppoort commands in one string")
# print("Only suppoort commands in one string")
exit()
# ssh = paramiko.SSHClient()
# ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
# ssh.connect(hostname=self.server_ip_address, port=self.server_port, username=self.server_username,
# password=self.server_password)
exec_cmd = 'source .bash_profile || source .profile;' + cmd_str
logger.info('run cmd: {}'.format(cmd_str))
stdin, stdout, stderr = self.ssh.exec_command(exec_cmd)
res, err = stdout.read(), stderr.read()
result = res if res else err
logger.debug("[%s]".center(50, "-") % self.server_ip_address)
# print("[%s]".center(50, "-") % self.server_ip_address)
logger.info('[{}] status: {}'.format(self.server_ip_address, 'cmd executed'))
# print('run cmd: {}'.format(cmd_str))
logger.debug(result.decode())
def search(self):
all_list = self.list_all_files(self.src_path)
for one_file in all_list:
fh = open(one_file, mode='r+t', encoding='utf-8')
try:
text_lines = fh.readlines()
row = 1
for one_line in text_lines:
for match_key in self.match_keys:
if re.search(match_key, one_line):
logger.info(
'file: [{}], lines: [{}] match the key: {} '.
format(one_file, row, match_key))
logger.debug(
'match row content: [{}]'.format(one_line))
# print('file: [{}], lines: [{}] match the key: {} '.format(match_key, one_file, row))
row += 1
except Exception as err:
logger.error('skip file: {}, \n error: {}'.format(
one_file, err))
# print('[Warning]skip file: {}, error: {}'.format(err, one_file))
finally:
fh.close()
def __init__(self, cfg_path):
self.cfg_path = cfg_path
if not os.path.exists(self.cfg_path):
logger.error('file: [{}] not found'.format(self.cfg_path))
exit()